I need a non clustered index on a column in one of my databases, what is the T-SQL to create that?
Create NonClustered Index [indexname] on tablename([columnname] ASC|DESC)
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]
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
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
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!
No one has followed this question yet.