Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checking the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the cake and have it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing the
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK and
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non
-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checkin
g the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the
cake and have it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...seagreen">
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing th
e
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK an
d
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?
On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:

>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com... [vbcol=seagreen]
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
the
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:

>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:

>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.
4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>
the[vbcol=seagreen]
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:

>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
> >hi,
> >
> >i want to create index to speed up one operation (DELETE) and the drop
the
> >index to resume.
> >
> >any side effects or impacts on the db?
> >
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Any free sql backup compression utility ?

Hi,
I perform backup by maintenance plan tasks and think about add additional
task to compress backups and delete original backups.
Do you know some script base resolution (winzip, winrar, 7zip...)?
Regards,
anxcomp
From what I understand there is a command line version of winzip. But that
does not give you the same thing as using one of the tools from Red-gate or
Quest. They compress the backup as it is being read and when written it is
already compress. You would have to do it in two steps and it would require
much more CPU & Disk I/O than with Red-Gate. Not to mention you now have to
have extra disk space to store the initial backup before you compress it.
The same is true for restores. It makes the process much longer and more
difficult than it needs to be.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:F6756C86-51F1-4344-85ED-A20ADA7E32F8@.microsoft.com...
> Hi,
> I perform backup by maintenance plan tasks and think about add additional
> task to compress backups and delete original backups.
> Do you know some script base resolution (winzip, winrar, 7zip...)?
> --
> Regards,
> anxcomp
|||Hello,
This is good for me, I don't want live compression and decompression. My
databases are on simple mode I perform only full nightly backup.
Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
perform this task?
Thank you inadvice
Regards,
anxcomp
|||What does the fact this is in simple mode have to do with this? In any case
have a look here:
http://www.winzip.com/prodpagecl.htm
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:6195075F-3BB9-4D62-8088-A107309DC75E@.microsoft.com...
> Hello,
> This is good for me, I don't want live compression and decompression. My
> databases are on simple mode I perform only full nightly backup.
> Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
> perform this task?
> Thank you inadvice
> --
> Regards,
> anxcomp