Professional SQL Server 2005 Programming

by Vieira, Robert

• ISBN13: 9780764584343
• ISBN10: 0764584340
• Format: Paperback
• Copyright: 2006-12-01
• Publisher: Wrox

Summary

Professional SQL Server 2005 Programming shows experienced developers how to master the substantially revamped feature set of the latest release of Microsoft SQL Server. The book begins with a concise overview of the new features of SQL Server that is of interest to experienced developers. This is especially important given the substantial changes to SQL Server with this release. From there, the book quickly moves on to the meat of the title. Beginning-level material has been removed to provide more room for covering new features and more extensive code examples.

Author Biography

Robert Vieira is one of the leading authorities on Microsoft SQL Server and author of the bestselling title from Wrox Press, Professional SQL Server 2000 Programming, with nearly 40,000 copies in print.  Vieira is currently a Software Architect for NetIQ Corporation in Portland, OR, where he performs design work and coaches other developers. He speaks frequently at both public and private conferences nationally, and is perhaps best known for his light-hearted approach to teaching and writing.

Table of Contents

Acknowledgments	v
Introduction	xxiii
Being Objective: Re-Examining Objects in SQL Server	1	(16)
So, What Exactly Do We Have Here?	1	(1)
An Overview of Database Objects	2	(9)
The Database Object	2	(3)
The Transaction Log	5	(1)
The Most Basic Database Object: Table	5	(1)
Schemas	6	(1)
Filegroups	7	(1)
Diagrams	7	(1)
Views	7	(2)
Stored Procedures	9	(1)
User-Defined Functions	9	(1)
Users and Roles	10	(1)
Rules	10	(1)
Defaults	10	(1)
User-Defined Data Types	10	(1)
Full-Text Catalogs	11	(1)
SQL Server Data Types	11	(4)
NULL Data	14	(1)
SQL Server Identifiers for Objects	15	(1)
What Gets Named?	15	(1)
Rules for Naming	15	(1)
Summary	16	(1)
Tool Time	17	(18)
Books Online	18	(1)
The SQL Server Configuration Manager	19	(5)
Service Management	19	(1)
Network Configuration	20	(1)
The Protocols	21	(2)
On to the Client	23	(1)
The SQL Server Management Studio	24	(8)
Getting Started	25	(2)
Query Window	27	(5)
SQL Server Business Intelligence Development Studio	32	(1)
SQL Server Integration Services (SSIS)	32	(1)
Reporting Services	33	(1)
Bulk Copy Program (bcp)	33	(1)
SQL Server Profiler	33	(1)
sqlcmd	34	(1)
Summary	34	(1)
Basic T-SQL	35	(38)
The Basic Select Statement	36	(23)
The Select Statement and From Clause	36	(2)
The Join Clause	38	(7)
The Where Clause	45	(4)
Order By	49	(3)
Aggregating Data Using the Group By Clause	52	(4)
Placing Conditions on Groups with the Having Clause	56	(1)
Outputting XML Using the for XML Clause	57	(1)
Making Use of Hints Using the Option Clause	57	(1)
Distinct	57	(2)
Adding Data with the Insert Statement	59	(3)
The Insert Into . . . Select Statement	61	(1)
Changing What You've Got with the Update Statement	62	(2)
The Delete Statement	64	(2)
Exploring Alternative Syntax for Joins	66	(3)
An Alternative Inner Join	67	(1)
An Alternative Outer Join	67	(1)
An Alternative Cross Join	68	(1)
The Union	69	(3)
Summary	72	(1)
Creating and Altering Tables	73	(28)
Object Names in SQL Server	73	(4)
Schema Name (a.k.a. Ownership)	74	(2)
The Database Name	76	(1)
Naming by Server	76	(1)
The Create Statement	77	(12)
Create Database	77	(5)
Create Table	82	(7)
The Alter Statement	89	(6)
Alter Database	89	(3)
Alter Table	92	(3)
The Drop Statement	95	(1)
Using the GUI Tool	96	(3)
Creating or Editing the Database	96	(1)
Creating and Editing Tables	97	(2)
Summary	99	(2)
Reviewing Keys and Constraints	101	(32)
Types of Constraints	102	(2)
Domain Constraints	103	(1)
Entity Constraints	103	(1)
Referential Integrity Constraints	104	(1)
Constraint Naming	104	(1)
Key Constraints	105	(13)
Primary Key Constraints	106	(2)
Foreign Key Constraints	108	(9)
Unique Constraints	117	(1)
Check Constraints	118	(1)
Default Constraints	119	(2)
Defining a Default Constraint in Your Create Table Statement	120	(1)
Adding a Default Constraint to an Existing Table	121	(1)
Disabling Constraints	121	(5)
Ignoring Bad Data When You Create the Constraint	122	(2)
Temporarily Disabling an Existing Constraint	124	(2)
Rules and Defaults: Cousins of Constraints	126	(3)
Rules	126	(2)
Defaults	128	(1)
Determining Which Tables and Data Types Use a Given Rule or Default	129	(1)
Triggers for Data Integrity	129	(1)
Choosing What to Use	129	(2)
Summary	131	(2)
Asking a Better Question: Advanced Queries	133	(22)
What Is a Subquery?	134	(1)
Building a Nested Subquery	135	(4)
Nested Queries Using Single Value Select Statements	136	(1)
Nested Queries Using Subqueries That Return Multiple Values	137	(1)
The Any, Some, and All Operators	138	(1)
Correlated Subqueries	139	(5)
How Correlated Subqueries Work	140	(1)
Correlated Subqueries in the Where Clause	140	(2)
Correlated Subqueries in the Select List	142	(2)
Derived Tables	144	(2)
The Exists Operator	146	(2)
Using Exists in Other Ways	147	(1)
Mixing Data Types: Cast and Convert	148	(2)
Using External Calls to Perform Complex Actions	150	(1)
Performance Considerations	151	(2)
Joins vs. Subqueries vs.?	152	(1)
Summary	153	(2)
Daring to Design	155	(34)
Normalization 201	156	(2)
Where to Begin	156	(1)
Getting to Third Normal Form	157	(1)
Other Normal Forms	157	(1)
Relationships	158	(1)
Diagramming	159	(6)
A Couple of Relationship Types	160	(1)
The Entity Box	160	(1)
The Relationship Line	161	(1)
Terminators	162	(3)
Logical versus Physical Design	165	(3)
Purpose of a Logical Model	165	(1)
Parts of a Logical Model	166	(2)
Dealing with File-Based Information	168	(3)
Subcategories	171	(6)
Types of Subcategories	172	(1)
Keeping Track of What's What --- Implementing Subcategories	173	(2)
Getting Physical --- The Physical Implementation of Subcategories	175	(1)
Adding to Extensibility with Subcategories	176	(1)
Database Reuse	177	(2)
Candidates for Reusable Databases	177	(1)
How to Break Things Up	178	(1)
The High Price of Reusability	179	(1)
De-Normalization	179	(1)
Partitioning for Scalability	180	(1)
The SQL Server Diagramming Tools	181	(6)
Tables	183	(2)
Dealing with Constraints	185	(2)
Summary	187	(2)
SQL Server --- Storage and Index Structures	189	(42)
SQL Server Storage	189	(5)
The Database	189	(1)
The File	190	(1)
The Extent	190	(1)
The Page	191	(2)
Rows	193	(1)
Full-Text Catalogs	194	(1)
Understanding Indexes	194	(14)
To ``B,'' or Not to ``B'': B-Trees	195	(4)
How Data Is Accessed in SQL Server	199	(1)
Index Types and Index Navigation	200	(8)
Creating, Altering, and Dropping Indexes	208	(10)
The Create Index Statement	208	(6)
Creating XML Indexes	214	(1)
Implied Indexes Created with Constraints	215	(1)
Alter Index	215	(3)
Drop Index	218	(1)
Choosing Wisely: Deciding What Index Goes Where and When	218	(5)
Selectivity	218	(1)
Watching Costs: When Less Is More	219	(1)
Choosing That Clustered Index	219	(3)
Column Order Matters	222	(1)
Dropping Indexes	222	(1)
Use the Database Engine Tuning Advisor	222	(1)
Maintaining Your Indexes	223	(5)
Fragmentation	223	(1)
Identifying Fragmentation	224	(4)
Summary	228	(3)
Views	231	(16)
Simple Views	232	(1)
More Complex Views	233	(4)
Using a View to Change Data --- Before Instead Of Triggers	236	(1)
Editing Views with T-SQL	237	(1)
Dropping Views	238	(1)
Auditing: Displaying Existing Code	238	(1)
Protecting Code: Encrypting Views	239	(2)
About Schema Binding	241	(1)
Making Your View Look Like a Table with View_Metadata	241	(1)
Indexed (Materialized) Views	242	(2)
Partitioned Views	244	(1)
Summary	244	(3)
Scripts and Batches	247	(34)
Script Basics	248	(5)
The Use Statement	248	(1)
Declaring Variables	249	(3)
Using @@Identity	252	(1)
Using @@Rowcount	252	(1)
Batches	253	(6)
Errors in Batches	255	(1)
When to Use Batches	256	(3)
SQLCMD	259	(1)
Dynamic SQL: Generating Your Code on the Fly with the EXEC Command	260	(5)
The Gotchas of EXEC	262	(3)
Control-of-Flow Statements	265	(15)
The If . . . Else Statement	266	(4)
The Case Statement	270	(5)
Looping with the While Statement	275	(2)
The Wait for Statement	277	(1)
Try/Catch Blocks	277	(3)
Summary	280	(1)
Getting Procedural: Stored Procedures and User-Defined Functions	281	(48)
Creating the Sproc: Basic Syntax	282	(1)
An Example of a Basic Sproc	282	(1)
Changing Stored Procedures with Alter	283	(1)
Dropping Sprocs	283	(1)
Parameterization	284	(4)
Declaring Parameters	284	(1)
Creating Output Parameters	285	(3)
Confirming Success or Failure with Return Values	288	(2)
How to Use Return	288	(2)
Dealing with Errors	290	(11)
The Way We Were	291	(5)
Manually Raising Errors	296	(3)
Adding Your Own Custom Error Messages	299	(2)
What a Sproc Offers	301	(3)
Creating Callable Processes	301	(1)
Using Sprocs for Security	301	(1)
Sprocs and Performance	302	(2)
Extended Stored Procedures (XPs)	304	(1)
A Brief Look at Recursion	305	(2)
User-Defined Functions (UDFs)	307	(3)
What a UDF Is	308	(1)
UDFs Returning a Scalar Value	308	(2)
UDFs That Return a Table	310	(7)
Understanding Determinism	316	(1)
Debugging	317	(10)
Setting Up SQL Server for Debugging	318	(1)
Starting the Debugger	318	(3)
Parts of the Debugger	321	(1)
Using the Debugger after It's Started	322	(5)
Summary	327	(2)
Transactions and Locks	329	(32)
Transactions	329	(7)
Begin Tran	331	(1)
Commit Tran	331	(1)
Rollback Tran	331	(1)
Save Tran	331	(5)
How the SQL Server Log Works	336	(5)
Using the Checkpoint Command	337	(1)
Checkpoint on Recovery	337	(1)
At Normal Server Shutdown	338	(1)
At a Change of Database Options	338	(1)
When the Truncate on Checkpoint Option Is Active	338	(1)
When Recovery Time Would Exceed the Recovery Interval Option Setting	339	(1)
Failure and Recovery	339	(1)
Implicit Transactions	340	(1)
Locks and Concurrency	341	(12)
What Problems Can Be Prevented by Locks	342	(4)
Lockable Resources	346	(1)
Lock Escalation and Lock Effects on Performance	346	(1)
Lock Modes	347	(2)
Lock Compatibility	349	(1)
Specifying a Specific Lock Type --- Optimizer Hints	349	(4)
Setting the Isolation Level	353	(2)
Read Committed	353	(1)
Read Uncommitted	354	(1)
Repeatable Read	354	(1)
Serializable	355	(1)
Dealing with Deadlocks (a.k.a. ``A 1205'')	355	(3)
How SQL Server Figures Out There's a Deadlock	356	(1)
How Deadlock Victims Are Chosen	356	(1)
Avoiding Deadlocks	356	(2)
Summary	358	(3)
Triggers	361	(32)
What Is a Trigger?	362	(5)
ON	363	(1)
With Encryption	363	(1)
The For|After versus the Instead Of Clause	364	(2)
With Append	366	(1)
Not for Replication	367	(1)
AS	367	(1)
Using Triggers for Data Integrity Rules	367	(5)
Dealing with Requirements Sourced from Other Tables	367	(2)
Using Triggers to Check the Delta of an Update	369	(2)
Using Triggers for Custom Error Messages	371	(1)
Other Common Uses for Triggers	372	(3)
Updating Summary Information	372	(1)
Feeding Data into De-normalized Tables for Reporting	372	(1)
Setting Condition Flags	373	(2)
Other Trigger Issues	375	(5)
Triggers Can Be Nested	376	(1)
Triggers Can Be Recursive	376	(1)
Debugging Triggers	376	(1)
Triggers Don't Get in the Way of Architecture Changes	377	(1)
Triggers Can Be Turned Off without Being Removed	377	(1)
Trigger Firing Order	378	(2)
Instead of Triggers	380	(6)
Instead of Insert Triggers	381	(3)
Instead of Update Triggers	384	(1)
Instead of Delete Triggers	384	(2)
If Update() and Columns_Updated	386	(2)
The Update() Function	386	(1)
The Columns_Updated() Function	386	(2)
Performance Considerations	388	(2)
Triggers Are Reactive Rather Than Proactive	388	(1)
Triggers Don't Have Concurrency Issues with the Process That Fires Them	389	(1)
Keep It Short and Sweet	389	(1)
Don't Forget Triggers When Choosing Indexes	389	(1)
Try Not to Roll Back within Triggers	389	(1)
Dropping Triggers	390	(1)
Debugging Triggers	390	(2)
Summary	392	(1)
Nothing But NET!	393	(28)
Assemblies 101	394	(1)
Compiling an Assembly	394	(3)
Uploading Your Assembly to SQL Server	397	(1)
Creating Your Assembly-Based Stored Procedure	398	(2)
Creating Scalar User-Defined Functions from Assemblies	400	(3)
Creating Table-Valued Functions	403	(4)
Creating Aggregate Functions	407	(5)
Creating Triggers from Assemblies	412	(5)
Custom Data Types	417	(2)
Creating Your Data Type from Your Assembly	418	(1)
Accessing Your Complex Data Type	418	(1)
Dropping Data Types	419	(1)
Summary	419	(2)
SQL Cursors	421	(36)
What Is a Cursor?	421	(1)
The Lifespan of a Cursor	422	(5)
Types of Cursors and Extended Declaration Syntax	427	(25)
Scope	428	(4)
Scrollability	432	(2)
Cursor Types	434	(13)
Concurrency Options	447	(3)
Detecting Conversion of Cursor Types: Type_Warning	450	(2)
For <Select>	452	(1)
For Update	452	(1)
Navigating the Cursor: The Fetch Statement	452	(1)
Altering Data within Your Cursor	453	(3)
Summary	456	(1)
XML Integration	457	(46)
The XML Data Type	458	(11)
Defining a Column as Being of XML Type	458	(2)
XML Schema Collections	460	(1)
Creating, Altering, and Dropping XML Schema Collections	461	(2)
XML Data Type Methods	463	(6)
Enforcing Constraints beyond the Schema Collection	469	(1)
Retrieving Relational Data in XML Format	469	(28)
The for XML Clause	469	(24)
Openxml	493	(4)
A Quick (Very Quick) Reminder of XML Indexes	497	(1)
HTTP Endpoints	497	(4)
Security	498	(1)
HTTP Endpoint Methods	499	(1)
Creating and Managing a HTTP Endpoint	499	(1)
Closing Thoughts	500	(1)
Summary	501	(2)
Reporting for Duty, Sir!	503	(22)
Reporting Services 101	504	(1)
Building Simple Report Models	504	(13)
Data Source Views	507	(5)
Report Creation	512	(5)
Report Server Projects	517	(6)
Deploying the Report	522	(1)
Summary	523	(2)
Buying in Bulk: the Bulk Copy Program (BCP) and Other Basic Bulk Operations	525	(20)
BCP Utility	526	(10)
BCP Syntax	526	(5)
BCP Import	531	(3)
BCP Export	534	(2)
Format Files	536	(5)
When Your Columns Don't Match	538	(2)
Using Format Files	540	(1)
Maximizing Import Performance	541	(1)
Bulk Insert	541	(1)
Openrowset (Bulk)	542	(1)
Rows_Per_Batch	543	(1)
Single_Blob, Single_Clob, Single_Nclob	543	(1)
Summary	543	(2)
Getting Integrated	545	(20)
Understanding the Problem	545	(1)
An Overview of Packages	546	(6)
Tasks	548	(3)
The Main Window	551	(1)
Solution Explorer	552	(1)
The Properties Window	552	(1)
Building a Simple Package	552	(8)
Executing Packages	560	(3)
Using the Execute Package Utility	560	(2)
Executing within Management Studio	562	(1)
Summary	563	(2)
Replication	565	(42)
Replication Basics	566	(4)
Considerations When Planning for Replication	566	(2)
Replication Roles	568	(1)
Subscriptions	569	(1)
Types of Subscribers	570	(1)
Filtering Data	570	(1)
Replication Models	570	(11)
Snapshot Replication	571	(3)
Merge Replication	574	(3)
Transactional Replication	577	(3)
Immediate-Update Subscribers	580	(1)
Mixing Replication Types	581	(1)
Replication Topology	581	(6)
Simple Models	581	(3)
Mixed Models	584	(3)
Planning for Replication	587	(1)
Data Concerns	587	(1)
Mobile Devices	588	(1)
Setting Up Replication in Management Studio	588	(17)
Configufing the Server for Replication	588	(4)
Configuring a Publication	592	(6)
Setting Up Subscribers (via Management Studio)	598	(5)
Using Our Replicated Database	603	(2)
Replication Management Objects (RMO)	605	(1)
Summary	606	(1)
Looking at Things in Full: Full-Text Search	607	(26)
Full-Text Search Architecture	608	(2)
Setting Up Full-Text Indexes and Catalogs	610	(14)
Enabling Full-Text for Your Database	610	(1)
Creating, Altering, Dropping, and Manipulating a Full-Text Catalog	611	(3)
Creating, Altering, Dropping, and Manipulating Full-Text Indexes	614	(5)
Creating Full-Text Catalogs Using the Old Syntax	619	(2)
Old Syntax for Indexes	621	(1)
More on Index Population	622	(2)
Full-Text Query Syntax	624	(7)
Contains	624	(2)
Freetext	626	(1)
Containstable	626	(2)
Freetexttable	628	(1)
Dealing with Phrases	628	(1)
Booleans	629	(1)
Proximity	629	(1)
Weighting	630	(1)
Inflectional	631	(1)
Noise Words	631	(1)
Summary	632	(1)
Security	633	(36)
Security Basics	634	(4)
One Person, One Login, One Password	634	(1)
Password Expiration	635	(2)
Password Length and Makeup	637	(1)
Number of Tries to Log In	637	(1)
Storage of User and Password Information	637	(1)
Security Options	638	(9)
SQL Server Security	639	(1)
Creating and Managing Logins	640	(6)
Windows Integrated Security	646	(1)
User Rights	647	(8)
Granting Access to a Specific Database	647	(1)
Granting Object Permissions within the Database	648	(6)
User Rights and Statement-Level Permissions	654	(1)
Server and Database Roles	655	(6)
Server Roles	657	(1)
Database Roles	658	(3)
Application Roles	661	(3)
Creating Application Roles	662	(1)
Adding Permissions to the Application Role	662	(1)
Using the Application Role	662	(1)
Getting Rid of Application Roles	663	(1)
More Advanced Security	664	(2)
What to Do about the guest Account	664	(1)
TCP/IP Port Settings	664	(1)
Don't Use the sa Account	665	(1)
Keep xp_cmdshell under Wraps	665	(1)
Don't Forget Views, Stored Procedures, and UDFs as Security Tools	665	(1)
Certificates and Asymmetric Keys	666	(1)
Certificates	667	(1)
Asymmetric Keys	667	(1)
Summary	667	(2)
Playing a Good Tune: Performance Tuning	669	(30)
When to Tune	670	(1)
Index Choices	671	(2)
Check the Index Tuning Tool in the Database Tuning Advisor	672	(1)
Client- vs. Server-Side Processing	673	(1)
Strategic De-Normalization	674	(1)
Routine Maintenance	674	(1)
Organizing Your Sprocs Well	675	(2)
Keeping Transactions Short	675	(1)
Using the Least Restrictive Transaction Isolation Level Possible	675	(1)
Implementing Multiple Solutions If Necessary	675	(1)
Avoiding Cursors if Possible	676	(1)
Uses for Temporary Tables	677	(1)
Sometimes, It's the Little Things	677	(1)
Hardware Considerations	678	(8)
Exclusive Use of the Server	679	(1)
I/O vs. CPU Intensive	679	(5)
OLTP vs. OLAP	684	(1)
On-Site vs. Off-Site	684	(1)
The Risks of Being Down	685	(1)
Lost Data	685	(1)
Is Performance Everything?	685	(1)
Driver Support	686	(1)
The Ideal System	686	(1)
Troubleshooting	686	(12)
The Various Showplans and Statistics	687	(5)
The Database Consistency Checker (DBCC)	692	(1)
The Query Governor	692	(1)
The SQL Server Profiler	693	(3)
The Performance Monitor (Perfmon)	696	(2)
Summary	698	(1)
Administrator	699	(40)
Scheduling Jobs	700	(22)
Creating an Operator	701	(3)
Creating Jobs and Tasks	704	(18)
Backup and Recovery	722	(11)
Creating a Backup --- a.k.a. ``A Dump''	722	(6)
Recovery Models	728	(1)
Recovery	729	(4)
Index Maintenance	733	(3)
Alter Index	734	(1)
Index Name	734	(1)
Table or View Name	734	(1)
Rebuild	734	(1)
Disable	735	(1)
Reorganize	735	(1)
Archiving of Data	736	(1)
Summary	736	(3)
SMO: SQL Management Objects	739	(22)
The History of SQL Server Management Object Models	740	(2)
SQL Distributed Management Objects	740	(1)
SQL Namespaces	740	(1)
Windows Management Instrumentation	741	(1)
SMO	741	(1)
The SMO Object Model	742	(2)
Walking through Some Examples	744	(6)
Getting Started	744	(1)
Creating a Database	745	(1)
Creating Tables	746	(4)
Dropping a Database	750	(1)
Backing Up a Database	750	(2)
Scripting	752	(1)
Pulling It All Together	753	(5)
Summary	758	(3)
Appendix A: System Functions	761	(54)
Appendix B: Connectivity	815	(10)
Appendix C: Getting Service	825	(18)
Index	843