pivot information: unique

Showing posts with label unique. 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?

Saturday, February 25, 2012

pivot table without aggregation?

Hello, I have a resultset as follows:

fields: Name, RankID

values:
Prorduct A, 4
Product B, 33
Product C, 221
(etc)

Name is always unique. RankID may not be.

I want to take that result set and basically pivot it to have the Name
values as columns, and the RankID of each one as the data. So you
would end up with only one row like:

Product A | Product B | Product C | etc
4 | 33 | 221 | etc

Is this possible? I do not want to sum the data or anything, simply
rotate it sort of.

Any advice is appreciated.Something like this should do it:

SELECT SUM(CASE WHEN [Name] = 'Product A' THEN RankID ELSE 0 END) AS
'Product A',
SUM(CASE WHEN [Name] = 'Product B' THEN RankID ELSE 0 END) AS
'Product B',
SUM(CASE WHEN [Name] = 'Product C' THEN RankID ELSE 0 END) AS
'Product C'
FROM PRODUCTS

Note that since your product name is unique the SUM here is just to pull the
data into one row. You could use MAX too but then if there is negative rank
you may have to handle it differently.

If this list of products is very dynamic, then you should look into dynamic
pivoting. Here are a few resources if you want to look in that direction:
http://www.sqlmag.com/articles/inde...articleid=15608
http://www.sqlmag.com/articles/inde...articleid=94268
http://www.sqlteam.com/item.asp?ItemID=2955
Also, most reporting tools have very good support for pivoting.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Plamen Ratchev wrote:

Quote:

Originally Posted by

I think SQL Server 2005 has a built-in pivoting function, but I've
never used it so I don't know the syntax.|||"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:460c63e2$0$24701$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

>
I think SQL Server 2005 has a built-in pivoting function, but I've
never used it so I don't know the syntax.

Here is how it can be done with PIVOT in SQL Server 2005:

SELECT [Product A],
[Product B],
[Product C]
FROM Products
PIVOT (SUM(RankID) FOR [Name] IN ([Product A],
[Product B],
[Product C])) AS P

I find the PIVOT syntax to be not so intuitive (unlike UNPIVOT which is
easier to use and understand).

Plamen Ratchev
http://www.SQLStudio.com

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

Saturday, February 25, 2012

pivot table without aggregation?

pivot information

Blog Archive

About Me