CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlalter-table
abhishek khandait
abhishek khandait

Reputation: 2157

How to alter column of a table with indexes?

I would like to change the column size of the a column in a table with some indexes from varchar(200) to varchar(8000). How should I proceed?

Upvotes: 5

Views: 30471

Answers (3)

amar pimple
amar pimple

Reputation: 1

Example in Oracle:

CREATE TABLE EMP (NAME VARCHAR(200));

ALTER TABLE  EMP MODIFY  NAME VARCHAR(800);

Upvotes: -4

Vkl125
Vkl125

Reputation: 138

Try this for MSSQL:

drop index person.idx_pers_fullname
ALTER table person alter COLUMN  pers_firstname nvarchar(8000)
create index idx_pers_fullname on person(pers_firstname)

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294287

Since is VARCHAR and you're increasing the size, then simply ALTER TABLE ... ALTER COLUMN ... should be enough.

The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

Otherwise you would drop the index(es), alter the column, then add back the index(es).

Be aware though that SQL Server maximum index key size is 900 (or 1700 for newer editions), so even though the ALTER will succeed, a future INSERT of data over the 900 length limit will fail with error:

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length ... bytes for the index '...' exceeds the maximum length of 900 bytes.

Upvotes: 17

Related Questions