Step 3: Consider using implicit transactions |
 |
By Serdar Yegulalp, Contributor
13 May 2005 | SearchSQLServer.com |
|
|
One of the server-level options available in SQL Server causes all operations to be encapsulated in the context of a transaction. This adds another layer of integrity protection, but if you do this, be extra-conscious of three things: the size of the transactions themselves and the size of the transaction log. The latter will be affected by the former, so if you do this, set up a transaction log with plenty of initial space and have it auto-resize by megabytes rather than percentage.
Another consideration is data recovery. Remember, you can only recover committed data when you replay logs, so big transactions that don't commit if there's a problem will be lost. Performance is another consideration: Using transactions will add a bit more overhead to everything, but the overhead probably will not even be noticeable most of the time.
SQL Server professional Erland Sommarskog says transactions that affect 100,000 rows at a time are about the right size for a transactional chunk. Any number smaller than that, and you'll run into performance overhead issues. Alternatively, you can use BCP or BULK INSERT, which forces commits on each row written but, obviously, this is not going to be practical in all situations.
ENSURING DATA INTEGRITY IN SQL SERVER
 Home: Introduction
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget
|
|
|
|
|
|
