Preventing Deadlock: key

Showing posts with label key. Show all posts

Monday, March 26, 2012

primay key

How do you assign a primary key to a column in SQL 2005? Thanks.Hi
CREATE TABLE dbo.Test (c INT NOT NULL PRIMARY KEY)
"00KobeBrian" <a@.b.com> wrote in message
news:eVQgpKz8GHA.2248@.TK2MSFTNGP04.phx.gbl...
> How do you assign a primary key to a column in SQL 2005? Thanks.
>|||00KobeBrian wrote:
> How do you assign a primary key to a column in SQL 2005? Thanks.
ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
--
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
--|||Sorry. How do you do that in SQL Server Management Studio?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
> --
> 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
> --
>|||On Oct 19, 11:48 am, "00KobeBrian" <a...@.b.com> wrote:
> Sorry. How do you do that in SQL Server Management Studio?
> "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote in messagenews:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>
> > 00KobeBrian wrote:
> >> How do you assign a primary key to a column in SQL 2005? Thanks.
> > ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
> > --
> > 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
> > -- Hide quoted text -- Show quoted text -
right click on table - select modify table.
select column you want to set as primary key and right click and select
option to set primary key
Regards
Amish Shah
http://shahamishm.tripod.com|||Can anyone please help?
"00KobeBrian" <a@.b.com> wrote in message
news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
> Sorry. How do you do that in SQL Server Management Studio?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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 mean like SQL 2000. All I do is open up the Enterprise Manager and click
the key icon in the top to the column of a table and then the primary index
get assigned. How can I do it similiarly in SQL 2005? There got to be a way,
right? Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
> Sorry. How do you do that in SQL Server Management Studio?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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
>> --
>|||Right click mouse on the column...
"00KobeBrian" <a@.b.com> wrote in message
news:e4leM28AHHA.3316@.TK2MSFTNGP02.phx.gbl...
>I mean like SQL 2000. All I do is open up the Enterprise Manager and click
>the key icon in the top to the column of a table and then the primary index
>get assigned. How can I do it similiarly in SQL 2005? There got to be a
>way, right? Thanks.
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23w0gVq08GHA.788@.TK2MSFTNGP05.phx.gbl...
>> Sorry. How do you do that in SQL Server Management Studio?
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1161239072.297334.114770@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> How do you assign a primary key to a column in SQL 2005? Thanks.
>> ALTER TABLE tbl ADD CONSTRAINT tbl_pk PRIMARY KEY (col);
>> --
>> 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
>> --
>>
>|||00KobeBrian wrote:
> I mean like SQL 2000. All I do is open up the Enterprise Manager and click
> the key icon in the top to the column of a table and then the primary index
> get assigned. How can I do it similiarly in SQL 2005? There got to be a way,
> right? Thanks.
>
On Server Management Studio, create the table and right click on the
left one column. Select "Set Primary Key" from the popup menu.
--
Arto Viitanen, CSC Ltd.
Espoo, Finland|||Hi Guys,
I cannot find "Set Primary Key" from the popup menu. It only has
new column, modify, rename, delete, refresh and properties. In properties
page, the primary key field is grey out. I cannot change from no to yes.
Please help. Thanks.
"Arto Viitanen" <arto.viitanen@.csc.fi> wrote in message
news:4552da27$0$8957$ba624cd0@.newsread.funet.fi...
> 00KobeBrian wrote:
>> I mean like SQL 2000. All I do is open up the Enterprise Manager and
>> click the key icon in the top to the column of a table and then the
>> primary index get assigned. How can I do it similiarly in SQL 2005? There
>> got to be a way, right? Thanks.
> On Server Management Studio, create the table and right click on the
> left one column. Select "Set Primary Key" from the popup menu.
> --
> Arto Viitanen, CSC Ltd.
> Espoo, Finland|||Can anyone please help? Thanks.
"00KobeBrian" <a@.b.com> wrote in message
news:ubG6kP9AHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hi Guys,
> I cannot find "Set Primary Key" from the popup menu. It only
> has new column, modify, rename, delete, refresh and properties. In
> properties page, the primary key field is grey out. I cannot change from
> no to yes. Please help. Thanks.
> "Arto Viitanen" <arto.viitanen@.csc.fi> wrote in message
> news:4552da27$0$8957$ba624cd0@.newsread.funet.fi...
>> 00KobeBrian wrote:
>> I mean like SQL 2000. All I do is open up the Enterprise Manager and
>> click the key icon in the top to the column of a table and then the
>> primary index get assigned. How can I do it similiarly in SQL 2005?
>> There got to be a way, right? Thanks.
>>
>> On Server Management Studio, create the table and right click on the
>> left one column. Select "Set Primary Key" from the popup menu.
>> --
>> Arto Viitanen, CSC Ltd.
>> Espoo, Finland
>|||00KobeBrian wrote:
> Can anyone please help? Thanks.
>
The best answer is the one already given: use ALTER TABLE ADD
CONSTRAINT. That way if it doesn't work you'll get an error message
that will explain why it won't work. I recommend you do not use the
Enterprise Manager / Management Studio interface to modify a production
system. The GUI can be unreliable in some respects and doesn't give you
as much control as using the SQL syntax.
As for the reason why you can't create a PK using the GUI - there could
be several possibilities. Maybe the table already has a PK (you can't
create another) or maybe the column(s) is/are nullable or of a datatype
that isn't valid for a Primary Key.
--
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
--|||Thanks for the sharing. I understand it is better to issue a command. On
the other hand, I just found out that set primary key icon is grey out. I
don't know if there is any problem with my database or not and want to find
out. Do you have any idea? Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1163119406.397364.220710@.b28g2000cwb.googlegroups.com...
> 00KobeBrian wrote:
>> Can anyone please help? Thanks.
> The best answer is the one already given: use ALTER TABLE ADD
> CONSTRAINT. That way if it doesn't work you'll get an error message
> that will explain why it won't work. I recommend you do not use the
> Enterprise Manager / Management Studio interface to modify a production
> system. The GUI can be unreliable in some respects and doesn't give you
> as much control as using the SQL syntax.
> As for the reason why you can't create a PK using the GUI - there could
> be several possibilities. Maybe the table already has a PK (you can't
> create another) or maybe the column(s) is/are nullable or of a datatype
> that isn't valid for a Primary Key.
> --
> 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
> --
>|||If its greyed out, then another column is already chosen as a PK.
Its a bit confusing to explain the GUI issues, may be a screenshot will
help.
"00KobeBrian" <a@.b.com> wrote in message
news:u3WPMpGBHHA.3560@.TK2MSFTNGP03.phx.gbl...
> Thanks for the sharing. I understand it is better to issue a command. On
> the other hand, I just found out that set primary key icon is grey out. I
> don't know if there is any problem with my database or not and want to
> find out. Do you have any idea? Thanks.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1163119406.397364.220710@.b28g2000cwb.googlegroups.com...
>> 00KobeBrian wrote:
>> Can anyone please help? Thanks.
>>
>> The best answer is the one already given: use ALTER TABLE ADD
>> CONSTRAINT. That way if it doesn't work you'll get an error message
>> that will explain why it won't work. I recommend you do not use the
>> Enterprise Manager / Management Studio interface to modify a production
>> system. The GUI can be unreliable in some respects and doesn't give you
>> as much control as using the SQL syntax.
>> As for the reason why you can't create a PK using the GUI - there could
>> be several possibilities. Maybe the table already has a PK (you can't
>> create another) or maybe the column(s) is/are nullable or of a datatype
>> that isn't valid for a Primary Key.
>> --
>> 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
>> --
>

Primarykey Fields

I'm try to get table columns name & primary key to generat Dlete/Insert
script to all my database tables..
I was able to get list of all user tables from sysobject and columns list
from syscolumns..
Now i need to know the primary key column (For delete Statments)..
How could i detrmine which column is primarykey or composite key'
thanxA couple of suggestions:
First, you may be trying to reinvent the wheel; have you looked at
using SQL-DMO or SCPTXFR to script out your database? May save you a
lot of time and energy.
Second, wherever possible, use the INFORMATION_SCHEMA views rather than
the system tables to query this type of information; look at the
following queries i nthe pubs database as an example:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'authors'
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'authors'
Note that the identification of a primary key is a constraint on the
table, not a property of a column.
Of course, this only rings true for SQL Server 2000; no clue about
SS2005.
HTH,
Stu|||Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> I'm try to get table columns name & primary key to generat Dlete/Insert
> script to all my database tables..
> I was able to get list of all user tables from sysobject and columns list
> from syscolumns..
> Now i need to know the primary key column (For delete Statments)..
> How could i detrmine which column is primarykey or composite key'
> thanx
Here is a query that lists the PK columns for all tables in a database.
There is a restriction that the query as written will not cover keys
with more than 10 columns, but this is easy to address.
select o.name,
MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
from sysobjects o
join sysindexes i on i.id = o.id
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c on ik.id = c.id
and ik.colid = c.colid
join sysobjects pk ON i.name = pk.name
AND o.id = pk.parent_obj
group by o.name
order by o.name
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanx so much for this query..
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD9D836B2FYazorman@.127.0.0.1...
> Islamegy (NULL_Islamegy_NULL@.yahoo.com) writes:
> Here is a query that lists the PK columns for all tables in a database.
> There is a restriction that the query as written will not cover keys
> with more than 10 columns, but this is easy to address.
> select o.name,
> MAX(CASE ik.keyno WHEN 1 THEN c.name END) +
> coalesce(MAX(CASE ik.keyno WHEN 2 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 3 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 4 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 5 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 6 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 7 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 8 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 9 THEN ', ' + c.name END), '') +
> coalesce(MAX(CASE ik.keyno WHEN 10 THEN ', ' + c.name END), '')
> from sysobjects o
> join sysindexes i on i.id = o.id
> join sysindexkeys ik on i.id = ik.id
> and i.indid = ik.indid
> join syscolumns c on ik.id = c.id
> and ik.colid = c.colid
> join sysobjects pk ON i.name = pk.name
> AND o.id = pk.parent_obj
> group by o.name
> order by o.name
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

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, Indexes and Foreign Key - Best Place for them

Hello
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>

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.

Primary keys, indexes and speed

My table consists of several key fields (i.e. used in a complex primary key)
and some other fields where I do aggregation:
CREATE TABLE t
(
id INT NOT NULL,
ts DATETIME NOT NULL,
k1 INT NOT NULL,
k2 INT NOT NULL,
...
a1 FLOAT NOT NULL,
a2 FLOAT NOT NULL,
...
PRIMARY KEY (id, ts, k1, k2)
)
A SELECT query I execute on this table looks like:
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
Do I need to add some more indexes to improve speed of retrieving
or above PRIMARY KEY is enough?It seems to be a good candidate for an indexed view ,isn't it? Read about it
in the BOL
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Have you tried it yet? Or are you just guessing? Do you have a lot of
other columns? Since you have this as the clustered index, if it is the
ONLY index involved in any query, it might be enough.
Either way, you should test it out and see what happens. A lot depends on
what else you do with the table, because it can be too costly to add a bunch
of indexes to a table to speed up one query and hurt the others.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
> key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Tumurbaatar S.,
What about the execution plan, can you post it?
set showplan_text on
go
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
go
set showplan_text off
go
AMB
P.S. Your last name remind me a student in my classroom, when I was in
college (studying Physics), he was from Mongolia.
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Since ts predicate is a range, I'd try changing the clustered PK index to
put ts first, instead of second... That might make a substantial
improvement...
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Yes, it is. But my application and db is designed to
work with any (at least, with many) OLEDB/ADO compliant
databases. And indexed view, I think, is MS SQL feature.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehYzaRpOFHA.2132@.TK2MSFTNGP14.phx.gbl...
> It seems to be a good candidate for an indexed view ,isn't it? Read about
> it
> in the BOL
>
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> key)
>|||Yes, I tried. My server app works (24/7) and regularly adds new records.
And other, client, app retrieves these records to analyze. No update/delete
happens on the table. Only insert and select. And no many variants
of select, just one I posted above.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e47gILqOFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Have you tried it yet? Or are you just guessing? Do you have a lot of
> other columns? Since you have this as the clustered index, if it is the
> ONLY index involved in any query, it might be enough.
> Either way, you should test it out and see what happens. A lot depends on
> what else you do with the table, because it can be too costly to add a
> bunch of indexes to a table to speed up one query and hurt the others.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
>|||Ok, I will try.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:36D28CF4-7839-4F8B-ACDC-D4D23DA61AA9@.microsoft.com...
> Since ts predicate is a range, I'd try changing the clustered PK index to
> put ts first, instead of second... That might make a substantial
> improvement...
>
> "Tumurbaatar S." wrote:
>|||On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:

>See attachment. There's an actual table structure, a query I used and
>a resulting plan.
Hi Tumurbaatar,
I think the query will be executed at about the maximum possible speed.
The clustered index that is created because of the PRIMARY KEY
constraint is optimal for this query. (Note - near the end of this
message, I'll discuss one possible improvement).
Allow me to use an analogy to explain: imagine that you have a phone
book of a large town, were all people are listed in alphabetic order of
the surname. Further, people with the same surname are ordered by first
name. In database terms, the phone book is a table with clustered index
on (Surname, FirstName).
Now suppose you are given the task of finding all people with Surname
'Smith' and whose first name starts with H, I or J. That's not hard -
you quickly flip to the page where the Smith's are listed, skip to the
first whose name starts with H, then start scanning all names - and you
stop as soon as you encounter a Smith whose first name starts with K.
But what if your task had been to find all people with first name 'John'
and surname starting with K, L, or M? In that case, you'd have to scan a
much larger proportion of the phone book (the complete listing of all
surnames starting with K, L, or M, to be precise). That's why I don't
think that CBretana's suggestion will improve the speed of your query.

>By the way, I understood nothing there.
The execution plan, you mean? Okay, take it easy. The best way to
interpret an execution plan is from innermost to outermost. This plan is
quite straightforward, actually :-)
The last line says "Clustered Index S

". That is the process I
described above (in the phone book analogy) - the database uses the
index' structure to go straight to the first row with intid = 1 and ts >
'2005-4-1 00:00', then starts processing rows until it is past the last
row with intid = 1 and ts <= '2005-4-1 00:30'.
The line before that is a sort step. This is used to satisfy the group
by clause - after the sort, all rows that need to be grouped together
are together. (There are more strategies the optimizer can use for a
group by, but in this case the optimizer expects the sorting to be the
fastest).
The Stream Aggregate step takes the sorted output and calculates one row
from each group. If you inspect the entire line on your plan, you'll see
the three aggregate expressions you used in the query appear here. Note
that each result is given a name (Expr1002 through Expr1004).
And the final step is another sort step - this time to satisfy the ORDER
BY you specified. Note that the plan says to order by [Expr1004] - and
if you check the Stream Aggregate step, you'll see that Expr1004 is
exactly the name given to the expression you use in the ORDER BY.
I promised a possible improvement. I can't say if this works or not. The
idea is to eliminate one of the sort steps, at the cost of having to
scan a much bigger part of the table. If only a small number of rows in
your table satisfy the criteria in the WHERE clause, you can expect to
see performance plummetting. But if the WHERE clause is not very
selective and you were already reading most of your data anyway, then
this suggestion might help:
Change the table definition to:
CREATE TABLE dilink
(
intid SMALLINT NOT NULL,
ts SMALLDATETIME NOT NULL,

dr INT NOT NULL,
daddr INT NOT NULL,
ib FLOAT NOT NULL,
ob FLOAT NOT NULL,
CONSTRAINT dilink_intidts

drdaddr
PRIMARY KEY (

dr, daddr, intid, ts),
CONSTRAINT dilink_intidts
FOREIGN KEY (intid, ts)
REFERENCES didata (intid, ts) ON DELETE CASCADE
)
The only thing I changed is the order of columns in the primary key
constraint. This will als affect the order of columns in the index that
gets created for this constraint. The effect will be that the database
now has to scan the whole table to find the rows that match the WHERE
clause, but that the rows found will already be in order of

dr, daddr
so that there is no need to sort in order to satsify the GROUP BY.
I must add that I *expect* this version to suck. It will only help you
if your table has very unusual data distribution!
If performance of your query is really critical, you'd be better advised
to change this query into an indexed view. That will result in instant
results when you want to see this data - but at the price of slower
inserts, updates and deletes (as SQL Server has to do extra work to keep
the indexed view current).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks!
The most important criteria is a database size, SELECT speed
is the second one, so may be I will keep a current structure.
Thanks again!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgao51t7i2cv1simi0o9p975bv01m9cbuv@.
4ax.com...
> On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:
>
> Hi Tumurbaatar,
> I think the query will be executed at about the maximum possible speed.
> The clustered index that is created because of the PRIMARY KEY
> constraint is optimal for this query. (Note - near the end of this
> message, I'll discuss one possible improvement).
> Allow me to use an analogy to explain: imagine that you have a phone
> book of a large town, were all people are listed in alphabetic order of
> the surname. Further, people with the same surname are ordered by first
> name. In database terms, the phone book is a table with clustered index
> on (Surname, FirstName).
> Now suppose you are given the task of finding all people with Surname
> 'Smith' and whose first name starts with H, I or J. That's not hard -
> you quickly flip to the page where the Smith's are listed, skip to the
> first whose name starts with H, then start scanning all names - and you
> stop as soon as you encounter a Smith whose first name starts with K.
> But what if your task had been to find all people with first name 'John'
> and surname starting with K, L, or M? In that case, you'd have to scan a
> much larger proportion of the phone book (the complete listing of all
> surnames starting with K, L, or M, to be precise). That's why I don't
> think that CBretana's suggestion will improve the speed of your query.
>
> The execution plan, you mean? Okay, take it easy. The best way to
> interpret an execution plan is from innermost to outermost. This plan is
> quite straightforward, actually :-)
> The last line says "Clustered Index S

". That is the process I
> described above (in the phone book analogy) - the database uses the
> index' structure to go straight to the first row with intid = 1 and ts >
> '2005-4-1 00:00', then starts processing rows until it is past the last
> row with intid = 1 and ts <= '2005-4-1 00:30'.
> The line before that is a sort step. This is used to satisfy the group
> by clause - after the sort, all rows that need to be grouped together
> are together. (There are more strategies the optimizer can use for a
> group by, but in this case the optimizer expects the sorting to be the
> fastest).
> The Stream Aggregate step takes the sorted output and calculates one row
> from each group. If you inspect the entire line on your plan, you'll see
> the three aggregate expressions you used in the query appear here. Note
> that each result is given a name (Expr1002 through Expr1004).
> And the final step is another sort step - this time to satisfy the ORDER
> BY you specified. Note that the plan says to order by [Expr1004] - and
> if you check the Stream Aggregate step, you'll see that Expr1004 is
> exactly the name given to the expression you use in the ORDER BY.
>
> I promised a possible improvement. I can't say if this works or not. The
> idea is to eliminate one of the sort steps, at the cost of having to
> scan a much bigger part of the table. If only a small number of rows in
> your table satisfy the criteria in the WHERE clause, you can expect to
> see performance plummetting. But if the WHERE clause is not very
> selective and you were already reading most of your data anyway, then
> this suggestion might help:
> Change the table definition to:
> CREATE TABLE dilink
> (
> intid SMALLINT NOT NULL,
> ts SMALLDATETIME NOT NULL,
>

dr INT NOT NULL,
> daddr INT NOT NULL,
> ib FLOAT NOT NULL,
> ob FLOAT NOT NULL,
> CONSTRAINT dilink_intidts

drdaddr
> PRIMARY KEY (

dr, daddr, intid, ts),
> CONSTRAINT dilink_intidts
> FOREIGN KEY (intid, ts)
> REFERENCES didata (intid, ts) ON DELETE CASCADE
> )
> The only thing I changed is the order of columns in the primary key
> constraint. This will als affect the order of columns in the index that
> gets created for this constraint. The effect will be that the database
> now has to scan the whole table to find the rows that match the WHERE
> clause, but that the rows found will already be in order of

dr, daddr
> so that there is no need to sort in order to satsify the GROUP BY.
> I must add that I *expect* this version to suck. It will only help you
> if your table has very unusual data distribution!
>
> If performance of your query is really critical, you'd be better advised
> to change this query into an indexed view. That will result in instant
> results when you want to see this data - but at the price of slower
> inserts, updates and deletes (as SQL Server has to do extra work to keep
> the indexed view current).
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

primary key's

Is it absolutely necessary to have primary keyâ's on every table. I have
several examples in a db where the logical choice for a pk is never used in
joins, where clauses, or order by clauses. I would like to create a
clustered index on these tables but not necessarily a primary key. Anyone
have advice for me?"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:65ADC838-7965-44D2-B994-FFDD3D8EBD41@.microsoft.com...
> Is it absolutely necessary to have primary key's on every table. I have
> several examples in a db where the logical choice for a pk is never used
> in
> joins, where clauses, or order by clauses. I would like to create a
> clustered index on these tables but not necessarily a primary key. Anyone
> have advice for me?
Why would you want duplicate rows in these tables?
David|||Hi,
Looks like your database is not fully a normalized one. Look into the below
considerations before creating a clusterd index:- (FROM Books online)
Before creating clustered indexes, understand how your data will be
accessed. Consider using a clustered index for:
a.. Columns that contain a large number of distinct values.
b.. Queries that return a range of values using operators such as BETWEEN,
>, >=, <, and <=.
c.. Columns that are accessed sequentially.
d.. Queries that return large result sets.
e.. Columns that are frequently accessed by queries involving join or
GROUP BY clauses; typically these are foreign key columns. An index on the
column(s) specified in the ORDER BY or GROUP BY clause eliminates the need
for SQL Server to sort the data because the rows are already sorted. This
improves query performance.
f.. OLTP-type applications where very fast single row lookup is required,
typically by means of the primary key. Create a clustered index on the
primary key.
Clustered indexes are not a good choice for:
a.. Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the
data values of a row in physical order). This is an important consideration
in high-volume transaction processing systems where data tends to be
volatile.
b.. Wide keys
The key values from the clustered index are used by all nonclustered
indexes as lookup keys and therefore are stored in each nonclustered index
leaf entry.
Thanks
Hari
SQL Server MVP
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:65ADC838-7965-44D2-B994-FFDD3D8EBD41@.microsoft.com...
> Is it absolutely necessary to have primary key's on every table. I have
> several examples in a db where the logical choice for a pk is never used
> in
> joins, where clauses, or order by clauses. I would like to create a
> clustered index on these tables but not necessarily a primary key. Anyone
> have advice for me?|||Yes...you really should have a primary key for your tables.
Just because the PK is not used in joins, where clauses or
order by clauses doesn't mean you should not have one. Use
in joins, order by or where clauses is not what you would
use as the criteria in selecting a primary key.
The primary key does not have to be the clustered index. You
can still create those on other columns.
-Sue
On Wed, 31 Aug 2005 14:45:10 -0700, Wes
<Wes@.discussions.microsoft.com> wrote:
>Is it absolutely necessary to have primary key?s on every table. I have
>several examples in a db where the logical choice for a pk is never used in
>joins, where clauses, or order by clauses. I would like to create a
>clustered index on these tables but not necessarily a primary key. Anyone
>have advice for me?sql

Primary Keys

Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.
Thanks,
FadyEnterprise Manager ?|||I know I can use enterprise manager and I know that I can get the name of the primary key from sysobjects in a query but I need to know if I can in one sql statement (using alter table ... drop constraint as example) drop the primary key for a certain table.|||One of the odder requests seen on this forum.

To do it as a single statement you would need to EXECUTE an SQL String that you construct on the fly from the schema tables.

And I can't believe there would be a good reason for doing this.

primary keys

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."

can soemone explain to me why i have this kind of error?

i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!

all the values from of application_input will be stored also in approve_overtime.

sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

Check if you are wanting to insert duplicate id_no into approve_overtime ?

Friday, March 23, 2012

PRIMARY key?

Hi, how to list the table's PRIMARY key info?Select Constraint_name,Constraint_type,Table_Na
me
from Information_schema.Table_constraints
WHere Constraint_Type = 'PRIMARY KEY'
AND Table_Name = 'Orders'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:Otfg71mVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, how to list the table's PRIMARY key info?
>|||sp_primarykeys
Returns the primary key columns, one row per key column, for the specified
remote table.
"js" <js@.someone@.hotmail.com> wrote in message
news:Otfg71mVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi, how to list the table's PRIMARY key info?
>|||Thanks Jens...
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Omd806mVFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Select Constraint_name,Constraint_type,Table_Na
me
> from Information_schema.Table_constraints
> WHere Constraint_Type = 'PRIMARY KEY'
> AND Table_Name = 'Orders'
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:Otfg71mVFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||also want to know what fields are component of the key, how to know?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:Omd806mVFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Select Constraint_name,Constraint_type,Table_Na
me
> from Information_schema.Table_constraints
> WHere Constraint_Type = 'PRIMARY KEY'
> AND Table_Name = 'Orders'
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:Otfg71mVFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||Perhaps you should consider the Information Views on your own to see what
you can do with that:
http://www.insidesql.de/index.php?o...tart=0&limit=50
Regards, Jens.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uLJCI9mVFHA.3424@.TK2MSFTNGP09.phx.gbl...
> also want to know what fields are component of the key, how to know?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:Omd806mVFHA.3316@.TK2MSFTNGP14.phx.gbl...
>

Primary Key: I am trying to understand--please help if you can.

Hi all,

So I've been reading the newsgroups, and reading a few books trying to learn
SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know (I think) that a Primary Key is a
special field that uniquely identifies each record or row within a table.

My question is this: If I have a field or column whose values are all (and
will be) different in every row, is that what a Primary Key is, or is it not
a primary key until I designate it as such with either a statement or by
right-clicking a little key on the table diagram.

If so, then what is the reason _behind_ why I have to do that, and have a
primary key?

I've been playing with a few tables, and I can select information just fine,
relate tables together with joins, and everything works fine (so far) all
without my designating any Primary Keys--so why, exactly, do I need one, and
exactly what is it?

I would appreciate any help and clarification.

Thanks.Replied in microsoft.public.sqlserver.programming.

Please don't multi-post.

--
David Portas
SQL Server MVP
--|||David Portas scratched out in the sand

> Replied in microsoft.public.sqlserver.programming.
> Please don't multi-post.

...and for those of us who don't subscribe to m.p.s.p, your answer, David,
was...?

--
kai - kai at 3gproductions dot com
www.gamephreakz.com || www.filesite.org
"friends don't let friends use windows xp"|||Hi Mavis,

As you suggest, a Primary key is a constraint on the column requiring that
every value is (and will be) different
in every row. Furthermore, it requires that no row can have a NULL value for
that column. So those are the restrictions
on a Primary Key field, that it be non-null and that each value be unique.
Further, a Primary Key can be made up of more than
one column. So, each table can have only one Primary Key, but it can be
made up of several columns. (jeesh- so far I'm probably
just making this MORE confusing).

So, although those are the characteristics of a Primary Key, a column is not
one until you designate it as such. The reason behind it is
twofold (there may be more reasons, but only two come to mind at the
moment - Note to self: more coffee):
1. Performance - when you designate a column as a Primary Key, it is
(clustered) indexed to assist in data retrieval.
2. Data Integrity - the database will ensure that every value that is
inserted (or updated) in the table will meet the requirements of a
Primary Key (i.e. unique and non-null) so you don't have to write those
checks into application code.

It's assumed that the Primary Key field will typically be the field used in
table joins, so because it's indexed (it's also a good general plan
to index the foreign key field) your queries will perform better.

I hope this is of some help Mavis,

Steve

"Mavis Tilden" <mavistilden@.hotmail.com> wrote in message
news:XVead.9397$3C6.206028@.news20.bellglobal.com.. .
> Hi all,
> So I've been reading the newsgroups, and reading a few books trying to
learn
> SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
> every table should have one. I know (I think) that a Primary Key is a
> special field that uniquely identifies each record or row within a table.
> My question is this: If I have a field or column whose values are all (and
> will be) different in every row, is that what a Primary Key is, or is it
not
> a primary key until I designate it as such with either a statement or by
> right-clicking a little key on the table diagram.
> If so, then what is the reason _behind_ why I have to do that, and have a
> primary key?
> I've been playing with a few tables, and I can select information just
fine,
> relate tables together with joins, and everything works fine (so far) all
> without my designating any Primary Keys--so why, exactly, do I need one,
and
> exactly what is it?
> I would appreciate any help and clarification.
> Thanks.|||On Mon, 11 Oct 2004 04:50:11 -0700, filesiteguy wrote:

>David Portas scratched out in the sand
>> Replied in microsoft.public.sqlserver.programming.
>>
>> Please don't multi-post.
>>
>...and for those of us who don't subscribe to m.p.s.p, your answer, David,
>was...?

Hi filesiteguy,

http://www.google.com/groups?hl=en&...bellglobal.com

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

primary key/foreign key relationship

I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?

Bob K.

There is not designer support for doing this with SQL CE, so you must use:

Code Snippet

ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)

Designer support for this is promised for the next version of SQL CE (3.5) - in the "Orcas" timeframe.

|||thanks Eric.
|||Eric,

is there a way to enter the above sql statement from with Studio Express?
|||Connect to the SDF file, and select File, New, New query, type the text in the query editor and press F5 to execute...|||once again, thank you.
|||uhhh... and the beat goes on...

i've entered the following sql statement:

ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)

and received the following error:
Major Error 0x80040E14, Minor Error 25501
> ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = FOREIGN ]

i've attached a link that shows the tables and columns i'm attempting to update. i just can't see what i'm doing wrong. could someone help?

bob k.

|||the link:

http://www.rkamarowski.com/errors/error.jpg

|||

Sorry, the correct syntax is (using the sample Northwind.sdf):

Code Snippet

ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])

|||perfect! thanks eric.
|||

Good Afternoon Erik,

Can you help me out with a project of my?! I programming for the first time in the .net cf and i create my database all manualy thought SqlCeEngine..

But i want to know if there is a better approach to do this by the "next, next theory" (visuali) . if i create the database using a GUI of sql serv mobile edition, how can i choose where this database will be created and how can i indicate to the datasource in the connection string of an sqlConnection where is my database?! can you help me? i have a checkpoint day 17 to present my work and i would like to finish the hole project will that date to get some time to another project that is coming up, so its kind of urgent subject.

Thank you anyway!

Primary Key, Normalizing?

I am a beginner, so please bare with me. I get very confused on how to normalize my database.

Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.

Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.

How do I setup this table structure?

Employees Table
Login (PK) | First | Last | Extension......
--------------
jsmith | John | Smith | 280

Department Title Breakdown
Department | Title
--------
Engineering | Detailer
Engineering | Engineer
Management | ProjectManager

Job Description
Login | Title
--------
jsmith | Engineer
jsmith | Detailer
jsmith | ProjectManager

This is important to break this down because for each project the following is saved:

Project Listing
Project | Detailer | Estimator | Sales | Engineer |...... | Location
10001 | jsmith | jdoe | mslick | sjunk | ..... | Las Vegas

Or should the project be broken down as well

Project Listing
Project | Location
10001 | Las Vegas

Project Team
Project | Member | Activity
10001 | jsmith | Engineer
10001 | mstevens | Detailer

Any thoughts on how to normalize this?

Mike BMikeB,
As far as I can tell you have a decent start on the process and relationships. To me, the key determinant of whether you proceed to separate out the various "step owners" for a particular project is how stable the steps to your projects are. If you have always had an Estimator, a Detailer, an Engineer and a Salesperson for every project and every project has just one person doing that particular task, then it's only extra work to break the relationship out into a separate table.

BUT, if ever there has been a situation where more than one person has done the Estimating (for example) for a project, or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.

Look at your business and try to foresee what things might be like six months from now. If in your mind's eye things aren't going to change, then avoid the extra work and invest it somewhere else.

But that's just my opinion (and I'm lazy).

Regards,

hmscott

Edit note: Apparently, I'm so lazy that adding an "n't" is just too much work. Sigh.|||when you say 'Normalize', what do you mean?

Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. I used to get argued about this one quite a bit,, it actually means 1NF)

How far do you want to go?

BoyceCoddNF or 3NF or higher

the true trick to normalization is to consider the pimary key as the parent within the table iteself in a relationship with the other attributes(cols)
if a col has a many to one relationship to the key, and it isnt a shared dependency with another column in the table. then you are on the right track to 3NF and BCNF|||[quote]
and it isnt a shared dependency with another column
[quote]

What is meant by a shared dependency?

Let's say I have a Company Table

Company
ID (PK) | Name | Address | City | State | ....

The City and State would be repeated many times, therefore, not dependent and can be many keys to one city / state. This is what you mean correct?

Should be broken yet even further.

Company
ID (PK) | Name | Address | Postal (FK)

Zip
Postal (PK) | City | State

Mike B|||i dont know what i was thinking
i meant functional dependency

this is where one item is dependent on another for its definition.
the dependency must hold true for every possible value of the item
currently and in the future
++++++++++++++++++++++++++++++
Product , Price , Weight
rock, 1.00, 1#
rock, 1.00, 1.5#
Rock, 1.00, .75#
brick, .75, 2.0#
brick, .75, 2.3#
stone, .85,.25#
marble, 45., 15.#
++++++++++++++++++++++++++++++
price for brick is always .75 even if the weight changes.
price is functionaly dependent on product
reperesented as (Product) -> Price
and actually product is fd on price because each individual product has only one price.
so that would be (Price) -> Product

if the price of rocks went up based on the weight (example,,, all rocks that weigh 1.5# will now cost $1.50

now price is no longer functionally dependent on product alone now the FD is represented as
(Product, Weight) -> Price

this is something that you should be aware of and consider highly while designing your tables but remember that the relational rules are good ones but are in no way a law "more of a guideline than anything"|||Originally posted by hmscott
or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.

Very very possible. Thanks for you comments.

Mike B|||Originally posted by Ruprect
i dont know what i was thinking
i meant functional dependency

Thank you for your comments. I am looking at this, and to tell you the truth, I cannot see one area where this is a problem. Time to take out the magnifying glass and get a closer look :)

Mike B

Primary key, Identity, Integrity constraints

Hi,

We will be using SQL Compact Edition in our application. We are doing some tests to make sure that SQL Compact is suitable for our requirments.

Our problem is with Primary keys and how to maintain data integrity since we will have 100+ clients inserting, updating, and synchronizing with SQL Server.

Can we control Identity range?

Shall we use UniqueIdentifires insted of int?

How can we detect errors at the client side? (For example, if two clients are trying to synchronize 2 columns with the same primary key value)

We will use Merge replication.

Thank you

First, understand that a SQLCe database is SINGLE user. If you want to share the data, you'll need to replicate to a SQL Server Workgroup edition or better. In this case, you'll need to use UniqueIdentifiers to make sure each separate database has unique values for the PKs. This is discussed in my new EBook. See www.hitchhikerguides.net.sql

Primary Key, bigint or char?

Hey guys, just a quick question for you. I think I know the answer, but I
would like to get the expert's opinions...
I have a primary key called RequestID that is to be constructed of the
year, month, day, and a four-digit sequence number with leading zeros. An
example of the ninth Request placed today would be:
200506090009
As well as being the primary key, this field will be used in WHERE clauses
and in joins to several other tables in my schema.
My question is; should the data type be bigint or char(12)?Well, judging by size, I would choose the bigint since it is only 8 bytes
instead of the 12 bytes that are used by a char(12). I am not sure on this,
but I would assume that searching on an int would be faster. Again, that is
pure speculation on my part and I have no way to back it up. Just my 2￠.
Chris
"Tim Baur" wrote:

> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?
>|||>> As well as being the primary key, this field will be used in WHERE
Logically, the data type of an attribute is often decided based on the
operations applicable on its values. Therefore as a primary consideration,
identify the potential operations -- either integer specific or character
specific -- and use that for determining the type.
Also, physical factors like storage size of the values, potential
performance implications etc. can be a valid considerations during specific
implementation, depending on how the queries are formulated and how the data
is updated. So 8 bytes vs. 12 bytes, esp. in a large table often tend to
favor smaller size datatype, esp. for key columns.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in
news:ePLNgaTbFHA.3932@.TK2MSFTNGP12.phx.gbl:

> Logically, the data type of an attribute is often decided based on the
> operations applicable on its values. Therefore as a primary
> consideration, identify the potential operations -- either integer
> specific or character specific -- and use that for determining the
> type.
> Also, physical factors like storage size of the values, potential
> performance implications etc. can be a valid considerations during
> specific implementation, depending on how the queries are formulated
> and how the data is updated. So 8 bytes vs. 12 bytes, esp. in a large
> table often tend to favor smaller size datatype, esp. for key columns.
>
Thank you, Chris and Anith, for your input. I also suspect that bigint is
the best choice, but because it is numeric more than size considerations.
If given the choice between char(8) and bigint I would still choose bigint.
I know from general programming experience that numeric lists are faster to
index, sort, and search than strings. I suspect that SQL Server wouldn't
be any different in this, but that is just a guess. I would be curious to
hear the definitive answer on the topic.|||Well, I am definitely not an expert, but I definitely have opinions, so here
goes...
May I choose super-secret option number three, "None of the Above"? How
about using a two-column key, consisting of RequestDate(smalldatetime) and
RequestNumber(smallint)? This would provide the following benefits:
1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime = 4
bytes and smallint = 2 bytes)
2) You get a larger set of available numbers for RequestNumber - 10000 in
you setup versus 32000+ in my setup (if you only use the positive values).
3) Makes standard types of calculations extremely easy. If you want to
retrieve items for a range of dates, you do not need to parse the key column
to extract the date portion. If you want to look for 'gaps' in the request
numbers for a given day, you do not need to parse the key column. The list
goes on and on.
4) Searches for a specific value are no problem - just allow the user to
enter the value in the encoded format, and convert it to your two column
values. When presenting the value, re-format to the encoded format (which
should really be done in the user interface layer anyway). The users never
need to deal with the actual storage format - and that should (almost) never
be the end-users' concern anyway.
Since you did not post DDL for this table, it is impossible to know, but I
would guess that you might already have a column for RequestDate. In that
case, there really is no good reason for the duplication - just run with
what you have.
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns9670A0623995Ctrbo20DISREGARDyaho
o@.207.46.248.16...
> Hey guys, just a quick question for you. I think I know the answer, but I
> would like to get the expert's opinions...
> I have a primary key called RequestID that is to be constructed of the
> year, month, day, and a four-digit sequence number with leading zeros. An
> example of the ninth Request placed today would be:
> 200506090009
> As well as being the primary key, this field will be used in WHERE clauses
> and in joins to several other tables in my schema.
> My question is; should the data type be bigint or char(12)?|||Just a thought,
How about Decimal(12,0)?
That will use 9 bytes though, not really any advantage from a "compact key"
perspective.
Better than having a composite primary key. IMO composite primary key's are
evil.
Just my $0.02.
Richard|||"Jeremy Williams" <jeremydwill@.netscape.net> wrote in
news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:

> Subject: Re: Primary Key, bigint or char?
> From: "Jeremy Williams" <jeremydwill@.netscape.net>
> Newsgroups: microsoft.public.sqlserver.programming
> Well, I am definitely not an expert, but I definitely have opinions,
> so here goes...
> May I choose super-secret option number three, "None of the Above"?
> How about using a two-column key, consisting of
> RequestDate(smalldatetime) and RequestNumber(smallint)? This would
> provide the following benefits:
> 1) Uses two less bytes than a bigint (bigint = 8 bytes, smalldatetime
> = 4 bytes and smallint = 2 bytes)
> 2) You get a larger set of available numbers for RequestNumber - 10000
> in you setup versus 32000+ in my setup (if you only use the positive
> values). 3) Makes standard types of calculations extremely easy. If
> you want to retrieve items for a range of dates, you do not need to
> parse the key column to extract the date portion. If you want to look
> for 'gaps' in the request numbers for a given day, you do not need to
> parse the key column. The list goes on and on.
> 4) Searches for a specific value are no problem - just allow the user
> to enter the value in the encoded format, and convert it to your two
> column values. When presenting the value, re-format to the encoded
> format (which should really be done in the user interface layer
> anyway). The users never need to deal with the actual storage format -
> and that should (almost) never be the end-users' concern anyway.
> Since you did not post DDL for this table, it is impossible to know,
> but I would guess that you might already have a column for
> RequestDate. In that case, there really is no good reason for the
> duplication - just run with what you have.
>
These are all good points, Jeremy, and your approach was considered.
The way it currently stands:
The request table has an assignment log that will log the creation
date. The point to using a date in the key is because I want the
request number to reset after each day. I would rather the users not be
able to easily figure out how many requests the help desk gets during
the course of a w

.
There will *never* be more than 9,999 requests in one day. There
will probably never be more than 10.
Gaps in the sequence are no big deal. I would use Count() to get a
count of records for any particular day or date range.
Composite Keys are not the friendliest to use, especially in a table
as central to the schema as this one. I do use them in other places,
but this particular key serves as a foreign key in six other tables.|||Yeah, I know some developers/DBAs have an unnatural aversion to composite
keys. It seems to "make their teeth itch".
I have never understood the problem myself - it seems much more natural to
use the key information you are already collecting than to make up yet
another key just so you can get it down to one column. As often as not, all
this does is force the developer to join tables unnecessarily when
retrieving data, or use inefficient criteria in their WHERE clauses because
they are parsing the "composite" single column.
As for keeping the user from knowing the number of requests per w

, I was
under the impression you would reset the request number each day no matter
how you constructed the key. It would not seem to make a difference whether
you were munging that in with the date in one column, or keeping them
separate - the work is the same. You must have something more going on here
that you have not shown, I guess.
Since your design is already decided on (no composite keys in this
situation), then there really does not seem to be anything compelling
criteria other than storage size to recommend one choice over the other. Go
with the bigint (as others have already said) - it should suit you design
well. Thanks for the dialog!
"Tim Baur" <trbo20DISREG@.ARDyahoo.com> wrote in message
news:Xns96715ECD5928Etrbo20DISREGARDyaho
o@.207.46.248.16...
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in
> news:OIfXkkTbFHA.2420@.TK2MSFTNGP12.phx.gbl:
>
> These are all good points, Jeremy, and your approach was considered.
> The way it currently stands:
> The request table has an assignment log that will log the creation
> date. The point to using a date in the key is because I want the
> request number to reset after each day. I would rather the users not be
> able to easily figure out how many requests the help desk gets during
> the course of a w

.
> There will *never* be more than 9,999 requests in one day. There
> will probably never be more than 10.
> Gaps in the sequence are no big deal. I would use Count() to get a
> count of records for any particular day or date range.
> Composite Keys are not the friendliest to use, especially in a table
> as central to the schema as this one. I do use them in other places,
> but this particular key serves as a foreign key in six other tables.
>|||>> Yeah, I know some developers/DBAs have an unnatural aversion to composite
Actually some level of aversion might be quite natural given the existence
of known issues caused by composite keys that are well documented in
relational literature. Not sure if my teeth itches :-)
Not specific to this case, but in general, simple keys often assist in
formulating simpler relational expressions. Simple keys are often minimal
and always irreducible and therefore partial key dependencies are never an
issue.
And as already mentioned above, there are certain known problems exhibited
by compound keys in referencing table when data may be missing. For
instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing.
You are right; often developers include additional identifiers assuming they
are a panacea, without understanding the potential benefits or implications
of using an existing key, simple or composite.
Anith|||Thanks for replying, Anith - It is nice to get the input of one of the
actual experts!
Can you guide me to some resources where I can study the composite-key
issues you mentioned further? I have not come across any issues so far, but
I have only been developing in SQL for 10 years or so, so I am sure I have
not seen all there is to see.
My experiences with partial key dependencies typically signified that there
was normalization issues that needed to be worked out - creating an
additional simple key in addition to the composite key would really not have
helped at all. And I freely admit that I do not understand what you mean by:
"For instance, there is no logical choice for a DBMS to decide on whether to
insert/update a row in a referencing table when the values in a subset of
the columns in a multi-column referencing key are missing." If the composite
key is being used in a referencing/referenced table scenario, how does
missing data get involved - a normalized design would require all components
of the composite key to exist, wouldn't it?
I am always looking to expand my knowledge, so please let me know where I
can find out more. Thanks!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uYEHCldbFHA.1384@.TK2MSFTNGP09.phx.gbl...
composite
> Actually some level of aversion might be quite natural given the existence
> of known issues caused by composite keys that are well documented in
> relational literature. Not sure if my teeth itches :-)
>
yet
> Not specific to this case, but in general, simple keys often assist in
> formulating simpler relational expressions. Simple keys are often minimal
> and always irreducible and therefore partial key dependencies are never an
> issue.
> And as already mentioned above, there are certain known problems exhibited
> by compound keys in referencing table when data may be missing. For
> instance, there is no logical choice for a DBMS to decide on whether to
> insert/update a row in a referencing table when the values in a subset of
> the columns in a multi-column referencing key are missing.
>
their
> You are right; often developers include additional identifiers assuming
they
> are a panacea, without understanding the potential benefits or
implications
> of using an existing key, simple or composite.
> --
> Anith
>

Monday, March 26, 2012

primay key

primay key

primay key

Primarykey Fields

PrimaryKey and Index

PrimaryKey and Index

PrimaryKey and Index

Primary, Indexes and Foreign Key - Best Place for them

Primary versus unique keys

Primary keys, indexes and speed

primary key's

Primary Keys

primary keys

Friday, March 23, 2012

PRIMARY key?

Primary Key: I am trying to understand--please help if you can.

primary key/foreign key relationship

Primary Key, Normalizing?

Primary key, Identity, Integrity constraints

Primary Key, bigint or char?

Preventing Deadlock

Blog Archive

About Me