To ensure the integrity of a database, you can restrict the data entered into the tables of the database. For example, you can require integer data in one column of a table and character data in another. To restrict the data entered, you can use system datatypes, user-defined datatypes, constraints, defaults, or the IDENTITY property.
For details on system and user-defined datatypes, see Working with Tables, and the Datatypes topic in the Microsoft SQL Server Transact-SQL Reference.
For details on constraints and defaults, see the CREATE TABLE and ALTER TABLE statements in the Microsoft SQL Server Transact-SQL Reference.
For details on the IDENTITY property, see the CREATE TABLE statement in the Microsoft SQL Server Transact-SQL Reference.
Keeping data consistent in a database is important. If you change data in one table without changing related data in other tables, problems can develop. For example, if you discover in the pubs sample database that the au_id entry for Sylvia Panteley is incorrect and change it in the authors table, you must also change it in the titleauthor table (and in any other table in the database with a column containing that value). If you don't, you'll never be able to find information about Sylvia Panteley.
Keeping data modifications consistent throughout a database enforces referential integrity. This means that the integrity of cross-table references is maintained. You can maintain referential integrity by using constraints (see Working with Tables). Another way is to create and use stored procedures called triggers (see Using Stored Procedures and Triggers).
When you change data in a database, a copy of the old and new state of each row affected by data modification statements (except UPDATETEXT and WRITETEXT) is written to the transaction log. This means that if you begin a transaction by executing a BEGIN TRANSACTION statement, and then decide not to permanently make the change and so roll back the transaction, the data can be restored to its previous condition.
The default mode of operation for UPDATETEXT and WRITETEXT does not log the transactions. This avoids filling up the transaction log with the extremely long blocks of data that text and image columns can contain. To log changes made with these statements, you must use the WITH LOG option.
Note Changes made on a remote SQL Server (one that the user is not directly connected to) by means of a remote procedure cannot be rolled back.