Dropping FK Constraints in a Stored Procedure

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux...some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.The only justification for this technique is developer productivity. It's a
lot easier to write code without those nasty foreign key constraints getting
in the way :-)

Seriously, I can't think of a good reason to do this. There may be some
situations where temporarily removing constraints can improve batch
performance but this kind of thing should only be done during maintenance
windows. Adding and removing constraints during normal operation can lead
to blocking and data integrity issues.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dmitri" <nienna.gaia@.gmail.com> wrote in message
news:1111719489.616490.262060@.f14g2000cwb.googlegr oups.com...
>I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?
> I was furious when I found this because every once in a while I would
> notice that the constraints would be in flux...some days they were
> there, othere days there were not. I mean, this is a good enough reason
> to NOT do this, but I need some additional feedback. Maybe there *is* a
> good reason, and that the logic just needs tweaking.
> Thanks.|||Dmitri (nienna.gaia@.gmail.com) writes:
> I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?

Good reason...

There is a problem with constraints and triggers in SQL Server, as they
always fire at statemet time. Other products have commit-time constraints
and/or triggers. This is good, because that permits you to violate
constraints temporarily in a transaction where it does not matter. One
example is that you have Orders and OrderDetails, and for some reason
need to reallocating order numbers and the constraints are not defined
as cascading. (Maybe because of the many restrictions with cascading
DRI in SQL Server.)

That said, I would say that it vert bad practice to do this in application
code. For a maintenance procedure or a fix proecedure that is run once in
a blue moon it could be acceptable. If there is some really difficult
situation where you must to this, you *must* do it within the realm of a
transaction, so that the final result of the operation in case of an error
or a power failure is that the constraints are gone.

Performance implications? You bet. If, as you say, he drops and re-adds
the constraints, there is a cost for checking the constraints. An
alternative would be to disable the constraints and enabling them
again without checking. In this case, the constraints would not be
trusted by the optimizer, and this could affect query plans. (And of
course, if his logic violated the constraints, no one would know.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Standard SQL has a DEFERABLE option on constraints. They can be turned
on or off by default or by action. But all constraints have to be true
at COMMIT time. I would guess that he is used to DB2 or another SQL
that hs this feature.

SQL Server does not work this way. So this is very dangerous and he
should not be doing it. He can lock up or trash the whole system with
his stored procedures. My guess would be that his procedure ought to
update the table in the proper order, or that the schema has something
really ugly in it, like a cyclic reference.

If this has been goign on for awhile, you better do a data audit, too.|||Thanks for the input all!

Dropping an old foreign key restraint?

This forum looked like the only one where I may get an answer to this question.

I built a database with a set of tables in Sql Server 2005 sp2.

I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.

Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.

How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!

In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.

Is there a system SPROC that I can execute to show and delete this item?

Dear Michael,

Try this code:

IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')

HTH,

Suprotim Agarwal

Dropping an old foreign key restraint?

This forum looked like the only one where I may get an answer to this question.

I built a database with a set of tables in Sql Server 2005 sp2.

I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.

Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.

How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!

In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.

Is there a system SPROC that I can execute to show and delete this item?

Dear Michael,

Try this code:

IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')

HTH,

Suprotim Agarwal

Dropping an auto numbered primary key and add a new one

The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.

I need to write SQL to drop the current primary key and add a new one Say "RecordId"

as the new primary key and which should be a autonumber too.

any idea.

thanks in advance

droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want...

alter table tablename drop constraint oldpk

alter table tablename add constraint newpk primary key(id)

|||

What's the point? It is easy enough, just drop the column and add another one, but there might be an easier way to do what you are wanting to do.

Here is a script that does it:

set nocount on
drop table test
go
create table test
(
testId int identity constraint PKtest primary key,
value datetime default (getdate())
)
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
go
select *
from test
go
alter table test
drop PKtest
go
alter table test
drop column testId
go
select *
from test
go
alter table test
add testId int identity (2,2) constraint PKtest primary key
go
select *
from test --the identity values will start at 2 and go up by steps of 2 (so you can see the diff)

sql

Dropping all foreign keys

I would like to have a procedure that I can call that will drop all foreign
keys. I can get the constraint name from sysobjects if I select all fkey
constraints, but how can I get the associated table name so that I can plug
that into an alter table statement?
I appreciate any suggestions.
ThanksSee the first query at http://www.aspfaq.com/2520
Just change it from "SELECT FK_Table ... FROM" to the following:
SELECT 'ALTER TABLE '+FK.TABLE_NAME+' DROP CONSTRAINT '+C.CONSTRAINT_NAME
FROM
Run it in query analyzer and you will generate a script in the bottom pane,
which you can then copy and run in a new Query Analyzer window.
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks
Here is one that I wrote. It's not pretty and it's not optimized, but it
works.
Rick Sawtell
----
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END|||WHY? Do you really want to sail through the windshield?
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
> I would like to have a procedure that I can call that will drop all
foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||*All* of them? I just hope some of them will make it back... someday.
ML

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>
|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...[vbcol=seagreen]
> Narayana Vyas Kondreddi wrote:
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
the[vbcol=seagreen]
lot
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
> is
> the
> lot
>
>
|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
> > They are effectively the same, but dropping the table in the right order
is
> > effectively quicker.
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
> >
> > Anyway, you could really gain some performance, if you could look into
the
> > possibility of incremental updates, instead of getting rid of the whole
lot
> > and repopulating.
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> > news:4092B4F1.6060700@.deltanet.com...
> > I am setting up about 60 or so DTS packages to run every night. These
> > packages will be recreating all the tables from a foreign database.
> >
> > At the moment, I have them dropping the tables, creating the tables,
> > copying all the data to the new table and creating the keys.
> >
> > Would it be better to just drop the keys, truncate the tables, copy the
> > data and recreate the keys? Or does it really matter? I am, in
> > essence, doing the same thing.
> >
> > Just trying to see if I am going about the task in the best way.
> >
> > Thanks,
> >
> > Tom.
> >
> >
> >
> >
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>>Narayana Vyas Kondreddi wrote:
>>
>>They are effectively the same, but dropping the table in the right order
> is
>>effectively quicker.
>>
>>I thought so, also. But someone had mentioned that tables are meant to
>>be permanent, so it would be better to truncate. The problem is just
>>because they are meant to be permanent, doesn't mean there is a problem
>>doing it.
>>Someone else said it would be better to do all the table drops and
>>creates - then populate them. I'm not sure why that would be better.
>>Why would dropping them in the correct order be quicker - I assume you
>>are talking about referential integrity. In my case, I am deleting
>>everything each time at night.
>>
>>Anyway, you could really gain some performance, if you could look into
> the
>>possibility of incremental updates, instead of getting rid of the whole
> lot
>>and repopulating.
>>
>>I agree. But in our case the tables are already created on a foreign
>>system and we have no control over the schema, so it would be difficult
>>to do incremental updates.
>>Thanks,
>>Tom.
>>
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>Is .NET important for a database professional?
>>http://vyaskn.tripod.com/poll.htm
>>
>>
>>"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
>>news:4092B4F1.6060700@.deltanet.com...
>>I am setting up about 60 or so DTS packages to run every night. These
>>packages will be recreating all the tables from a foreign database.
>>At the moment, I have them dropping the tables, creating the tables,
>>copying all the data to the new table and creating the keys.
>>Would it be better to just drop the keys, truncate the tables, copy the
>>data and recreate the keys? Or does it really matter? I am, in
>>essence, doing the same thing.
>>Just trying to see if I am going about the task in the best way.
>>Thanks,
>>Tom.
>>
>>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FK
Joe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
--
David Portas
SQL Server MVP
--

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below
.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP
--

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, do
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
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
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, d
o
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
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
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Drop foreign keys for replication

We set up transaction replication on our production server, and have a
subsriber pulling to a report server. The problem was that I needed to drop
all the foreign keys in order for the snapshot to succeed. My question is, do
I need to add all the foreign keys back to those tables on the subscriber or
can I leave the foreign keys alone?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1If it is not immediate updating (replicating both ways) then I wouldn't
bother.
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
drop
> all the foreign keys in order for the snapshot to succeed. My question is,
do
> I need to add all the foreign keys back to those tables on the subscriber
or
> can I leave the foreign keys alone?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||That depends. You could add them through a post snapshot script. However you
will only need to add fks on your subscriber if you are doing DML there and
want to enforce them. Otherwise if its read only you really don't need them
at all.
--
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
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:69a026f456b86@.uwe...
> We set up transaction replication on our production server, and have a
> subsriber pulling to a report server. The problem was that I needed to
> drop
> all the foreign keys in order for the snapshot to succeed. My question is,
> do
> I need to add all the foreign keys back to those tables on the subscriber
> or
> can I leave the foreign keys alone?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Drop a constrain

Hi,
I need to delete a table WITH foreign key constrain. What is the fast way to
drop the table?
ThanksHi all,
The table has SP and tables as dependencies
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23N7ZVEjjGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I need to delete a table WITH foreign key constrain. What is the fast way
> to drop the table?
> Thanks
>|||On Mon, 12 Jun 2006 11:01:26 -0400, mecn wrote:
>Hi,
>I need to delete a table WITH foreign key constrain. What is the fast way to
>drop the table?
ALTER TABLE MyTable
DROP CONSTRAINT MyConstraint;
DROP TABLE MyTable;
--
Hugo Kornelis, SQL Server MVP