Primary Key Vs. Not NULL Unique Key

Hi,
Can anybody tell me the difference between a
primary key constraint
and
Not NULL Unique Key constraint with clustered index
Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance.
One more question that's running in my mind is, if a clustered index is created on Unique key column, what will be the index key of a NULL value
Thanks very much in advanc
GYKOne difference is that the primary key constraint is not necessarily a
clustered index. As a matter of fact, I would suggest that in most cases
the clustered index should not be a unique constraint as it will typically
cause for slow inserts. It might be better to cluster by run time criteria,
such as what are the rows that need to be referenced at the same time (more
often than not), that way when the data page is brought into RAM it is all
time well spent. Other than that, I am unaware of any difference. Anybody
else?
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||Ata
First of all I have already answered this question in programming group but
going back to your suggestions
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
I always like to say it depends so
When you create a clustered index, try to create it as a UNIQUE clustered
index, not a non-unique clustered index. The reason for this is that while
SQL Server will allow you to create a non-unique clustered index, under the
surface, SQL Server will make it unique for you by adding a 4-byte
"uniqueifer" to the index key to guarantee uniqueness. This only serves to
increase the size of the key, which increases disk I/O, which reduces
performance. If you specify that your clustered index is UNIQUE when it is
created, you will prevent this unnecessary overhead.
"Ata" <NO_SPAMar_alias001@.NO_SPAMparvan.net> wrote in message
news:pOPKb.10951$JQ1.8335@.pd7tw1no...
> One difference is that the primary key constraint is not necessarily a
> clustered index. As a matter of fact, I would suggest that in most cases
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
>
> --
> Ata R
> Parvan Consulting Inc
> NO_SPAMar_alias001@.NO_SPAMparvan.net
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> > Hi,
> >
> > Can anybody tell me the difference between a
> >
> > primary key constraint
> > and
> > Not NULL Unique Key constraint with clustered index.
> >
> > Theoritically both looks the same, but am interested to know their
> differences in terms of their storage and performance.
> >
> > One more question that's running in my mind is, if a clustered index is
> created on Unique key column, what will be the index key of a NULL value?
> >
> > Thanks very much in advance
> > GYK
>|||Ata,
>It might be better to cluster by run time criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
As a side note, this behavior is often undesirable in high-volume OLTP
environments, creating very contentious pages at the 'bottom' of the table.
This phenomenon is known as 'hot-spotting'
In that case, a more intelligent choice of clustering key is needed.
James Hokes|||The primary key is a constraint held against the table
whereas the unique index is a separate object linked to
the table (not very important just changes the way they
are handled).
Conceptually the PK identifies a record whereas a unique
index just prevents duplicate values. So theoretically the
PK should never be updated (delete + insert if required)
but this is not enforced.
Some things that need to identify records will use the PK
and will not work unless one is defined.
>--Original Message--
>Hi,
>Can anybody tell me the difference between a
>primary key constraint
>and
>Not NULL Unique Key constraint with clustered index.
>Theoritically both looks the same, but am interested to
know their differences in terms of their storage and
performance.
>One more question that's running in my mind is, if a
clustered index is created on Unique key column, what will
be the index key of a NULL value?
>Thanks very much in advance
>GYK
>.
>|||Another difference is that you may ONLY have ONE PK constraint on a table,
but you may have MANY unique constraints.;
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||James,
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
Which behaviour is "this behavior". Is hot-spotting having a unique
clustered index or is it the approach I was suggesting. Pls explain?
Ata.
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:OQ86tvS1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Ata,
> >It might be better to cluster by run time criteria,
> > such as what are the rows that need to be referenced at the same time
> (more
> > often than not), that way when the data page is brought into RAM it is
all
> > time well spent. Other than that, I am unaware of any difference.
> Anybody
> > else?
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
> In that case, a more intelligent choice of clustering key is needed.
> James Hokes
>|||Another problem of non-unique clustered indexes is when
non-clustered indexes are also on the table. Another
respondent pointed out the additional overhead storing the
keys. Another issue is where you want to reindex either by
BDReindex or by Creating Index with the Drop existing
option. Doing either will require the non-clustered
indexes also be recreated affecting downtime/usability of
the table during the operation.sql

Primary key on combination of nullable fields, at least one not-null

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one):
CREATE FUNCTION ApplyActionPK(
@.IP int = NULL,
@.DNS varchar(64) = NULL
)
RETURNS varchar(74) -- NOT NULL
AS
BEGIN
declare @.val varchar(74)
set @.val = str(ISNULL(@.IP, 0), 10)
set @.val = @.val + ISNULL(@.DNS, '')
return @.val
-- Also tried "return str(ISNULL(@.IP, 0), 10)+ISNULL(@.DNS, '')"
-- Also tried "return ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
-- ... and other things...
END
GO
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as dbo.ApplyActionPK(ComputerID, DNS), -- PK value
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target)
)
SQL Server always complains that the primary key constraint cannot be
created over a nullable field - even though in no case will the 'Target'
field be NULL.
Please don't explain that I should store an IP address as a string.
Though that would suffice for this example, it doesn't solve my
actual problem (where there are four nullable fields, two of which
are FKs into other tables).
What's the reason for SQL Server deciding that the value is NULLable?
What's the usual way of handling such alternate PKs?
Clifford Heath.On Tue, 26 Apr 2005 15:49:23 +1000, Clifford Heath wrote:

>I have a case where a table has two candidate primary keys,
>but either (but not both) may be NULL. I don't want to store
>a copy of the concatenated ISNULL'ed fields as an additional
>column, though that would work if necessary. Instead, I tried
>the following (this is a related simplified example, not my
>real one):
(snip)
Hi Clifford,
I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...

>What's the reason for SQL Server deciding that the value is NULLable?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.

>What's the usual way of handling such alternate PKs?
One way around this would be to to change the table def as follows:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Another way is to include a surrogate key as primary key, and to declare
the Act, Target combination as a UNIQUE constraint. Or even omit the
computed column, ann declare (Act, IP, DNS) as UNIQUE constraint. The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be, but it is exactly what is needed for this case.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A primary key must be non-nullable, by definition. Create another table
for the entity identified by IP/DNS and then reference that table's key
in ApplyAction. Unfortunately, SQL Server doesn't support
ANSI-compliant UNIQUE and CHECK constraints so it is much harder than
it should be to guarantee integrity.
CREATE TABLE Devices (network_address VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_address IN (ip_address,dns_address) AND
COALESCE(ip_address,dns_address) IS NOT NULL) /* Key must be either IP
or DNS */)
GO
/* Views enforce nullable unique constraints */
CREATE VIEW devices_ip_address
WITH SCHEMABINDING
AS
SELECT ip_address
FROM dbo.Devices
WHERE ip_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_ip_address
ON devices_ip_address (ip_address)
GO
CREATE VIEW devices_dns_address
WITH SCHEMABINDING
AS
SELECT dns_address
FROM dbo.Devices
WHERE dns_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_dns_address
ON devices_dns_address (dns_address)
GO
David Portas
SQL Server MVP
--|||Clifford Heath (no@.spam.please.net) writes:
> What's the reason for SQL Server deciding that the value is NULLable?
Probably not a very good one. This is accepted in SQL 2005:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(IP,'')+ISNULL(DNS,'') persisted,
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Your UDF did not fly, because it had problems with determism. Not the
PERSISTED keyword, this is new for SQL 2005.
Unfortunately, the above is useless, as is Hugo's suggestion. Because
of the data-type precedence rules in SQL Server, DNS will be converted
to integer. Here is a version, ugly as it is, that works in SQL 2000:
create table ApplyAction4( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction4 PRIMARY KEY(Act, Target),
)

> What's the usual way of handling such alternate PKs?
Normally, I would go with an artificial primary key, typically an
identity column, and then have a UNIQUE constraint on (Act, IP, DNS).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hugo Kornelis wrote:
> I don't really understand the above - you say that you don't want to store
> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
> function) that concatenates the ISNULL'ed columns and add a computed
> column with the result of that UDF...
Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?

> The computed column is based on a UDF. The arguments to the UDF can be
> NULL. From that, SQL Server concluded that the result might be NULL as
> well. SQL Server won't check the source of the UDF for this, so regardless
> of what you change in the UDF, the problem will persevere.
However it *does* check the UDF for determinism. Plus, the return value
is defined to be VARCHAR, not VARCHAR NULL - which you can't declare :-(
so I'd expect SQL Server to enforce that a non-null value was returned.

> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),

It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.

> Another way is to include a surrogate key as primary key
Didn't want to do that. I like to have PRIMARY declared on my natural
keys, and use unique constraints on the synthetic key, if any. Plus,
our code generator prefers things that way, though it works both ways.
:-)

> The way
> SQL Server treats NULL values in a UNIQUE constraint is not as I would
> like it to be
Nor is it what's documented in BOL :-(. Been there, fallen over that...|||Erland Sommarskog wrote:
> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
Bingo! Convert() rather than Str().
I don't suppose I'm the only one surprised that these aren't equivalent?
Thanks everyone,
Clifford.|||On Wed, 27 Apr 2005 14:35:20 +1000, Clifford Heath wrote:

>Hugo Kornelis wrote:
>Without having checked, I assumed that the UDF would be called whenever
>a value was desired. I assume you're telling me that the value will be
>computed at INSERT or UPDATE and stored, not computed when needed?
Hi Clifford,
Yes and no :-)
Normally, a computed column is not computed at INSERT and UPDATE time and
not stored in the database; instead, the expression is evaluated when data
is read from the table. But this changes when you include the computed
column in an index - as soon as you do that, the expression will be
evaluated on INSERT and UPDATE and the result will be stored.
As far as I know, this behaviour is not different when the computed column
is based on a UDF.

>It appears I was close. Erland's version is identical except for using
>CONVERT instead of STR, and is preferable to yours.
Yep, you was. And so was I :-) Somehow, somewhere along the line I left
out the STR (which was included in your original version). I'm glad Erland
noticed that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clifford Heath (no@.spam.please.net) writes:
> Erland Sommarskog wrote:
> Bingo! Convert() rather than Str().
> I don't suppose I'm the only one surprised that these aren't equivalent?
I will have to admit that I have banged my head against that one as
well. But if you look at the syntax for str(), it's all clear:
STR ( float_expression [ , length [ , decimal ] ] )
Anything with float in it is imprecise and indeterministic, and a computed
column with a float expression in it - directly or indirectly - cannot be
indexed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Primary key on combination of nullable fields, at least one not-null

I have a case where a table has two candidate primary keys,
but either (but not both) may be NULL. I don't want to store
a copy of the concatenated ISNULL'ed fields as an additional
column, though that would work if necessary. Instead, I tried
the following (this is a related simplified example, not my
real one):
CREATE FUNCTION ApplyActionPK(
@.IP int = NULL,
@.DNS varchar(64) = NULL
)
RETURNS varchar(74) -- NOT NULL
AS
BEGIN
declare @.val varchar(74)
set @.val = str(ISNULL(@.IP, 0), 10)
set @.val = @.val + ISNULL(@.DNS, '')
return @.val
-- Also tried "return str(ISNULL(@.IP, 0), 10)+ISNULL(@.DNS, '')"
-- Also tried "return ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
-- ... and other things...
END
GO
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as dbo.ApplyActionPK(ComputerID, DNS), -- PK value
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target)
)
SQL Server always complains that the primary key constraint cannot be
created over a nullable field - even though in no case will the 'Target'
field be NULL.
Please don't explain that I should store an IP address as a string.
Though that would suffice for this example, it doesn't solve my
actual problem (where there are four nullable fields, two of which
are FKs into other tables).
What's the reason for SQL Server deciding that the value is NULLable?
What's the usual way of handling such alternate PKs?
Clifford Heath.On Tue, 26 Apr 2005 15:49:23 +1000, Clifford Heath wrote:
>I have a case where a table has two candidate primary keys,
>but either (but not both) may be NULL. I don't want to store
>a copy of the concatenated ISNULL'ed fields as an additional
>column, though that would work if necessary. Instead, I tried
>the following (this is a related simplified example, not my
>real one):
(snip)
Hi Clifford,
I don't really understand the above - you say that you don't want to store
the concatenated ISNULL'ed columns, then you present a UDF (user-defined
function) that concatenates the ISNULL'ed columns and add a computed
column with the result of that UDF...
>What's the reason for SQL Server deciding that the value is NULLable?
The computed column is based on a UDF. The arguments to the UDF can be
NULL. From that, SQL Server concluded that the result might be NULL as
well. SQL Server won't check the source of the UDF for this, so regardless
of what you change in the UDF, the problem will persevere.
>What's the usual way of handling such alternate PKs?
One way around this would be to to change the table def as follows:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Another way is to include a surrogate key as primary key, and to declare
the Act, Target combination as a UNIQUE constraint. Or even omit the
computed column, ann declare (Act, IP, DNS) as UNIQUE constraint. The way
SQL Server treats NULL values in a UNIQUE constraint is not as I would
like it to be, but it is exactly what is needed for this case.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A primary key must be non-nullable, by definition. Create another table
for the entity identified by IP/DNS and then reference that table's key
in ApplyAction. Unfortunately, SQL Server doesn't support
ANSI-compliant UNIQUE and CHECK constraints so it is much harder than
it should be to guarantee integrity.
CREATE TABLE Devices (network_address VARCHAR(64) PRIMARY KEY,
ip_address VARCHAR(15) NULL, dns_address VARCHAR(64) NULL, CHECK
(network_address IN (ip_address,dns_address) AND
COALESCE(ip_address,dns_address) IS NOT NULL) /* Key must be either IP
or DNS */)
GO
/* Views enforce nullable unique constraints */
CREATE VIEW devices_ip_address
WITH SCHEMABINDING
AS
SELECT ip_address
FROM dbo.Devices
WHERE ip_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_ip_address
ON devices_ip_address (ip_address)
GO
CREATE VIEW devices_dns_address
WITH SCHEMABINDING
AS
SELECT dns_address
FROM dbo.Devices
WHERE dns_address IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_devices_dns_address
ON devices_dns_address (dns_address)
GO
--
David Portas
SQL Server MVP
--|||Clifford Heath (no@.spam.please.net) writes:
> What's the reason for SQL Server deciding that the value is NULLable?
Probably not a very good one. This is accepted in SQL 2005:
create table ApplyAction( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(IP,'')+ISNULL(DNS,'') persisted,
CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target),
)
Your UDF did not fly, because it had problems with determism. Not the
PERSISTED keyword, this is new for SQL 2005.
Unfortunately, the above is useless, as is Hugo's suggestion. Because
of the data-type precedence rules in SQL Server, DNS will be converted
to integer. Here is a version, ugly as it is, that works in SQL 2000:
create table ApplyAction4( -- An action applies to a computer
Act varchar(16) NOT NULL, -- The action to apply
IP int NULL, -- The computer IP address, or
DNS varchar(64) NULL, -- The DNS name of the computer
Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
-- Also tried "Target as ISNULL(STR(@.IP, 10), ISNULL(@.DNS, ''))"
CONSTRAINT PK_ApplyAction4 PRIMARY KEY(Act, Target),
)
> What's the usual way of handling such alternate PKs?
Normally, I would go with an artificial primary key, typically an
identity column, and then have a UNIQUE constraint on (Act, IP, DNS).
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hugo Kornelis wrote:
> I don't really understand the above - you say that you don't want to store
> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
> function) that concatenates the ISNULL'ed columns and add a computed
> column with the result of that UDF...
Without having checked, I assumed that the UDF would be called whenever
a value was desired. I assume you're telling me that the value will be
computed at INSERT or UPDATE and stored, not computed when needed?
> The computed column is based on a UDF. The arguments to the UDF can be
> NULL. From that, SQL Server concluded that the result might be NULL as
> well. SQL Server won't check the source of the UDF for this, so regardless
> of what you change in the UDF, the problem will persevere.
However it *does* check the UDF for determinism. Plus, the return value
is defined to be VARCHAR, not VARCHAR NULL - which you can't declare :-(
so I'd expect SQL Server to enforce that a non-null value was returned.
> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
It appears I was close. Erland's version is identical except for using
CONVERT instead of STR, and is preferable to yours.
> Another way is to include a surrogate key as primary key
Didn't want to do that. I like to have PRIMARY declared on my natural
keys, and use unique constraints on the synthetic key, if any. Plus,
our code generator prefers things that way, though it works both ways.
:-)
> The way
> SQL Server treats NULL values in a UNIQUE constraint is not as I would
> like it to be
Nor is it what's documented in BOL :-(. Been there, fallen over that...|||Erland Sommarskog wrote:
> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
Bingo! Convert() rather than Str().
I don't suppose I'm the only one surprised that these aren't equivalent?
Thanks everyone,
Clifford.|||On Wed, 27 Apr 2005 14:35:20 +1000, Clifford Heath wrote:
>Hugo Kornelis wrote:
>> I don't really understand the above - you say that you don't want to store
>> the concatenated ISNULL'ed columns, then you present a UDF (user-defined
>> function) that concatenates the ISNULL'ed columns and add a computed
>> column with the result of that UDF...
>Without having checked, I assumed that the UDF would be called whenever
>a value was desired. I assume you're telling me that the value will be
>computed at INSERT or UPDATE and stored, not computed when needed?
Hi Clifford,
Yes and no :-)
Normally, a computed column is not computed at INSERT and UPDATE time and
not stored in the database; instead, the expression is evaluated when data
is read from the table. But this changes when you include the computed
column in an index - as soon as you do that, the expression will be
evaluated on INSERT and UPDATE and the result will be stored.
As far as I know, this behaviour is not different when the computed column
is based on a UDF.
>> Target as ISNULL(ISNULL(IP,'')+ISNULL(DNS,''),''),
>It appears I was close. Erland's version is identical except for using
>CONVERT instead of STR, and is preferable to yours.
Yep, you was. And so was I :-) Somehow, somewhere along the line I left
out the STR (which was included in your original version). I'm glad Erland
noticed that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clifford Heath (no@.spam.please.net) writes:
> Erland Sommarskog wrote:
>> Target as ISNULL(convert(varchar(11), IP),'')+ISNULL(DNS,''),
> Bingo! Convert() rather than Str().
> I don't suppose I'm the only one surprised that these aren't equivalent?
I will have to admit that I have banged my head against that one as
well. But if you look at the syntax for str(), it's all clear:
STR ( float_expression [ , length [ , decimal ] ] )
Anything with float in it is imprecise and indeterministic, and a computed
column with a float expression in it - directly or indirectly - cannot be
indexed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Primary key allows null?

XP Pro / Access 2003 Project / SQL Server 2000 Backend
I have a table that I have assigned multiple fields as the primary key(s).
There are about 6 fields that make up the primary key for this table. When
entering a new record into the table the primary key forces me to enter all
the data fields for the primary key. BUT.. if I were to copy a record and
remove the data from one of the PK fields, SQL Server accepts this. How is
this possible? I have this field designated as PK and it cannot accept a
null, but it does. Any help on this would be much appreciated.Mark wrote:
> XP Pro / Access 2003 Project / SQL Server 2000 Backend
> I have a table that I have assigned multiple fields as the primary key(s).
> There are about 6 fields that make up the primary key for this table. Whe
n
> entering a new record into the table the primary key forces me to enter al
l
> the data fields for the primary key. BUT.. if I were to copy a record an
d
> remove the data from one of the PK fields, SQL Server accepts this. How i
s
> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Depends what you mean by "remove all the data". If it's a string then
it can be empty. An empty string isn't null. If it's a numeric then it
can be zero. Did you run a query to check what the value *really* is in
the table rather than just look at what you are shown in a grid
control?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Are you sure? Are you certain that a unique constraint is not being used as
a PK (which will allow nulls)? Below is a simple proof.
set nocount on
-- this will fail
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx primary key (id1, id2) )
go
-- this will succeed
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx unique (id1, id2) )
go
-- 1 of 2 will succeed
insert #test (id1, id2, other_junk) values (1, 1, 'test')
insert #test (id1, id2, other_junk) values (1, 1, 'test')
go
-- all will succeed
insert #test (id1, id2, other_junk) values (1, 2, 'test')
insert #test (id1, id2, other_junk) values (2, 1, 'test')
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will fail
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will succeed
update #test set id2 = null where id1 = 2 and id2 = 1
go
-- final result
select * from #test
go
drop table #test
go

Primary key allows null?

XP Pro / Access 2003 Project / SQL Server 2000 Backend
I have a table that I have assigned multiple fields as the primary key(s).
There are about 6 fields that make up the primary key for this table. When
entering a new record into the table the primary key forces me to enter all
the data fields for the primary key. BUT.. if I were to copy a record and
remove the data from one of the PK fields, SQL Server accepts this. How is
this possible? I have this field designated as PK and it cannot accept a
null, but it does. Any help on this would be much appreciated.Mark wrote:
> XP Pro / Access 2003 Project / SQL Server 2000 Backend
> I have a table that I have assigned multiple fields as the primary key(s).
> There are about 6 fields that make up the primary key for this table. When
> entering a new record into the table the primary key forces me to enter all
> the data fields for the primary key. BUT.. if I were to copy a record and
> remove the data from one of the PK fields, SQL Server accepts this. How is
> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Depends what you mean by "remove all the data". If it's a string then
it can be empty. An empty string isn't null. If it's a numeric then it
can be zero. Did you run a query to check what the value *really* is in
the table rather than just look at what you are shown in a grid
control?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> this possible? I have this field designated as PK and it cannot accept a
> null, but it does. Any help on this would be much appreciated.
Are you sure? Are you certain that a unique constraint is not being used as
a PK (which will allow nulls)? Below is a simple proof.
set nocount on
-- this will fail
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx primary key (id1, id2) )
go
-- this will succeed
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx unique (id1, id2) )
go
-- 1 of 2 will succeed
insert #test (id1, id2, other_junk) values (1, 1, 'test')
insert #test (id1, id2, other_junk) values (1, 1, 'test')
go
-- all will succeed
insert #test (id1, id2, other_junk) values (1, 2, 'test')
insert #test (id1, id2, other_junk) values (2, 1, 'test')
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will fail
insert #test (id1, id2, other_junk) values (1, null, 'test')
go
-- this will succeed
update #test set id2 = null where id1 = 2 and id2 = 1
go
-- final result
select * from #test
go
drop table #test
go

Primary Key

Please help:

I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo

... what would be the primary key?! I know it would be DateTo and sumfing..... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"

... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to?

Thanks...... but I need a PK to make sure a bonus is not entered twice... not entered twice for what?

if you pay a $50 bonus, does this mean you will never pay another bonus for $50? of course not!

so you have to ask yourself how do you identify the separate entities that are getting the bonus

what are these things, anyway? products? you want to pay a bonus on a product?

don't the product headings relate to a product table or something?

can the same three product headings occur again on different dates?

you need to give some more thought to exactly what this table represents|||Soz sounds like i aint explained the table properly!!
headings are product groups...
eg.

Heading1 - Heading2 - Heading3- ProductID
Clothing - T-shirt - Lady - 705921

Bonus (or Commission) is likely to be a percentage value, the sales team get for the products that fall into that heading...
eg Clothing might have an overall value of 20%
while Clothing - T-shirt - Lady might have a specfic value of 10% for 10/10/04 to 10/10/06 and then Clothing - T-shirt - Lady has value of 30% 11/10/06 - 10/10/07...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

you should really be putting the bonus on the product group in the product group table

and if you don't have a product group table, you should|||Sounds like time for a new table:

ProductID Bonus DateFrom DateTo


Unless you plan on offering different bonus amounts for the same product id in overlapping data ranges.

Or do you change the ProductID and have the same product under 2 (or more) ProductID numbers? (I once worked backend for a MAJOR big-box retailer ... that's how their folks handled discounted items ... same thing with different product id ... didn't agree with it, but was in no position to voice my concerns!)|||[QUOTE=tomh53]Sounds like time for a new table:

ProductID Bonus DateFrom DateTo


QUOTE]

that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...

a product can onli be in 1 heading group, no overlapping...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

you should really be putting the bonus on the product group in the product group table

and if you don't have a product group table, you should

yup, its hierarchical...but what would the product group table have:

GroupID , heading1, heading2, heading3, bonus

BUT what do I do when the bonus is on

heading1, heading2... and not the whole Group?!|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)

a typical hierarchical structure, known as the adjacency model

see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure

link each product to whichever product group it belongs to

product groups at the top of their hierarchy have parent_id null|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)

a typical hierarchical structure, known as the adjacency model

see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure

link each product to whichever product group it belongs to

product groups at the top of their hierarchy have parent_id null

THANKS...looks promising (yet hard... think there is a lot of data so be a while 2 populate the table... currently they are just normal columns in my product table!) I'll give it a try and let u no how it goes...

Thanks agian :)|||sounds like it's time to normalize the model|||can i not write a insert query something lik this?!:

INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)

basically I want to insert what is in my subquery in2 ma new table?!|||sounds like it's time to normalize the model

Someone else created the database...lucki me... i remba how much i LUVED normalisation at uni :S

Thanks :)|||Stucky,

Please read the sticky (is he a relation of yours) at the top of this board, and post your question in the terms that it asks for.

Probably will get a solution in, oh say, 5 minutes|||can i not write a insert query something lik this?!:

INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)

basically I want to insert what is in my subquery in2 ma new table?!yes, you can write a query likje that

not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two

but you are on the right track

however, something is of great concern -- you have 3 headings in the product table?

this database is going to require a lot of redesign work...|||Stucky,

(is he a relation of yours)

nope :) just good minds fink alike :p

thanks again :)|||not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two

this database is going to require a lot of redesign work...

yeh onli supplied 2 cos i made the id an identity so gets assigned by system!!

yeh it does seem lik it will be a long job... I have created the table and filled it wiv sum records... will create the front end (VB.Net) before filling the rest... just in chance the project spec gets changed!! :p

Thanks for all the quick responses :) :angel:|||that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...

a product can onli be in 1 heading group, no overlapping...

Just a thought ... will the bonus be cumulative or exclusive?

For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?|||Just a thought ... will the bonus be cumulative or exclusive?

For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?

exclusive i fink?!|||Hey ... im bak LOLS!!!

well now i have created the application which saves the headingID and bonus.... (Quick refresh: 1 product has three heading levels - the new app assigns a bonus to a heading at ANY level)

I have to now write an update query that will calculate the Bonus for each transaction on the products.... where it selects the Bonus for the heading (and makes sure the transaction date is between bonus dateTo and bonus dateFrom)... this is all fine!

The problem is the update query should be:

If heading 1 has an assigned bonus, the transaction should be calcuted using it
else if Heading2 has an assigned bonus, use this
else if heading3 has an assigned bonus, use this
else bonus is Null

how would I write this case select so that it stops as soon as it finds an assigned bonus???

THANKS :)|||hey, ive got a similar problem. actually, if i start explaining the exact situation, ppl will get confused.

but anyway, i only want to say that this situation is absolutely possible.

the problem is if we make them as a composite primary key, all attribs which are part of the primary key have not null constraints.

is there a way to create composite keys such that either of the attributes must have not null constraint or are the databases built in such a fashion to support only the former?

i think the only other way out would be to provide some default values to prevent the violation of not null!!! this method is very local(i mean non-standard) i guess.|||i still think the hierarchy is the better design

no problem with nulls then

see post #8 in this thread|||Hi

I have used the hierarchy table to create a table which has the headingID and assigned bonus(productheadings)... what I'm stuck wiv is, how do I use this table to calculate the bonus for each transaction (Calculted bonus = sales*assignedbonus)...

Each transaction is with a product which always has 3 headings...
what i need to do is
1) check if heading1 has an assigned bonus (within transaction date)
2) If not, check heading2
3) if no bonus for heading2, check for heading3

If a bonus is assigned at any of the 3 stages, the query should calculate bonus and stop (or else bonus column in link table is null)... I dont no how to write this part of the query...
If I was only looking at 1 heading I would write something like:
(SALES TABLE - where I would store TranscationID and calculated bonus
SALES_STOCK - product table
productheadings - heading table
SALES_STOCK_BONUS - Link table... where I would store headingID and calculated bonus...

SELECT SALES.Product, Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3)
inner join SALES_STOCK_BONUS on
SALES_STOCK_BONUS.id = productheadings.id
Group by SALES.Product, Bonus, SALES.[Date], SALES_STOCK_BONUS.DateFrom, SALES_STOCK_BONUS.DateTo
having SALES.[Date] >= SALES_STOCK_BONUS.DateFrom and SALES.[Date] <= SALES_STOCK_BONUS.DateTo

how would I change this query so that it is in a if loop.... please help!!!|||dear mr/ms stuck1234

please can you show your exact table layout

in one sentence you say you have created the hierarchy table, but in the next sentence you say "Each transaction is with a product which always has 3 headings"

what is going on? i fail to understand|||productheading hierarchy table has
headingID, dateto, datefrom, bonus, headinglevel (ie 1, 2,3)

SALES_STOCK
PK - ProductID, WHse
- here there are 3 headings associated with each product

SALES
PK - transaction table which should contain a column CalculatedBonus = (productheadings.Bonus * Sale /100)

I can see that the query I wrote is all wrong, it has an extra table in it...should a bit more like:

SELECT SALES.Product, productheadings.Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3) -- This bit is wrong.... how do get it 2 do the if statement I explained above
Group by SALES.Product, productheadings.Bonus, SALES.[Date], productheadings.DateFrom, productheadings.DateTo
having SALES.[Date] >=productheadings.DateFrom and SALES.[Date] <= productheadings.DateTo|||Would this work:

DECLARE @.Date INT
SET @.Date = (Select Date from SALES)
SET @.ID1 = (Select ID1 from SALES)
SET @.ID2 = (Select Date from SALES)
SET @.ID3 = (Select Date from SALES)

If (select Bonus from productheadings where ID = @.ID1
and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
ELSE
If (select Bonus from productheadings where ID = @.ID2 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
ELSE
If (select Bonus from productheadings where ID = @.ID3 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
END

I will check 2moro if it wrks... but I got a feeling the if statement is right...but declaring the variables at the top is wrong... but how else would i get that data for each line...|||you did not understand the hierarchy table at all

i am sorry, but i cannot help you any further

good luck|||The structure I sed yesda was wrong soz...

this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus

Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...

I have seemed to have got majorly confused on this last night.... :(|||The structure I sed yesda was wrong soz...

this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus

Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...

I have seemed to have got majorly confused on this last night.... :(|||I just wana know if my thinkin of this is right?!

SALES_STOCK now has a foreign key called headingID which is linked to productheadings.id... headingID will be the ID of heading1... so now i need to write a query which looks for the Bonus (in SALES_STOCK_BONUS) for headingID, which is the NODE in the hierarchy table, if NULL then look at node child1, if NULL look for Bonus in node child2...

Is this correct line of thought??

Primary key

I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
as Primary key and by default also clustered index.
I use this ID in my INNER JOINS with order items to connect them.
I have also orderDate column in my order table, which is datetime field..
A lot of my queries include search or order condition by date, for example,
simplified one:
SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
o.orderDate
Now I would like to speed up the execution of this query.
I have 3 options:
1: orderDate as Primary key (it will be clustered index) and ORDER_ID not in
any index
2: orderDate+ORDER_ID as Primary key
3:orderDate as Primary key and nonclustered index on ORDER_ID column
Now, date will be in clustered index and select will be much faster, because
date is usually in where and order parts of query.
Order_ID is usually only in join conditions, so, I think it's not so
important to be as clustered index - if, than it should be append to date
column and both will present clustered index.
What is yours opinion?
Any suggestions, expirience with that?
Thank you,
SimonLet's start with basics. An IDENTITY columns can not be a key by
definition. It is not an attribute in the data model, but an exposed
physical locator for the physical storage of the data. It cannot be
validated or verified.
Newbies use it because they don't know what a key is and this looks
like a pointer or record number. Next, rows are not records and columns
are not fields.
It looks like you use date *ranges*, so a clustered index on the date
column would help quite a bit.
But order_id sounds like the natural key for an Orders table (once you
make it a real data type, add a check digit or validation rule, etc.).
There are primary indexes -- those required to enforce business rules
(UNIQUE, PRIMARY KEY) and secondary indexes -- those added for
performance. You have one of each.
As an aside, other products like Sybase will see the PK-FK relationship
between Orders and OrderItems and build a pointer structure that will
"pre-join" them and things will much faster.
In SQL Server you currently have to add indexing to the referencing
table on your own. The original design of SQL Server was done by
people who mapped tables to single files rather than viewing the schema
as a whole. This is why I keep beating people up about confusing
files/records/fields with tables/rows/columns; a bad mental model leads
to bad code.|||Everything Joe Celko said. I'll add a suggestion for generating your order
number (which you *should* be using as a PK). Your order number should be a
"smart key" -- ie, it will actually convey info about your order, unlike
Order #1702. Here's an exmple technique.
Find the magnitude of average orders per day and add one. If you see 40-70
orders, then your magnitude will be three.
You have two options from here. I'd go by your customer/order ratio. If it
is above 0.1 (which I would guess), then don't cater to only a handful of
customers, so a date is better to embed in the order number. Otherwise,
you'll want to embed the customer number.
Normal Ratio: Y{1,2}DDD-S+
Low Ration: C+-S+
Let's explore ...
Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005). It
all depends on how long you need to remember orders.
DDD is day of year. By doing this instead of MMDD, you save a digit. Not
for disk space, for short term memory.
S+ is the daily sequence number. The extra magnitude is for growth.
C+ is your customer number
- helps to split the number (mentally) and make it easier to remember.
Now when you cluster your PK (Order Num), it's ordered by the info you need.
And you can always index the other (Date).
-- Alex Papadimoulis
"simon" wrote:

> I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
> as Primary key and by default also clustered index.
> I use this ID in my INNER JOINS with order items to connect them.
> I have also orderDate column in my order table, which is datetime field..
> A lot of my queries include search or order condition by date, for example
,
> simplified one:
> SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
> WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
> o.orderDate
> Now I would like to speed up the execution of this query.
> I have 3 options:
> 1: orderDate as Primary key (it will be clustered index) and ORDER_ID not
in
> any index
> 2: orderDate+ORDER_ID as Primary key
> 3:orderDate as Primary key and nonclustered index on ORDER_ID column
> Now, date will be in clustered index and select will be much faster, becau
se
> date is usually in where and order parts of query.
> Order_ID is usually only in join conditions, so, I think it's not so
> important to be as clustered index - if, than it should be append to date
> column and both will present clustered index.
> What is yours opinion?
> Any suggestions, expirience with that?
> Thank you,
> Simon
>
>
>|||> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
I don't know. I agree with your order number, I just still like to use
identity values for primary keys, with a unique key on things like this
order_id. There are quite a few positive reasons to do so (performance
being one, and development pattern simplification being another) and really,
as long as you have a natural key, it is an exceptionally useful way to have
a non-changing key. And there is never a need to modify a primary key,
which is usually a real pain.
I have never heard an argument against identities that made enough sense to
balance out the ease of use. I certainly will never agree that they are
"exposed physical locators" but I will agree that they cannot be "validated
or verified" as Joe Celko has said. If they were physical locators they
would change as the physical storage of a row was moved. They aren't. They
are not a value I would share with the user, but a convienience in
development that keeps key size managable.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:0C0B35F4-07FC-416F-BA3E-C398C4D30525@.microsoft.com...
> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
> "smart key" -- ie, it will actually convey info about your order, unlike
> Order #1702. Here's an exmple technique.
> Find the magnitude of average orders per day and add one. If you see 40-70
> orders, then your magnitude will be three.
> You have two options from here. I'd go by your customer/order ratio. If it
> is above 0.1 (which I would guess), then don't cater to only a handful of
> customers, so a date is better to embed in the order number. Otherwise,
> you'll want to embed the customer number.
> Normal Ratio: Y{1,2}DDD-S+
> Low Ration: C+-S+
> Let's explore ...
> Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005).
> It
> all depends on how long you need to remember orders.
> DDD is day of year. By doing this instead of MMDD, you save a digit. Not
> for disk space, for short term memory.
> S+ is the daily sequence number. The extra magnitude is for growth.
> C+ is your customer number
> - helps to split the number (mentally) and make it easier to remember.
> Now when you cluster your PK (Order Num), it's ordered by the info you
> need.
> And you can always index the other (Date).
> -- Alex Papadimoulis
> "simon" wrote:
>|||On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:

> I certainly will never agree that they are
> "exposed physical locators" but I will agree that they cannot be "validate
d
> or verified" as Joe Celko has said. If they were physical locators they
> would change as the physical storage of a row was moved. They aren't.
Mr. Celko's use of the word "physical" is still a higher level than what
most people think of as physical. It's higher than magnetic spins on the
hard drive; higher than sectors on the hard drive; higher than bytes in the
file on the filesystem; higher even than the structure inside a DAT file.
Anything that can't be ported from one platform to the next strictly with
SQL statements is "physical" in Mr. Celko's view, because it's part of the
implementation that might change in the next release of the software. Since
the Identity() attribute of a column is specific to MS SQL Server, and
requires internal code to run at the time of insert, it's physical in that
sense. And, since the identity value may be different depending on the
order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
the query optimizer may reorder however it likes), it really has nothing
whatsoever to do with the values in the row (or as Mr. Celko would want me
to think, nothing to do with the actual identity of the entity that the
table represents).
As a surrogate key, yes, they're awfully convenient. But I've found that
when I take the trouble to use real keys in my schema, all my code ends up
simplified, not complexified.|||Thanks very much Ross, for your translation of what Joe means when he says
'physical'. It drives me up the wall every time I read a message from him
telling someone that an IDENTITY is a physical locator for the physical
storage. I don't think Joe understands anything about SQL Server real
physical storage. But now knowing that Joe means something entirely
different when he uses that term, I will stop pulling my hair out. But we
will have to be ever vigilant to make sure new users know that when used by
Joe, 'physical' does not mean what they think it means.
Thanks again...
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1xbcau9ino4a7$.dlg@.rpresser.invalid...
> On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
>
> Mr. Celko's use of the word "physical" is still a higher level than what
> most people think of as physical. It's higher than magnetic spins on the
> hard drive; higher than sectors on the hard drive; higher than bytes in
> the
> file on the filesystem; higher even than the structure inside a DAT file.
> Anything that can't be ported from one platform to the next strictly with
> SQL statements is "physical" in Mr. Celko's view, because it's part of the
> implementation that might change in the next release of the software.
> Since
> the Identity() attribute of a column is specific to MS SQL Server, and
> requires internal code to run at the time of insert, it's physical in that
> sense. And, since the identity value may be different depending on the
> order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
> the query optimizer may reorder however it likes), it really has nothing
> whatsoever to do with the values in the row (or as Mr. Celko would want me
> to think, nothing to do with the actual identity of the entity that the
> table represents).
> As a surrogate key, yes, they're awfully convenient. But I've found that
> when I take the trouble to use real keys in my schema, all my code ends up
> simplified, not complexified.|||>> I'll add a suggestion for generating your order number (which you
Can you explain how and why it is beneficial to use a "smart key" for an
identifier? Are you aware of any drawbacks of using such "smart" or
intelligent keys?
Anith|||>> But we will have to be ever vigilant to make sure new users know that
Then OTOH, we will have to be aware that the ones using the terms "physical
table", "physical row", "physical column" etc do not mean what they think it
means either :-)
Anith|||Anith,
According to some (such as Louis, who replied earlier), the draw backs are:
> performance
> storage size
> they can change
But let's think about each of those. Are they "real" problems?
Performance. Did you know, your app can shave maybe 30ns if you forgo
database technology altogether. And loops (for, while, etc) -- you can easil
y
save a few clock cycles by not using them. Never accept "performance" as a
reason for doing something unless there are real world data to back this
claim up. I could make silly performance articles about AutoID as well -- th
e
system has to take extra cycles to generate the ID, check that you don't try
to insert it, etc.
Storage Size. The key I suggested was 8 bytes (YDDDSSSS). This would allow
for 999 orders a day for 10 years. That's well over 3 million orders. If you
wanted to do the same with an auto ID, you'd need a bigint (8 bytes).
Whoops. Let's compare a smaller key (YDDDSS, 6 bytes) versus int (4 bytes).
Even if we max out the int (at 2.15 Million), we save a whopping 4.3 Million
bytes. How about we just delete "solitare" instead of worrying about this?
They can change. Oh this is my favorite. How many times has amazon.com told
you "dear customer, we're sorry, but your order number has changed from
21040204-a34 to 24030204-a34." Find me a case where your PK will change, and
I'll show you a poorly designed system.
AutoIDs should remain in MS Access. They're good for one thing -- whipping
together a quick and dirty database. The whole point of "Relational"
databases is to allow data to relate to other data by the data iteself (keys
)
instead of these artificial AutoIDs.
-- Alex Papadimoulis
"Anith Sen" wrote:

> Can you explain how and why it is beneficial to use a "smart key" for an
> identifier? Are you aware of any drawbacks of using such "smart" or
> intelligent keys?
> --
> Anith
>
>|||I also react to the "academic" view of Primary Keys... One argument that
always irritates me is the idea that Identitys are bad because they have no
relationship or connection to the entity in the row... (It is not an
attribute in the data model) But then I will see it argued by the same
individual, that Social Security Number, or CustomerNo, or PartNumber (all
constructed artificuially by a third party) IS an appropriate key! (... But
order_id sounds like the natural key...)
Any value that uniquely identifies a row, imho, is a suitable candidate for
a Key. That value Must be constructed... either from meaningful data, or
from non-meaningful data. If you choose to construct it from meaningful dat
a
(Attributes in the data model) Then you ALWAYS have the problem of picking a
n
attribute (or set of attributes) whose values are least likely to change -
AND the isssue of propagating changes when the real-world values of the
attributes for that row DO change, (And they always will - because nothing i
n
the real-world is 100% fixed, no matter what the academics think.)
If you choose a non-meaningful key, how it is constructed - whether you use
a Identity, or some arbitrary algorithm, doesn't really matter, as long as
you can guarantee uniqueness. And Identities do that quite nicely.
"Anith Sen" wrote:

> Then OTOH, we will have to be aware that the ones using the terms "physica
l
> table", "physical row", "physical column" etc do not mean what they think
it
> means either :-)
> --
> Anith
>
>