pivot information: clustered

Showing posts with label clustered. Show all posts

Friday, March 9, 2012

PK vs. Unique clustered indexes

Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between
>> having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the
>> tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is
>> drop
>> this index and recreated it as a primary key with the same columns and
>> name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>|||Good catch, Jacco!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
>> unique index (you have control over whether it is to be a clustered index or not).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
>> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is drop
>> this index and recreated it as a primary key with the same columns and name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>>
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> > There is one thing to look out for and that is that unique indexes can be on nullable columns
> > (although they only allow one NULL value), but primary keys can't. But if the columns in the
> > unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> > there isn't one already on the table.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> >> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
> >> unique index (you have control over whether it is to be a clustered index or not).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> >> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> >> Are there any differences, performance related or other wise, between having
> >> a clustered primary key vs. a clustered unique key?
> >>
> >> I'm am trying to ready a SQL2K server for replication. Some of the tables
> >> in database A don't have a primary key but they do have a cluster unique
> >> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> >> this index and recreated it as a primary key with the same columns and name.
> >> Would this pose any type of possible problem ?
> >>
> >> TIA,
> >> Billy
> >>
> >>
> >
> >
>
>

PK vs. Unique clustered indexes

PK vs Unique Clustered Index

When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index?

Thanks for your thoughts

Other that the NULL value issue, both will maintain uniqueness if that is your purpose.

|||

Yeah, the "UNIQUE" will assure uniqueness. I know that created a PK on a table creates a UNIQUE CLUSTERED index on whatever field(s) are included in the key.

My question is, is there a real difference between creating a PK on a table, and creating the equivalent UNIQUE CLUSTERED INDEX on the same table instead of a PK? If the PK is not referenced in any FK, then in my thinking, there is no difference.

Thoughts...anyone?

PK vs Unique Clustered Index

Thanks for your thoughts

Other that the NULL value issue, both will maintain uniqueness if that is your purpose.

|||

Yeah, the "UNIQUE" will assure uniqueness. I know that created a PK on a table creates a UNIQUE CLUSTERED index on whatever field(s) are included in the key.

Thoughts...anyone?

Wednesday, March 7, 2012

PK design in clustered environment

We are designing a large transactional database that will end up being deployed in a clustered SQL Server 2000 environment. We have initially designed all of our primary key fields as GUID type to eliminate any potential future replication issues. Will
this design impact the performance of the db negatively versus using an auto-increment PK design using big int type fields and following a ranged identities design approach?
Primary keys selection and performance is the same regardless of whether you
are in a clustered environment or not.
GUIDs as PKs are not a good idea. First, they are effectively random, so
there will be lots of page splits and table/index fragementation if you
allow the default of clustered order on the PK. Second, they are totally
artificial, which I think is a very bad idea from a data integrity
standpoint. Finally, they are wide. Remember, every non-clustered index
uses the clustered index to look up date rows. GUIDs are very wide and will
slow down index intersection as a possible query resolution path.
Here is the (very condensed) version of how I prefer to layout index
structures.
First, I create a RowID field Int (or BigInt) Identity(1,1) on each table.
I then create a unique, clustered index on this column. Note it is NOT the
Primary Key. Primary keys are data-centric, not artificial. If you cannot
identify a data-centric key, then you need to rethink your design since it
is by definition not third normal form. With data-centric keys, I don't
worry about new RowIDs if I need to replicate the data.
My PK index is non-clustered. Any additional indexes are also
non-clustered.
Since the data is now in insert order, the cache manager now has a bit of
help. Most databases query newly inserted data more often than older data,
especially data in large tables. Inserts are intentionally hot-spotted,
also helping out the lazy writer and checkpoint processes. Index
intersection is highly optimized.
I have been using this technique for a few years now and it makes a HUGE
difference in performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"toml" <anonymous@.discussions.microsoft.com> wrote in message
news:A2293202-5C5F-4240-9413-628E5913FF58@.microsoft.com...
> We are designing a large transactional database that will end up being
deployed in a clustered SQL Server 2000 environment. We have initially
designed all of our primary key fields as GUID type to eliminate any
potential future replication issues. Will this design impact the
performance of the db negatively versus using an auto-increment PK design
using big int type fields and following a ranged identities design approach?

Friday, March 9, 2012

PK vs. Unique clustered indexes

PK vs. Unique clustered indexes

PK vs. Unique clustered indexes

PK vs Unique Clustered Index

PK vs Unique Clustered Index

Wednesday, March 7, 2012

PK design in clustered environment

pivot information

Blog Archive

About Me