T-SQL

Question

Post reply

T-SQL

raj_143bhoomika

Newbie

Points: 2
More actions
May 16, 2009 at 5:56 pm

#137836

Hi,
i have a small doubt . When we create a non clustered index on a column sql server does not allow it? Whats the reason?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Perhaps if you provided the complete error message you are receiving we could help you figure out what is wrong.

raj_143bhoomika Newbie Points: 2 More actions · Answer 2

hi , i just need what may be the reasons . If we might across a condition

When creating a non clustered index on a column sql server does not allow it? What would be the reason?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

raj_143bhoomika (5/16/2009)
hi , i just need what may be the reasons . If we might across a condition
When creating a non clustered index on a column sql server does not allow it? What would be the reason?

If you could not create a nonclustered index, there had to be a reason, an error message. If you want to know why, tell us what the error message was that you received. I'm not a mind reader so I am not going to start listing all of the different reasons you might not be able to create a nonclustered index.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

raj_143bhoomika (5/16/2009)
hi , i just need what may be the reasons . If we might across a condition
When creating a non clustered index on a column sql server does not allow it? What would be the reason?

Heh... Homework, test, or interview question? Make Books Online your friend... I got the following from "Create Index"... it contains many of the reasons you seek.

index_name Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

Primary XML index names cannot start with the following characters: #, ##, @, or @@.

column

Is the column or columns on which the index is based. Specify two or more column names to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

You can create indexes on CLR user-defined type columns if the type supports binary ordering. You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. For more information about indexing CLR user-defined type columns, see CLR User-defined Types.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.arnott SSChampion Points: 11882 More actions · Answer 5

raj_143bhoomika (5/16/2009)
Hi,
i have a small doubt . When we create a non clustered index on a column sql server does not allow it? Whats the reason?

How "small" is your "doubt" that you post this generalized background info question on multiple sites?

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a4df3bda-331f-4cc3-ba62-3ef8e0ff2e17

So, could you consider coming clean with us. Is this a homework question from a class? Or an interview question? or what? How about some honest background on why you're asking this.