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

Step 7: Use a 'check-in/check-out' mechanism for contested data

This guide tells you how to enforce and ensure SQL Server data integrity on many fronts at once.

Knowing how to handle contention for updating a particular row is a common problem with database applications where...

many users may be accessing or attempting to modify the same data. For instance, if a client opens some data through a Web browser form, then gets up for a cup of coffee, how does he know that by the time he sits back down someone else hasn't changed the data he's looking at (which has been presented to him on a static page)? Allowing people to work with stale data is one surefire way to ruin the integrity of your data in the long run.

A very common solution to this involves placing a column in the relevant data table that indicates whether a particular user is currently looking at that row. This column could contain either a NULL value (to indicate no one is currently attempting to modify the data) or a time/date (indicating when the row was opened by a client for modification). When the client submits modifications properly, that column is NULLed, and the row is freed up for editing once again. This would help protect against breaking integrity if people try to work from a stale data set.


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
 

Dig Deeper on Database management system (DBMS) architecture, design and strategy

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close