The guru's guide to Transact-SQL

Foreword. Preface. About the Sample Databases. Results Abridged. On Formality. Acknowledgments. 1. Introductory Transact-SQL. Choosing a SQL Editor. Creating a Database. Creating Tables. Inserting Data. Updating Data. Deleting Data. Querying Data. Column Lists. SELECTing Variables and Expressions. Functions. Converting Data Types. CASE. Aggregate Columns. Filtering Data. Joins. Outer Joins. Other Types of Joins. Subqueries. Grouping Data. HAVING. Ordering Data. Column Aliases. Table Aliases. Managing Transactions. Summary. 2. Transact-SQL Data Type Nuances. Dates. Y2K and Other Date Problems. Date Functions. Dates and Simple Arithmetic. Determining Time Gaps. Building Calendars. Strings. Concatenation. Char vs. Varchar. SET ANSI_PADDING. String Functions. CHARINDEX(). SOUNDEX(). A Better SOUNDEX(). DIFFERENCE(). Xp_sprintf. Xp_sscanf. Masks. Executing Strings. Unicode. Numerics. Floating Point Fun. Division by Zero. Funny Money. Formatting Numeric Data. BLOBs. Caveats. Retrieving BLOB Data. Updating BLOB Data. BLOB Updates and the Transaction Log. Bits. UNIQUEIDENTIFIER. Cursor Variables. Timestamps. Summary. 3. @AHEADS = Missing Values. NULL and Expressions. NULL and Functions. NULL and ANSI SQL. NULL and Stored Procedures. NULL If You Must. 4. DDL Insights. Create Table. Some Thoughts on Referential Integrity. Foreign Keys. ANSI Referential Actions. The NULL Exception. Unique Index Requirement. No Truncate Table. Default Constraints. Dropping Objects. CREATE INDEX. No Bit or BLOB Indexes. No Computed Column Indexes. Pad_Index. Drop_Existing. Temporary Objects. No More Unusable Temporary Objects. Can't Create Objects in Other Databases. Temporary Stored Procedures. Increased Temporary Table Name Length. Global Temporary Status Tables. Object Naming and Dependencies. Changing the Database Context Temporarily. Temporary Table Indexes. Be Wary of Unusable Views. Object Dependencies. Summary. 5. DML Insights. Insert. Default and Null. Set Identity_Insert. Insert...Default Values. Insert...Select. Insert...Exec. Extended Procedures. Insert and Errors. Using Insert to Remove Duplicate Rows. Insert and Clustered Indexes. Bulk Insert. Bulk Insert and Triggers. Bulk Insert and Constraints. Bulk Insert and Identity Columns. Update. The Halloween Problem. Update and Case. Using Update to Check Constraints. Limiting the Number of Rows Affected by an Update. Swapping Column Values with Update. Update and Cursors. Delete. Delete and Cursors. Truncate Table. Detecting DML Errors. Summary. 6. The Mighty Select Statement. Simple Selects. Computational and Derived Fields. Select Top. Derived Tables. Joins. Outer Joins and Join Order. Predicates. Between. Like. Exists. Nulls. Exists and In. Joins. Result Set Emptiness. Exists Outside Where and Having. In. Optimizing In. Any and All. Subqueries. Where and Subqueries. Correlated Subqueries. Relational Division. Aggregate Functions. Group by and Having. Pivot Tables. Cube and Rollup. Having. Union. Order by. Summary. 7. Views. Restrictions. DML Restrictions. ANSI SQL Schema Views. Getting a View's Source Code. Updatable View's. With Check Option. Derived Tables. Dynamic View's. Partitioning Data Using Views. Summary. 8. Statistical Functions. The Case for Case. Efficiency Concerns. Variance and Standard Deviation. Medians. The Identity Column Technique. The Case Technique. Vector Medians. Duplicate Values. Clipping. Returning the Top n Rows. Set Rowcount. Rankings. Modes. Histograms. Stratified Histograms. Cumulative and Sliding Aggregates. Sliding Aggregates. Extremes. Determining Extreme Attributes. Summary. 9. Runs and Sequences. Sequences. Time Series Fluctuation. Sampling Every nth Value. Regions. Relative Condition Regions. Constraining Region Sizes. Region Boundaries. Runs. Regions. Region Boundaries. Constrained Regions. Intervals. Partitioned Intervals. Summary. 10. Arrays. Arrays as Big Strings. Modifying Array Elements. Arrays as Tables. Sorting. Transposing Dimensions. Ensuring Array Integrity. Reshaping the Array. Comparing Arrays. Summary. 11. Sets. Unions. Differences. Intersections. Subsets. Returning Every nth Row. Summary. 12. Hierarchies. Simple Hierarchies. Multilevel Hierarchies. Indenting a Hierarchy. Another Approach. Listing Leaf Nodes. Indented Lists. Summary. 13. Cursors. On Cursors and Isams. Types of Cursors. Forward-Only Cursors. Dynamic Cursors. Static Cursors. Keyset Cursors. Appropriate Cursor Use. Dynamic Queries. Row-Oriented Operations. Scrollable Forms. T-SQL Cursor Syntax. Declare Cursor. Global vs. Local Cursors. Open. Fetch. Close. Deallocate. Configuring Cursors. Asynchronous Cursors. ANSI/ISO Automatic Cursor Closing. Defaulting to Global or Local Cursors. Updating Cursors. Cursor Variables. Cursor Stored Procedures. Optimizing Cursor Performance. Summary. 14. Transactions. Transactions Defined. The ACID Test. Atomicity. Consistency. Isolation. Durability. How SQL Server Transactions Work. Transactions and Nonlogged Operations. Transactions and Triggers. Types of Transactions. Automatic Transactions. Implicit Transactions. User-Defined Transactions. Distributed Transactions. Avoiding Transactions Altogether. Commands That Minimize Logging. Read-Only and Single-User Databases. Automatic Transaction Management. Transaction Isolation Levels. Read Uncommitted. Read Committed. Repeatable Read. Serializable. Transaction Commands and Syntax. Nested Transactions. Save Tran and Save Points. Avoid Accidental Rollbacks. Invalid T-SQL Syntax in Transactions. Debugging Transactions. Optimizing Transactional Code. Summary. 15. Stored Procedures and Triggers. Stored Procedure Advantages. Internals. Creation. User Execution. Compilation. Server Execution. Creating Stored Procedures. Creation Tips. Temporary Procedures. System Procedures. Extended Procedures. Faux Procedures. Executing Stored Procedures. Insert and Exec. Environmental Concerns. Parameters. Output Parameters. Result Codes. Listing Procedure Parameters. General Parameter Notes. Important Automatic Variables. Flow Control Language. Errors. @&commet;Error. xp_logevent. Nesting. Recursion. Autostart Procedures. Encryption. Triggers. Debugging Procedures. Summary. 16. Transact-SQL Performance Tuning. General Performance Guidelines. Database Design Performance Tips. Index Performance Tips. Select Performance Tips. Insert Performance Tips. Bulk Copy Performance Tips. Delete and Update Performance Tips. Cursor Performance Tips. Stored Procedure Performance Tips. Sargs. Denormalization. Basic Guidelines. Basic Techniques. Contrived Columns. Redundant Data. Summary Tables. Vertical Partitioning. Horizontal Partitioning. The Query Optimizer. The Index Tuning Wizard. Profiler. Perfmon. Summary. 17. Administrative Transact-SQL. GUI Administration. System Stored Procedures. Administrative Transact-SQL Commands. Administrative Automatic Variables. Where's the Beef? Summary. 18. Full-Text Search. Full-Text Predicates. Rowset Functions. Summary. 19. OLE Automation. sp_exporttable. sp_importtable. sp_getsSQLregistry. Summary. 20. Undocumented T-SQL. Defining Undocumented. Undocumented DBCC Commands. Undocumented Functions and Variables. Undocumented Trace Flags. Undocumented Procedures. Summary. 21. Potpourii. Obscure Function. Data Scrubbing. Iteration Tables. Summary. Appendix: Suggested Resources. Books. Internet Resources. 0201615762T04062001