indexes and selects

Im trying to figure out the best way and/or fastest way to Match some data
comming in....example below
the below is all the incomming information i recieve to match to a row in
our system, we do send out a TransactionId but reports comming
back in do not reflect this id.. so i have to match the data. Matching is
pretty easy enough, but only been working with SqlServer about a Year now
and the existing system well Sucks on matching, so i want your advice and
the best way and fastest way to match up rows
with a high percentage..

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

all you input i very welcome
tks
Mike


--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

Re: indexes and selects

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx