Difference Between primary key and unique key

Hi,
Can somebody explain me the difference between primary key
and unique key and in which scenarios we use them. please
explain with a example.
Regards
ChipThe primary distinction is that Primary key column(s) must be non-nullable
while Unique constraints allow a NULL value. Also, there can be only one
primary key in a table while there can be more than one Unique constraints
defined for a table.
--
Anith|||Hi,
Primary key :
- The key which will not allow any NULL Values.
- Will Create a Clustered Index by default.
Unique Key
- The Key will allow 1 Null Value
- Will Create a non clustered index by default
Since Primary key will not allow NULLS values it can be used in scenario to
preserve more data integrity
Thanks
Hari
MCDBA
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:12c4c01c3f6a9$9ac762d0$a401280a@.phx.gbl...
> Hi,
> Can somebody explain me the difference between primary key
> and unique key and in which scenarios we use them. please
> explain with a example.
> Regards
> Chipsql

Difference Between primary key and unique key

Hi,
Can somebody explain me the difference between primary key
and unique key and in which scenarios we use them. please
explain with a example.
Regards
ChipThe primary distinction is that Primary key column(s) must be non-nullable
while Unique constraints allow a NULL value. Also, there can be only one
primary key in a table while there can be more than one Unique constraints
defined for a table.
Anith|||Hi,
Primary key :
- The key which will not allow any NULL Values.
- Will Create a Clustered Index by default.
Unique Key
- The Key will allow 1 Null Value
- Will Create a non clustered index by default
Since Primary key will not allow NULLS values it can be used in scenario to
preserve more data integrity
Thanks
Hari
MCDBA
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:12c4c01c3f6a9$9ac762d0$a401280a@.phx
.gbl...
> Hi,
> Can somebody explain me the difference between primary key
> and unique key and in which scenarios we use them. please
> explain with a example.
> Regards
> Chip

Difference between MSDE 7 and SQL 7

Is there a registry key that will tell me if am running MSDE 7 or MS
SQL 7?
Thanks
I guess MSDE 7 is just a typo of yours, but there does not have to be
only the one OR the other, there can also be the two of them installed
on the server, so it would be evenbetter for you to enumerate the
instances on the machine using DMO or SMO.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||This is the situation, I have a wise installation that needs to check
if the target pc is running either MSDE or full SQL. In our case the
target pc will only be running one or the other. Thats why I was
wondering if there is a registry key that will tell me which one is
running.
|||Mapping MSDE versions to their corresponding versions of SQL Server:
MSDE 1.0 used SQL Server 7.0 technology.
MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
2000 technology.
SQL Server 2005 Express Edition is the MSDE replacement for SQL Server 2005.
You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
computer, support for multiple instances of the Database Engine on one
computer was not introduced until SQL Server 2000.
I don't know of a registry key, but if you issue a SELECT @.@.VERSION
statement it will report the version of SQL Server 7.0 (MSDE or an edition
such as Standard or Enterprise).
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Alan is right, but there are also .NET Framework factory classes that can
report all instances (2000 or 2005) on a network and on any specific
instance, the version etc. There is an example of this on my book's DVD.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:OCdydtkRHHA.4060@.TK2MSFTNGP03.phx.gbl...
> Mapping MSDE versions to their corresponding versions of SQL Server:
> MSDE 1.0 used SQL Server 7.0 technology.
> MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
> 2000 technology.
> SQL Server 2005 Express Edition is the MSDE replacement for SQL Server
> 2005.
> You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
> computer, support for multiple instances of the Database Engine on one
> computer was not introduced until SQL Server 2000.
> I don't know of a registry key, but if you issue a SELECT @.@.VERSION
> statement it will report the version of SQL Server 7.0 (MSDE or an edition
> such as Standard or Enterprise).
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

Diff for tables *structures* rather than data

If I have two sql server databases that started out with identical
table/key/index structures, but were not properly kept in sync, is
there any way I can generate a table change script to essentially
'diff' the two databases and come up with table change scripts to
bring one in line with the other?

An answer to this age-old question of mine would make me very
happy...!

BrianERwin will do this easily for you (reverse engineer one database and then do
a compare with the other). PowerDesigner and ER/Studio will probably also
do it.

Downside? They're not free...

To find out what's different, you could do something like this:

select so.name, sc.name, sc.type from sysobject so inner join syscolumns sc
on so.id = sc.id
order by so.name, sc.name

in both databases, paste the output into .TXT files and then do a compare on
the .TXT files (WINDIFF utility) to get started. You'll have to generate
the change scripts by hand, of course.

You could completely script the databases and then WINDIFF the scripts.
However, the scripting order might be different between the two databases
and this may muddy the waters (you could rearrange the scripts by hand to
resolve some ordering problems).

If you have a little money, see if you can find a database consultant with
access to ERwin or one of the other tools to come in for a couple of hours
and use his tools to generate the scripts for you. It might save a lot of
time. You could ask him to print diagrams, too, which might be helpful down
the road.

If you have a fair amount of cash, consider buying one of these tools
yourself - they're very, very handy. ER/Studio used to offer a freely
downloadable demo; don't know about ERwin or PowerDesigner. It seems to me
that ERwin is something like $4000. I think ER/Studion was less, don't
recall about PowerDesigner.

DesktopDBA, if it's still around, may also offer some capability this way.

I suppose you could check C|Net, SQLServerCentral or some of the other
SQL-oriented group sites for freely downloadable utilities, too.

"Brian McGee" <brian.mcgee@.Sentrio.com> wrote in message
news:831a513c.0309110332.2184b751@.posting.google.c om...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?
> An answer to this age-old question of mine would make me very
> happy...!
> Brian|||In article <831a513c.0309110332.2184b751@.posting.google.com>,
brian.mcgee@.Sentrio.com says...
> If I have two sql server databases that started out with identical
> table/key/index structures, but were not properly kept in sync, is
> there any way I can generate a table change script to essentially
> 'diff' the two databases and come up with table change scripts to
> bring one in line with the other?

I like Red-gate Softwares "SQL Tools" product for that.
(http://www.red-gate.com) You can get a single-user license for the SQL
Compare portion of the product for about $200. That would bring the
table definitions in line. If you also want scripts to modify the
contents of the tables, that's another $200. Of course, at that point
you're better off with the bundle, which is $350 and includes DTS
Compare which diffs server settings, DTS packages, jobs and logins.

-- Rick

P.S. No affiliation at all with Red-Gate software but their product
saved my cojones once, so I'm just passing on my experience.

Developer Edition Product Key

Ive got a server which I want to install the SQL Server Developer Edition on but it previously had the full version on which has been uninstalled but every time I try to install the dev edition it it its prompting me for a Product key which I know for the developer edition isnt needed. Is there a registry key from the previous install which is triggering this ?

Developer edition does require a product key. The only editions that don't are Eval and Express. Enter the key supplied when you purchased the Dev edition and you're good to go.

Thanks,
Sam Lester (MSFT)

|||Does the product key for SQL Server come with the CD or does it need activated online?

Developer Edition Product Key

Ive got a server which I want to install the SQL Server Developer Edition on but it previously had the full version on which has been uninstalled but every time I try to install the dev edition it it its prompting me for a Product key which I know for the developer edition isnt needed. Is there a registry key from the previous install which is triggering this ?

Developer edition does require a product key. The only editions that don't are Eval and Express. Enter the key supplied when you purchased the Dev edition and you're good to go.

Thanks,
Sam Lester (MSFT)

|||Does the product key for SQL Server come with the CD or does it need activated online?