{"id":3500,"date":"2021-07-30T17:25:09","date_gmt":"2021-07-31T00:25:09","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3500"},"modified":"2021-07-30T17:25:10","modified_gmt":"2021-07-31T00:25:10","slug":"how-to-alter-column-size-in-sql-server","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3500","title":{"rendered":"How to Alter column size in SQL Server"},"content":{"rendered":"\n

If you\u2019re using\u00a0SQL Server, and you want to use\u00a0T-SQL\u00a0to change the size of an existing column, this article can help.<\/p>\n\n\n\n

By \u201cchanging the size of an existing column\u201d, I mean changing the data size. For example, say you have a varchar(255)<\/code> but you want to increase its size to varchar(500)<\/code>. Here\u2019s what you need to do in that case.<\/p>\n\n\n\n

The ALTER TABLE Statement<\/h2>\n\n\n\n

If you\u2019re doing this with T-SQL, you need to use the ALTER TABLE<\/code> statement. This statement enables you to change a table\u2019s definition after it has already been created (and it may also contain data).<\/p>\n\n\n\n

Here\u2019s an example of changing the size of an existing column:<\/p>\n\n\n\n

ALTER TABLE Tasks\n    ALTER COLUMN TaskDescription varchar(500);\nGO\n<\/pre>\n\n\n\n
This example modifies the Tasks<\/code> table by changing the size of the TaskDescription<\/code> column.<\/p>\n\n\n\n
This obviously assumes that the column (and table) already exist in the database. Otherwise you\u2019ll get an error.<\/p>\n\n\n\n
Check the Results<\/h2>\n\n\n\n
You can check the column size by running a query against INFORMATION_SCHEMA.COLUMNS<\/code>. Like this:<\/p>\n\n\n\n
USE Solutions;\nSELECT \n    COLUMN_NAME,\n    DATA_TYPE,\n    CHARACTER_MAXIMUM_LENGTH,\n    CHARACTER_OCTET_LENGTH\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = 'Tasks';\n<\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
COLUMN_NAME      DATA_TYPE  CHARACTER_MAXIMUM_LENGTH  CHARACTER_OCTET_LENGTH\n---------------  ---------  ------------------------  ----------------------\nTaskId           int        null                      null                  \nTaskName         nvarchar   255                       510                   \nTaskDescription  varchar    500                       500<\/pre>\n\n\n\n
In this case, we switch to the correct database (Solutions<\/code>), then we query\u00a0INFORMATION_SCHEMA.COLUMNS<\/code>\u00a0for information about the columns in the\u00a0Tasks<\/code>\u00a0table. We could have narrowed this down to just the column we\u2019re interested in if there were too many columns in the table, but for this example, three columns are no problem.<\/p>\n\n\n\n
For Microsoft SQL<\/strong><\/p>\n\n\n\n
For most things, I prefer scripting. But for column changes, I like the UI. Don’t have to remember to check for NOT NULL or any calculations or any additional column properties.<\/p>\n\n\n\n
Select table–> Design–> change value in Data Type shown in following Fig.<\/p>\n\n\n\n
\"enter<\/a><\/figure>\n\n\n\n
Save tables design.<\/p>\n\n\n\n
Addtional Tips – try uncheck beow option if you got a trouble: <\/p>\n\n\n\n
Tools -> Options… \/ >Designers -> Table and Database Designers — uncheck “Prevent saving changes that require table re-creation”<\/p>\n","protected":false},"excerpt":{"rendered":"
If you\u2019re using\u00a0SQL Server, and you want to use\u00a0T-SQL\u00a0to change the size of an existing column, this article can help. By \u201cchanging the size of an existing column\u201d, I mean changing the data size. For example, say you have a varchar(255) but you want to increase its size to varchar(500). Here\u2019s what you need to do in that Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,625],"tags":[799,798],"class_list":["post-3500","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql","tag-alter-column-size-in-sql","tag-alter-column-size-in-sql-server"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3500"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3500\/revisions"}],"predecessor-version":[{"id":3501,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3500\/revisions\/3501"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}