book

Mastering SQL Server® 2008

by Michael Lee, Gentry Bieker

January 2009

Intermediate to advanced

792 pages

23h 8m

English

Sybex

Read now

Unlock full access

Who Should Read This BookThe Mastering SeriesWhat Is Covered in This BookHow to Contact the Authors

SQL Server in the Enterprise WorldInstalling SQL Server 2008The SQL Server Tool SetThe Bottom Line
Essential InternalsThe SQL Server System CatalogDatatypingThe Bottom Line
Planning a DatabaseCreating DatabasesManaging Databases, Files, and FilegroupsThe Bottom Line
Designing Data StorageCreating and Dropping SQL Server TablesSelecting Data from a TableModifying DataThe Bottom Line
Procedural StructuresStored ProceduresViewsUser-Defined FunctionsUsing SynonymsThe Bottom Line
Understanding Data Integrity ControlsWorking with ConstraintsWorking with DML TriggersRule and Default ObjectsThe Bottom Line
Returning XML from SQL Server Using FOR XMLThe xml DatatypeValidating XML with SchemasImplementing XQueryXML IndexesThe Bottom Line
The SQL Server Security InfrastructureImplementing SQL Server Principals and AuthenticationImplementing Permissions in SQL ServerThe Bottom Line
Transaction ArchitectureBackup and Restore StrategiesPerforming BackupsPerforming RestoresOther IssuesThe Bottom Line
Using System MonitorUsing SQL Profiler & TraceDDL TriggersEvent NotificationsThe Bottom Line
SQL Agent and AutomationCreating JobsCreating AlertsCreating OperatorsScripting the SQL AgentThe Bottom Line
Overview of SQL Server Availability and Distribution OptionsDatabase Log ShippingDatabase MirroringReplicationThe Bottom Line
Understanding the SQL Server Key InfrastructureUsing Transparent Data EncryptionEncrypting Data Using SQL ServerModule SigningThe Bottom Line
Index ArchitectureCreating and Managing IndexesSpecial Index ConsiderationsThe Bottom Line
The SQL Server Locking ArchitectureTransaction ManagementDistributed TransactionsGetting Transaction and Locking InformationThe Bottom Line
The Resource GovernorUnderstanding Policy-Based ManagementThe Bottom Line
Understanding ADO.NETSpecial Operations in ADO.NETIntroducing Language Integrated Query (LINQ)LINQ and the Entity FrameworkThe Bottom Line
CLR ConceptsCreating Managed Objects in SQL ServerDeploying Managed Objects in SQL ServerManaging CLR SecuritySecurity ConsiderationsThe Bottom Line
Spatial Data ConceptsImplementing the Geometry TypeImplementing the Geography TypeSpatial IndexesThe Bottom Line
The Essentials of Service-Oriented ArchitectureUnderstanding the Service BrokerImplementing Service BrokerThe Bottom Line
Full-Text ArchitectureCreating Full-Text IndexesWriting Full-Text QueriesThe Bottom Line
Chapter 1: Overview of Microsoft SQL Server 2008Chapter 2: Understanding SQL Server ArchitectureChapter 3: Databases, Files, and Resource ManagementChapter 4: Tables and CRUD OperationsChapter 5: Procedural ProgrammingChapter 6: Managing Data IntegrityChapter 7: SQL Server and XMLChapter 8: Managing User SecurityChapter 9: Data RecoveryChapter 10: Monitoring SQL Server ActivityChapter 11: Using the SQL Server Agent ServiceChapter 12: Data Availability and DistributionChapter 13: SQL Server Data SecurityChapter 14: Indexing Strategies for Query OptimizationChapter 15: Transactions and LockingChapter 16: Using the Resource Governor and Policy-Based ManagementChapter 17: SQL Server and the .NET ClientChapter 18: SQL Server and the Common Language RuntimeChapter 19: Using Spatial DataChapter 20: Service-Oriented Architecture and the Service BrokerChapter 21: Full-Text Search Services
Designing the Data WarehouseUnderstanding Analysis Services
Configuring Reporting ServicesCreating a Simple ReportDeploying the Report

Content preview from Mastering SQL Server® 2008

Chapter 6 Managing Data Integrity

Data integrity controls help to guarantee the accuracy, validity, and correctness of data stored in a database. Data integrity controls can be thought of as rules regarding what is considered to be valid data. Data integrity can be enforced using stored procedures, or it can even be enforced outside of SQL Server in the calling application.

However, data integrity is mainly enforced by constraints and triggers. Constraints are additional rules applied to a table that determine valid data. They are automatically enforced by the database engine.

Triggers are special stored procedures that execute in response to events on the server. Data Manipulation Language (DML) triggers can respond to, or execute instead of ...