Create clustered or non-clustered index on large table ( SQL Server 7 )

Question

Post reply

Create clustered or non-clustered index on large table ( SQL Server 7 )

2ajaypatel

SSC Veteran

Points: 294
More actions
January 3, 2008 at 6:01 pm

#181732

I have large table with 10million records. I would like to create clustered or non-clustered index.
What is the quick way to create? I have tried once and it took more than 10 min.
please help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Did you use Clustered or Non-Clustered?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

2ajaypatel SSC Veteran Points: 294 More actions · Answer 2

In any case, I need to add clustered index as well as non-clustered index in sample large table.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Considering that a clustered index will dictate the physical order in which data is stored in the table, creating a clustered index will likely take a while with 10 Million rows. I'm not sure how much data you have - but on a busy server 10 minutes might not be entirely out of the question.

If you need to build both a clustered and a non-clustered index, build the clustered index first, since the NCI stores the keys from the clustered index to know how to get to the records. Building the non-clustered index first will only cause it to be rebuilt once you create the clustered index.

It might not be fast, since it sounds like it will rearrange all of that data, but that usually should be your order of attack.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

2ajaypatel SSC Veteran Points: 294 More actions · Answer 4

Next question:

Should I use script like below (Query Analyzer ) or use SQL Server 7 Enterprise Manager and do table design option? Which one is faster?

CREATE CLUSTERED INDEX TblPartBit_CustomerID_IDX

ON TblPartBit (tableID)

GO

thanks

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Both will take the same time because EM7 will run the same code as what you've typed behind the scenes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Use query analyser. While they run the same code, if the operation takes too long, enterprise manager may timeout and undo the operation. Query analyser doesn't timeout.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass