This is an addition to my book, on page 255, to the end of the section on collation (before the statement that I won't delve any deeper into collations, of course :)), I want to add the following bit of information about collations and how they affect sorting and searching. 

You can download the entire addendum with source code from the:
Pro SQL Server 2005 Database Design and Optimization Homepage

Sort and Searching

One of the more confusing parts of collation is how they affect the ordering of data when sorted and how that can affect other operations like the LIKE operator.  When it comes to case insensitive collations, it is pretty clear, data is sorted as if 'A' and 'a', are the same character.  The other common situation is accent insensitivity where 'a' and 'ấ' appear as the same character.  When you are using a case and accent insensitive collation there will be no guarantee when sorting data that either of these characters would come first in the list.  The great part about this is that when you search for where like '%A%', you know that you will get back: aardvark, Abel, Penấ with no worries. 

In some situations, this is not desirable to have this be the case, and you set up a column, table, or database to be case sensitive and accent sensitive.  This is where sometimes you can get confused when using between, like, or other range type queries on characters.

As an example, let me create the following table and seed it with some characters:

create table dbo.TestSorting
(
     value nvarchar(1) collate Latin1_General_CI_AI
)
insert into dbo.TestSorting
select 'A'
union all
select 'a'
union all
select nchar(256) --Ā
union all
select 'b'
union all
select 'B'

Doing a normal case insensitive search using the base collation:

select value
from dbo.TestSorting
where value like '[A-Z]%'

All rows are returned, as expected:

value
-----
A
a
Ā
b
B

The problem comes in when you want to do a case sensitive search.  You choose a case sensitive collation, and either use it in the WHERE clause or in your table declaration, and:

select value
from dbo.TestSorting
where value like '[A-Z]%' collate Latin1_General_CS_AI

This returns what seems a confusing set:

value
-----
A
Ā
b
B

Everyone who does this the first time thinks "Why?" And I did too (in fact it was why I wrote this section of the book!)  The key is to kook at how the values sort in this collation:

select value
from dbo.TestSorting
order by value collate Latin1_General_CS_AI

value
-----
a
Ā
A
b
B

Case sensitive sorts things aAbBcC, not, as most of us programmers expect intuitively: ABCabc. That is actually how a binary collation works, since it sorts based on the ASCII or Unicode value of the character.  Using a binary collation would work, but then you lose the ability to include the special characters in your search which is given to you by using accent insensitivity.

Instead, you can simply use a LIKE such as this:

select value
from dbo.TestSorting
where value like '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' collate Latin1_General_CS_AI

This returns only the capital letters including the accented capital letter I included.

value
-----
A
Ā
B

It is a good idea to take a look at the sorting of data when you choose a collation to make sure it is clear to you how data will be sorted.

First in the data you have in your table, like this, if you want to see how it will sort in a binary collation:

select value
from dbo.TestSorting
order by value collate Latin1_General_bin

value
-----
A
B
a
b
Ā

And then I have a query I use for this purpose that I can use to generate the characters I am interested and see how they sort

;with digits (i) as( select 1 as i union all select 2 as i union all select 3 union all
                           select 4 union all select 5 union all select 6 union all select 7 union all
                           select 8 union all select 9 union all select 0)
,sequence (i) as (
                           SELECT D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i)
                           FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 
                                        CROSS JOIN digits AS D4 CROSS JOIN digits as D5
)
select i, nchar(i) as character
from sequence
where i between 48 and 122 --vary to include any characters in the character set of choice
order by nchar(i) collate Latin1_General_bin

Crossposted to http://sqlblog.com/blogs/louis_davidson/default.aspx

One of the things I promised when I published my last book was to answer my critics.  To take the good and take the bad and fix stuff.  So when I first read this review on a popular book sales site that rhymes with Carnes and Shobles, it was positive, but tonight when I (for some reason) went to look at rankings there, I noticed that it was kind a strange.  To be honest, my book title includes the word "Optimization".  And it is not a lie, my goal is to help the reader start out straight and end up with an optimized database.  Fine. 

However, this review states that my book is "An extensive Database optimization book."  It goes on to state that "This book completely demystifies a number of SQL tuning concepts."  I don't know if this person read the book (I hope not, truthfully) as I don't know that I do that.  The book is about designing databases first and foremost, with a coverage of optimization topics related to implementing databases that you have designed.  Admittedly, some of what the review says is true: "This book provides a superb overview of the various performance topics"  (I am going to go ahead and accept the generous "superb" thing.)  But I don't "drill down down deep enough into the concepts to give you the insights necessary to tackle thorny performance problems with multiple causes."  I leave that to Kalen and others to do.  My goal is to stop you from digging the hole too deep in the first place.

And the part that says: "The book delivers in-depth information on indexing, locking, the optimizer and many other topics in a very readable format."  Well, I have been told that it is in a readable format, and there is broad overview information about indexing and locking, but not a tremendous amount of depth.  Again, check Kalen's books for that.

What I find strange is the purpose of this review.  If the book company paid for it, they didn't get their money's worth.  At least those phony movie reviewers appeared to have seen the movie in question.   I can't think of any other reason to publish a positive review of a book you didn't read (I have seen plenty of bad reviews by people who had clearly not read a book, but never the other way around.)  So there you have it, a negative review of a positive review of my own book.  But I did promise.

And no, it doesn't have anything to do with a number two pencil and an ear.  No, in this case a person emailed me and said "I am interested in your book '*Pro SQL Server 2005 Database Design and
Optimization*' and wonder how much of it is about DBA work and how much of it is programming related?"

Now, here is where the little marketing/sales devil guy sits on your shoulder and says: "Hey, you. Sell him a book, he will like it, even if it isn't exactly what he wants" and this little tech guy angel sits on the other shoulder and goes "You know there are better books out there for him to start with."  Now perhaps these two little apparitions wouldn't have appeared if I kept sharp things out of my ear, but the fact is, it is never natural to tell people to purchase something that doesn't benefit you, particularly when you have a direct interest involved.  This was part of the reason I was a terrible Radio Shack employee around 18 years ago.  I liked a lot of their products, and it was a cool place, but all of the big ticket items that would have actually made it lucrative to work there were inferior to products you could get elsewhere.  In this case, I know for sure that this person would be better off starting somewhere else, though I would like to see his second or third book be mine, as I am very keen to push the "why normalize"/"why use constraints/triggers" agenda.

So, what did I do?  Well, clearly if I had listened to marketing guy I wouldn't be sitting here proudly admitting that I told the guy to buy my book would I?  No, in the end what I hope is that one day after he reads Itzik's and Bob's books, he comes back and purchases mine.  Then maybe he will tell others, and they will tell others, and then I wake up and mysteriously there sits a can of pencils, but luckily they are number 3H, and not 2.  Crisis averted.

And then I give up.  If you can't get it from the final two clues alone then you basically don't want to win :)

select cast(0x486F706566756C6C7920796F752073686F756C64206E6F74206E656564206D6F726520636C756573207468616E2074686973 as varchar(60))

I will try to post the answer on the 27th, if I have Internet (going on vacation!)

So, hope you enjoyed my contest, and have a Merry Christmas and a Happy New Year.  See you next year with my recap of this year and my next year's resolutions.

I (and a few diligent readers, who really pay attention) found five things that I am going to add to the book today, before the holidays.  I got really crunched for time near the end of the year, so I didn't get as much of this done as I would like to have. 

1. A bug in some of the code in chapter 4.

2. An incorrect index on a diagram in chapter 5.

3. A new tip about heaps, and an extra word removed from a paragraph  in Chapter 8.

4. Much better information about the date datatype, particularly with regards to standard date formats in Appendix B

5. An issue with a query of the sys.schemas view. in chapter 5.

More to come next year, as I am going to have to catch up some of the book to match SP2, I am thinking. 

Download the addendum here: http://drsql.org/Documents/Pro_SQL_Server_2005_Dat abase_Design_and_Optimization_Addendum.zip

Okay, so the contest (which I have gotten very little feedback on yet, other than a death threat or two for the very cryptic clues I have been dishing out...) comes to an end in a few weeks now, so the next 4 clues are going to take someone to the prize, so start hunting!

3-Dec  On December 25, Santa delivers toys to good boys and girls.  In our country, we left him a cookie as a snack so he would bring us a Red Ryder BB Gun.  Of course, our parents told us that we would shoot our eye out, but we knew better.

Most of what you need to find the answer is right there in the clue.  So if you have read the book, think back to see if one of the words I used rings a bell... 

So click here: http://drsql.org/hiddenMessageContest.aspx, check the rules, prizes, and then send your email to contest@drsql.org.  Good luck!

• Heading - jumps to the next Heading 1, Heading 2, Heading 3, etc. 
• Find - jumps to the next item in the find list (there doesn't seem to be a keystroke for find next, I know I have hit F3 hundreds of times hoping for that action)

Just figured that if you use Word for writing articles/papers and you primarily work with SQL like I do, you probably don't know all of the features either, and this is a very useful one.

So you can click through to each of the heading entries