Concat columns

I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:

> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:

>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:

> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>