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 Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,
Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh
|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
sql

Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance o
f
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance
of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA

Primary Key Non-Cluster Index

Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA

Preview is better than web browser in this case

I like the functionality in the preview - that if all the parameters
have a valid value... it starts the query without pressing 'View
Report'.
Whereas in a published report in a browser, I always have to press the
'view report'.
In some cases one is better than the other - specially if the query is
small... but navigation needs to be faster.
I have repeatedly asked this question - without any answers.
But if it works in Preview... why not in the browser...'
Seems like there is setting... but no documentation...
I think we need both the options.Yes, we have struggled to answer this questions ourselves. We keep coming
back to this and will take your comments into consideration for sure. To
explain:
The problem is that on the server, there is really no way to stop the
execution of the query that was spawned by requesting a particular report.
So if you have all the defaults specified, then (for a live report) the
server will immediately kick off a report execution. This will inturn
execute the query. That query could take some time to run. Now, if the
user wants to change the values from default before the report finishes
executing, s/he might just click stop on the browser, change the paramters,
and click view report. Now due to how the browser works, the server
doesn't know the user has changed their minds. To the server it is just
another request. So now you have 2 report executions occuring for the same
user. The orignal request eventually will be killed on the report server
(once we realize the connection is closed/stale) but that still leaves the
long running query on the DB server.
So in effect by automatically running the report with the default parameters
you're increasing load significantly on the server. Now imagine how this
affects the system when you scale to 1000's of users.
In the designer, you don't have the same problem so you're more free to do
increase responsivness.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407221045.28cb83ac@.posting.google.com...
>I like the functionality in the preview - that if all the parameters
> have a valid value... it starts the query without pressing 'View
> Report'.
> Whereas in a published report in a browser, I always have to press the
> 'view report'.
> In some cases one is better than the other - specially if the query is
> small... but navigation needs to be faster.
> I have repeatedly asked this question - without any answers.
> But if it works in Preview... why not in the browser...'
> Seems like there is setting... but no documentation...
> I think we need both the options.|||I would say - give those controls to the designer.
Let him/her decide -
1. On Default parameters - should execute immediately or not
2. On Change selection - should execute immediately or not
- parameter wise control. I.e. decide whether a particular
parameter should execute the report immediately or not
Provide with a control with each parameter.
Cascading parameters should have that option also - Specially if
selection of Parameter 1 leads to default of Parameter 2.
- In which case - first of all the "dependency" should be explicitly
defined.
- And then - whether to execute the report immediately or not - could
also be a designers choice.
Just some thoughts...

Preventing history snapshot generation

Hello,
is there any way to prevent scheduled history snaphsot generation in case
there are no recerds retrieved from report underlaying query ?No there is not.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"jacek" <jacek@.discussions.microsoft.com> wrote in message
news:CFA802BC-F5B6-4509-AD93-00E1ACE2B30F@.microsoft.com...
> Hello,
> is there any way to prevent scheduled history snaphsot generation in case
> there are no recerds retrieved from report underlaying query ?