Stumped on some code...

SQL Apprentice Question
ok, so I'm workin on a customer & leads part of this application. I
need to setup a way to set a territory location status.

We have two territories - LA and NY...
LA Territory - California Customers & Leads.
NY Territory - Customers & Leads in Connecticut, Maine, Massachusetts,
New Hampshire, New Jersey, New York, Pennsylvania, Rhode Island,
Vermont.


I've got tables that store State, Area Codes and Zipcodes. I've got a
bit field setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes
for these states.


We don't always get complete data, so we have to check all of these
variables to see what area the customer or lead belongs to.


I've tried using a UDF & passing in the State, Area, Zip of the
customer/lead and returning what territory the cust/lead belongs to.


I've tried having a case statement.


When I setup the UDF in the search query, our sql server came to a
screamin halt after the memery used by sql maxed out our server... it
jumped from only usin a few megs to just over two gigs, then our server
crashed... so I figured that UDF's are good but not for bulk-results...


I've tried usin the case statement, but it increases the query time
from 4 seconds to 30 seconds.


I've tried checking if the area/zip codes matchup using "IN" and
"EXISTS" tags...


I'm at a loss... seems like whatever I do, the queries go from quick to
crawl.
Any fresh idea's on how to grab this sorta data quickly would be much
appreciated.



Celko Answers
>> I've got tables that store State, Area Codes and Zipcodes. I've got a bit field [sic] setup to flag the 9 states, 33 Area Codes, and 7,564 Zipcodes for these states. <<


Do not program SQL as if it were a low-level language. Fields and
columns are totally different concepts. It sounds like your tables
should look more like this:

CREATE TABLE Area_Codes
(state_code CHAR(2)NOT NULL
CHECK (state_code IN ('CA', 'NY', ..),
area_code CHAR(3) NOT NULL
CHECK (area_code IN ('212', ..),
PRIMARY KEY (state_code, area_code)
);


-- Using ranges instead of bits will make the data much smaller.


CREATE TABLE ZipCodes
(state_code CHAR(2)NOT NULL
CHECK (territory_code IN ('CA', 'NY', ..),
high_zipcode CHAR(5) NOT NULL UNIQUE
CHECK (high_zipcode LIKE '[0-9][0-9][0-9][0-9][0-9]'),
low_zipcode CHAR(5) NOT NULL UNIQUE
CHECK (low_zipcode LIKE '[0-9][0-9][0-9][0-9][0-9]'),
CHECK (low_zipcode <= high_zipcode)
);


I am guessing that you do not need to match area codes to ZIP codes,
since they can overlap. There are tools for that from mailing list
software companies.


You can now compute the Territory code with a CASE expression in a VIEW
or query from the State codes.


Another point is that SQ: is meant to do JOINs and not functions and
procedures. Start thinking that way instead of in proceudral code.