how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
Looks like you have it almost right:
alter table MyTable
add
constraint PK_MyTable primary key (ClientID, UserID)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Michael Haberichter" <Haberichter@.community.nospam> wrote in message
news:ED15E772-0327-4C98-8FBD-92AE3717D1FB@.microsoft.com...
how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
sql
Showing posts with label char. Show all posts
Showing posts with label char. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Primary Key, bigint or char?
Hey guys, just a quick question for you. I think I know the answer, but I
would like to get the expert's opinions...
I have a primary key called RequestID that is to be constructed of the
year, month, day, and a four-digit sequence number with leading zeros. An
example of the ninth Request placed today would be:
200506090009
As well as being the primary key, this field will be used in WHERE clauses
and in joins to several other tables in my schema.
My question is; should the data type be bigint or char(12)?Well, judging by size, I would choose the bigint since it is only 8 bytes
instead of the 12 bytes that are used by a char(12). I am not sure on this,
but I would assume that searching on an int would be faster. Again, that is
pure speculation on my part and I have no way to back it up. Just my 2￠.
Chris
"Tim Baur" wrote:
> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?
>|||>> As well as being the primary key, this field will be used in WHERE
Logically, the data type of an attribute is often decided based on the
operations applicable on its values. Therefore as a primary consideration,
identify the potential operations -- either integer specific or character
specific -- and use that for determining the type.
Also, physical factors like storage size of the values, potential
performance implications etc. can be a valid considerations during specific
implementation, depending on how the queries are formulated and how the data
is updated. So 8 bytes vs. 12 bytes, esp. in a large table often tend to
favor smaller size datatype, esp. for key columns.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in
news:ePLNgaTbFHA.3932@.TK2MSFTNGP12.phx.gbl:
> Logically, the data type of an attribute is often decided based on the
> operations applicable on its values. Therefore as a primary
> consideration, identify the potential operations -- either integer
> specific or character specific -- and use that for determining the
> type.
> Also, physical factors like storage size of the values, potential
> performance implications etc. can be a valid considerations during
> specific implementation, depending on how the queries are formulated
> and how the data is updated. So 8 bytes vs. 12 bytes, esp. in a large
> table often tend to favor smaller size datatype, esp. for key columns.
>
Thank you, Chris and Anith, for your input. I also suspect that bigint is
the best choice, but because it is numeric more than size considerations.
If given the choice between char(8) and bigint I would still choose bigint.
I know from general programming experience that numeric lists are faster to
index, sort, and search than strings. I suspect that SQL Server wouldn't
be any different in this, but that is just a guess. I would be curious to
hear the definitive answer on the topic.|||Well, I am definitely not an expert, but I definitely have opinions, so here
goes...
May I choose super-secret option number three, "None of the Above"? How
about using a two-column key, consisting of RequestDate(smalldatetime) and
RequestNumber(smallint)? This would provide the following benefits:
1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime = 4
bytes and smallint = 2 bytes)
2) You get a larger set of available numbers for RequestNumber - 10000 in
you setup versus 32000+ in my setup (if you only use the positive values).
3) Makes standard types of calculations extremely easy. If you want to
retrieve items for a range of dates, you do not need to parse the key column
to extract the date portion. If you want to look for 'gaps' in the request
numbers for a given day, you do not need to parse the key column. The list
goes on and on.
4) Searches for a specific value are no problem - just allow the user to
enter the value in the encoded format, and convert it to your two column
values. When presenting the value, re-format to the encoded format (which
should really be done in the user interface layer anyway). The users never
need to deal with the actual storage format - and that should (almost) never
be the end-users' concern anyway.
Since you did not post DDL for this table, it is impossible to know, but I
would guess that you might already have a column for RequestDate. In that
case, there really is no good reason for the duplication - just run with
what you have.
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns9670A0623995Ctrbo20DISREGARDyaho
o@.207.46.248.16...
> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?|||Just a thought,
How about Decimal(12,0)?
That will use 9 bytes though, not really any advantage from a "compact key"
perspective.
Better than having a composite primary key. IMO composite primary key's are
evil.
Just my $0.02.
Richard|||"Jeremy Williams" <jeremydwill@.netscape.net> wrote in
news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:
> Subject: Re: Primary Key, bigint or char?
> From: "Jeremy Williams" <jeremydwill@.netscape.net>
> Newsgroups: microsoft.public.sqlserver.programming
> Well, I am definitely not an expert, but I definitely have opinions,
> so here goes...
> May I choose super-secret option number three, "None of the Above"?
> How about using a two-column key, consisting of
> RequestDate(smalldatetime) and RequestNumber(smallint)? This would
> provide the following benefits:
> 1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime
> = 4 bytes and smallint = 2 bytes)
> 2) You get a larger set of available numbers for RequestNumber - 10000
> in you setup versus 32000+ in my setup (if you only use the positive
> values). 3) Makes standard types of calculations extremely easy. If
> you want to retrieve items for a range of dates, you do not need to
> parse the key column to extract the date portion. If you want to look
> for 'gaps' in the request numbers for a given day, you do not need to
> parse the key column. The list goes on and on.
> 4) Searches for a specific value are no problem - just allow the user
> to enter the value in the encoded format, and convert it to your two
> column values. When presenting the value, re-format to the encoded
> format (which should really be done in the user interface layer
> anyway). The users never need to deal with the actual storage format -
> and that should (almost) never be the end-users' concern anyway.
> Since you did not post DDL for this table, it is impossible to know,
> but I would guess that you might already have a column for
> RequestDate. In that case, there really is no good reason for the
> duplication - just run with what you have.
>
These are all good points, Jeremy, and your approach was considered.
The way it currently stands:
The request table has an assignment log that will log the creation
date. The point to using a date in the key is because I want the
request number to reset after each day. I would rather the users not be
able to easily figure out how many requests the help desk gets during
the course of a w
.
There will *never* be more than 9,999 requests in one day. There
will probably never be more than 10.
Gaps in the sequence are no big deal. I would use Count() to get a
count of records for any particular day or date range.
Composite Keys are not the friendliest to use, especially in a table
as central to the schema as this one. I do use them in other places,
but this particular key serves as a foreign key in six other tables.|||Yeah, I know some developers/DBAs have an unnatural aversion to composite
keys. It seems to "make their teeth itch".
I have never understood the problem myself - it seems much more natural to
use the key information you are already collecting than to make up yet
another key just so you can get it down to one column. As often as not, all
this does is force the developer to join tables unnecessarily when
retrieving data, or use inefficient criteria in their WHERE clauses because
they are parsing the "composite" single column.
As for keeping the user from knowing the number of requests per w, I was
under the impression you would reset the request number each day no matter
how you constructed the key. It would not seem to make a difference whether
you were munging that in with the date in one column, or keeping them
separate - the work is the same. You must have something more going on here
that you have not shown, I guess.
Since your design is already decided on (no composite keys in this
situation), then there really does not seem to be anything compelling
criteria other than storage size to recommend one choice over the other. Go
with the bigint (as others have already said) - it should suit you design
well. Thanks for the dialog!
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns96715ECD5928Etrbo20DISREGARDyaho
o@.207.46.248.16...
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in
> news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:
>
> These are all good points, Jeremy, and your approach was considered.
> The way it currently stands:
> The request table has an assignment log that will log the creation
> date. The point to using a date in the key is because I want the
> request number to reset after each day. I would rather the users not be
> able to easily figure out how many requests the help desk gets during
> the course of a w.
> There will *never* be more than 9,999 requests in one day. There
> will probably never be more than 10.
> Gaps in the sequence are no big deal. I would use Count() to get a
> count of records for any particular day or date range.
> Composite Keys are not the friendliest to use, especially in a table
> as central to the schema as this one. I do use them in other places,
> but this particular key serves as a foreign key in six other tables.
>|||>> Yeah, I know some developers/DBAs have an unnatural aversion to composite
Actually some level of aversion might be quite natural given the existence
of known issues caused by composite keys that are well documented in
relational literature. Not sure if my teeth itches :-)
Not specific to this case, but in general, simple keys often assist in
formulating simpler relational expressions. Simple keys are often minimal
and always irreducible and therefore partial key dependencies are never an
issue.
And as already mentioned above, there are certain known problems exhibited
by compound keys in referencing table when data may be missing. For
instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing.
You are right; often developers include additional identifiers assuming they
are a panacea, without understanding the potential benefits or implications
of using an existing key, simple or composite.
Anith|||Thanks for replying, Anith - It is nice to get the input of one of the
actual experts!
Can you guide me to some resources where I can study the composite-key
issues you mentioned further? I have not come across any issues so far, but
I have only been developing in SQL for 10 years or so, so I am sure I have
not seen all there is to see.
My experiences with partial key dependencies typically signified that there
was normalization issues that needed to be worked out - creating an
additional simple key in addition to the composite key would really not have
helped at all. And I freely admit that I do not understand what you mean by:
"For instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing." If the composite
key is being used in a referencing/referenced table scenario, how does
missing data get involved - a normalized design would require all components
of the composite key to exist, wouldn't it?
I am always looking to expand my knowledge, so please let me know where I
can find out more. Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uYEHCldbFHA.1384@.TK2MSFTNGP09.phx.gbl...
composite
> Actually some level of aversion might be quite natural given the existence
> of known issues caused by composite keys that are well documented in
> relational literature. Not sure if my teeth itches :-)
>
yet
> Not specific to this case, but in general, simple keys often assist in
> formulating simpler relational expressions. Simple keys are often minimal
> and always irreducible and therefore partial key dependencies are never an
> issue.
> And as already mentioned above, there are certain known problems exhibited
> by compound keys in referencing table when data may be missing. For
> instance, there is no logical choice for a DBMS to decide on whether to
> insert/update a row in a referencing table when the values in a subset of
> the columns in a multi-column referencing key are missing.
>
their
> You are right; often developers include additional identifiers assuming
they
> are a panacea, without understanding the potential benefits or
implications
> of using an existing key, simple or composite.
> --
> Anith
>
would like to get the expert's opinions...
I have a primary key called RequestID that is to be constructed of the
year, month, day, and a four-digit sequence number with leading zeros. An
example of the ninth Request placed today would be:
200506090009
As well as being the primary key, this field will be used in WHERE clauses
and in joins to several other tables in my schema.
My question is; should the data type be bigint or char(12)?Well, judging by size, I would choose the bigint since it is only 8 bytes
instead of the 12 bytes that are used by a char(12). I am not sure on this,
but I would assume that searching on an int would be faster. Again, that is
pure speculation on my part and I have no way to back it up. Just my 2￠.
Chris
"Tim Baur" wrote:
> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?
>|||>> As well as being the primary key, this field will be used in WHERE
Logically, the data type of an attribute is often decided based on the
operations applicable on its values. Therefore as a primary consideration,
identify the potential operations -- either integer specific or character
specific -- and use that for determining the type.
Also, physical factors like storage size of the values, potential
performance implications etc. can be a valid considerations during specific
implementation, depending on how the queries are formulated and how the data
is updated. So 8 bytes vs. 12 bytes, esp. in a large table often tend to
favor smaller size datatype, esp. for key columns.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in
news:ePLNgaTbFHA.3932@.TK2MSFTNGP12.phx.gbl:
> Logically, the data type of an attribute is often decided based on the
> operations applicable on its values. Therefore as a primary
> consideration, identify the potential operations -- either integer
> specific or character specific -- and use that for determining the
> type.
> Also, physical factors like storage size of the values, potential
> performance implications etc. can be a valid considerations during
> specific implementation, depending on how the queries are formulated
> and how the data is updated. So 8 bytes vs. 12 bytes, esp. in a large
> table often tend to favor smaller size datatype, esp. for key columns.
>
Thank you, Chris and Anith, for your input. I also suspect that bigint is
the best choice, but because it is numeric more than size considerations.
If given the choice between char(8) and bigint I would still choose bigint.
I know from general programming experience that numeric lists are faster to
index, sort, and search than strings. I suspect that SQL Server wouldn't
be any different in this, but that is just a guess. I would be curious to
hear the definitive answer on the topic.|||Well, I am definitely not an expert, but I definitely have opinions, so here
goes...
May I choose super-secret option number three, "None of the Above"? How
about using a two-column key, consisting of RequestDate(smalldatetime) and
RequestNumber(smallint)? This would provide the following benefits:
1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime = 4
bytes and smallint = 2 bytes)
2) You get a larger set of available numbers for RequestNumber - 10000 in
you setup versus 32000+ in my setup (if you only use the positive values).
3) Makes standard types of calculations extremely easy. If you want to
retrieve items for a range of dates, you do not need to parse the key column
to extract the date portion. If you want to look for 'gaps' in the request
numbers for a given day, you do not need to parse the key column. The list
goes on and on.
4) Searches for a specific value are no problem - just allow the user to
enter the value in the encoded format, and convert it to your two column
values. When presenting the value, re-format to the encoded format (which
should really be done in the user interface layer anyway). The users never
need to deal with the actual storage format - and that should (almost) never
be the end-users' concern anyway.
Since you did not post DDL for this table, it is impossible to know, but I
would guess that you might already have a column for RequestDate. In that
case, there really is no good reason for the duplication - just run with
what you have.
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns9670A0623995Ctrbo20DISREGARDyaho
o@.207.46.248.16...
> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?|||Just a thought,
How about Decimal(12,0)?
That will use 9 bytes though, not really any advantage from a "compact key"
perspective.
Better than having a composite primary key. IMO composite primary key's are
evil.
Just my $0.02.
Richard|||"Jeremy Williams" <jeremydwill@.netscape.net> wrote in
news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:
> Subject: Re: Primary Key, bigint or char?
> From: "Jeremy Williams" <jeremydwill@.netscape.net>
> Newsgroups: microsoft.public.sqlserver.programming
> Well, I am definitely not an expert, but I definitely have opinions,
> so here goes...
> May I choose super-secret option number three, "None of the Above"?
> How about using a two-column key, consisting of
> RequestDate(smalldatetime) and RequestNumber(smallint)? This would
> provide the following benefits:
> 1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime
> = 4 bytes and smallint = 2 bytes)
> 2) You get a larger set of available numbers for RequestNumber - 10000
> in you setup versus 32000+ in my setup (if you only use the positive
> values). 3) Makes standard types of calculations extremely easy. If
> you want to retrieve items for a range of dates, you do not need to
> parse the key column to extract the date portion. If you want to look
> for 'gaps' in the request numbers for a given day, you do not need to
> parse the key column. The list goes on and on.
> 4) Searches for a specific value are no problem - just allow the user
> to enter the value in the encoded format, and convert it to your two
> column values. When presenting the value, re-format to the encoded
> format (which should really be done in the user interface layer
> anyway). The users never need to deal with the actual storage format -
> and that should (almost) never be the end-users' concern anyway.
> Since you did not post DDL for this table, it is impossible to know,
> but I would guess that you might already have a column for
> RequestDate. In that case, there really is no good reason for the
> duplication - just run with what you have.
>
These are all good points, Jeremy, and your approach was considered.
The way it currently stands:
The request table has an assignment log that will log the creation
date. The point to using a date in the key is because I want the
request number to reset after each day. I would rather the users not be
able to easily figure out how many requests the help desk gets during
the course of a w
.There will *never* be more than 9,999 requests in one day. There
will probably never be more than 10.
Gaps in the sequence are no big deal. I would use Count() to get a
count of records for any particular day or date range.
Composite Keys are not the friendliest to use, especially in a table
as central to the schema as this one. I do use them in other places,
but this particular key serves as a foreign key in six other tables.|||Yeah, I know some developers/DBAs have an unnatural aversion to composite
keys. It seems to "make their teeth itch".
I have never understood the problem myself - it seems much more natural to
use the key information you are already collecting than to make up yet
another key just so you can get it down to one column. As often as not, all
this does is force the developer to join tables unnecessarily when
retrieving data, or use inefficient criteria in their WHERE clauses because
they are parsing the "composite" single column.
As for keeping the user from knowing the number of requests per w
, I wasunder the impression you would reset the request number each day no matter
how you constructed the key. It would not seem to make a difference whether
you were munging that in with the date in one column, or keeping them
separate - the work is the same. You must have something more going on here
that you have not shown, I guess.
Since your design is already decided on (no composite keys in this
situation), then there really does not seem to be anything compelling
criteria other than storage size to recommend one choice over the other. Go
with the bigint (as others have already said) - it should suit you design
well. Thanks for the dialog!
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns96715ECD5928Etrbo20DISREGARDyaho
o@.207.46.248.16...
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in
> news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:
>
> These are all good points, Jeremy, and your approach was considered.
> The way it currently stands:
> The request table has an assignment log that will log the creation
> date. The point to using a date in the key is because I want the
> request number to reset after each day. I would rather the users not be
> able to easily figure out how many requests the help desk gets during
> the course of a w
.> There will *never* be more than 9,999 requests in one day. There
> will probably never be more than 10.
> Gaps in the sequence are no big deal. I would use Count() to get a
> count of records for any particular day or date range.
> Composite Keys are not the friendliest to use, especially in a table
> as central to the schema as this one. I do use them in other places,
> but this particular key serves as a foreign key in six other tables.
>|||>> Yeah, I know some developers/DBAs have an unnatural aversion to composite
Actually some level of aversion might be quite natural given the existence
of known issues caused by composite keys that are well documented in
relational literature. Not sure if my teeth itches :-)
Not specific to this case, but in general, simple keys often assist in
formulating simpler relational expressions. Simple keys are often minimal
and always irreducible and therefore partial key dependencies are never an
issue.
And as already mentioned above, there are certain known problems exhibited
by compound keys in referencing table when data may be missing. For
instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing.
You are right; often developers include additional identifiers assuming they
are a panacea, without understanding the potential benefits or implications
of using an existing key, simple or composite.
Anith|||Thanks for replying, Anith - It is nice to get the input of one of the
actual experts!
Can you guide me to some resources where I can study the composite-key
issues you mentioned further? I have not come across any issues so far, but
I have only been developing in SQL for 10 years or so, so I am sure I have
not seen all there is to see.
My experiences with partial key dependencies typically signified that there
was normalization issues that needed to be worked out - creating an
additional simple key in addition to the composite key would really not have
helped at all. And I freely admit that I do not understand what you mean by:
"For instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing." If the composite
key is being used in a referencing/referenced table scenario, how does
missing data get involved - a normalized design would require all components
of the composite key to exist, wouldn't it?
I am always looking to expand my knowledge, so please let me know where I
can find out more. Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uYEHCldbFHA.1384@.TK2MSFTNGP09.phx.gbl...
composite
> Actually some level of aversion might be quite natural given the existence
> of known issues caused by composite keys that are well documented in
> relational literature. Not sure if my teeth itches :-)
>
yet
> Not specific to this case, but in general, simple keys often assist in
> formulating simpler relational expressions. Simple keys are often minimal
> and always irreducible and therefore partial key dependencies are never an
> issue.
> And as already mentioned above, there are certain known problems exhibited
> by compound keys in referencing table when data may be missing. For
> instance, there is no logical choice for a DBMS to decide on whether to
> insert/update a row in a referencing table when the values in a subset of
> the columns in a multi-column referencing key are missing.
>
their
> You are right; often developers include additional identifiers assuming
they
> are a panacea, without understanding the potential benefits or
implications
> of using an existing key, simple or composite.
> --
> Anith
>
Monday, March 12, 2012
Primary Key
I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.
Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
EG:
-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
-------
1 | REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------
1 | 1 | 1 | 0.15
-- New way
tbProductionLabour
Labour | fkCostCode
-------
REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
--------------
1 | REBAR | 1 | 0.15
This is a very basic example, but you get the idea of what I am referring to.
Any thoughts?
MikeI didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
The light! Don't look at the light!
I guess I'll add you to the "not preffering surrogates" group
http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx
Excuse me while I climb back upon my barst...um desk chair...yeah that's right...|||EDIT: Didn't we have this conversation already?|||EDIT: Didn't we have this conversation already?
Probably, but as an in-experienced developer (wannabe) I was wondering if not using a identity field will really make that great of a performance difference.
I think the char(20) for a primary key is better mainly because when I open a table I am not seeing a number which I will have to look up. It also would reduce the number of joins I would have to make (which I guess would be better for performance). But, for looking up values or joining on that field, would the performance reduction be neglable or significant enough to want to use the identity field?
Mike|||Let me ask you this...
What do you think would be faster.
A). An umpteen table join on surrogate keys to get the data, or
B). A SELECT against 1 Table|||Let me ask you this...
What do you think would be faster.
A). An umpteen table join on surrogate keys to get the data, or
B). A SELECT against 1 Table
I would assume option B, but let me ask you this.
What do you think would be faster:
A) Looking up values based on an integer
or
B) Looking up values based on an umpteen char string
?
Mike B|||OK, I'll give you 80/1000 of a second
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 sysname)
CREATE TABLE myTable00(Col1 sysname, Col2 int IDENTITY(1,1))
GO
DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
INSERT INTO myTable00(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
SELECT @.x = @.x + 1
END
INSERT INTO myTable99(Col1) SELECT 'Brett'
CREATE INDEX myIndex99 ON myTable99(Col1)
CREATE INDEX myIndex00 ON myTable00(Col2)
SELECT COUNT(*) FROM myTable99
DECLARE @.x1 datetime, @.y1 datetime, @.x2 datetime, @.y2 datetime
SELECT @.x1 = GetDate()
SELECT @.x1 AS systime, 'Starting int look up'
SELECT * FROM myTable00 WHERE Col2 = 216784
SELECT @.y1 = GetDate()
SELECT @.y1 AS systime, 'Endinging int look up'
SELECT @.x2 = GetDate()
SELECT @.x2 AS systime, 'Starting sysname look up'
SELECT * FROM myTable00 WHERE Col1 = 'Brett'
SELECT @.y2 = GetDate()
SELECT @.y2 AS systime, 'Endinging int look up'
SELECT DATEDIFF(ms,@.x1, @.y1), DATEDIFF(ms,@.x2, @.y2)
GO
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.
The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!
-PatP|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.
The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!
-PatP
Yeah, I pretty much aggree with that. The tables I am refering to will not be that big and they are kind of complex so I thought it would be best to use as many natural keys as possible. Of course there are places in my DB where I use the identity because I don't think the natural keys are all that good to use.
How many people use CompanyName as a natural key in a companies table?
I understand there maybe more then one CompanyName in the table but should they be unique by appending a number or geographical location, or using the CompanyName / Address as the primary key.
My thought is that it is best to use a surrogate here because to carry a company name and address as a forein key to other tables is probably costly.
There is the argument that the address can change but is that really a problem since you can specify "Cascade update related fields" on the other tables?
I would personally love to see something other then a number when I am looking at these tables but....
Mike B|||At least in my opinion, company name stinks as a primary key. We aren't all that big, but we have several hundred companies scattered wildly about North America with the same name, and on a worldwide basis it gets even worse.
JOINs are cheap. SQL Server makes them nearly free IF you keep the FK value small (INT or smaller) and you've got enough RAM in your server.
-PatP|||JOINs are cheap.
That's gotta be the most open ended statement I've heard in a while...
Also... "Company's with the same name scattered around"?
Either they truly are a different company, which means they are separate legal entity, or they are a site for a company...
Are you essentially saying that accessing 1 table would be slower than accessing many?|||Many are franchises, some just reuse common names in different jurisdictions. The net result is that if you look for companies with names like Subway or McDonald's you find hundreds of hits, most (but not all) with separate EIN values.
If you have to haul a fifty byte VARCHAR off the disk versus a four byte integer for every row in a 34 million row table, versus a join to a table cached in RAM, then the JOIN is cheaper than the single table. While SQL Server is good at hiding physical details from the user, some things are still big enough tasks so that smart design beats brute force every time.
-PatP
Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
EG:
-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
-------
1 | REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------
1 | 1 | 1 | 0.15
-- New way
tbProductionLabour
Labour | fkCostCode
-------
REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
--------------
1 | REBAR | 1 | 0.15
This is a very basic example, but you get the idea of what I am referring to.
Any thoughts?
MikeI didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
The light! Don't look at the light!
I guess I'll add you to the "not preffering surrogates" group
http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx
Excuse me while I climb back upon my barst...um desk chair...yeah that's right...|||EDIT: Didn't we have this conversation already?|||EDIT: Didn't we have this conversation already?
Probably, but as an in-experienced developer (wannabe) I was wondering if not using a identity field will really make that great of a performance difference.
I think the char(20) for a primary key is better mainly because when I open a table I am not seeing a number which I will have to look up. It also would reduce the number of joins I would have to make (which I guess would be better for performance). But, for looking up values or joining on that field, would the performance reduction be neglable or significant enough to want to use the identity field?
Mike|||Let me ask you this...
What do you think would be faster.
A). An umpteen table join on surrogate keys to get the data, or
B). A SELECT against 1 Table|||Let me ask you this...
What do you think would be faster.
A). An umpteen table join on surrogate keys to get the data, or
B). A SELECT against 1 Table
I would assume option B, but let me ask you this.
What do you think would be faster:
A) Looking up values based on an integer
or
B) Looking up values based on an umpteen char string
?
Mike B|||OK, I'll give you 80/1000 of a second
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 sysname)
CREATE TABLE myTable00(Col1 sysname, Col2 int IDENTITY(1,1))
GO
DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
INSERT INTO myTable00(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
SELECT @.x = @.x + 1
END
INSERT INTO myTable99(Col1) SELECT 'Brett'
CREATE INDEX myIndex99 ON myTable99(Col1)
CREATE INDEX myIndex00 ON myTable00(Col2)
SELECT COUNT(*) FROM myTable99
DECLARE @.x1 datetime, @.y1 datetime, @.x2 datetime, @.y2 datetime
SELECT @.x1 = GetDate()
SELECT @.x1 AS systime, 'Starting int look up'
SELECT * FROM myTable00 WHERE Col2 = 216784
SELECT @.y1 = GetDate()
SELECT @.y1 AS systime, 'Endinging int look up'
SELECT @.x2 = GetDate()
SELECT @.x2 AS systime, 'Starting sysname look up'
SELECT * FROM myTable00 WHERE Col1 = 'Brett'
SELECT @.y2 = GetDate()
SELECT @.y2 AS systime, 'Endinging int look up'
SELECT DATEDIFF(ms,@.x1, @.y1), DATEDIFF(ms,@.x2, @.y2)
GO
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.
The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!
-PatP|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.
The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!
-PatP
Yeah, I pretty much aggree with that. The tables I am refering to will not be that big and they are kind of complex so I thought it would be best to use as many natural keys as possible. Of course there are places in my DB where I use the identity because I don't think the natural keys are all that good to use.
How many people use CompanyName as a natural key in a companies table?
I understand there maybe more then one CompanyName in the table but should they be unique by appending a number or geographical location, or using the CompanyName / Address as the primary key.
My thought is that it is best to use a surrogate here because to carry a company name and address as a forein key to other tables is probably costly.
There is the argument that the address can change but is that really a problem since you can specify "Cascade update related fields" on the other tables?
I would personally love to see something other then a number when I am looking at these tables but....
Mike B|||At least in my opinion, company name stinks as a primary key. We aren't all that big, but we have several hundred companies scattered wildly about North America with the same name, and on a worldwide basis it gets even worse.
JOINs are cheap. SQL Server makes them nearly free IF you keep the FK value small (INT or smaller) and you've got enough RAM in your server.
-PatP|||JOINs are cheap.
That's gotta be the most open ended statement I've heard in a while...
Also... "Company's with the same name scattered around"?
Either they truly are a different company, which means they are separate legal entity, or they are a site for a company...
Are you essentially saying that accessing 1 table would be slower than accessing many?|||Many are franchises, some just reuse common names in different jurisdictions. The net result is that if you look for companies with names like Subway or McDonald's you find hundreds of hits, most (but not all) with separate EIN values.
If you have to haul a fifty byte VARCHAR off the disk versus a four byte integer for every row in a 34 million row table, versus a join to a table cached in RAM, then the JOIN is cheaper than the single table. While SQL Server is good at hiding physical details from the user, some things are still big enough tasks so that smart design beats brute force every time.
-PatP
Subscribe to:
Posts (Atom)