Prev: Truncate temp table
Next: Attach a SQL 2005 Express Database to SQL Server 2005
From: Derek Hart on 14 Apr 2010 20:32
I have a GUID in a table that I fill programmatically. Often the table will
be searched on the GUID. It is just a GUID generated from dotnet in a
varchar field. The majority of the time the table will be searched on this
GUID... only sometimes will be it be searched on an the primary key ID (just
an identity). What type of index, if any, should I place on the GUID? Unique
Constraint, Unique Index?

The table may get to a couple million records.


From: Dan Guzman on 14 Apr 2010 22:26
I suggest you store the GUID value in a uniqueidentifier column instead of
varchar . This way, you'll store the native 16 byte of binary value instead
of a 36 character string.

Either a non-clustered unique constraint or unique index will ensure the
value is unique and maximize performance for a single-row select.
Personally, I would use a unique constraint since the uniqueness is a
desired characteristic of the data rather than solely for performance.

Be aware that the random nature of GUIDs causes index fragmentation so
consider specifying an index fill factor and rebuild/defrag periodically.


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Derek Hart" <derekmhart(a)yahoo.com> wrote in message
news:upYdyMD3KHA.4332(a)TK2MSFTNGP02.phx.gbl...
> I have a GUID in a table that I fill programmatically. Often the table
> will be searched on the GUID. It is just a GUID generated from dotnet in a
> varchar field. The majority of the time the table will be searched on this
> GUID... only sometimes will be it be searched on an the primary key ID
> (just an identity). What type of index, if any, should I place on the
> GUID? Unique Constraint, Unique Index?
>
> The table may get to a couple million records.
>
From: John Bell on 15 Apr 2010 03:37
On Wed, 14 Apr 2010 17:32:58 -0700, "Derek Hart"
<derekmhart(a)yahoo.com> wrote:

>I have a GUID in a table that I fill programmatically. Often the table will
>be searched on the GUID. It is just a GUID generated from dotnet in a
>varchar field. The majority of the time the table will be searched on this
>GUID... only sometimes will be it be searched on an the primary key ID (just
>an identity). What type of index, if any, should I place on the GUID? Unique
>Constraint, Unique Index?
>
>The table may get to a couple million records.
>

You may want to look at
http://sqlblogcasts.com/blogs/martinbell/archive/tags/GUID/default.aspx
specifically guid ordering in SQL Server. You will probably see
performance drop if you make the index clustered if not ordered
correctly. The only way to be certain about performance is to try the
options and look at the query plans, missing index information etc
that are generated/updated when your queries run. Just because it is a
guid doesn't mean you have to disregard the usual practices when
adding indexes.


John
 | 
Pages: 1
Prev: Truncate temp table
Next: Attach a SQL 2005 Express Database to SQL Server 2005