Advice on select statement
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)],
SELECT j.JobID --j.JobID is Unique Clustered Index
FROM
CONTAINSTABLE(Jobs, JobDetails, 'Keyword') as j
JOIN ZipCodes z ON j.ZipCodeID=z.ZipCodeID
WHERE
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... :)
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)],
SELECT j.JobID --j.JobID is Unique Clustered Index
FROM
CONTAINSTABLE(Jobs, JobDetails, 'Keyword') as j
JOIN ZipCodes z ON j.ZipCodeID=z.ZipCodeID
WHERE
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... :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
have you ever worked with the database like that? I mean US zipcode database, it is very strange that two same zipcodes have two diferent Cities THAT IS NOT RIGHT, businesswize of course :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
scrolldown to the end of the page and you'll see
http://www.zipcodedownload.com/Products/?Product=DataPremiumUSA&StateID=98a81675-3e9f-4b64-bd65-e105e73c0ab6
http://www.zipcodedownload.com/Products/?Product=DataPremiumUSA&StateID=98a81675-3e9f-4b64-bd65-e105e73c0ab6
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can assure you that all zipcodes are unique across the country and now in my database as well :)
ASKER
I guess US postal service cares about their database as much as we do :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
S- Standard ZIP Code
P - PO Box Only
U - Used for Organizations such as large businesses or buildings
M - Military, often used for military vessels, but mostly for military bases
D - Default City Name - recommended by the USPS
A - Alternate City Name, also used by the USPS, although not the primary name of that ZIP Code.
N - Not-recommended City Name, identifies the name of the ZIP Code - often used by locals as a place name. Not used, nor accepted, by the USPS.
these are all type of zipcodes and cityes that US has and I guess to setisfy my business needs S and U are more then enough. As for cities D is doing just fine, all that makes 29,564 of unique total rows in my database
P - PO Box Only
U - Used for Organizations such as large businesses or buildings
M - Military, often used for military vessels, but mostly for military bases
D - Default City Name - recommended by the USPS
A - Alternate City Name, also used by the USPS, although not the primary name of that ZIP Code.
N - Not-recommended City Name, identifies the name of the ZIP Code - often used by locals as a place name. Not used, nor accepted, by the USPS.
these are all type of zipcodes and cityes that US has and I guess to setisfy my business needs S and U are more then enough. As for cities D is doing just fine, all that makes 29,564 of unique total rows in my database
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> if someone from Gresham with that ZIP code that bhess1 mentioned is a cli...
in this case of mistake I will display all possible cities coresponding that particular zipcode or whatever they enter, it is only matter of little SQL select statement... :)
and my select statement should do well without join
declare @ZipCode Char(5)
select @ZipCode=ZipCode from ZipCodes where City='Cedar Grove' and State='NJ'
SELECT JobID
FROM
CONTAINSTABLE(Jobs, JobDetails, 'Keyword')
WHERE
Datein between GETDATE() and '10/17/2004'
and ZipCode=@ZipCode
in this case I will make Index on (City, State) in my ZipCodes table
in this case of mistake I will display all possible cities coresponding that particular zipcode or whatever they enter, it is only matter of little SQL select statement... :)
and my select statement should do well without join
declare @ZipCode Char(5)
select @ZipCode=ZipCode from ZipCodes where City='Cedar Grove' and State='NJ'
SELECT JobID
FROM
CONTAINSTABLE(Jobs, JobDetails, 'Keyword')
WHERE
Datein between GETDATE() and '10/17/2004'
and ZipCode=@ZipCode
in this case I will make Index on (City, State) in my ZipCodes table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> in this case of mistake I will display all possible cities coresponding that particular zipcode or whatever they enter, it is only matter of little SQL select statement... :)
I mean, that search will not be performed, instead there will be links like:
Please choose one or more specific area:
==========================
Cedar Grove, NJ
Cedar Grove, CA
Cedar Grove, WA
...
...
...
and then they are more then welcome to search :)
there are 9 cities most with the same name in US so I don't think it's gonna hurt performance and I don't either that 100% of the visitors are going to make this mistakes and even they do that should not produse any noticableoverhead
I mean, that search will not be performed, instead there will be links like:
Please choose one or more specific area:
==========================
Cedar Grove, NJ
Cedar Grove, CA
Cedar Grove, WA
...
...
...
and then they are more then welcome to search :)
there are 9 cities most with the same name in US so I don't think it's gonna hurt performance and I don't either that 100% of the visitors are going to make this mistakes and even they do that should not produse any noticableoverhead
ASKER
could you please explain diference between these
>> https://www.experts-exchange.com/questions/21185640/Advice-on-select-statement.html#12437513
>> https://www.experts-exchange.com/questions/21185640/Advice-on-select-statement.html#12437513
Personal opinion: The only substantive difference is that Primary Key automatically disallows NULL values in the underlying field(s), and that there can be only one Primary Key. If the underlying data field(s) are defined as Not Null, then there is no substantive difference.
ASKER
as long as there is no diference performancewize I don't care... :)
thank you very much guys
thank you very much guys
You will not be able to create a foreign key unless it points to a primary key. This is one of those dualities that gets easily missed: SQL Server treats primary keys both as constraints from a conceptual model point a view and as indexes from a physical model point of view. A primary key (if nothing else is specified) is by default a unique clustered index, and a unique constraint. But if you don't call it a primary key, you will not be able to create a foreign key to it.
ASKER
BTW, another question: what is the diference between how I create my index
>> ZipCode Char(5) Not null Primary Key Clustered
AND
>> Create Unique Clustered Index on dbo.ZipCodes(ZipCode)
especialy in my case? what if I do it this way, I mean not specifying the Primary key:
>> ZipCode Char(5) Not null
and then create index, it still going to be unique anyway, or is it all about [Primary Key]..?
>> Create Unique Clustered Index on dbo.ZipCodes(ZipCode)