Category - SQL Server 2014

All About SQL Server 2014

Altering an Indexed Text Column

In this post, I am going to demonstrate how increasing the size of a text column with an Hastindex on it can lead to serious troubles without warning. I tested it two instances of SQL Server: SQL Server 2016 and 2017, but the same behavior should be expected with older versions; only the maximal allowed column size would be different (900 instead of 1.700 bytes).

 

Use the following code to create a sample table and populate it with 1M rows:

DROP TABLE IF EXISTS dbo.AlterColumnTest;
CREATE TABLE dbo.AlterColumnTest(
id INT NOT NULL,
descr NVARCHAR(500) NOT NULL DEFAULT 'test',
CONSTRAINT PK_AlterColumnTest PRIMARY KEY CLUSTERED (id ASC)
);
GO
INSERT INTO dbo.AlterColumnTest(id)
SELECT n
FROM dbo.GetNums(1000000)
GO
CREATE INDEX ix1 ON dbo.AlterColumnTest(descr)
GO

As you can see, there is a non-clustered index on the descr column. From SQL Server 2016 the maximum size for index keys with nonclustered indexes has been increased to 1.700 bytes. That means that the size of the column cannot exceed value of 850. However, when you try to increased it to a value grater than 850, this will be accepted. The following command will be successfully executed:

ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(1000) NOT NULL;
GO 
Commands completed successfully.

 

The command will be executed instantly since it updates only meta-data; existing rows are not touched at all. And there is no warning about the potential exceeding of allowed index key size!

When you try to update a row so that it contains a 850 character long text value, the update will work:

UPDATE dbo.AlterColumnTest SET descr = REPLICATE(N'a', 850) WHERE id = 1;

(1 row affected)

(1 row affected)

However, if text is longer than this, you will get an error:

UPDATE dbo.AlterColumnTest SET descr = REPLICATE(N'a', 851) WHERE id = 1;

Msg 1946, Level 16, State 3, Line 34
Operation failed. The index entry of length 1702 bytes for the index 'ix1' exceeds the maximum length of 1700 bytes for nonclustered indexes.

OK, you have got an exception, only after we tried to store data longer than the limit and now you have to alter the text column to the allowed size (850):

ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(850) NOT NULL;

Instead of decreasing the column size and an intently operation, you will get the following error:

Msg 5074, Level 16, State 1, Line 42
The index 'ix1' is dependent on column 'descr'.
Msg 4922, Level 16, State 9, Line 42
ALTER TABLE ALTER COLUMN descr failed because one or more objects access this column.

To proceed with this action, you need to drop the underlined index, alter the column size and rebuild the index!

DROP INDEX IX1 ON dbo.AlterColumnTest;
GO
ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(850) NOT NULL WITH (ONLINE = ON);
GO
CREATE INDEX IX1 ON dbo.AlterColumnTest(descr) WITH (ONLINE = ON);
GO

For large tables, this action can take some time and during it, you will have no index on that column. The ALTER TABLE statement this time is not a meta-data operation; since the column size is decreased, the action affect all rows of the table, although there is no single rows with a column longer than the new length! For the ALTER statement, you can choose between the offline and online operation. In the first case, it will be faster, but the table will be locked and unavailable for querying and modifications; an online operation will let table be available for these actions, but it will take more time and resources (a new table will be created in shadow and will replace the original one).

This is definitely not something that you have expected after a successful initial command which increased the column size to 1.000 characters. At least a warning should be shown in this case.

Thanks for reading.

M.