| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2004-02-18 : 19:07:25
|
| Would an index on a uniqueidentifier field be faster than a table scan on that field? Or does SQL not have any searching algorithms for such a field?My table will have several thousand entries, all scanned once everytime a website is hit. Afterwards, the clustered index is used on the primary key. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-19 : 00:05:15
|
| Yes, an index on the column would allow faster searching of that column than a table scan. SQL Server can index any data type except text, ntext, and image (and bit if you're using SQL 7.0 or earlier).However, a clustered index on a uniqueidentifier column is almost always less than ideal. The reason is that a GUID is unique and would never be part of a value range search (WHERE LastName BETWEEN 'A' AND 'B', for example) Other columns that would be queried, like names, dates, or non-unique numeric values, would be better candidates for clustering. |
 |
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-25 : 13:40:01
|
Great thanks for the reply, I added a unique constraint via EM for my GUID fields.Quick question: Since the GUID fields are unique, the index added should be unique if possible because this provides the query optimizer with more information correct? BOL under the section "Using Unique Indexes" describes unique indexes and constraints like so:quote:
Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table. There are no significant differences between creating a UNIQUE constraint and creating a unique index manually. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or created manually. A unique index and a UNIQUE constraint cannot be created if there duplicate key values exist.
Do you know whyEM provides the ability to create a unique constraint or a unique index under the Indexes/Keys tab? This just seems weird to me. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-25 : 13:41:44
|
| It provides the ability because you can create either. With a unique constraint, a unique index is automatically created. With a unique index, you only get the index.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-25 : 15:18:26
|
| Gotcha |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-25 : 17:25:52
|
| tribune,Don't rely on the statement that a GUID is unique. Can you prove it or are you relying on a MS paper? When it comes to uniqueness in a DB, enforce it yourself...DavidM"SQL-3 is an abomination.." |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 21:24:34
|
| No offence byrmol but GUID's are widely accepted as unique on a global scale and I think it's pretty safe to say that they would be unique within an application. Thus, adding a unique constraint is totally useless...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-25 : 21:40:42
|
| None taken.....Ohh.. I just found that it is based on the algorithm from OSF DCE.. ( Open Software Foundation (OSF) for their Distributed Computing Environment (DCE).) That's a pretty respected organisation, so yeah I suppose you could trust it..."A Useless Unique Constraint".. That's heresy!! ;-)DavidM"SQL-3 is an abomination.." |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 21:59:26
|
| Found this in BOL:"The Transact-SQL NEWID function and the application API functions and methods generate new uniqueidentifier values from the identification number of their network card plus a unique number from the CPU clock. Each network card has a unique identification number. The uniqueidentifier returned by NEWID is generated using the network card on the server. The uniqueidentifier returned by application API functions and methods is generated using the network card on the client."Sounds prety bullet-proof to me...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-25 : 22:09:05
|
| GUID's still fascinate me .... is it really possible that everywhere they've ever been used, the same sequence has NEVER been generated and never will be ????it boggles the mind !- Jeff |
|
|
|
|
indexing on a uniqueidentifier field?