Preventing Deadlock: index

Showing posts with label index. Show all posts

Monday, March 26, 2012

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
John
Primary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon
|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?
|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.c om...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?
|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ
|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ
|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
--
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Primary versus unique keys

What impact is there in using a unique index instead of a primary key?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

http://www.mssqlcity.com/FAQ/General/primary_vs_unique_constraints.htm

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

It that the only difference?

Is there any impact on Replication, Database Mirroing (SQL 2005), or any other features?

Thanks

|||

There is an impact on transactional replication (all options) since it requires a primary key (unique indexes do not work). No impact to either merge or snapshot replication.

Database Mirroring and other features don't care.

Friday, March 23, 2012

Primary key without clustered index

Hi Experts, Is it possible to create a primary key without Clustered index?
If yes, where exactly would you use it?
Regards
Reshma> Hi Experts, Is it possible to create a primary key without Clustered
> index?
Sure,
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
dt SMALLDATETIME
);
GO
CREATE CLUSTERED INDEX i_dt ON dbo.foo(dt);
GO

> If yes, where exactly would you use it?
Hundreds of ways. What is your question really?
A|||Yes it is possible to create a primary key without clustered index. For your
better understanding find below a sample script I have attached.
--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int null
Constraint pk_tblTest primary key nonclustered (Field1)
)
Go
--Check the constraint type, key, name etc., (Just for cross checking)
sp_helpconstraint tblTest
1. We can have only one clustered index per table.
2. Normally all tables would have a primary key.
3. Clustered index would be created automatially if we create a PK field on
a table.
That said, based on your query pattern if you feel that another field marked
as clustered index would help the performance then this is the way to go.
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Reshma" wrote:

> Hi Experts, Is it possible to create a primary key without Clustered index
?
> If yes, where exactly would you use it?
> Regards
> Reshma|||Yes Aaron is right. We can directly say "primary key nonclustered" while
creating that field as shown in his example. But if at all you want to creat
e
a primary key based on more than one field (composite primary key) then my
script would be of help.
i.e.,
--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int not null
Constraint pk_tblTest primary key nonclustered (Field1, Field3)
)
Go
Best Regards
Vadivel
http://vadivel.blogspot.com
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure,
> CREATE TABLE dbo.foo
> (
> id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
> dt SMALLDATETIME
> );
> GO
> CREATE CLUSTERED INDEX i_dt ON dbo.foo(dt);
> GO
>
> Hundreds of ways. What is your question really?
> A
>
>|||Thanks for the explanation vadivel. Your second response indeed was really
helpful.
Regards
Reshma
"Reshma" wrote:

> Hi Experts, Is it possible to create a primary key without Clustered index
?
> If yes, where exactly would you use it?
> Regards
> Reshma

Primary Key with Cluster Index

Hi All,

One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables

Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table

As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data

I tried following options with vain

1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also

Pls give me any other solution or suggestion in this regard

with Thanks & Regards
BhaskaraFirst suggestion: pick a column name more descriptive than "ID"....

Second suggestion: one million rows is not that much data. Could there be some other reason why inserts are taking so long?

Third suggestion: you can reduce the number of initial page splits setting the fill factor lower when you create the index.|||If the clustered index is an identity then you are not getting page splits unless you are making changes to data attribute columns and increasing the size of this data. On inserts you are certainly not getting page splits - monotonically increasing clustered indexes like this will not create splits and are the fastest possible index for inserts (faster even than no clustered index).

Hang on - I just reread - are you changing the clustered index constantly?|||There appears to be more than one problem here, so it may take several steps to correct all of the problems.

First and foremost, you specified in your ALTER TABLE that you wanted the index to be clustered, so SQL Server dutifully did what you asked. That is not part of your problem at all.

A million rows is not a large table for SQL Server. A billion rows might be large, but a million definitely is not.

SQL Server disk I/O is what drives log usage. The log file is essentially a record of what changes were made to the database at the disk (binary) level.

Dropping a clustered index shouldn't be expensive in terms of time, but the server should not permit you to drop an index that is placed by the server to protect DRI (Declarative Referential Integrity). It shouldn't take a long time, but the DROP INDEX ought to fail.

When you create a new clustered index, it can cause massive amounts of I/O operations because a clustered index effectively rearranges the data store for the entire table. That means that every row in the table can (and probably will) move, and that every existing index on the table will need to be rebuilt.

When you change a table with a clustered index (using INSERT, UPDATE, or DELETE) the amount of I/O should be nearly the same as updating the same table without the clustered index. It is possible that you might have a page split that a heap insert would not incur, but the grand total of the I/O shouldn't be significantly different.

Whatever is causing the change in disk I/O and log file usage is almost certainly not the clustered index alone. Something else is either part or all of this problem.

To answer your questions as you posted them:

1) Take regular incremental backups to allow log space to be reused. This should not affect the time needed, but it will reduce the log file growth.

2) Dropping the clustered index should be impossible using DROP INDEX. I think you'll need to use ALTER TABLE to make this happen. Dropping the index ought to be quick, although creating a new index may take a while.

-PatP|||Dropping a clustered index shouldn't be expensive in terms of time

Unless there are other indexes on the table.|||are there any triggers on this table?|||If the clustered index is an identity then you are not getting page splits unless you are making changes to data attribute columns and increasing the size of this data. On inserts you are certainly not getting page splits - monotonically increasing clustered indexes like this will not create splits and are the fastest possible index for inserts (faster even than no clustered index).

Hang on - I just reread - are you changing the clustered index constantly?

Thanks for your interest in my problem
When I observed in Sql profiler, if there is any Insertion or Updation, it is trying re-page(indexing Pages) whole

come to last point made by you
Intially I declared it as clustered index, now because of slow performance i wanted it to drop and create NON-CLUSTER index|||Hi Pat Phelan,
Thanks for your interest in my problem

I Got what exactly you wanted to say
still I have some clarifications

1) I try to drop the clustered index using "ALTER TABLE" after removing DRI
and then I recreate the NONCLUSTERED INDEX using
"ALTER Table Document ADD CONSTRAINT PK_Document_id PRIMARY KEY NONCLUSTERED (id) ON [PRIMARY] "

whether it will give any impact on performance|||OK, you're dropping the constraint, not the index

Do you know what clustering means?

primary key vs. unique index

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||Hi,
If you create a primary key, by default it will create clustered index (if
there doesn't exist any) on that column(s) and if you create a unique key
then by default it will create non-clustered index on the column(s).
- Kanth
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||A primary key a logical concept which is declared for integrity reasons.
Indexes are physical artifacts added mostly for performance reasons. A table
can have only one primary key while a table can have more than one indexes.
Primary key columns must be non-nullable while unique index columns can be
nullable even though only one NULL is allowed (even for unique index on
multiple columns). Primary keys guarantee uniqueness irrespective of any
operations in the table while certain option on unique indexes can ignore
new or updated data that would create a duplicate key in the index. In SQL
Server, a declaration of primary key results in the creation of an index, by
default.
- Anith
( Please reply to newsgroups only )|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A

table

quote:

> can have only one primary key while a table can have more than one

indexes.

quote:

> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,

quote:

> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
----
"Zeng" <zzy@.nonospam.com> wrote in message
news:#X#glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...

quote:

> Hello,
> What's the difference between primary key and unique index? At this

point,

quote:

> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>

|||It's all clear to me now. Thank you everybody!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A

table

quote:

> can have only one primary key while a table can have more than one

indexes.

quote:

> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,

quote:

> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

primary key vs. unique index

Hello,
What's the difference between primary key and unique index? At this point,
they are the same to me, but I could be wrong. Thank you in advance for
your comments.When you create a primary key, sqlserver will implement an unique index on
the column(s).
--
-oj
http://www.rac4sql.net
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||Hi,
If you create a primary key, by default it will create clustered index (if
there doesn't exist any) on that column(s) and if you create a unique key
then by default it will create non-clustered index on the column(s).
- Kanth
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23X%23glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||A primary key a logical concept which is declared for integrity reasons.
Indexes are physical artifacts added mostly for performance reasons. A table
can have only one primary key while a table can have more than one indexes.
Primary key columns must be non-nullable while unique index columns can be
nullable even though only one NULL is allowed (even for unique index on
multiple columns). Primary keys guarantee uniqueness irrespective of any
operations in the table while certain option on unique indexes can ignore
new or updated data that would create a duplicate key in the index. In SQL
Server, a declaration of primary key results in the creation of an index, by
default.
--
- Anith
( Please reply to newsgroups only )|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A
table
> can have only one primary key while a table can have more than one
indexes.
> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,
by
> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||1. Primary Key is a field or group of fields within the table that uniquely
identifies thge record.
2. It cannot be repeated in any sybsequent row no matter how many rows are
added.
3 .Groups of primary key = Composite key.
1. SQL Server automatically creates an index for the table's PRIMARY KEY.
2. Totally we can have 250 Indexes. {Includes only 1 Clustered + 249 Non
Clustered}
3. Unique index contains no Duplicate values.
Bhaskaran.B
----
"Zeng" <zzy@.nonospam.com> wrote in message
news:#X#glov3DHA.3656@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What's the difference between primary key and unique index? At this
point,
> they are the same to me, but I could be wrong. Thank you in advance for
> your comments.
>|||It's all clear to me now. Thank you everybody!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23NdUGex3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> A primary key a logical concept which is declared for integrity reasons.
> Indexes are physical artifacts added mostly for performance reasons. A
table
> can have only one primary key while a table can have more than one
indexes.
> Primary key columns must be non-nullable while unique index columns can be
> nullable even though only one NULL is allowed (even for unique index on
> multiple columns). Primary keys guarantee uniqueness irrespective of any
> operations in the table while certain option on unique indexes can ignore
> new or updated data that would create a duplicate key in the index. In SQL
> Server, a declaration of primary key results in the creation of an index,
by
> default.
> --
> - Anith
> ( Please reply to newsgroups only )
>

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 vs unique key, and indexing

Hi,
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>|||More discussion about this question.
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=17b7877f-6978-4038-bc16-0c426b6cf7eb&sloc=en-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
> frederic.nourry@.gmail.com,
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
> > Hi,
> >
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> >
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> > if so do i need to create an index on the 3 fields too or the unique
> > constraint is being indexed anyway ?
> >
> > Thanks
> >
> >|||Yes, I stand corrected again :) Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...
> frederic.nourry@.gmail.com,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things

primary key vs unique key, and indexing

Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things

Primary key vs Clustered Key

Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that is
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
Jamie
That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than have
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey is
constrained by that primary key, is there truly any use for it?
Regards,
Jamie
"MC" wrote:

> What do you mean?
> When creating PK you by default get clustered index on same columns. In some
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>
|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with an
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck at
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
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
|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustered
index as unique as opposed to a primary key that references the cluster allow
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> 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
> --
>
|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is completely
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a clustered
> index as unique as opposed to a primary key that references the cluster allow
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
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
|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working with
> a view that must be faster and more efficient. If I can eliminate a primary
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
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
|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being more
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. With
the primary key, every row could have a clustered field as null.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> 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
> --
>
|||I see.
The primary key will always be unique, will it not? A unique cluster can
be the only unique key in a table. There can't be both a primary key and a
unique cluster. So not having a primary key would actually result in one
less column. It would mean not having many records that could contain all
null values for that cluster. I think it answers the question. The unique
cluster is a better choice than a primary key if there are to be no foreign
keys for the table. I hope I understand it correctly.
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Narrow indexes have nothing to do with whether an index is a PRIMARY
> KEY or not.
> Maybe what you really mean is that you want to drop a *column* or
> remove a *column* from an index. But that has nothing to do with having
> a primary key or not - obviously you could just as easily put the
> PRIMARY KEY constraint on the other unique column(s) you have.
> Remember that a PRIMARY KEY is automatically indexed. There is no need
> to create another index.
> --
> 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
> --
>
sql

Primary key vs Clustered Key

Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that i
s
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
JamieWhat do you mean?
When creating PK you by default get clustered index on same columns. In some
cases, it would be better to change that to nonclustered and then create
clustered index on diff column(s). It depends on how that table is used
(queries, fk and so on)
MC
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
> Is there an advantage to a unique clustered key over a primary key? To be
> sure, there is a primary key in a table with a unique clustered index that
> is
> being held as a clustered - non unique table [with a primary key as th
e
> unique index]. There are no foreign keys for this table and I wonder if
> it
> would improve performance to modify the table in this manner.
> --
> Regards,
> Jamie|||That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than hav
e
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey i
s
constrained by that primary key, is there truly any use for it?
--
Regards,
Jamie
"MC" wrote:

> What do you mean?
> When creating PK you by default get clustered index on same columns. In so
me
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with a
n
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck a
t
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
>|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
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
--|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustere
d
index as unique as opposed to a primary key that references the cluster allo
w
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
--
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> 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
> --
>|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
--
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is complete
ly
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with
an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck
at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
>|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a cluste
red
> index as unique as opposed to a primary key that references the cluster al
low
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
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
--|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working wi
th
> a view that must be faster and more efficient. If I can eliminate a prima
ry
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
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
--|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being mor
e
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. Wit
h
the primary key, every row could have a clustered field as null.
--
Regards,
Jamie
"David Portas" wrote:

> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> 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
> --
>

Primary key vs Clustered Index with respect to Replication.

Is the following statement is TRUE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:

> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.

PRIMARY key versus UNIQUE index

Hi Group
Other than the fact that PRIMARY keys seem to be defined at table
creation time - what's the difference between a primary key and a
UNIQUE index? If there is a difference, what's the typical use of a
PRIMARY key?
TIA
MarkusPrimary keys do not allow null values ... that is the only difference betwee
n
Primary key and Unique Key.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Markus Zingg" wrote:

> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||and unique key allows just one null value.
"Vadivel" wrote:
> Primary keys do not allow null values ... that is the only difference betw
een
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||Hi
Not, the other difference is that when you create a primary key, SQL Server
creates a clustered index ,as opposite UNIQUE key it creates unique
nonclustered index
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
> Primary keys do not allow null values ... that is the only difference
> between
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||From the database design point of view the PRIMARY KEY should reflect the
natural key of your data.
From the technical point of view, a PRIMARY KEY does not allow NULL's, where
as a UNIQUE Constraint / Index allows at least 1 NULL.
From my own stand point I use PRIMARY KEY constraints on the natural key and
a UNIQUE Constraint for the surrogate key eg...
CREATE TABLE currency (
id int not null identity constraint sk_currency unique clustered,
code char(3) not null constraint pk_currency primary key nonclustered
)
CREATE TABLE trade (
..
...
settlement_currency_id int not null references currency( id ),
trade_currency_id int not null references currency( id ),
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Markus Zingg" <m.zingg@.nct.ch> wrote in message
news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||Unless it's composite.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:01F43F78-1A63-4AD9-B5C0-D4F2486524C1@.microsoft.com...
> and unique key allows just one null value.
> "Vadivel" wrote:
>|||Just for the sake of completion. We can create Primary Keyws without
clustered index also. Check out
http://vadivel.blogspot.com/2006/03...el.blogspot.com
"Uri Dimant" wrote:

> Hi
> Not, the other difference is that when you create a primary key, SQL Serve
r
> creates a clustered index ,as opposite UNIQUE key it creates unique
> nonclustered index
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
>
>|||This is a default behavior for the creation of a primary key; you can
override it, and make your underlying index a nonclustered one.
CREATE TABLE foo (pkid int PRIMARY KEY NONCLUSTERED,
doe smalldatetime)
CREATE CLUSTERED INDEX clidx ON foo(doe)
I try to keep the concepts of indexes and constraints as seperate as
possible in my mind (and in my designs); one is a physical tool to
assist in database performance, the other is a logical tool to assist
in data integrity. Sometimes it's useful to have a clustered primary
key; sometimes it's not.
Stu|||Hi Tony!

> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
Interesting... I have a feeling that many people does it the other way aroun
d (identity is PK, and
natural key(s) are UQ). Is it just by habit, or do you have any particular r
eason doing it that way?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> From the database design point of view the PRIMARY KEY should reflect the
natural key of your
> data.
> From the technical point of view, a PRIMARY KEY does not allow NULL's, whe
re as a UNIQUE
> Constraint / Index allows at least 1 NULL.
> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
> CREATE TABLE currency (
> id int not null identity constraint sk_currency unique clustered,
> code char(3) not null constraint pk_currency primary key nonclustered
> )
> CREATE TABLE trade (
> ...
> ...
> settlement_currency_id int not null references currency( id ),
> trade_currency_id int not null references currency( id ),
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Markus Zingg" <m.zingg@.nct.ch> wrote in message
> news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
>|||Hi Tibor - long time no beer....
My feeling and approach is this, most tables will have a natural key that we
need to provide uniqueness for, I make the natural key the PRIMARY KEY (it
also flows naturally from the logical design), the surrogate key is there as
part of the implementation phase to improve schema performance and
scalability hence I make it a unique constraint to enforce uniqueness, and
yes - use IDENTITY to auto populate.
There are a number of occaisions where there is no practical usable natural
key and in that instance I will just make the column with the IDENTITY
property the PRIMARY KEY and not bother with the surrogate.
I never use the natural key anywhere else - there is just one copy of it and
thats in its base table, and foreign key references I use the surrogate key.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCpTmJSaGHA.4544@.TK2MSFTNGP02.phx.gbl...
> Hi Tony!
>
> Interesting... I have a feeling that many people does it the other way
> around (identity is PK, and natural key(s) are UQ). Is it just by habit,
> or do you have any particular reason doing it that way?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
>

Wednesday, March 21, 2012

primary key problem

hello!
i'm new to sql server and having some problem getting the primary key or index (Reference column). opening up the design table, the primary key or index column has an identity seed number that may vary in time. the identity increment is 1. in my code, i'm trying to get the next value and showing it in a textbox (txtReference). but i'm getting the wrong value.
example: if the last row in the table TaskOrder has a value of 150 in the Reference column, i'm getting the value of 151. but bec. of the identity seed of 200, when the row is actually added, the value for the Reference column is 201 or higher.
how do i get the actual value that the database will use? i need to change my select statement.
my code:
sReference = "Select max(Reference) From TaskOrder";
sqlConn = new SqlConnection(ConfigurationSettings.AppSettings["sql2000"]);
sqlCMD = new SqlCommand(sReference);
sqlCMD.Connection = sqlConn;
sqlConn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlCMD);
DataSet ds = new DataSet();
adapter.Fill(ds);
int tableRowCount = ds.Tables[0].Rows.Count;
if (tableRowCount == 1)
{
sReference = ds.Tables[0].Rows[0].ItemArray[0].ToString();
nReference = int.Parse(sReference) + 1;
sReference = nReference.ToString();
Session["sReference"] = sReference;
txtReference.Text = sReference;
}
else
{
txtReference.Text = "1";
}
sqlConn.Close();

IDENT_CURRENT('table_name') will return the last identity value inserted. If you add the increment value to it, that will be the next identity value. Please change'table_name' to actual table name you have. Please correct me if I am wrong..|||how do i use IDENT_CURRENT('TaskOrder') and identity increment in c# asp.net? do i put them in a select statement?|||You have to use in the select command as you originally wrote:
select IDENT_CURRENT('TaskOrder') from TaskOrder
Let me know if this works|||thanks a lot, Mohammad Musa. it's working now.

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

Monday, March 26, 2012

PrimaryKey and Index

PrimaryKey and Index

PrimaryKey and Index

Primary versus unique keys

Friday, March 23, 2012

Primary key without clustered index

Primary Key with Cluster Index

primary key vs. unique index

primary key vs. unique index

Primary Key Vs. Not NULL Unique Key

primary key vs unique key, and indexing

primary key vs unique key, and indexing

Primary key vs Clustered Key

Primary key vs Clustered Key

Primary key vs Clustered Index with respect to Replication.

PRIMARY key versus UNIQUE index

Wednesday, March 21, 2012

primary key problem

Primary Key Non-Cluster Index

Preventing Deadlock

Blog Archive

About Me