book

Professional Microsoft® SQL Server® 2008 Programming

Name: Professional Microsoft® SQL Server® 2008 Programming
ISBN: 9780470257029

by Rob Vieira, Mahsa Nakhjiri

April 2009

Intermediate to advanced

959 pages

25h 32m

English

Wrox

Read now

Unlock full access

Copyright
About the Author
Credits
Acknowledgments
Introduction
Version IssuesWho This Book Is ForWhat This Book CoversHow This Book Is StructuredConventionsSource CodeErratap2p.wrox.comwww.professionalsql.com
1. Being Objective: Re-Examining Objects in SQL Server
1.1. So, What Exactly Do We Have Here?1.2. An Overview of Database Objects1.2.1. The Database Object1.2.1.1. The master Database1.2.1.2. The model Database1.2.1.3. The msdb Database1.2.1.4. The tempdb Database1.2.1.5. AdventureWorks/AdventureWorks20081.2.1.6. AdventureWorksLT/AdventureWorksLT20081.2.1.7. AdventureWorksDW/AdventureWorksDW20081.2.2. The Transaction Log1.2.3. The Most Basic Database Object: Table1.2.3.1. Indexes1.2.3.2. Triggers1.2.3.3. Constraints1.2.4. Schemas1.2.5. Filegroups1.2.6. Diagrams1.2.7. Views1.2.8. Stored Procedures1.2.9. User-Defined Functions1.2.10. Users and Roles1.2.11. Rules1.2.12. Defaults1.2.13. User-Defined Data Types1.2.14. Full-Text Catalogs1.3. SQL Server Data Types1.3.1. NULL Data1.4. SQL Server Identifiers for Objects1.4.1. What Gets Named?1.4.2. Rules for Naming1.5. Summary
2. Tool Time
2.1. Books Online2.2. The SQL Server Configuration Manager2.2.1. Service Management2.2.2. Network Configuration2.2.3. The Protocols2.2.3.1. Named Pipes2.2.3.2. TCP/IP2.2.3.3. Shared Memory2.2.3.4. VIA2.2.4. On to the Client2.3. The SQL Server Management Studio2.3.1. Getting Started2.3.1.1. Server Type2.3.1.2. SQL Server2.3.1.3. Authentication Type2.3.1.3.1. Windows Authentication2.3.1.3.2. Mixed Authentication2.3.2. Query Editor2.3.2.1. Getting Started2.3.2.2. Results in Text2.3.2.3. Results in Grid2.3.2.4. Results to File2.3.2.5. SQLCMD Mode2.3.2.6. Show Execution Plan2.3.2.7. The DB Combo Box2.3.2.8. The Object Explorer2.4. SQL Server Business Intelligence Development Studio2.5. SQL Server Integration Services (SSIS)2.6. Reporting Services2.7. Bulk Copy Program (bcp)2.8. SQL Server Profiler2.9. sqlcmd2.10. Summary
3. Asking a Better Question: Advanced Queries
3.1. A Quick Review of Subqueries3.2. Building a Nested Subquery3.2.1. Nested Queries Using Single Value SELECT Statements3.2.2. Nested Queries Using Subqueries That Return Multiple Values3.2.2.1. Using a Nested SELECT to Find Orphaned Records3.2.3. The ANY, SOME, and ALL Operators3.3. Correlated Subqueries3.3.1. How Correlated Subqueries Work3.3.2. Correlated Subqueries in the WHERE Clause3.3.3. Correlated Subqueries in the SELECT List3.4. Derived Tables3.5. The EXISTS Operator3.5.1. Using EXISTS in Other Ways3.6. The INTERSECT and EXCEPT Operators3.6.1. EXCEPT3.6.2. INTERSECT3.6.3. Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents3.7. Common Table Expressions (CTEs)3.8. Recursive Queries3.9. MERGE3.10. Using External Calls to Perform Complex Actions3.11. Performance Considerations3.11.1. JOINs vs. Subqueries vs. ?3.12. Summary
4. XML Integration
4.1. The XML Data Type4.1.1. Defining a Column as Being of XML Type4.1.2. XML Schema Collections4.1.3. Creating, Altering, and Dropping XML Schema Collections4.1.3.1. CREATE XML SCHEMA COLLECTION4.1.3.2. ALTER XML SCHEMA COLLECTION4.1.3.3. DROP XML SCHEMA COLLECTION4.1.4. XML Data Type Methods4.1.4.1. .query (SQL Server's Implementation of XQuery)4.1.4.2. .value4.1.4.3. .modify4.1.4.4. .nodes4.1.4.5. .exist4.1.5. Enforcing Constraints beyond the Schema Collection4.2. Retrieving Relational Data in XML Format4.2.1. The FOR XML Clause4.2.1.1. RAW4.2.1.2. AUTO4.2.1.3. EXPLICIT4.2.1.3.1. Tag and Parent4.2.1.3.2. Column Naming4.2.1.3.3. element4.2.1.3.4. xml4.2.1.3.5. hide4.2.1.3.6. id, idref, and idrefs4.2.1.3.7. xmltext4.2.1.3.8. cdata4.2.1.4. PATH4.2.1.4.1. PATH 1014.2.1.4.2. Unnamed Columns4.2.1.4.3. Named Columns4.2.1.4.4. But Wait, There's More ...4.2.2. OPENXML4.3. A Quick Heads Up Regarding XML Indexes4.4. A Brief Word on Hierarchical Data4.5. Summary
5. Daring to Design
5.1. Normalization 2015.1.1. Where to Begin5.1.2. Getting to Third Normal Form5.1.3. Other Normal Forms5.2. Relationships5.3. Diagramming5.3.1. A Couple of Relationship Types5.3.1.1. Identifying Relationships5.3.1.2. Non-Identifying Relationships5.3.2. The Entity Box5.3.3. The Relationship Line5.3.4. Terminators5.4. Logical versus Physical Design5.4.1. Purpose of a Logical Model5.4.2. Parts of a Logical Model5.4.2.1. Structure5.4.2.2. Constraints5.4.2.3. Rules5.5. Dealing with File-Based Information Via Classic BLOBs5.6. Subcategories5.6.1. Types of Subcategories5.6.2. Keeping Track of What's What — Implementing Subcategories5.6.3. Getting Physical — The Physical Implementation of Subcategories5.6.4. Adding to Extensibility with Subcategories5.7. Database Reuse5.7.1. Candidates for Reusable Databases5.7.2. How to Break Things Up5.7.3. The High Price of Reusability5.8. De-Normalization5.9. Partitioning for Scalability5.10. The SQL Server Diagramming Tools5.10.1. Tables5.10.1.1. Adding Tables5.10.1.2. Dropping Tables from Either the Database or Diagram5.10.2. Dealing with Constraints5.10.2.1. Primary Keys5.10.2.2. Foreign Keys5.10.2.3. CHECK Constraints5.11. Regarding Date Columns5.12. Summary

6. Core Storage and Index Structure
6.1. SQL Server Storage6.1.1. The Database6.1.2. The File6.1.3. The Extent6.1.4. The Page6.1.4.1. Data Pages6.1.4.2. Index Pages6.1.4.3. BLOB Pages6.1.4.4. Global Allocation Map, Shared Global Allocation Map, and Page Free Space Pages6.1.4.5. Bulk Changed Map6.1.4.6. Differential Changed Map6.1.4.7. Page Splits6.1.5. Rows6.1.6. Full-Text Catalogs6.1.7. File Streams6.2. Understanding Indexes6.2.1. To "B," or Not to "B": B-Trees6.2.1.1. Page Splits — A Deeper Look6.2.2. How Data Is Accessed in SQL Server6.2.2.1. Use of Table Scans6.2.2.2. Use of Indexes6.2.3. Index Types and Index Navigation6.2.3.1. Clustered Indexes6.2.3.1.1. Navigating the Tree6.2.3.2. Non-Clustered Indexes on a Heap6.2.3.3. Non-Clustered Indexes on a Clustered Table6.3. Creating, Altering, and Dropping Indexes6.3.1. The CREATE INDEX Statement6.3.1.1. ASC/DESC6.3.1.2. INCLUDE6.3.1.3. WITH6.3.1.4. PAD_INDEX6.3.1.5. FILLFACTOR6.3.1.6. IGNORE_DUP_KEY6.3.1.7. DROP_EXISTING6.3.1.8. STATISTICS_NORECOMPUTE6.3.1.9. SORT_IN_TEMPDB6.3.1.10. ONLINE6.3.1.11. ALLOW ROW/PAGE LOCKS6.3.1.12. MAXDOP6.3.1.13. ON6.3.2. Implied Indexes Created with Constraints6.3.3. ALTER INDEX6.3.3.1. Index Name6.3.3.2. Table or View Name6.3.3.3. REBUILD6.3.3.4. DISABLE6.3.3.5. REORGANIZE6.3.4. DROP INDEX6.4. Choosing Wisely: Deciding What Index Goes Where and When6.4.1. Selectivity6.4.2. Watching Costs: When Less Is More6.4.3. Choosing That Clustered Index6.4.3.1. The Pros6.4.3.2. The Cons6.4.4. Column Order Matters6.4.5. Dropping Indexes6.4.6. Use the Database Engine Tuning Advisor6.5. Maintaining Your Indexes6.5.1. Fragmentation6.5.2. Identifying Fragmentation6.5.2.1. sy.sdm_db_index_physical_stats6.5.2.2. Backward Compatibility6.5.2.3. DBREINDEX — That Other Way of Maintaining Your Indexes6.6. Summary
7. More Advanced Index Structures
7.1. XML Indexes7.1.1. The Primary XML Index7.1.2. Secondary XML Indexes7.1.2.1. PATH XML Indexes7.1.2.2. VALUE XML Indexes7.1.2.3. PROPERTY XML Indexes7.1.3. Creating XML Indexes7.2. User-Defined Data Types7.2.1. Classic UDTs7.2.2. .NET UDTs7.2.3. Tabular UDTs7.2.3.1. Creating a Table User-Defined Data Type7.2.4. Dropping a User-Defined Type7.3. Hierarchical Data7.3.1. Understanding Depth Versus Fanout7.3.2. HierarchyID Type Structure7.3.3. Working with HierarchyID Values — HierarchyID Methods7.3.3.1. Methods Related to Retrieving a Given Level of Hierarchy Data7.3.3.2. Methods Related to Retrieving Parent or Child Hierarchy Data7.3.3.3. Inserting New Hierarchical Data7.3.3.4. Moving Sub-Trees Between Parents7.3.3.5. Getting the Root of a Hierarchy7.3.4. Indexing Hierarchy Data7.3.5. Performance Considerations7.4. Spatial Data7.4.1. Spatial Concepts7.4.1.1. Planar (Flat Earth) Data7.4.1.2. Geodetic (Round Earth) Data7.4.1.3. Representing Spatial Data7.4.2. Implementing Planar Data Representations — The GEOMETRY Data Type7.4.3. Implementing Geodetic Representations — The GEOGRAPHY Type7.5. Filestreams7.6. Enabling Filestreaming7.6.1. Enabling a Database for Filestreams7.6.2. Creating a Filestream-Enabled Table7.6.3. Using T-SQL with Filestreams7.6.4. Using Filestreams with .NET7.7. Table Compression7.7.1. Enabling Compression7.8. Summary
8. Views
8.1. Reviewing View Syntax8.2. More Complex Views8.2.1. Using a View to Change Data — Before INSTEAD OF Triggers8.2.1.1. Dealing with Changes in Views with Joined Data8.2.1.2. Required Fields Must Appear in the View or Have the Default Value8.2.1.3. Limit What's Inserted into Views — WITH CHECK OPTION8.3. Editing Views with T-SQL8.4. Dropping Views8.5. Auditing: Displaying Existing Code8.6. Protecting Code: Encrypting Views8.7. About Schema Binding8.8. Making Your View Look Like a Table with VIEW_METADATA8.9. Indexed (Materialized) Views8.10. Partitioned Views8.11. Summary
9. Scripts and Batches
9.1. Script Basics9.2. Batches9.2.1.9.2.1.1. A Line to Itself9.2.1.2. Each Batch Is Sent to the Server Separately9.2.1.3. GO Is Not a T-SQL Command9.2.2. Errors in Batches9.2.3. When to Use Batches9.2.3.1. Statements That Require Their Own Batch9.2.3.2. Using Batches to Establish Precedence9.3. SQLCMD9.4. Dynamic SQL: Generating Your Code on the Fly with the EXEC Command9.4.1. The Gotchas of EXEC9.4.1.1. The Scope of EXEC9.4.1.1.1. A Small Exception to the Rule9.4.1.2. Security Contexts and EXEC9.4.1.3. Use of Functions in Concatenation and EXEC9.4.1.4. EXEC and UDFs9.5. Control-of-Flow Statements9.5.1. The IF ... ELSE Statement9.5.1.1. The ELSE Clause9.5.1.2. Grouping Code into Blocks9.5.2. The CASE Statement9.5.2.1. A Simple CASE9.5.2.2. A Searched CASE9.5.3. Looping with the WHILE Statement9.5.4. The WAITFOR Statement9.5.4.1. The DELAY Parameter9.5.4.2. The TIME Parameter9.5.5. TRY/CATCH Blocks9.6. Summary
10. Advanced Programmability
10.1. A More Advanced Look At Stored Procedures10.1.1. Output Parameters10.1.2. Dealing with Errors10.1.2.1. The Way We Were10.1.2.1.1. Handling Inline Errors10.1.2.1.2. Using @@ERROR10.1.2.1.3. Using @@ERROR in a Sproc10.1.2.2. Manually Raising Errors10.1.2.2.1. Message ID/Message String10.1.2.2.2. Severity10.1.2.2.3. State10.1.2.2.4. Error Arguments10.1.2.2.5. WITH <option>10.1.2.2.6. WITH LOG10.1.2.2.7. WITH SETERROR10.1.2.2.8. WITH NOWAIT10.1.2.3. Adding Your Own Custom Error Messages10.1.2.3.1. @lang10.1.2.3.2. @with_log10.1.2.3.3. @replace10.1.2.3.4. Using sp_addmessage10.1.2.3.5. Removing an Existing Custom Message10.2. Table-Valued Parameters (TVPs)10.3. Debugging10.3.1. Starting the Debugger10.3.2. Parts of the Debugger10.3.2.1. The Locals Window10.3.2.2. The Watch Window10.3.2.3. The Call Stack Window10.3.2.4. The Output Window10.3.2.5. The Command Window10.3.3. Using the Debugger Once It's Started10.4. Understanding the SQLCLR and .NET Programming in SQL Server10.4.1. Assemblies 10110.4.2. Compiling an Assembly10.4.3. Uploading Your Assembly to SQL Server10.4.4. Creating Your Assembly-Based Stored Procedure10.4.5. Creating Scalar User-Defined Functions from Assemblies10.4.6. Creating Table-Valued Functions10.5. Creating Aggregate Functions10.5.1. Creating Triggers from Assemblies10.6. Custom Data Types10.6.1. Creating Your Data Type from Your Assembly10.6.2. Accessing Your Complex Data Type10.6.3. Dropping Data Types10.7. Summary
11. Transactions and Locks
11.1. Transactions11.1.1. BEGIN TRAN11.1.1.1. Regarding Marking Transactions11.1.2. COMMIT TRAN11.1.3. ROLLBACK TRAN11.1.4. SAVE TRAN11.2. How the SQL Server Log Works11.2.1. Using the CHECKPOINT Command11.2.2. At Normal Server Shutdown11.2.3. At a Change of Database Options11.2.4. When the Truncate on Checkpoint Option Is Active11.2.5. When Recovery Time Would Exceed the Recovery Interval Option Setting11.2.6. Failure and Recovery11.2.6.1. Transaction 111.2.6.2. Transaction 211.2.6.3. Transaction 311.2.6.4. Transaction 411.2.6.5. Transaction 511.2.7. Implicit Transactions11.3. Locks and Concurrency11.3.1. What Problems Can Be Prevented by Locks11.3.1.1. Dirty Reads11.3.1.2. Non-Repeatable Reads11.3.1.3. Phantoms11.3.1.4. Lost Updates11.3.2. Lockable Resources11.3.3. Lock Escalation and Lock Effects on Performance11.3.4. Lock Modes11.3.4.1. Shared Locks11.3.4.2. Exclusive Locks11.3.4.3. Update Locks11.3.4.4. Intent Locks11.3.4.5. Schema Locks11.3.4.6. Bulk Update Locks11.3.4.7. Ranged Keylocks11.3.5. Lock Compatibility11.3.6. Specifying a Specific Lock Type — Optimizer Hints11.3.6.1. Determining Locks Using the Management Studio11.4. Setting the Isolation Level11.4.1. READ COMMITTED11.4.2. READ UNCOMMITTED11.4.3. REPEATABLE READ11.4.4. SERIALIZABLE11.4.5. SNAPSHOT11.5. Dealing with Deadlocks (a.k.a. "A 1205")11.5.1. How SQL Server Figures Out There's a Deadlock11.5.2. How Deadlock Victims Are Chosen11.5.3. Avoiding Deadlocks11.5.3.1. Using Objects in the Same Order11.5.3.2. Keeping Transactions As Short As Possible11.5.3.3. Using the Lowest Transaction Isolation Level Possible11.5.3.4. Allowing No Open-Ended Transactions11.5.3.5. Using Bound Connections11.6. Summary
12. Triggers
12.1. What Is a Trigger?12.1.1. ON12.1.2. WITH ENCRYPTION12.1.3. The FOR\AFTER versus the INSTEAD OF Clause12.1.3.1. INSERT Trigger12.1.3.2. DELETE Trigger12.1.3.3. UPDATE Trigger12.1.4. WITH APPEND12.1.5. NOT FOR REPLICATION12.1.6. AS12.2. Using Triggers for Data Integrity Rules12.2.1. Dealing with Requirements Sourced from Other Tables12.2.2. Using Triggers to Check the Delta of an Update12.2.3. Using Triggers for Custom Error Messages12.3. Other Common Uses for Triggers12.3.1. Updating Summary Information12.3.2. Feeding Data into De-normalized Tables for Reporting12.3.3. Setting Condition Flags12.4. Other Trigger Issues12.4.1. Triggers Can Be Nested12.4.2. Triggers Can Be Recursive12.4.3. Debugging Triggers12.4.4. Triggers Don't Get in the Way of Architecture Changes12.4.5. Triggers Can Be Turned Off without Being Removed12.4.6. Trigger Firing Order12.4.6.1. Controlling Firing Order for Logic Reasons12.4.6.2. Controlling Firing Order for Performance Reasons12.5. INSTEAD OF Triggers12.5.1. INSTEAD OF INSERT Triggers12.5.2. INSTEAD OF UPDATE Triggers12.5.3. INSTEAD OF DELETE Triggers12.6. IF UPDATE() and COLUMNS_UPDATED()12.6.1. The UPDATE() Function12.6.2. The COLUMNS_UPDATED() Function12.7. Performance Considerations12.7.1. Triggers Are Reactive Rather Than Proactive12.7.2. Triggers Don't Have Concurrency Issues with the Process That Fires Them12.7.3. Keep It Short and Sweet12.7.4. Don't Forget Triggers When Choosing Indexes12.7.5. Try Not to Roll Back within Triggers12.8. Dropping Triggers12.9. Summary
13. SQL Cursors
13.1. What Is a Cursor?13.2. The Life Span of a Cursor13.3. Types of Cursors and Extended Declaration Syntax13.3.1. Scope13.3.2. Scrollability13.3.2.1. FORWARD_ONLY13.3.2.2. SCROLLABLE13.3.3. Cursor Types13.3.3.1. Static Cursors13.3.3.2. Keyset-Driven Cursors13.3.3.3. Dynamic Cursors13.3.3.4. FAST_FORWARD Cursors13.3.4. Concurrency Options13.3.4.1. READ_ONLY13.3.4.2. SCROLL_LOCKS13.3.4.3. OPTIMISTIC13.3.5. Detecting Conversion of Cursor Types: TYPE_WARNING13.3.6. FOR <SELECT>13.3.7. FOR UPDATE13.4. Navigating the Cursor: The FETCH Statement13.5. Altering Data within Your Cursor13.6. Summary
14. Reporting Services
14.1. A Quick Look at Reports as a Concept14.2. Reporting Services 10114.2.1. Tools Used with Reporting Services14.2.2. Other Means of Accessing Reporting Services14.3. Report Server Projects14.3.1. Data Sources14.3.1.1. Creating a Data Source14.3.2. Using the Report Wizard14.3.3. Editing Reports14.3.4. Parameterizing Reports14.3.5. Providing/Controlling Parameter Values and How They Are Used14.3.5.1. Creating Pre-set Parameter Lists14.3.5.2. Creating Parameter Lists from Queries14.3.5.3. Getting Headings and Other Fields from Parameters14.3.6. Adding Charts14.3.7. Linking Reports14.3.8. Deploying the Report14.4. A Brief Note on RDL14.5. Summary
15. Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations
15.1. bcp Utility15.1.1. bcp Syntax15.1.2. bcp Import15.1.2.1. Data Import Example15.1.2.2. Logged vs. Non-logged15.1.3. bcp Export15.2. Format Files15.2.1. When Your Columns Don't Match15.2.1.1. Files with Fewer Columns Than the Table15.2.1.2. More Columns in the File Than in the Table15.2.1.3. Mismatched Field Order15.2.2. Using Format Files15.2.3. Maximizing Import Performance15.3. BULK INSERT15.4. OPENROWSET (BULK)15.4.1. ROWS_PER_BATCH15.4.2. SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB15.5. Summary
16. Getting Integrated
16.1. Understanding the Problem16.2. An Overview of Packages16.2.1. Tasks16.2.2. The Main Window16.2.2.1. Control Flow16.2.2.2. Data Flow16.2.2.3. Event Handlers16.2.2.4. Package Explorer16.2.3. Solution Explorer16.2.4. The Properties Window16.3. Building a Simple Package16.4. Executing Packages16.4.1. Using the Execute Package Utility16.4.1.1. General16.4.1.2. Configurations16.4.1.3. Command Files16.4.1.4. Connection Managers16.4.1.5. Execution Options16.4.1.6. Reporting16.4.1.7. Logging16.4.1.8. Set Values16.4.1.9. Verification16.4.1.10. Command Line16.4.1.11. Executing the Package16.4.2. Executing within Management Studio16.5. Summary
17. Replication
17.1. Replication Basics17.1.1. Considerations When Planning for Replication17.1.1.1. Autonomy17.1.1.2. Latency17.1.1.3. Data Consistency17.1.1.4. Schema Consistency17.1.1.5. Other Considerations17.1.2. Replication Roles17.1.2.1. The Publisher17.1.2.2. The Distributor17.1.2.3. The Subscriber17.1.3. Subscriptions17.1.4. Types of Subscribers17.1.5. Filtering Data17.2. Replication Models17.2.1. Snapshot Replication17.2.1.1. How Snapshot Replication Works17.2.1.1.1. Snapshot Agent17.2.1.1.2. Distribution Agent17.2.1.1.3. The Process of Snapshot Replication17.2.1.2. When to Use Snapshot Replication17.2.1.3. Special Planning Requirements17.2.2. Merge Replication17.2.2.1. Merge Agent17.2.2.2. The Process of Merge Replication17.2.2.3. When to Use Merge Replication17.2.2.4. Special Planning Requirements17.2.3. Transactional Replication17.2.3.1. Log Reader Agent17.2.3.2. The Process of Transactional Replication17.2.3.3. When to Use Transactional Replication17.2.3.4. Special Planning Requirements17.2.4. Immediate-Update Subscribers17.2.5. Mixing Replication Types17.3. Replication Topology17.3.1. Simple Models17.3.1.1. Central Publisher/Distributor17.3.1.2. Central Publisher/Remote Distributor17.3.1.3. Central Subscriber17.3.2. Mixed Models17.3.2.1. Publishing Subscriber17.3.2.2. Publisher/Subscriber17.3.2.3. Multiple Subscribers/Multiple Publishers17.3.2.4. Self-Publishing17.4. Planning for Replication17.4.1. Data Concerns17.4.1.1. timestamp17.4.1.2. uniqueidentifier17.4.1.3. User-Defined Data Types17.4.1.4. NOT FOR REPLICATION17.4.2. Mobile Devices17.5. Setting Up Replication in Management Studio17.5.1. Configuring the Server for Replication17.5.2. Configuring a Publication17.5.3. Setting Up Subscribers (via Management Studio)17.5.4. Using Our Replicated Database17.6. Replication Management Objects (RMO)17.7. Summary
18. Looking at Things in Full: Full-Text Search
18.1. Full-Text Search Architecture18.2. Setting Up Full-Text Indexes and Catalogs18.2.1. Enabling Full-Text for Your Database18.2.2. Creating, Altering, Dropping, and Manipulating a Full-Text Catalog18.2.2.1. Altering Full-Text Catalogs18.2.2.1.1. REBUILD18.2.2.1.2. REORGANIZE18.2.2.1.3. AS DEFAULT18.2.2.1.4. Dropping Full-Text Catalogs18.2.3. Creating, Altering, Dropping, and Manipulating Full-Text Indexes18.2.3.1. Creating Full-Text Indexes18.2.3.1.1. Column List18.2.3.1.2. LANGUAGE18.2.3.1.3. TYPE COLUMN18.2.3.1.4. KEY INDEX18.2.3.1.5. ON18.2.3.1.6. WITH18.2.3.1.7. CHANGE_TRACKING18.2.3.1.8. NO POPULATION18.2.3.1.9. STOPLIST18.2.3.2. Altering Full-Text Indexes18.2.3.2.1. ENABLE/DISABLE18.2.3.2.2. ADD18.2.3.2.3. DROP18.2.3.2.4. START ... POPULATION18.2.3.2.5. FULL18.2.3.2.6. INCREMENTAL18.2.3.2.7. UPDATE18.2.3.2.8. STOP, PAUSE, RESUME18.2.3.3. Dropping Full-Text Indexes18.2.4. A Note Regarding the Older Syntax18.3. More on Index Population18.4. Full-Text Query Syntax18.4.1. CONTAINS18.4.2. FREETEXT18.4.3. CONTAINSTABLE18.4.4. FREETEXTTABLE18.4.5. Dealing with Phrases18.4.6. Booleans18.4.7. Proximity18.4.8. Weighting18.4.9. Inflectional18.5. Stop Words18.6. Summary
19. Feeling Secure
19.1. Security Basics19.1.1. One Person, One Login, One Password19.1.2. Password Expiration19.1.2.1. What Do You Get for Your Effort?19.1.2.2. Now the Bad News19.1.3. Password Length and Makeup19.1.3.1. Password Length19.1.3.2. Password Makeup19.1.4. Number of Tries to Log In19.1.5. Storage of User and Password Information19.2. Security Options19.2.1. SQL Server Security19.2.2. Creating and Managing Logins19.2.2.1. CREATE LOGIN19.2.2.1.1. CREATE LOGIN ... WITH19.2.2.1.2. CREATE LOGIN ... FROM19.2.2.1.3. ALTER LOGIN19.2.2.1.4. DROP LOGIN19.2.2.2. Creating a Login Using the Management Studio19.2.2.3. SQL Management Objects19.2.2.4. Legacy Options19.2.2.4.1. A Quick Look at sp_addlogin19.2.2.4.2. sp_password19.2.2.4.3. sp_grantlogin19.2.3. Windows Authentication19.3. User Permissions19.3.1. Granting Access to a Specific Database19.3.1.1. CREATE USER19.3.1.2. sp_grantdbaccess19.3.2. Granting Object Permissions within the Database19.3.2.1. GRANT19.3.2.2. DENY19.3.2.3. REVOKE19.3.3. User Rights and Statement-Level Permissions19.4. Server and Database Roles19.4.1. Server Roles19.4.2. Database Roles19.4.2.1. Fixed Database Roles19.4.2.2. User-Defined Database Roles19.4.2.2.1. Creating a User-Defined Role Using CREATE ROLE19.4.2.3. Using sp_addrole19.4.2.3.1. Adding Users to a Role19.4.2.3.2. Removing a User from a Role19.4.2.3.3. Dropping Roles19.5. Application Roles19.5.1. Creating Application Roles19.5.2. Adding Permissions to the Application Role19.5.3. Using the Application Role19.5.4. Getting Rid of Application Roles19.6. More Advanced Security19.6.1. What to Do About the Guest Account19.6.2. TCP/IP Port Settings19.6.3. Don't Use the sa Account19.6.4. Keep xp_cmdshell under Wraps19.6.5. Don't Forget Views, Stored Procedures, and UDFs as Security Tools19.7. Certificates and Asymmetric Keys19.7.1. Certificates19.7.2. Asymmetric Keys19.7.3. Database Encryption19.8. Summary
20. A Grand Performance: Designing a Database That Performs Well
20.1. When to Tune20.2. Index Choices20.2.1. Check the Index Tuning Tool in the Database Engine Tuning Advisor20.3. Client vs. Server-Side Processing20.4. Strategic De-Normalization20.5. Organizing Your Sprocs Well20.5.1. Keeping Transactions Short20.5.2. Using the Least Restrictive Transaction Isolation Level Possible20.5.3. Implementing Multiple Solutions if Necessary20.5.4. Avoiding Cursors if Possible20.6. Uses for Temporary Tables20.6.1. Using Temp Tables to Break Apart Complex Problems20.6.2. Using Temp Tables to Allow Indexing on Working Data20.7. Update Your Code In a Timely Fashion20.8. Sometimes, It's the Little Things20.9. Hardware Considerations20.9.1. Exclusive Use of the Server20.9.2. I/O vs. CPU Intensive20.9.2.1. I/O Intensive20.9.2.1.1. A Brief Look at RAID20.9.2.2. CPU Intensive20.9.3. OLTP vs. OLAP20.9.4. On-Site vs. Off-Site20.9.5. The Risks of Being Down20.9.6. Lost Data20.9.7. Is Performance Everything?20.9.8. Driver Support20.9.9. The Ideal System20.10. Summary
21. What Comes After: Forensic Performance Tuning
21.1. When to Tune (Mark Two)21.2. Routine Maintenance21.3. Troubleshooting21.3.1. The Data Collector21.3.2. The Various Showplans and STATISTICS21.3.2.1. SHOWPLAN TEXT|ALL21.3.2.2. Graphical Showplan21.3.2.3. STATISTICS21.3.2.3.1. SET STATISTICS IO ON|OFF21.3.2.3.2. SET STATISTICS TIME ON|OFF21.3.2.3.3. Include Client Statistics21.3.3. Database Console Commands (DBCC)21.3.4. Dynamic Management Views21.3.5. The Activity Monitor21.3.5.1. Overview21.3.5.2. Processes21.3.5.3. Resource Waits21.3.5.4. Data File I/O21.3.5.5. Recent Expensive Queries21.3.6. The SQL Server Profiler21.3.7. The Performance Monitor (PerfMon)21.4. Summary
22. Administration
22.1. Scheduling Jobs22.1.1. Creating an Operator22.1.1.1. Creating an Operator Using Management Studio22.1.1.2. Creating an Operator Using T-SQL22.1.2. Creating Jobs and Tasks22.1.2.1. Creating Jobs and Tasks Using Management Studio22.1.2.2. Creating Jobs and Tasks Using T-SQL22.1.2.2.1. sp_add_job22.1.2.2.2. sp_add_jobserver22.1.2.2.3. sp_add_jobstep22.1.2.2.4. sp_add_jobschedule22.1.2.3. Maintaining and Deleting Jobs and Tasks22.1.2.3.1. Editing and Deleting Jobs with T-SQL22.2. Backup and Recovery22.2.1. Creating a Backup — a.k.a. "A Dump"22.2.1.1. Backup Type22.2.1.2. Backup Set22.2.1.3. Destination22.2.1.4. Options22.2.1.5. Schedule22.2.1.6. Backing Up Using T-SQL22.2.2. Recovery Models22.2.3. Recovery22.2.3.1. Restoring to a Different Location22.2.3.2. Recovery Status22.2.3.3. Restoring Data Using T-SQL22.3. Index Maintenance22.3.1. ALTER INDEX22.3.2. Index Name22.3.3. Table or View Name22.3.4. REBUILD22.3.5. DISABLE22.3.6. REORGANIZE22.4. Archiving of Data22.5. PowerShell22.5.1. Trying Out PowerShell22.5.2. Navigating in PowerShell22.5.3. A Final Word on PowerShell22.6. Policy-Based Management22.7. Summary
23. SMO: SQL Management Objects
23.1. The History of SQL Server Management Object Models23.1.1. SQL Distributed Management Objects23.1.2. SQL Namespaces23.1.3. Windows Management Instrumentation23.1.4. SMO23.2. The SMO Object Model23.3. Walking through Some Examples23.3.1. Getting Started23.3.1.1. Declarations23.3.1.2. Basic Connection and Server References23.3.2. Creating a Database23.3.3. Creating Tables23.4. Dropping a Database23.5. Backing Up a Database23.6. Scripting23.7. Pulling It All Together23.8. Summary
24. Data Warehousing
24.1. Considering Differing Requirements24.1.1. Online Transaction Processing (OLTP)24.1.2. Online Analytical Processing (OLAP)24.1.3. A Brief Word on Data Mining24.1.4. OLTP or OLAP?24.2. Dimensional Databases24.2.1. The Fact Table24.2.2. The Dimension Tables24.2.3. The Star and Snowflake Schemas24.2.4. Data Cubes24.3. Data Warehouse Concepts24.3.1. Data Warehouse Characteristics24.3.2. Data Marts24.4. SQL Server Integration Services24.4.1. Data Validation24.4.2. Data Scrubbing24.5. Creating an Analysis Services Solution24.6. Accessing a Cube24.7. Summary
25. Being Well Connected
25.1. Access Models Past and Present25.1.1. DB-Lib/VBSQL25.1.2. Open Database Connectivity: ODBC25.1.3. Data Access Objects (DAO)25.1.4. Remote Data Objects (RDO)25.1.5. Java Database Connectivity (JDBC)25.1.6. OLE-DB25.1.7. ActiveX Data Objects (ADO)25.1.8. Multi-Dimensional OLE-DB/ADO (OLE-DB MD/ADO MD)25.1.9. ADO.NET25.1.10. Language Integrated Query (aka LINQ)25.2. Some General Concepts25.2.1. Connection25.2.2. Command25.2.3. Data Set25.2.4. Bound Controls25.3. General Performance Considerations25.3.1. Connection Management25.3.2. Roundtrips25.3.3. Hierarchies25.3.4. Dependent Data25.3.5. Bandwidth25.4. Getting Ready for the More Individual Examples25.4.1. Setting Up the Example Project25.5. Exploring ADO.NET25.5.1. Getting Connected with ADO.NET25.5.2. Using Bound Controls in ADO.NET25.6. Language Integrated Query (aka LINQ)25.6.1. Understanding Key Differences Between LINQ Query Syntax and SQL25.6.2. Building a LINQ to SQL Entity Model25.6.3. Utilizing a LINQ to SQL Class in Code25.6.4. A Few Other Notes on LINQ25.7. A Quick Discussion of Entity Frameworks25.8. Pros and Cons25.9. Summary
A. System Functions
A.1. Legacy System Functions (a.k.a. Global Variables)A.1.1. @@CONNECTIONSA.1.2. @@CPU_BUSYA.1.3. @@IDLEA.1.4. @@IO_BUSYA.1.5. @@PACK_RECEIVED and @@PACK_SENTA.1.6. @@PACKET_ERRORSA.1.7. @@TIMETICKSA.1.8. @@TOTAL_ERRORSA.1.9. @@TOTAL_READ and @@TOTAL_WRITEA.1.10. @@TRANCOUNTA.2. Aggregate FunctionsA.2.1. AVGA.2.2. CHECKSUMA.2.3. CHECKSUM_AGGA.2.4. COUNTA.2.5. COUNT_BIGA.2.6. GROUPINGA.2.7. MAXA.2.8. MINA.2.9. STDEVA.2.10. STDEVPA.2.11. SUMA.2.12. VARA.2.13. VARPA.3. Configuration FunctionsA.3.1. @@DATEFIRSTA.3.2. @@DBTSA.3.3. @@LANGID and @@LANGUAGEA.3.4. @@LOCK_TIMEOUTA.3.5. @@MAX_CONNECTIONSA.3.6. @@MAX_PRECISIONA.3.7. @@NESTLEVELA.3.8. @@OPTIONSA.3.9. @@REMSERVERA.3.10. @@SERVERNAMEA.3.11. @@SERVICENAMEA.3.12. @@SPIDA.3.13. @@TEXTSIZEA.3.14. @@VERSIONA.4. Cryptographic FunctionsA.4.1. AsymKey_IDA.4.2. Cert_IDA.4.3. CertPropertyA.4.4. DecryptByAsmKeyA.4.5. DecryptByCertA.4.6. DecryptByKeyA.4.7. DecryptByPassPhraseA.4.8. EncryptByAsmKeyA.4.9. EncryptByCertA.4.10. EncryptByKeyA.4.11. EncryptByPassPhraseA.4.12. Key_GUIDA.4.13. Key_IDA.4.14. SignByAsymKeyA.4.15. SignByCertA.4.16. VerifySignedByAsymKeyA.4.17. VerifySignedByCertA.5. Cursor FunctionsA.5.1. @@CURSOR_ROWSA.5.2. @@FETCH_STATUSA.5.3. CURSOR_STATUSA.6. Date and Time FunctionsA.6.1. CURRENT_TIMESTAMPA.6.2. DATEADDA.6.3. DATEDIFFA.6.4. DATENAMEA.6.5. DATEPARTA.6.6. DAYA.6.7. GETDATEA.6.8. GETUTCDATEA.6.9. ISDATEA.6.10. MONTHA.6.11. SYSDATETIMEA.6.12. SYSDATETIMEOFFSETA.6.13. SYSUTCDATETIMEA.6.14. SWITCHOFFSETA.6.15. TODATETIMEOFFSETA.6.16. YEARA.7. Mathematical FunctionsA.7.1. ABSA.7.2. ACOSA.7.3. ASINA.7.4. ATANA.7.5. ATN2A.7.6. CEILINGA.7.7. COSA.7.8. COTA.7.9. DEGREESA.7.10. EXPA.7.11. FLOORA.7.12. LOGA.7.13. LOG10A.7.14. PIA.7.15. POWERA.7.16. RADIANSA.7.17. RANDA.7.18. ROUNDA.7.19. SIGNA.7.20. SINA.7.21. SQRTA.7.22. SQUAREA.7.23. TANA.8. Basic Metadata FunctionsA.8.1. COL_LENGTHA.8.2. COL_NAMEA.8.3. COLUMNPROPERTYA.8.4. DATABASEPROPERTYA.8.5. DATABASEPROPERTYEXA.8.6. DB_IDA.8.7. DB_NAMEA.8.8. FILE_IDA.8.9. FILE_NAMEA.8.10. FILEGROUP_IDA.8.11. FILEGROUP_NAMEA.8.12. FILEGROUPPROPERTYA.8.13. FILEPROPERTYA.8.14. FULLTEXTCATALOGPROPERTYA.8.15. FULLTEXTSERVICEPROPERTYA.8.16. INDEX_COLA.8.17. INDEXKEY_PROPERTYA.8.18. INDEXPROPERTYA.8.19. OBJECT_IDA.8.20. OBJECT_NAMEA.8.21. OBJECTPROPERTYA.8.22. OBJECTPROPERTYEXA.8.23. @@PROCIDA.8.24. SCHEMA_IDA.8.25. SCHEMA_NAMEA.8.26. SQL_VARIANT_PROPERTYA.8.27. TYPEPROPERTYA.9. Rowset FunctionsA.9.1. CHANGETABLEA.9.2. CONTAINSTABLEA.9.3. FREETEXTTABLEA.9.4. OPENDATASOURCEA.9.5. OPENQUERYA.9.6. OPENROWSETA.9.7. OPENXMLA.10. Security FunctionsA.10.1. HAS_DBACCESSA.10.2. IS_MEMBERA.10.3. IS_SRVROLEMEMBERA.10.4. SUSER_IDA.10.5. SUSER_NAMEA.10.6. SUSER_SIDA.10.7. SUSER_SNAMEA.10.8. USERA.10.9. USER_IDA.10.10. USER_NAMEA.11. String FunctionsA.11.1. ASCIIA.11.2. CHARA.11.3. CHARINDEXA.11.4. DIFFERENCEA.11.5. LEFTA.11.6. LENA.11.7. LOWERA.11.8. LTRIMA.11.9. NCHARA.11.10. PATINDEXA.11.11. QUOTENAMEA.11.12. REPLACEA.11.13. REPLICATEA.11.14. REVERSEA.11.15. RIGHTA.11.16. RTRIMA.11.17. SOUNDEXA.11.18. SPACEA.11.19. STRA.11.20. STUFFA.11.21. SUBSTRINGA.11.22. UNICODEA.11.23. UPPERA.12. System FunctionsA.12.1. APP_NAMEA.12.2. CASEA.12.2.1. Simple CASE function:A.12.2.2. Searched CASE function:A.12.3. CAST and CONVERTA.12.3.1. Using CAST:A.12.3.2. Using CONVERT:A.12.4. COALESCEA.12.5. COLLATIONPROPERTYA.12.6. CURRENT_USERA.12.7. DATALENGTHA.12.8. @@ERRORA.12.9. FORMATMESSAGEA.12.10. GETANSINULLA.12.11. HOST_IDA.12.12. HOST_NAMEA.12.13. IDENT_CURRENTA.12.14. IDENT_INCRA.12.15. IDENT_SEEDA.12.16. @@IDENTITYA.12.17. IDENTITYA.12.18. ISNULLA.12.19. ISNUMERICA.12.20. NEWIDA.12.21. NULLIFA.12.22. PARSENAMEA.12.23. PERMISSIONSA.12.24. @@ROWCOUNTA.12.25. ROWCOUNT_BIGA.12.26. SCOPE_IDENTITYA.12.27. SERVERPROPERTYA.12.28. SESSION_USERA.12.29. SESSIONPROPERTYA.12.30. STATS_DATEA.12.31. SYSTEM_USERA.12.32. USER_NAMEA.13. Text and Image FunctionsA.13.1. TEXTPTRA.13.2. TEXTVALID
B. Going Meta: Getting Data About Your Data
B.1. System ViewsB.1.1. sys.assembliesB.1.2. sys.columnsB.1.3. sys.databasesB.1.4. sys.database_filesB.1.5. sys.identity_columnsB.1.6. sys.indexesB.1.7. sys.index_columnsB.1.8. sys.objectsB.1.9. sys.partitionsB.1.10. sys.partition_functionsB.1.11. sys.schemasB.1.12. sys.serversB.1.13. sys.spatial_indexesB.1.14. sys.synonymsB.1.15. sys.user_tokenB.1.16. sys.xml_indexesB.2. Dynamic Management ViewsB.2.1. Index-Related dm Views and FunctionsB.2.1.1. sys.db_db_index_physical_statsB.2.1.2. sys.dm_db_index_usage_statsB.2.1.3. The sys.dm_db_missing_index_* family of views
C. The Basics
C.1. Everything to Do with QueriesC.1.1. The Basic SELECT StatementC.1.1.1. The WHERE ClauseC.1.1.2. ORDER BYC.1.1.3. The GROUP BY ClauseC.1.1.4. The HAVING ClauseC.1.2. Beyond Inner JoinsC.1.2.1. OUTER JoinsC.1.2.2. FULL JoinsC.1.2.3. CROSS JOINC.2. The INSERT StatementC.2.1. The INSERT INTO ... SELECT StatementC.3. The UPDATE StatementC.4. The DELETE StatementC.5. Exploring Alternative Syntax for JoinsC.5.1. An Alternative Inner JoinC.5.2. An Alternative Outer JoinC.5.3. An Alternative CROSS JOINC.6. UNIONC.6.1. Subqueries and Derived TablesC.6.1.1. Nested SubqueriesC.6.1.2. Correlated SubqueriesC.6.1.3. Derived TablesC.7. Summary

Content preview from Professional Microsoft® SQL Server® 2008 Programming

Chapter 7. More Advanced Index Structures

Alright, so we've walked through the basics of design. Heck, we've even walked through the advanced stages of traditional indexing. There are, however, some even more advanced things to think about in indexing and other storage. Among these are some of the atypical index and storage structures including:

XML indexes
Spatial data and their associated indexes
User-defined data types
Filestreams
Table compression
Hierarchical data

In this chapter, we'll take a look at each of these. Some of it will build on things you already know (like the XML data type and methods we've already talked about extensively), and some will likely be totally new. (Indeed, the remaining items are new with SQL Server 2008.)

The choice to group these particular items into one chapter may seem a bit crazy (even to me), but the thing they have in common is pretty simple. They are all somewhat out of the mainstream and require a bit of extra thinking to see how they work.

XML Indexes

XML indexes first appeared in SQL Server 2005, and I have to admit that I continue to be mildly amazed that Microsoft pulled it off. I've known some of that team for a very long time now, and I have a lot of confidence in them, but the indexing of something as unstructured as XML has been a problem that many have tried to address, but few have done with any real success. Kudos to the SQL Server team for pulling this one off. Enough gushing though. I want to get down to the business of what XML indexes ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Professional SQL Server™ 2005 Programming

Publisher Resources

ISBN: 9780470257029Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Professional Microsoft® SQL Server® 2008 Programming

by Rob Vieira, Mahsa Nakhjiri

Chapter 7. More Advanced Index Structures

XML Indexes

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.