Advice on select statement
Posted on 2004-10-27
I have following table [ZipCodes]. Last column with values of 1 and 2 is [ZipCodeID Int Identity(1,1)] which is [Unique Clustered Index]
07009 S Cedar Grove D New Jersey NJ 973 40.854334999999999 -74.231713999999997 1
07009 S Overbrook N New Jersey NJ 973 40.854334999999999 -74.231713999999997 2
(70,500 Records Total)
then I have table [Jobs], when somebody places a new job they fill in the form with [Address1, Address2, City, State, ZipCode], but table [Jobs] doesn't really have all this columns, it only has [Address1, Address2] and [ZipCodeID] which is [Nonclustered Index] Foreign Key referenced to [dbo.ZipCodes(ZipCodeID)], in order to find jobs I use following select statement:
SELECT j.JobID --j.JobID is Unique Clustered Index
CONTAINSTABLE(Jobs, JobDetails, 'Keyword') as j
JOIN ZipCodes z ON j.ZipCodeID=z.ZipCodeID
j.Datein between GETDATE() and '10/17/2004'
and z.ZipCode='07009' and z.State='NJ' and z.City='Cedar Grove'
now [j.Datein] is Unique Clustered Index and [z.ZipCode, z.State and z.City] are Nonclustered Indexes, so what I'm trying to do in in first place is that, I want to save some space because my hosting company allows me to have 250MB space for database and my empty database right not has reached up to 25MB and I would like to ask you 2 questions regarding my select statement
1) when I join tables like this [JOIN ZipCodes z ON j.ZipCodeID=z.ZipCodeID] and then match [z.ZipCode='07009' and z.State='NJ' and z.City='Cedar Grove'] is this gonna scan whole [ZipCodess] table for matching values in [z.ZipCode, z.State, z.City] or is it gonna go straight to the referenced record?
2) and after all is it worth saving space? Otherwize I have to store [City State and ZipCode] in five seperate tables, this way I have to store only [ZipCodeID] in every table which is Foreign Key to [ZipCodes]
sorry if all this sounds confusing, but that's the problem I have right now... :)