http://www.sqlservercentral.com/blogs/dugi/2010/03/29/cluster-amp-noncluster-index-where-you-cannot-create-them/

Printed 2014/10/24 10:11AM

Indexes - where you cannot create them!

By Dukagjin Maloku, 2010/03/29

Indexes are the objects that DBA uses them everyday and maintaining them very often, during the maintenance routines. The main purpose that we use the indexes is to retrieve data as fast as it possible.

Types of indexes are:

Cluster  - max 1 per table & more about the structure.
Non Cluster - max 999 per table & more about the structure.
Unique - can contains also one NULL value!

Index with included columns
Indexed Views
XML Indexs
Spatial Indexes
Full-Text
Filtered

There are many types of indexes that you can use them in different scenarios of your SQL Server databases environment. So the article covers Cluster, Non Cluster & Unique indexes that cannot created in a table or more exactly in which data type of the columns is not allowed to create indexes.

Indexes cannot created in LOB (Larg OBject data), in the columns with data types like:  text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, image.

Some test:

CREATE TABLE Tablet
(
    ID INT IDENTITY(1,1) NOT NULL,
    FNAME NVARCHAR(30),
    PICTURE IMAGE,
    EXPERIENCE NTEXT,
    BIO NVARCHAR(MAX)
)

--TRYING TO CREATE INDEXES, the index will created successfully, as you can see the index will created in the column with data type int:
CREATE CLUSTERED INDEX IDX_ID ON Tablet(ID);
GO

--CONTINUING TO CREATE INDEXES, it will fails for the rules where we cannot create them:
CREATE NONCLUSTERED INDEX IDX_PICATURE ON Tablet(PICTURE);
GO

The msg that we will win in this case is:
Msg 1919, Level 16, State 1, Line 2
Column 'PICTURE' in table 'Tablet' is of a type that is invalid for use as a key column in an index.

The reason is what we discuss above!

It is a little info during the work with indexes!

Stay Tuned!


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.