x

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?

more ▼

asked Oct 09, 2009 at 07:42 AM in Default

thatismatt gravatar image

thatismatt ♦♦
210 7 8 9

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first
Create NonClustered Index [indexname] on tablename([columnname] ASC|DESC) 
more ▼

answered Oct 09, 2009 at 11:46 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

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] 
more ▼

answered Oct 09, 2009 at 12:18 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Oct 09, 2009 at 02:23 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered Dec 15, 2009 at 05:39 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 09, 2009 at 02:47 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x348
x244
x131

asked: Oct 09, 2009 at 07:42 AM

Seen: 44605 times

Last Updated: Sep 28, 2012 at 02:54 AM