News Stay informed about the latest enterprise technology news and product updates.

Step 3: Consider using implicit transactions

Get up to speed on server-level options to add another layer of integrity protection.

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.


 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

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

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.