Index creation on VARCHAR(MAX) column in SQL server 2008 and 2012

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 648
-- run this script in MSSQL 2008
CREATE TABLE tblTestIndex
(
ID INT IDENTITY NOT NULL
, COL1 NVARCHAR(MAX) NOT NULL
, COL2 XML NOT NULL
)
GO

CREATE INDEX IND_tblTestIndex on tblTestOnlineIdx (ID) INCLUDE (COL1) WITH (ONLINE = ON)
GO

/* -- Index creation on large data types throws below error...
Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'tblTestOnlineIdx_IND_1' because the index contains column 'COL1' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.
*/

SQL Server 2012 allows index creation on large data types like varchar(max).
to prove this, run the same script in MSSQL 2012.

Comments or Responses

Login to post response