Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dukagjin Maloku - Dugi'DBA

Everything that I can post for the SQL Server ...

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:

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!

Comments

Posted by Jason Brimhall on 29 March 2010

Thanks Dugi for a nice article.

Posted by Dukagjin Maloku on 30 March 2010

You're welcome! It's a short info about Indexes.

Leave a Comment

Please register or log in to leave a comment.