MS SQL index question what to use for clustered index

i have a table with essentaily random data in it....  there is no data field that  represents what would be sequential adding of data to the table.

it currently does not have a identity field.   (the PK is a guid generated by  newid()  )

i have read that "heap" table index  (no clustered index) produces table scans in the execution plan  (which it does)

would it benifit me to add an identity field to the table and create a clustered index on that field?
CASorterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
It would be vastly better to cluster the table on what you typically search it by, if it's something other than the GUID.
0
CASorterAuthor Commented:
i search by, essentially, a barcode number...   which is fairly random...

so any inserts to the table would be shuffling the physical location of the records in the table each time something is added.  which i really dont think i want to do.

right?

i quess my question really is...    do the innerworkings of sql that use the indexes work better when there exists a clustered query...

if i am not mistaken, when you create a clustered query on a table that did not previously have one, SQL goes and updates all the existing indexes to utilize the clustered index...

so would having a bad clustered query ( on an identity ID field ) be better than having NO clustered query  (and haveing table scans all over)
0
Scott PletcherSenior DBACommented:
Yes, SQL tables work better with a clustered index.
Yes, creating a clustered index forces all existing indexes to be rebuilt.
Yes, an identity clus index is better than no clus index at all.

However, in general, you're vastly better off clustering on what you search/join by, even if it involves some fragmentation.  Yes, many people claim differently, but think about it: you do one INSERT and then hundreds, thousands, 10K, 100K, etc., reads of that row.  Which is more important to have perform the best: the one INSERT, or the gazillion SELECTs afterward?  

Perhaps more importantly, clustering the table correctly often allows you to get rid of multiple nonclustered indexes keyed by what would have been the clustered key, and each of those indexes will be fragmented.  For example, if you see a table clustered by ident and then 4 different nonclustered, covering indexes keyed by invoice_number, it's almost certain that table should be clustered by invoice_number to begin with.  A big % of my table tuning is just getting rid of the dopey identity clustered index and clustering by more applicable column(s).

To reduce fragemention, you can always leave some freespace in the table -- i.e., reduce the fillfactor below 100 -- if you need to [but don't get carried away: 85-90% is as low as you'd ever routinely want to go, without detailed analysis].  Or you could reorg or rebuild the table, unless of course it's too large.

Of course there will always be specific cases where this general rule may not hold, but it's much, much less often than what you're lead to believe.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CASorterAuthor Commented:
thank  you very much for your very detailed response.

i guess ,   like many things in SQL,   the answer comes down to

It depends.


part of my problem is that i cant really batch the inserts, they need to be added through the day.
the table in question is used as a lookup constantly, by another aspect of the system,  during the day..   and the response time HAS to be sub second, every time
if i were to use the look up field as the clustered index,  during the time that inserts are happening (and possibly shuffling the ENTIRE table down a row on the drive (i suppose mathematically it would average to be shuffling half the table on each add)) the response time on the lookup really suffers.  

i suppose i can look to try and batch the inserts and time it better so it is off hours when that happens.
0
Scott PletcherSenior DBACommented:
SQL would never have to affect more than one page at a time for an INSERT.  SQL uses pointers to keep rows in order, it doesn't physically have to shuffle all the rows.  That would be impossible for a table of, say, 2B rows, and we have tables like that here.
0
CASorterAuthor Commented:
thank you very much for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.