I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012 Enterprise edition. I know how to create index but its taking forever and would like know how to make it faster? Here is the script i tried.

create clustered index.....on table.name ([Column.name] desc)
    with (
            PAD_INDEX = off
            ,SORT_IN_TEMPDB = off
            ,DROP_EXISTING = off
            ,ONLINE = off
            ,ALLOW_ROW_LOCKS = on
            ,ALLOW_PAGE_LOCKS = on
            ) on [PRIMARY] 
  • msdn.microsoft.com/en-us/library/ms188783.aspx – Marc B Aug 18 '15 at 13:56
  • 2
    Is there more than one way to create an index for a table? CREATE <optional type> INDEX <name> ON <table> (<columns>) – Lasse V. Karlsen Aug 18 '15 at 13:58
  • Generally, the best way to create an index is on columns that is repeatedly used to join or filter data. – FutbolFan Aug 18 '15 at 14:05
  • I flagged as off-topic > too broad. Your question at hand is "how do I make this faster?" without any code that you have written. Right now, it seems to be about an idea or theory. Please post some code, even what you have tried so far to create a database. What is slow to you? What is fast? – Hunter Stevens Aug 18 '15 at 14:27
  • @Hunter I have edited my question and thanks for the comment – Jared Aug 18 '15 at 15:03

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on)

This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.

  • i am going to use these script below - should i pass oFF for Allow_row_lock and page_Lock please? CREATE CLUSTERED INDEX ..... ON Table.Name ( [Column.name] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO – Jared Aug 18 '15 at 14:26
  • You mean the above mentioned script... – benjamin moskovits Aug 18 '15 at 14:27
  • Yes, the above script. – Jared Aug 18 '15 at 14:34
  • 1
    Unless you have a good reason allow these settings to stay on leave it alone. SQL server needs to lock things during updates and deletes and you probably don't want to have table locks every time you update a row. – benjamin moskovits Aug 18 '15 at 14:39
  • @Yared if this works, then please upvote it as well. You can upvote answers to your own questions at your rep level (3) – Hunter Stevens Aug 18 '15 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.