Primary versus unique keys

Hi

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

|||

Hi

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.

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...
>

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!