Trying to stablish reatlionship between tables"
BioData and EventstHistory with a key called Enumber in
BioData and foreing key called also Enumber in
EventsHistory, i get this message:
'biodata' table saved successfully
'eventshistory' table
- Unable to create
relationship 'FK_eventshistory_biodata'.
ODBC error:
SQL Server]ALTER TABLE statement conflicted with COLUMN
FOREIGN KEY constraint 'FK_eventshistory_biodata'.
The conflict occurred in database 'hrMasterData',
table 'biodata',
column 'enumber'.Fredy,
Can you please pass us the ALTER TABLE script you executed and the structure
for the two tables?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
news:59fa01c37700$91828020$a601280a@.phx.gbl...
> Trying to stablish reatlionship between tables"
> BioData and EventstHistory with a key called Enumber in
> BioData and foreing key called also Enumber in
> EventsHistory, i get this message:
> 'biodata' table saved successfully
> 'eventshistory' table
> - Unable to create
> relationship 'FK_eventshistory_biodata'.
> ODBC error:
> SQL Server]ALTER TABLE statement conflicted with COLUMN
> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
> The conflict occurred in database 'hrMasterData',
> table 'biodata',
> column 'enumber'.|||I did not crate any script, i did it from the table Design
Table option, is there any way to gather the script
generated?
>--Original Message--
>Fredy,
>Can you please pass us the ALTER TABLE script you
executed and the structure
>for the two tables?
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
>news:59fa01c37700$91828020$a601280a@.phx.gbl...
>> Trying to stablish reatlionship between tables"
>> BioData and EventstHistory with a key called Enumber in
>> BioData and foreing key called also Enumber in
>> EventsHistory, i get this message:
>> 'biodata' table saved successfully
>> 'eventshistory' table
>> - Unable to create
>> relationship 'FK_eventshistory_biodata'.
>> ODBC error:
>> SQL Server]ALTER TABLE statement conflicted with COLUMN
>> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
>> The conflict occurred in database 'hrMasterData',
>> table 'biodata',
>> column 'enumber'.
>
>.
>|||Thanks, here its :
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.eventshistory ADD CONSTRAINT
FK_eventshistory_biodata FOREIGN KEY
(
enumber
) REFERENCES dbo.biodata
(
enumber
)
GO
COMMIT
>--Original Message--
>Fredy,
>Yes.There is a button called 'save change script' , the
third one from left.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"FREDYCOREA" <fredycorea@.hotmail.com> wrote in message
>news:586701c37703$2f03c7d0$a501280a@.phx.gbl...
>> I did not crate any script, i did it from the table
Design
>> Table option, is there any way to gather the script
>> generated?
>>
>> >--Original Message--
>> >Fredy,
>> >
>> >Can you please pass us the ALTER TABLE script you
>> executed and the structure
>> >for the two tables?
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
>> >news:59fa01c37700$91828020$a601280a@.phx.gbl...
>> >> Trying to stablish reatlionship between tables"
>> >> BioData and EventstHistory with a key called Enumber
in
>> >> BioData and foreing key called also Enumber in
>> >> EventsHistory, i get this message:
>> >>
>> >> 'biodata' table saved successfully
>> >> 'eventshistory' table
>> >> - Unable to create
>> >> relationship 'FK_eventshistory_biodata'.
>> >> ODBC error:
>> >>
>> >> SQL Server]ALTER TABLE statement conflicted with
COLUMN
>> >> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
>> >>
>> >> The conflict occurred in database 'hrMasterData',
>> >> table 'biodata',
>> >> column 'enumber'.
>> >
>> >
>> >.
>> >
>
>.
>|||Fredy,
The below script works fine.
CREATE TABLE biodata
(
enumber INT PRIMARY KEY
)
GO
CREATE TABLE eventshistory
(
enumber INT
)
GO
ALTER TABLE dbo.eventshistory ADD CONSTRAINT
FK_eventshistory_biodata FOREIGN KEY
(
enumber
) REFERENCES dbo.biodata
(
enumber
)
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"fredy" <fredycorea@.hotmail.com> wrote in message
news:5a7901c37706$1b5f85e0$a601280a@.phx.gbl...
> Thanks, here its :
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.eventshistory ADD CONSTRAINT
> FK_eventshistory_biodata FOREIGN KEY
> (
> enumber
> ) REFERENCES dbo.biodata
> (
> enumber
> )
> GO
> COMMIT
>
>
> >--Original Message--
> >Fredy,
> >
> >Yes.There is a button called 'save change script' , the
> third one from left.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"FREDYCOREA" <fredycorea@.hotmail.com> wrote in message
> >news:586701c37703$2f03c7d0$a501280a@.phx.gbl...
> >> I did not crate any script, i did it from the table
> Design
> >> Table option, is there any way to gather the script
> >> generated?
> >>
> >>
> >> >--Original Message--
> >> >Fredy,
> >> >
> >> >Can you please pass us the ALTER TABLE script you
> >> executed and the structure
> >> >for the two tables?
> >> >
> >> >--
> >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
> >> >news:59fa01c37700$91828020$a601280a@.phx.gbl...
> >> >> Trying to stablish reatlionship between tables"
> >> >> BioData and EventstHistory with a key called Enumber
> in
> >> >> BioData and foreing key called also Enumber in
> >> >> EventsHistory, i get this message:
> >> >>
> >> >> 'biodata' table saved successfully
> >> >> 'eventshistory' table
> >> >> - Unable to create
> >> >> relationship 'FK_eventshistory_biodata'.
> >> >> ODBC error:
> >> >>
> >> >> SQL Server]ALTER TABLE statement conflicted with
> COLUMN
> >> >> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
> >> >>
> >> >> The conflict occurred in database 'hrMasterData',
> >> >> table 'biodata',
> >> >> column 'enumber'.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Friday, March 30, 2012
relationships, primary key sql2000 question.
Hello,
I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.
When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.
Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.
Thanks
Peter
If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.
If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.
|||What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms177288.aspx
http://msdn2.microsoft.com/en-us/library/ms186973.aspx
|||
Thanks guys, working through links now.
Peter
sqlRelationships problem in Sql Server 2000
I am using Enterprise Manager for Sql Server 2000. In designing a foreign key relationship for a table, let's call it table A, I have more then one column acting as a foreign key for the same primary column in another table, let's call it table B. While I can specify for one of those foreign key columns in table A, which has the same column in another table as its primary key, to Cascade Deletes and Updates, successfully, when I try to also specify Cascade Delete and Updates for a second of my foreign key columns, which has the same column in another table as its primary key, I get an error saying that the second relationship with the Cascade "may cause cycles or multiple cascade paths."
I do not understand why this should be happening. If the Cascade is allowed for one of my columns, why does allowing it for more than one of my columns which point to the same primary key column cause this problem. Or is this just a bug in Sql Server 2000 which has been fixed in later releases of the product.No, it's not a bug, it's by design. If you want to create more than one cascade relationship between two tables, then there is only a way is to specify only first relationship as cascade and maintain other(s) via trigger(s).|||I do not understand the reasoning behind this design. I do not see how it matters if deleting a row in one table causes more than one row in another table to be deleted because of more than one cascade relationship between tables. Also the same situation for updated cascades should work properly also if there is more than one relationship between two tables. I can understand if the relationship goes both ways it could cause a recurring cycle, but if the relationship just goes one way it never can cause cascading problems AFAICS.
I do not understand why this should be happening. If the Cascade is allowed for one of my columns, why does allowing it for more than one of my columns which point to the same primary key column cause this problem. Or is this just a bug in Sql Server 2000 which has been fixed in later releases of the product.No, it's not a bug, it's by design. If you want to create more than one cascade relationship between two tables, then there is only a way is to specify only first relationship as cascade and maintain other(s) via trigger(s).|||I do not understand the reasoning behind this design. I do not see how it matters if deleting a row in one table causes more than one row in another table to be deleted because of more than one cascade relationship between tables. Also the same situation for updated cascades should work properly also if there is more than one relationship between two tables. I can understand if the relationship goes both ways it could cause a recurring cycle, but if the relationship just goes one way it never can cause cascading problems AFAICS.
Wednesday, March 28, 2012
relationship question
Hi It may be a stupid question but I can not see why I define a relationship
and disable the option "Disabling a Foreign Key Constraint with INSERT and
UPDATE Statements"
in this case I just dont define the relationship.
Somebody can say a comment.
You can define FK on tables on simply disable them for loading
maintainance or other purposes, and then enable them again. Otherwise
you would have to drop them and recreate them.
CREATE TABLE SomeParentTable
(
ParentPKCol INT
CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
)
Create table SomeChildTable
(
PKChildCol INT,
ParentPKCol INT NOT NULL
CONSTRAINT fk1_SomeParentTable
FOREIGN KEY
REFERENCES SomeParentTable (ParentPKCol)
)
--Doesn=B4t work, CHECK is enabled
insert into SomeChildTable Values (1,1)
--Disabling the CHECK
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--This works now
insert into SomeChildTable Values (1,1)
--Delete it once again
DELETE FROM SomeChildTable
--THis is the normal behaviour
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--Inserting first the parent then the child records
insert into SomeParentTable Values (1)
insert into SomeChildTable Values (1,1)
Drop table SomeChildTable
Drop table SomeParentTable
HTH, Jens Suessmeyer.
|||Ohh absolutly, now I see the reason
Kenny M.
"Jens" wrote:
> You can define FK on tables on simply disable them for loading
> maintainance or other purposes, and then enable them again. Otherwise
> you would have to drop them and recreate them.
>
> CREATE TABLE SomeParentTable
> (
> ParentPKCol INT
> CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
> )
> Create table SomeChildTable
> (
> PKChildCol INT,
> ParentPKCol INT NOT NULL
> CONSTRAINT fk1_SomeParentTable
> FOREIGN KEY
> REFERENCES SomeParentTable (ParentPKCol)
> )
>
> --Doesn′t work, CHECK is enabled
> insert into SomeChildTable Values (1,1)
> --Disabling the CHECK
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --This works now
> insert into SomeChildTable Values (1,1)
> --Delete it once again
> DELETE FROM SomeChildTable
> --THis is the normal behaviour
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --Inserting first the parent then the child records
> insert into SomeParentTable Values (1)
> insert into SomeChildTable Values (1,1)
>
> Drop table SomeChildTable
> Drop table SomeParentTable
>
> HTH, Jens Suessmeyer.
>
sql
and disable the option "Disabling a Foreign Key Constraint with INSERT and
UPDATE Statements"
in this case I just dont define the relationship.
Somebody can say a comment.
You can define FK on tables on simply disable them for loading
maintainance or other purposes, and then enable them again. Otherwise
you would have to drop them and recreate them.
CREATE TABLE SomeParentTable
(
ParentPKCol INT
CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
)
Create table SomeChildTable
(
PKChildCol INT,
ParentPKCol INT NOT NULL
CONSTRAINT fk1_SomeParentTable
FOREIGN KEY
REFERENCES SomeParentTable (ParentPKCol)
)
--Doesn=B4t work, CHECK is enabled
insert into SomeChildTable Values (1,1)
--Disabling the CHECK
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--This works now
insert into SomeChildTable Values (1,1)
--Delete it once again
DELETE FROM SomeChildTable
--THis is the normal behaviour
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--Inserting first the parent then the child records
insert into SomeParentTable Values (1)
insert into SomeChildTable Values (1,1)
Drop table SomeChildTable
Drop table SomeParentTable
HTH, Jens Suessmeyer.
|||Ohh absolutly, now I see the reason
Kenny M.
"Jens" wrote:
> You can define FK on tables on simply disable them for loading
> maintainance or other purposes, and then enable them again. Otherwise
> you would have to drop them and recreate them.
>
> CREATE TABLE SomeParentTable
> (
> ParentPKCol INT
> CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
> )
> Create table SomeChildTable
> (
> PKChildCol INT,
> ParentPKCol INT NOT NULL
> CONSTRAINT fk1_SomeParentTable
> FOREIGN KEY
> REFERENCES SomeParentTable (ParentPKCol)
> )
>
> --Doesn′t work, CHECK is enabled
> insert into SomeChildTable Values (1,1)
> --Disabling the CHECK
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --This works now
> insert into SomeChildTable Values (1,1)
> --Delete it once again
> DELETE FROM SomeChildTable
> --THis is the normal behaviour
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --Inserting first the parent then the child records
> insert into SomeParentTable Values (1)
> insert into SomeChildTable Values (1,1)
>
> Drop table SomeChildTable
> Drop table SomeParentTable
>
> HTH, Jens Suessmeyer.
>
sql
Relationship problem
Hi All... Two of my tables are:
Users - primary key is UserId, an int with identity turned on.
Messages - has a column named UserId that references the same in Users.
I'm using Visual Studio 2005 against a SQL 2005 database.
Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:
Users table saved successfully.
Messages table
- unable to create relationship 'FK_Messages_UserId'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.
I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt
You might already have entries in your child table which have no parent entry in the parent table.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt
relationship inside the same table
i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)
Monday, March 26, 2012
Relational databases
I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?
There's a couple of ways -
- Add a separate column for the membership id to the profile.
- Make the UserID in the Profile non-auto generated and set it when you create the record as under
INSERT INTO MembershipTable .....DECLARE @.UserID INTSET @.UserID = SCOPE_IDENTITY() -- gets the identity of the lastnew record-- create the profile recordINSERT INTO ProfileTable (UserId, .... VALUES(@.UserID, ....
Friday, March 23, 2012
Related Tables in Model Files
I want to create a model file that relates two tables. However, these tables do not have a foreign key relationship in the database. Is there any way to do this? I'm hoping to programmatically generate the XML for the smdl file. Here's the XML that is generated when there is a foreign key:
<Role ID="G958db767-3d65-4445-9ccb-5f76c41720fb">
<Name>Linked Problem</Name>
<RelatedRoleID>Gf5496c9e-13b8-4c66-965d-c3a3c9a78cc8</RelatedRoleID>
<Cardinality>OptionalOne</Cardinality>
<Relation Name="dbo_Incident_FK_Incident_Problem" RelationEnd="Target" />
</Role>
Is there any way to change this to XML reference a table and join fields instead of a FK?
The Role in the report model must be bound to a Relation in the Data Source View (DSV), but the Relation does not need to have any corresponding FK constraint in the underlying database. The solution is to define the Relation you want in the DSV section of the file, then create the Role pair and bind them to it.
|||Hi Bob,
To form the relation, I assume you mean that I make a key/keyref pair in the schema. Is this correct?
As for the role pair, I don't understand exactly where these items go. I looked at the ones generated when there was a FK in the database. It looks like one goes at the end of the first table's attribute (field) list and the second one replaces the attribute in the second table. Is this correct?
If you have any XML samples I would really appreciate it. I tried everything I could think of today and got nothing but errors when I tried to upload the model files.
Thanks!|||Never mind. I've since discovered that you can set up a foreign key with the "NO CHECK" option. I think I can do that and save myself from having to programmatically generate SMDL. Thanks!
Rejected Inserts: but key unique
I'm getting some records coming up in the conflict viewer which havn't been
inserted because there is a "Violation of the Unique key constraint". But if
I join the conflict table to the target table on the primary key I get no
result so keyfld in the conflict table is unique. Can anyone explain why it
is being rejected?
all the best
spike
Perhaps there is a unique index or unique constraint that is being voilated,
reather than the PK?
Rgds,
Paul Ibison
|||On Apr 30, 2:50 am, Spike <S...@.discussions.microsoft.com> wrote:
> I'm getting some records coming up in the conflict viewer which havn't been
> inserted because there is a "Violation of the Unique key constraint". But if
> I join the conflict table to the target table on the primary key I get no
> result so keyfld in the conflict table is unique. Can anyone explain why it
> is being rejected?
> all the best
> spike
I've seen this with merge replication in SQL 2005 with client pull
subscriptions, Scenario:
1) Publisher has master record "A" and detail records "1,2" with
foreign key constraint applied
2) Subscriber connects, receives master/detail data, disconnects
3) Publisher side deletes master record "A"
4) Subscriber {disconnected} adds detail records "3,4" for master
"A" {succeeds}
5) Subscriber replicates
This is what happens:
1) Master record "A" delete is processed without conflict
2) A compensating master "A" delete is sent to the subscriber - the
master record is removed from the subscription database
3) Detail record "3,4" inserts are sent up to publisher - they fail
with foreign key constraint error
4) Detail records "3,4" remain orphaned in the subscriber database -
each subsequent attempt to replicate generates a new insert/FK
constraint error - overwriting the old one - and the conflict date/
time is updated
Reinitializing the subscriber will remove the orphaned data from the
subscriber database and you should stop seeing the conflict date/time
update with subsequent replication attempts...
inserted because there is a "Violation of the Unique key constraint". But if
I join the conflict table to the target table on the primary key I get no
result so keyfld in the conflict table is unique. Can anyone explain why it
is being rejected?
all the best
spike
Perhaps there is a unique index or unique constraint that is being voilated,
reather than the PK?
Rgds,
Paul Ibison
|||On Apr 30, 2:50 am, Spike <S...@.discussions.microsoft.com> wrote:
> I'm getting some records coming up in the conflict viewer which havn't been
> inserted because there is a "Violation of the Unique key constraint". But if
> I join the conflict table to the target table on the primary key I get no
> result so keyfld in the conflict table is unique. Can anyone explain why it
> is being rejected?
> all the best
> spike
I've seen this with merge replication in SQL 2005 with client pull
subscriptions, Scenario:
1) Publisher has master record "A" and detail records "1,2" with
foreign key constraint applied
2) Subscriber connects, receives master/detail data, disconnects
3) Publisher side deletes master record "A"
4) Subscriber {disconnected} adds detail records "3,4" for master
"A" {succeeds}
5) Subscriber replicates
This is what happens:
1) Master record "A" delete is processed without conflict
2) A compensating master "A" delete is sent to the subscriber - the
master record is removed from the subscription database
3) Detail record "3,4" inserts are sent up to publisher - they fail
with foreign key constraint error
4) Detail records "3,4" remain orphaned in the subscriber database -
each subsequent attempt to replicate generates a new insert/FK
constraint error - overwriting the old one - and the conflict date/
time is updated
Reinitializing the subscriber will remove the orphaned data from the
subscriber database and you should stop seeing the conflict date/time
update with subsequent replication attempts...
Monday, March 12, 2012
Reindexing
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>
Reindexing
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
Have you any other indexes on the table? If so, drop them first and add
them back last.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
Have you any other indexes on the table? If so, drop them first and add
them back last.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
Reindexing
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
Friday, March 9, 2012
regular primary keys vs autonumber primary keys
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.Originally posted by Chuckt
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
Wednesday, March 7, 2012
Registry key setting for MS SQL Server
I am installing my application thru Installshield Professional 5. How
to know whether MS SQL Server is installed or not on the machine on
which i am installing my application. What is the registry key thru
which we can find this."harish" <harishksh@.yahoo.com> wrote in message
news:ff46df6e.0401090401.732c0ecb@.posting.google.c om...
> I am installing my application thru Installshield Professional 5. How
> to know whether MS SQL Server is installed or not on the machine on
> which i am installing my application. What is the registry key thru
> which we can find this.
to know whether MS SQL Server is installed or not on the machine on
which i am installing my application. What is the registry key thru
which we can find this."harish" <harishksh@.yahoo.com> wrote in message
news:ff46df6e.0401090401.732c0ecb@.posting.google.c om...
> I am installing my application thru Installshield Professional 5. How
> to know whether MS SQL Server is installed or not on the machine on
> which i am installing my application. What is the registry key thru
> which we can find this.
Have a look at points 12 and 13 in this KB article:
http://support.microsoft.com/defaul...6&Product=sql2k
Simon
Registry Key for SQL Server 2005 Surface Area Configuration...
Whitch registry key should be set to allow Local and remote connections in SQL Server 2005 Surface Area configuration?May I know why you need this explicit value to update, as it is not good in terms of updating registry key on fly.
registry key for sql server 2000
I would like to know what is the registry key where is written that SQL Server 2000 is installed and the registry key where is the path to the data folder (master database) on Windows Server 2003. thanks
IrinaI would not answer registry key part of your question but the Master database can be accessed in Enterprise Manager and the data files are in Data Folder in Microsoft SQL Server folder in the programs folder in Win2003. Hope this helps.
IrinaI would not answer registry key part of your question but the Master database can be accessed in Enterprise Manager and the data files are in Data Folder in Microsoft SQL Server folder in the programs folder in Win2003. Hope this helps.
Registration Problem
I tried to Register the SQL Express. I received the Email to comfirmation and verification but I don't received Any Key!
Can some one help me?
Thanks
Actually you don′t need to register within SQL Server Express, this will bring you only additional benefits like information about updates and other news about SQL Server (and as far as I can remeber some benefits), but since SQL Server Express is free you don′t even need to register nor need a license code.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Jens is correct, no key is needed. We do like people to register though; the registration process offers some benefits as well as the option to get updates about patches, etc. Registration also allows us to better understand how people are using SQL Express (there are a few questions), which helps me do a better job designing the next version of SQL Express.
Mike
|||Is there a evaluation copy for SQL2005/ Reporting Server 2005? How Can we verify if we have installed a trial version? And how can this be permanetly activated?
Prasanna
vvprasanna@.yahoo.com
|||Yes there is a trial version: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6931fa7f-c094-49a2-a050-2d07993566ec
Refer to the following snippet from the MS site:
Upgrading and Uninstalling the Trial Software
The 180-day trial version of SQL Server 2005 Enterprise Edition can be upgraded to the fully licensed version of SQL Server 2005 Enterprise Edition without uninstalling the trial software prior to the end of the 180-day period. If upgrading to the fully licensed version of SQL Server 2005 Standard Edition, Workgroup Edition, or Developer Edition, it is recommended that you uninstall the 180-day trial version of SQL Server 2005 Enterprise Edition before installing the fully licensed version of the other editions. Please refer to the product documentation for information about upgrading to the fully licensed software.
http://www.microsoft.com/sql/downloads/trial-system-requirements.mspx
If you want to upgrade the product you should do that before the trial period has ended to avoid problems like this:
http://support.microsoft.com/kb/914158/en-us
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||It's also worth noting that SQL Express with Advanced Services includes Reporting Services free of charge. There are some limitation as is common with free products, but it might meet you needs. Check out the feature comparison by Edition.
Mike
Subscribe to:
Posts (Atom)