Does optimizer know about GUIDs?

Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>

Does optimizer know about GUIDs?

Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua Stern
No.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/art...&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>
|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/art...&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.
|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>

Does optimizer know about GUIDs?

Just wondering if there's anything the optimizer treats differently
when it sees a GUID instead of, say, an identity int, as PK. GUIDs
being random and identities being sequential, one could impute some
differences in efficiency, especially when one or both are clustered.
Thanks.
Joshua SternNo.
And, who says that GUIDs aren't sequential?
http://www.informit.com/articles/ar...2&seqNum=7&rl=1
http://www.sqldev.net/xp/xpguid.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>|||On Fri, 12 Aug 2005 18:29:52 -0400, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:
>No.
>And, who says that GUIDs aren't sequential?
>http://www.informit.com/articles/ar...2&seqNum=7&rl=1
>http://www.sqldev.net/xp/xpguid.htm
Hmm.
J.|||No, I do not think the optimizer tries to *guess* or infer anything about
guids... You provide the PK, FK , Indexes etc for the optimizer to use..
Just because a column is a uniqueidentifier does not mean it has to be
unique.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:p82qf11f3bp12b9lkgt28q8f1gpinj6p8b@.
4ax.com...
> Just wondering if there's anything the optimizer treats differently
> when it sees a GUID instead of, say, an identity int, as PK. GUIDs
> being random and identities being sequential, one could impute some
> differences in efficiency, especially when one or both are clustered.
> Thanks.
> Joshua Stern
>

Does column order really matter?

Does column order really matter for Query Optimizer to pick index.

Case 1:
Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?

Statement 1:
SELECT * FROM CUSTOMER
WHERE FirstName = 'John' and LastName ='Smith'

Statement 2:
SELECT * FROM CUSTOMER
WHERE LastName ='Smith' and FirstName = 'John'

Will both statement 1 and 2 use the composite index or only statement 1?

Case 2:
Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?

I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.

Column order is not important but SQL Server 2005 comes with something that gives you the benefit of a composite yet it is not a composite, it is called index column include so the lastname will be covered in your query. Indexes are part of the physical design so the RDMS vendors owns and improves on it. Try the link below for details. Post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

Case 1:

Doesn't matter.

Case 2:

Doesn't matter.

Case 3:

SELECT FirstName FROM CUSTOMER WHERE LastName='Smith'

Now, it matters. The index is bad for this query. Create a new index on LastName,FirstName and this will run much faster.

The analogy is quite simple. Look at a telephone book. It's arranged by lastname,firstname. If I ask you to find me John Smith in the phone book, it'll take you a few seconds. If I ask you to find Smith, John in the phonebook, it'll take you a few seconds. If I ask you to find me all the first names of people whose last name is Smith (it'll take a little bit, but you can do it). Now what if I ask you to tell me the last name of every John? Uh...