question

thatismatt avatar image
thatismatt asked

How do you create a non clustered index?

I need a non clustered index on a column in one of my databases, what is the T-SQL to create that?

t-sqlsql-serverdatabaseindexing
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jack Corbett avatar image
Jack Corbett answered
Create NonClustered Index [indexname] on tablename([columnname] ASC|DESC)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Just clarifying a bit...

For a non-clustered index

CREATE NONCLUSTERED INDEX [indexname] 
ON table_or_view_name ([columnname] ASC|DESC) 
ON [filegroup_or_partition_name]

For a unique non-clustered index

CREATE UNIQUE NONCLUSTERED INDEX [indexname] 
ON table_or_view_name ([columnname] ASC|DESC) 
ON [filegroup_or_partition_name]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered

aw, heck! what about an example?

  CREATE NONCLUSTERED INDEX [MyTableIndex] ON [dbo].[MyTable] 
    (
        [Table_id] ASC,
        [Another_ID] ASC,
        [YetAnother_ID] ASC,
        [MyTableName] DESC,
        [StillAnother_id] ASC
    )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

but in the heat of the moment I might write

CREATE INDEX MyTableIndex ON MyTable (Table_id, Another_ID, YetAnother_ID, MyTableName DESC, StillAnother_id )
--and use the defaults
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor avatar image
Steve Jones - Editor answered

Phil has a good answer, and keep in mind that you can use multiple columns in the index. Choose the columns that are often used in the SELECT clause or the WHERE clause.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Dont forget, if you are using SSMS, that there are examples of lots of scripts like this in the templates. Hit Ctrl+Alt+T to see the template toolbar and then open the Index folder to see six different examples of index creation scripts. The Create Unique Nonclustered Index script would be closest to the one you want. Drag that to a new script window and edit it as the other answers have suggested, using an index name of your choice and the columns you need to have indexed. Then you simply need to make sure you are in the right database and run the script!

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RaviShankarKota avatar image
RaviShankarKota answered
Thanks for a nice article.Thanks for Fatherjack for worthy comment.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.