Indexes – where you cannot create them!

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:

Clustermax 1 per table & more about the structure.
Non Clustermax 999 per table & more about the structure.
Uniquecan 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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s