Which method is better for performance?

I have a identity field that holds order numbers.
I have another table that holds company code.

I concat company code + order number . So I have XYZ1234 or ABC56879
All fine. Now, user wants to search by entering ABC56879.

My options:
 1. Have a function to parse out ABC and use 56879 and search on that identity column.
 2. Create a new column. Concat company code + identity field. Use this new column to search.

which one is better/faster?
LVL 8
CamilliaAsked:
Who is Participating?
 
CamilliaAuthor Commented:
i didnt get an answer. I ended up creating a new column. In the stored proc, i concat the values and store in the new column.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2. can be achieved easily with a computed column, and can even be indexed.
sounds like it's exactly what you are looking for.
0
 
CamilliaAuthor Commented:
cant use computed column. To concat CompanyCode + OrderNumber, i have to join 2 tables.
Been researching this all day :(

 so, go with #2 , new column, not a function?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>cant use computed column.
yes, you can. with "computed column" I don't mean "computed inside a query", but a column in the table:

ALTER TABLE yourtable ADD COLUMN CombinedOrder = Company_Code + CAST(order_number as varchar(30))

Open in new window

0
 
CamilliaAuthor Commented:
let me try it. This is like creating a new column. I will post back. Let me read about "computed column".
0
 
CamilliaAuthor Commented:
That line gives me an error.

But here, i cant have a computed column on TWO tables. That "Company_Code" is coming from another table.
http://msdn.microsoft.com/en-us/library/ms191250.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I understand that the 2 fields are in 2 tables.
so, how is that supposed to work for the "search" ?
what is the link between the 2 tables?
0
 
CamilliaAuthor Commented:
the link between the 2 tables is an ID.

 For the search, if user enters XYZ1234, i can do a select on the composite column to get the result..  select * from table having (code+number)

no? I've probably missed the boat totally on this..

I can go with a new column, and on insert, just concat code+number and dump the values there...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
last clarifications:
how to "split" the search value?
will it ALWAYS be 3 characters + some digits, or should be take all the characters for the company code and the digits for the number?
0
 
CamilliaAuthor Commented:
any character for the company. I found this function to parse out the non-numeric and just spit out digits. From Scott Pletcher on this forum.
ALTER FUNCTION [dbo].[fn_strip_nonnumeric] (
    @string VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @SiteCode INT
SET @SiteCode = PATINDEX('%[^0-9]%', @string)
WHILE @SiteCode > 0
BEGIN
    SET @string = LEFT(@string, @SiteCode - 1) + SUBSTRING(@string, @SiteCode + 1, 1000)
    SET @SiteCode = PATINDEX('%[^0-9]%', @string)
END --WHILE
RETURN @string
END 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.