Best practice

Hi
What is the prefered practice to use when I have 2 or more related
tables and I want to delete a row in the master table and I want the
child tables to automatically delete their related rows, Should I use
triggers in the database, enable cascade delete in the dataset or
somthing else ?
I use visual studio 2005 and sql server 2005.
Thanks
RolfIt depends. If you want the child records deleted, then a cascade delete is
appropriate. If you want the application to deal with the child records
first, then a foreign key constraint that will stop deletes will be valid.
Both are best practices, depending on your application requirements.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||Cascade with error handling could be one way as you mention.
Another way wich I prefer is to use transaction when deleting data that
needs to delete related data.
I do recommend that you dont use triggers since that easily can make
things go out of control.|||It depends. Cascading deletes are the simplest to implement, but can
complicate deadlock minimization because the order in which locks are
obtained across tables is not clearly defined. In addition, INSTEAD OF
triggers cannot exist on the referencing table of a cascading referential
action. Using FOR or AFTER triggers to cascade deletes is generally a bad
idea--not because of the performance impact, but because several triggers
can exist for an action on a table and the order in which they are executed
is not deterministic, IMO they should be avoided. I prefer to perform
deletes within a transaction in a stored procedure. It is then clear from
reading the text of the proc which tables will be affected, and it's easier
to control the order in which locks are obtained to minimize the likelyhood
of a deadlock. The use of INSTEAD OF DELETE triggers instead of stored
procedures may warrant investigation because any locks applied depend on the
order in which statements appear within the trigger (none are applied as a
direct result of the trigger firing) in the same way as statements within a
stored procedure, and unlike the stored procedure method, they do not
require preventing direct access to the tables. (On the other hand, many
would say that you should always prevent direct access to the tables and
require all modifications to be performed using stored procedures.)
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||OK, thanks for all replies
I think I will avvoid triggers and use cascading delete. But what is
more efficent. Cascading deletes in the database or cascading deletes
in the dataset. Is there a performance difference between these two
options ?
Thanks
Rolf

best option when master db is not available

i want to setup my database to new server machine. i have backup as well as copy of database files (data & log), but i lost my master database from existing server.

so i have 2 ways of having my database

1. restore from backup

2. attach the data files.

which one is best option, when we start afresh on new machine with new master database.

Either method should work. I would prefer the RESTORE.

IF the file location (folder) is different, you may need to add the WITH MOVE option to the RESTORE.

Refer to Books Online, Topic: RESTORE

|||yes both options will work.......i'll advise you to go with restore db from backup but ensure that you start SQL Server in single user mode and then only you can perform restoration..........refer BOL its the best resource|||

thanks for your replies. I have gone thru the BOL.

One thing i need to reconfirm before i proceed, with the loss of master database i lost all the information that the master database hold, in this situation, which of the option will be best.

|||

i feel that there is some confusion in the requirement. ie Whether u r trying to resotre Master Database or User database. To restore a master database of one machine to another machine you have many restriction. OS/SQL SErver Version/Service pack and configuration (if i remember correctly) should be the same. I have also read somewhere that the backup of the same physical machine can ionly be restored(i have never tried this).

To transfer the objects from one server to other there are scripts available. You need to transfer Login/Jobs/DTS. THis is possible through Scripts.

(a) Install new instance of sql server in new machine

(b) transfer the LOgin refer : http://support.microsoft.com/default.aspx/kb/246133

(c) Make script of JOBs and run the script in the destination

(d) use save as option or file object trasfer for DTS

(e) Use Backup /restore for user databases.

Madhu

|||

there should not be any confusion, i want to restore user database.

since my old master db is lost, i lost login, etc. logins i can recreate manually, but i am worried is there is thing else about my User database which is lost along with master db, which i may not recover.

|||

All of the data in the users databases should be intact.

Any Jobs would be in the msdb database, so if you didn't lose msdb, you still have the jobs.

Most likely, the only thing lost is logins.

Benefit of moving Master & TempDB to diff HD

I am interested to hear if people think it would be a good idea to move
the Master & TempDB to a different HD.

Here is my DB Server's set up:
1. Processor: (1) AMD XP 2800
2. 1st HD (IDE 0) is the system & boot drive
3. (3) SCSI HD make up a hardware RAID level 0 (striped without
parity)solution - these striped drives are just for my working DBs
4. (1) SCSI HD that's not doing anything.

I want to put the Master & TempDB on the SCSI HD that's not doing
anything. Would that be the best place for it for maximum performance or
should I put in the striped array. I am leaning more towards putting on
the SCSI HD that's not doing anything. What do you all think?

Ed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!adude (nospam@.devdex.com) writes:
> I am interested to hear if people think it would be a good idea to move
> the Master & TempDB to a different HD.
> Here is my DB Server's set up:
> 1. Processor: (1) AMD XP 2800
> 2. 1st HD (IDE 0) is the system & boot drive
> 3. (3) SCSI HD make up a hardware RAID level 0 (striped without
> parity)solution - these striped drives are just for my working DBs
> 4. (1) SCSI HD that's not doing anything.
> I want to put the Master & TempDB on the SCSI HD that's not doing
> anything. Would that be the best place for it for maximum performance or
> should I put in the striped array. I am leaning more towards putting on
> the SCSI HD that's not doing anything. What do you all think?

I would not move master.

If that idle HD is on a different controller, moving something could be
good for performance. If you have lots of action in tempdb, this could
be a candidate. You could also consider moving transaction logs to the
idle disc.

If the disk in the same controller as the rest, I think it would be better
to move disk into the stripe. If the IDE disk is slow, you couls still
move tempdn into the RAID.

I need to add the disclaimer that hardware configuration is not my best
game.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp