non key select or search

hi all
i need to search a table for columns
i have to match data i recieve from a online transaction batch
example
in our system we send out
transactionno (unique)
Acctno (can be duplicated in transfile)
insuredacct (can be duplicated)
firstname (can be duplicated)
lastname (can be duplicated)
datofbirth (possible with the above duplicated)
transdate (not possible to be duplicated with the above)

we have a table with all completed transactions (few million)

I need to match the above to a transaction so i can report to the entity who
transmited the data to our company
the company we transmit to send back the above information..without the
transaction number

based on the above what is the best way to match to the correct row and how
should i build a index based on the above
thank you lots
analizer1

what is the best wa

Re: non key select or search

Analizer1 (vettes_n_jets@yahoo.com) writes:
> i need to search a table for columns
> i have to match data i recieve from a online transaction batch
> example
> in our system we send out
> transactionno (unique)
> Acctno (can be duplicated in transfile)
> insuredacct (can be duplicated)
> firstname (can be duplicated)
> lastname (can be duplicated)
> datofbirth (possible with the above duplicated)
> transdate (not possible to be duplicated with the above)
>
> we have a table with all completed transactions (few million)
>
> I need to match the above to a transaction so i can report to the entity
> who transmited the data to our company the company we transmit to send
> back the above information..without the transaction number
>
> based on the above what is the best way to match to the correct row and
> how should i build a index based on the above

I think you should take that again, but slower this time and with some
punctuation and capitals in the beginning of sentences. And most of all
clearer. At this point, I'm not really sure what is going on. But I get
an impression that there is something ugly going on if you sent out a
unique id, but do not get in back.

Anyway, a standard recommendation is that you include:
o CREATE TABLE statment(s) for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.

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

Re: non key select or search

thanks for the insult...but i asked for some help..
on the best way to build indexes and query based on
columns in the table...that are not key columns

have a good one
later

"Erland Sommarskog" wrote in message
news:Xns99DDEFC24DF59Yazorman@127.0.0.1...
> Analizer1 (vettes_n_jets@yahoo.com) writes:
>> i need to search a table for columns
>> i have to match data i recieve from a online transaction batch
>> example
>> in our system we send out
>> transactionno (unique)
>> Acctno (can be duplicated in transfile)
>> insuredacct (can be duplicated)
>> firstname (can be duplicated)
>> lastname (can be duplicated)
>> datofbirth (possible with the above duplicated)
>> transdate (not possible to be duplicated with the above)
>>
>> we have a table with all completed transactions (few million)
>>
>> I need to match the above to a transaction so i can report to the entity
>> who transmited the data to our company the company we transmit to send
>> back the above information..without the transaction number
>>
>> based on the above what is the best way to match to the correct row and
>> how should i build a index based on the above
>
> I think you should take that again, but slower this time and with some
> punctuation and capitals in the beginning of sentences. And most of all
> clearer. At this point, I'm not really sure what is going on. But I get
> an impression that there is something ugly going on if you sent out a
> unique id, but do not get in back.
>
> Anyway, a standard recommendation is that you include:
> o CREATE TABLE statment(s) for your table(s).
> o INSERT statements with sample data.
> o The desired result given the sample.
>
> --
> 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

Re: non key select or search

Analizer1 (vettes_n_jets@yahoo.com) writes:
> thanks for the insult...but i asked for some help..
> on the best way to build indexes and query based on
> columns in the table...that are not key columns

And I asked for more information so that I would be able to give you
the help.

And, insult or not, it does help if you want people to help that you
express yourself clearly, and also stick to normal writing rules.
Many problems related to databases (and programming general) are
complex. But the people who read your question have little of no
knowledge of the business domain you are working with. Therefore it
is essential that you express your questions clearly so that outsiders
can understand.

Yes, I could make a guess of what you might need, but that would be
just a guess, and it may not be applicable at all. And I don't really
feel comfortable with spending time on writing bad guesses.

I repeat from my previous post.

>> Anyway, a standard recommendation is that you include:
>> o CREATE TABLE statment(s) for your table(s).
>> o INSERT statements with sample data.
>> o The desired result given the sample.




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

Re: non key select or search

Analizer1 wrote:

> i need to search a table for columns
> i have to match data i recieve from a online transaction batch
> example
> in our system we send out
> transactionno (unique)
> Acctno (can be duplicated in transfile)
> insuredacct (can be duplicated)
> firstname (can be duplicated)
> lastname (can be duplicated)
> datofbirth (possible with the above duplicated)
> transdate (not possible to be duplicated with the above)
>
> we have a table with all completed transactions (few million)
>
> I need to match the above to a transaction so i can report to the entity who
> transmited the data to our company
> the company we transmit to send back the above information..without the
> transaction number
>
> based on the above what is the best way to match to the correct row and how
> should i build a index based on the above

Educated guess:

create unique clustered index index1 on the_table (transactionno)

create unique index index2 on the_table (acctno, insureacct,
firstname, lastname, datofbirth, transdate)