Posted by Erland Sommarskog on 10/19/07 21:39

MikeJ (analizer1@yahoo.com) writes:
> when building a Index for the below , does sql store the data being
> indexed in separate columns or are the Columns being indexed
> concatenated via the column order being sorted
>
> --not a key column
> accountNo account of incomming partners customer
> FirstName account holder first name
> last name account holder last name
> dateOfTrans account holder Transaction date

In separate columns. This is necessary for several reasons. One is that
different sort rules apply to different type of data. Assuming that the
account number is integer, Integer column can be sorted on the binary
pattern, while character data is lot more complex with case-insensitivity
and that. Also, concatenated variable-length column would give bad results.
You don't want Alex Smith to come between Al Davidson and Al Wesley.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]