Menu
I'm using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows. What I need to know is if there are any issues I have not considered.
I will be using this TSQL statement:
ALTER TABLE MyTable ALTER COLUMN [MyColumn] VARCHAR(1200) I've already tried it on a copy of the data and this statement had no ill effects that I could see.
So are there any possible problems from doing this that I may not have considered?
By the way, the column is not indexed.
201
Declared varchar column length will not affect the physical (on-disk) or data cache storage. It will affect the performance of actually using that index.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
Even using varchar(max) shouldn't have any impact on storage.
Yes, is matter when you indexing multiple columns. Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters.
The name column is of datatype varchar and size 5. To increase the size of the column, we shall run the following SQL Query. ALTER TABLE students MODIFY name VARCHAR(30); Now, let us see the modified schema if the column size has updated. The column size has been successfully updated to the new value. Previous Next .
To increase the size of the column, we shall run the following SQL Query. ALTER TABLE students MODIFY name VARCHAR(30); Now, let us see the modified schema if the column size has updated. The column size has been successfully updated to the new value. Previous Next .
Another reason why you should avoid converting the column to varchar (max) is because you cannot create an index on a varchar (max) column. In my case alter column was not working so one can use 'Modify' command, like: alter table [table_name] MODIFY column [column_name] varchar (1200);
Let us consider students table with the following schema. The name column is of datatype varchar and size 5. To increase the size of the column, we shall run the following SQL Query. Now, let us see the modified schema if the column size has updated. The column size has been successfully updated to the new value.
This is a metadata change only: it is quick.
An observation: specify NULL or NOT NULL explicitly to avoid "accidents" if one of the SET ANSI_xx settings are different eg run in osql not SSMS for some reason
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
