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)