0

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
            ,STATISTICS_NORECOMPUTE = off
            ,SORT_IN_TEMPDB = off
            ,DROP_EXISTING = off
            ,ONLINE = off
            ,ALLOW_ROW_LOCKS = on
            ,ALLOW_PAGE_LOCKS = on
            ) on [PRIMARY] 
            go
Improve this question
6
  • msdn.microsoft.com/en-us/library/ms188783.aspx
    – Marc B
    Aug 18, 2015 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, 2015 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, 2015 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, 2015 at 14:27
  • @Hunter I have edited my question and thanks for the comment
    – Jared
    Aug 18, 2015 at 15:03

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • 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, 2015 at 14:26
  • You mean the above mentioned script...
    – benjamin moskovits
    Aug 18, 2015 at 14:27
  • Yes, the above script.
    – Jared
    Aug 18, 2015 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, 2015 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, 2015 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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