Alter table
in [Programming SQL]
|
From: Ebbe on 7 Mar 2010 12:27 I tried the following statement: “ALTER TABLE STOCKTRANS ALTER COLUMN DATASET varchar(3) NOT NULL” I got this message in return: “--> Msg 5074, Level 16, State 1, Line 1 The index 'I_013ITEMNUMBERIDX' is dependent on column 'DATASET'. .. .. .. Msg 5074, Level 16, State 1, Line 1 The index 'I_013SEQNO' is dependent on column 'DATASET'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN DATASET failed because one or more objects access this column. <--“ As far as I understand the message above, it is not possible to use this command, when the column is a part of one or more indexes. How can I check and uncheck “Allow Nulls” in SQL Server Management Studio (SSMS) without having the same problems? I have been told that all what I can do in SSMS, can be done by commands/statements too.
From: Erland Sommarskog on 7 Mar 2010 14:16 Ebbe (Ebbe(a)discussions.microsoft.com) writes: > I tried the following statement: > �ALTER TABLE STOCKTRANS ALTER COLUMN DATASET varchar(3) NOT NULL� > > I got this message in return: > �--> > Msg 5074, Level 16, State 1, Line 1 > The index 'I_013ITEMNUMBERIDX' is dependent on column 'DATASET'. > . > . > . > Msg 5074, Level 16, State 1, Line 1 > The index 'I_013SEQNO' is dependent on column 'DATASET'. > Msg 4922, Level 16, State 9, Line 1 > ALTER TABLE ALTER COLUMN DATASET failed because one or more objects access > this column. ><--� > As far as I understand the message above, it is not possible to use this > command, when the column is a part of one or more indexes. Correct, you need to drop the indexes first in this case. Hade you only made the column longer, but not changed the nullability, you would have gotten away with it. > How can I check and uncheck �Allow Nulls� in SQL Server Management Studio > (SSMS) without having the same problems? Because behind your back, SSMS creates a new copy of the table, and copies data over, and then moves all dependent objects. Which is a far more complex and dangerous operation if not done right - and SSMS does not do it right. There are several very serious flaws with this funcationality in SSMS. As on example, say that you would use the Table Designer to change the data type from varchar(3) to varchar(5), which can be done with ALTER TABLE; SSMS would still drop the table and all that for you. In your case, I would recommend that you bring out your index definitions from your source-code repository, or script them if you don't have them. Then drop the indexes, alter the column, and recreate the indexes. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Ebbe on 7 Mar 2010 16:18 Hi Erland Thank You for the quick answer. I had hoped for an easier way to solve my problem. Are there other objects than indexes, that I have to have in mind, when I wish to change the NULL option on a column?
From: Erland Sommarskog on 7 Mar 2010 17:48 Ebbe (Ebbe(a)discussions.microsoft.com) writes: > Thank You for the quick answer. > I had hoped for an easier way to solve my problem. > > Are there other objects than indexes, that I have to have in mind, when I > wish to change the NULL option on a column? In the topic of ALTER TABLE in Books Online, you find a whole slew of exceptions in the section for ALTER COLUMN. I cannot vouch for that it is 100% correct, but if there are deviations, you will find as you try. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Ebbe on 8 Mar 2010 01:47 Hi Erland Tanks a lot. You have been a great help :-)
|
Pages: 1 Prev: Need help with a recursive query - DDL Part 3 Next: Xml shredding performance |