common Question about CustomerID search

[3xxx]

probably, all of you have seen this scenario a lot

when entering customerID into a large database

it takes AGES to bring back one simple record of a single customer

===

let's say, this has been DONE already , for tuning performance

separate the CUSTOMERS table into 2

TBLcustomer1 -- all the customers that got access in the last 2 months (small tables)

TBLcustomer2 -- all the customers in the company (that exclude TBL customer 1)

===== >> obj: without modifying the FORM code or VB/ASP(.net) code etc

Q:
how to write a sql stmt (i.e. in SQL server level, rather than programming level)
when USER ENTER one customerID for searching

it will first search

select * from TBLcustomer1 -- small talbe

(small table first, if THE CUSTOMER IS FOUND, then exit IF/then for..loop etc, and then all the customers details will be supplied to the program)

IF THE CUSTOMER IS NOT FOUND in the SMALL TABLE

then the program WILL search the much bigger table (this depends on searching on small table)

select * from TBLcustomer2 -- this will take a long time

===== GURU: could you suggest some ways to do it ===

this is very common, I have seen such scenario in ORACLE and SQL server, please kindly propose some viable solution -- I think this could be a Boss-Pleaser (to see instant improve in performance)

;)

[kb.net]

Hi,

Create a stores procedure (called ex: CustInfo) and forward it the customer code to search for. The body of the procedure will be:

CREATE PROCEDURE CustInfo(@CustCod int) AS

	IF (Select count(*) from CustNew where CustNum= @CustCod)>=1 

	Select * from CustNew  where CustNum= @CustCod

	ELSE Select * from CustOld  where CustNum= @CustCod

GO

Then all what you will need to call is the stored procedure with the parameter which varies depending on the programming language you will use (but you can test it in SQL Query analyzer as: CustInfo 236)