Primary keys ...

I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
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 :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:

> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>

Primary Key with Cluster Index

Hi All,

One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables

Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table

As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data

I tried following options with vain

1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also

Pls give me any other solution or suggestion in this regard

with Thanks & Regards
BhaskaraFirst suggestion: pick a column name more descriptive than "ID"....

Second suggestion: one million rows is not that much data. Could there be some other reason why inserts are taking so long?

Third suggestion: you can reduce the number of initial page splits setting the fill factor lower when you create the index.|||If the clustered index is an identity then you are not getting page splits unless you are making changes to data attribute columns and increasing the size of this data. On inserts you are certainly not getting page splits - monotonically increasing clustered indexes like this will not create splits and are the fastest possible index for inserts (faster even than no clustered index).

Hang on - I just reread - are you changing the clustered index constantly?|||There appears to be more than one problem here, so it may take several steps to correct all of the problems.

First and foremost, you specified in your ALTER TABLE that you wanted the index to be clustered, so SQL Server dutifully did what you asked. That is not part of your problem at all.

A million rows is not a large table for SQL Server. A billion rows might be large, but a million definitely is not.

SQL Server disk I/O is what drives log usage. The log file is essentially a record of what changes were made to the database at the disk (binary) level.

Dropping a clustered index shouldn't be expensive in terms of time, but the server should not permit you to drop an index that is placed by the server to protect DRI (Declarative Referential Integrity). It shouldn't take a long time, but the DROP INDEX ought to fail.

When you create a new clustered index, it can cause massive amounts of I/O operations because a clustered index effectively rearranges the data store for the entire table. That means that every row in the table can (and probably will) move, and that every existing index on the table will need to be rebuilt.

When you change a table with a clustered index (using INSERT, UPDATE, or DELETE) the amount of I/O should be nearly the same as updating the same table without the clustered index. It is possible that you might have a page split that a heap insert would not incur, but the grand total of the I/O shouldn't be significantly different.

Whatever is causing the change in disk I/O and log file usage is almost certainly not the clustered index alone. Something else is either part or all of this problem.

To answer your questions as you posted them:

1) Take regular incremental backups to allow log space to be reused. This should not affect the time needed, but it will reduce the log file growth.

2) Dropping the clustered index should be impossible using DROP INDEX. I think you'll need to use ALTER TABLE to make this happen. Dropping the index ought to be quick, although creating a new index may take a while.

-PatP|||Dropping a clustered index shouldn't be expensive in terms of time

Unless there are other indexes on the table.|||are there any triggers on this table?|||If the clustered index is an identity then you are not getting page splits unless you are making changes to data attribute columns and increasing the size of this data. On inserts you are certainly not getting page splits - monotonically increasing clustered indexes like this will not create splits and are the fastest possible index for inserts (faster even than no clustered index).

Hang on - I just reread - are you changing the clustered index constantly?

Thanks for your interest in my problem
When I observed in Sql profiler, if there is any Insertion or Updation, it is trying re-page(indexing Pages) whole

come to last point made by you
Intially I declared it as clustered index, now because of slow performance i wanted it to drop and create NON-CLUSTER index|||Hi Pat Phelan,
Thanks for your interest in my problem

I Got what exactly you wanted to say
still I have some clarifications

1) I try to drop the clustered index using "ALTER TABLE" after removing DRI
and then I recreate the NONCLUSTERED INDEX using
"ALTER Table Document ADD CONSTRAINT PK_Document_id PRIMARY KEY NONCLUSTERED (id) ON [PRIMARY] "

whether it will give any impact on performance|||OK, you're dropping the constraint, not the index

Do you know what clustering means?

Primary Key Violation Error in SQL 2005

Hey guys...

I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alotHey guys...

I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alot

Thats strange,you should get an error for that in MSSQL 2000 also.|||Can you post DDL (without editing) for the victim table..?

DDL for both tables i.e. table in 2000 & table in 2005.

I used to transfer data, but I didn't face such problem.|||Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?|||Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?

No,that will also give an error of Duplicate key error in your DTS Package execution.|||No,that will also give an error of Duplicate key error in your DTS Package execution.

maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?|||maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?
exactly ;)|||thanks for your replies. You can ignore my statements on "fail package on step failure." I am not using DTS for the import. Its an insert statement created from a recordset in a vb application.

In SQL 2000 when a duplicate record tries getting inserted it's ignored and moves on to the next record. In SQL 2005 when a duplicate record tries getting inserted I'm getting the "Duplicate Key" error. And the vb code is the same.

Not sure if this helps, but previously this error was resolved by re-restoring the database. Could this be something that was overlooked during the restoration procedure?|||if you are not using DTS, then my previous comment applies. This exact behavior can happen if you have a PK that was created with IGNORE_DUP_KEY=ON (in that case, dupes will be ignored and not inserted).

so check if the PK is IGNORE_DUP_KEY=ON on the 2000 box and IGNORE_DUP_KEY=OFF on the 2005 box.|||I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

Is there a way to ignore this error message from appearing? Thanks|||I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

Is there a way to ignore this error message from appearing? Thanks

Have you created that table in question?If not, then check for any trigger in that existing table...otherwise I think you should get an error ,and thats the way MSSQL works...sql

PRIMARY key versus UNIQUE index

Hi Group
Other than the fact that PRIMARY keys seem to be defined at table
creation time - what's the difference between a primary key and a
UNIQUE index? If there is a difference, what's the typical use of a
PRIMARY key?
TIA
MarkusPrimary keys do not allow null values ... that is the only difference betwee
n
Primary key and Unique Key.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Markus Zingg" wrote:

> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||and unique key allows just one null value.
"Vadivel" wrote:
> Primary keys do not allow null values ... that is the only difference betw
een
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||Hi
Not, the other difference is that when you create a primary key, SQL Server
creates a clustered index ,as opposite UNIQUE key it creates unique
nonclustered index
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
> Primary keys do not allow null values ... that is the only difference
> between
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||From the database design point of view the PRIMARY KEY should reflect the
natural key of your data.
From the technical point of view, a PRIMARY KEY does not allow NULL's, where
as a UNIQUE Constraint / Index allows at least 1 NULL.
From my own stand point I use PRIMARY KEY constraints on the natural key and
a UNIQUE Constraint for the surrogate key eg...
CREATE TABLE currency (
id int not null identity constraint sk_currency unique clustered,
code char(3) not null constraint pk_currency primary key nonclustered
)
CREATE TABLE trade (
..
...
settlement_currency_id int not null references currency( id ),
trade_currency_id int not null references currency( id ),
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Markus Zingg" <m.zingg@.nct.ch> wrote in message
news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||Unless it's composite.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:01F43F78-1A63-4AD9-B5C0-D4F2486524C1@.microsoft.com...
> and unique key allows just one null value.
> "Vadivel" wrote:
>|||Just for the sake of completion. We can create Primary Keyws without
clustered index also. Check out
http://vadivel.blogspot.com/2006/03...el.blogspot.com
"Uri Dimant" wrote:

> Hi
> Not, the other difference is that when you create a primary key, SQL Serve
r
> creates a clustered index ,as opposite UNIQUE key it creates unique
> nonclustered index
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
>
>|||This is a default behavior for the creation of a primary key; you can
override it, and make your underlying index a nonclustered one.
CREATE TABLE foo (pkid int PRIMARY KEY NONCLUSTERED,
doe smalldatetime)
CREATE CLUSTERED INDEX clidx ON foo(doe)
I try to keep the concepts of indexes and constraints as seperate as
possible in my mind (and in my designs); one is a physical tool to
assist in database performance, the other is a logical tool to assist
in data integrity. Sometimes it's useful to have a clustered primary
key; sometimes it's not.
Stu|||Hi Tony!

> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
Interesting... I have a feeling that many people does it the other way aroun
d (identity is PK, and
natural key(s) are UQ). Is it just by habit, or do you have any particular r
eason doing it that way?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> From the database design point of view the PRIMARY KEY should reflect the
natural key of your
> data.
> From the technical point of view, a PRIMARY KEY does not allow NULL's, whe
re as a UNIQUE
> Constraint / Index allows at least 1 NULL.
> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
> CREATE TABLE currency (
> id int not null identity constraint sk_currency unique clustered,
> code char(3) not null constraint pk_currency primary key nonclustered
> )
> CREATE TABLE trade (
> ...
> ...
> settlement_currency_id int not null references currency( id ),
> trade_currency_id int not null references currency( id ),
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Markus Zingg" <m.zingg@.nct.ch> wrote in message
> news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
>|||Hi Tibor - long time no beer....
My feeling and approach is this, most tables will have a natural key that we
need to provide uniqueness for, I make the natural key the PRIMARY KEY (it
also flows naturally from the logical design), the surrogate key is there as
part of the implementation phase to improve schema performance and
scalability hence I make it a unique constraint to enforce uniqueness, and
yes - use IDENTITY to auto populate.
There are a number of occaisions where there is no practical usable natural
key and in that instance I will just make the column with the IDENTITY
property the PRIMARY KEY and not bother with the surrogate.
I never use the natural key anywhere else - there is just one copy of it and
thats in its base table, and foreign key references I use the surrogate key.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCpTmJSaGHA.4544@.TK2MSFTNGP02.phx.gbl...
> Hi Tony!
>
> Interesting... I have a feeling that many people does it the other way
> around (identity is PK, and natural key(s) are UQ). Is it just by habit,
> or do you have any particular reason doing it that way?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
>

Primary Key is Defined, need to process thrown error:

Hello:

I have a database table where a primary key is defined. When I enter data that is the same as another table, it does not allow and throws an error which I do want. What happens is that a Server Error in Application error type is thrown with the following message:

Violation of PRIMARY KEY constraint 'PK_cs_sc'. Cannot insert duplicate key in object 'cs_sc'. The statement has been terminated.

How can I detect the error in my own asp.net page code so that it does not forward my users to the asp.net server error page? Heres my code below... how can I check for success or failure within this code?

Dim conSqlConnect As SqlConnection
Dim strInsert As String
Dim cmdInsert As Sqlcommand
conSqlConnect = New SqlConnection( "Server=localhost;uid=var;pwd=var;database=var" )
strInsert = "Insert cs_sc ( [name] ) Values ( @.name )"
cmdInsert = New SqlCommand ( strInsert, conSqlConnect )
conSqlConnect.Open()
cmdInsert.ExecuteNonQuery()
conSqlConnect.Close()erm, try...catch?|||give the table script, please|||What do you mean by table script?sql

Primary Key defined during Replication

Does Microsoft SQL 2000 Replication require that a primary key be defined on
all tables to be replicated between databases? We are planning on using
Replication to create a near real-time reporting database, based upon our
production database records.
Thanks
Vilma J.
For transactional replication, yes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vilma Johnson" <admin@.myself.org> wrote in message
news:9371ECDD-5A80-41A9-94A4-F4A70B855CB9@.microsoft.com...
> Does Microsoft SQL 2000 Replication require that a primary key be defined
> on
> all tables to be replicated between databases? We are planning on using
> Replication to create a near real-time reporting database, based upon our
> production database records.
> Thanks
> --
> Vilma J.