Sack SQL Server 2008 Transact-SQL Recipes

A Problem-Solution Approach
1. ed
ISBN: 978-1-4302-0625-5
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 872 Seiten

Without Transact-SQL, there is nothing. Developers and database administrators using Microsoft SQL Server 2008 are highly-motivated to learn Transact-SQL, because that language enables them to store and retrieve data, to move business logic into the database, and to interface with advanced SQL Server features. The recipe format facilitates just-in-time learning, showing readers immediately how to perform their assigned tasks, and is sure to be a hit with today's busy professional.

Joseph Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail, and the real estate industry. Prior to joining SQLskills, he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting, and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development, and high-availability. In 2006 Joe earned the Microsoft Certified Master: SQL Server 2005 certification; and in 2008, he earned the Microsoft Certified Master: SQL Server 2008 certification. In 2009 he took over responsibility for the SQL Server Microsoft Certified Master program and held that post until 2011. He is the author of a few books and white papers, including most recently, SQL Server 2008 Transact-SQL Recipes (Apress, 2008). Joe's blog is at www.SQLskills.com/blogs/joe, and he can be reached at [e-mail].

Autoren/Hrsg.

Sack, Joseph

Inhaltsverzeichnis

1;Contents at a Glance;4
2;Contents;6
3;About the Author;24
4;About the Technical Reviewer;25
5;Acknowledgments;26
6;Introduction;27
7;SELECT;28
7.1;The Basic SELECT Statement;28
7.2;Selective Querying Using a Basic WHERE Clause;30
7.3;Using Operators and Expressions;34
7.4;Grouping Data;41
7.5;Ordering Results;44
7.6;SELECT Clause Techniques;48
7.7;Subqueries;54
7.8;Querying from More Than One Data Source;55
7.9;Using APPLY to Invoke a Table-Valued Function for Each Row;62
7.10;Advanced Techniques for Data Sources;65
7.11;Summarizing Data;73
7.12;Common Table Expressions;83
8;Perform, Capture, and Track Data Modifications;90
8.1;INSERT;90
8.2;UPDATE;100
8.3;DELETE;113
8.4;Advanced Data Modification Techniques;116
8.5;Capturing and Tracking Data Modification Changes;120
9;Transactions, Locking, Blocking, and Deadlocking;141
9.1;Transaction Control;141
9.2;Locking;148
9.3;Transaction, Locking, and Concurrency;154
9.4;Blocking;160
9.5;Deadlocking;163
10;Tables;169
10.1;Table Basics;169
10.2;Collation Basics;181
10.3;Keys;183
10.4;Surrogate Keys;191
10.5;Constraints;194
10.6;Temporary Tables and Table Variables;202
10.7;Manageability for Very Large Tables;206
11;Indexes;222
11.1;Index Overview;222
11.2;Controlling Index Build Performance and Concurrency;232
11.3;Index Options;234
11.4;Managing Very Large Indexes;237
12;Full-Text Search;241
12.1;Full-Text Indexes and Catalogs;241
12.2;Basic Searching;254
12.3;Advanced Searching;256
12.4;Ranked Searching;259
13;Views;262
13.1;Regular Views;263
13.2;View Encryption;270
13.3;Indexed Views;271
13.4;Partitioned Views;274
14;SQL Server Functions;280
14.1;Aggregate Functions;280
14.2;Mathematical Functions;284
14.3;String Functions;286
14.4;Working with NULLs;297
14.5;Date Functions;300
14.6;Type Conversion;307
14.7;Ranking Functions;311
14.8;Probing Server, Database, and Connection-Level Settings Using System Functions;316
14.9;IDENTITY and uniqueidentifier Functions;326
15;Conditional Processing, Control-of- Flow, and Cursors;330
15.1;Conditional Processing;330
15.2;Control-of-Flow;335
15.3;Cursors;342
16;Stored Procedures;347
16.1;Stored Procedure Basics;347
16.2;Stored Procedure Security;357
16.3;Recompilation and Caching;362
17;User-Defined Functions and Types;365
17.1;UDF Basics;365
17.2;Benefitting from UDFs;379
17.3;UDT Basics;387
18;Triggers;395
18.1;DML Triggers;396
18.2;DDL Triggers;408
18.3;Managing Triggers;415
19;CLR Integration;423
19.1;CLR Overview;424
19.2;When (and When Not) to Use Assemblies;424
19.3;CLR Objects Overview;426
19.4;Creating CLR Database Objects;426
19.5;Administering Assemblies;439
20;XML, Hierarchies, and Spatial Data;441
20.1;Working with Native XML;441
20.2;Converting Between XML Documents and Relational Data;452
20.3;Working with Native Hierarchical Data;457
20.4;Native Spatial Data;464
21;Hints;471
21.1;Using Join Hints;471
21.2;Using Query Hints;473
21.3;Using Table Hints;476
22;Error Handling;481
22.1;System-Defined and User-Defined Error Messages;481
22.2;Manually Raising an Error;484
22.3;Trapping and Handling Application Errors;487
23;Principals;496
23.1;Windows Principals;496
23.2;SQL Server Principals;501
23.3;Database Principals;509
24;Securables, Permissions, and Auditing;521
24.1;Permissions Overview;522
24.2;Server-Scoped Securables and Permissions;525
24.3;Database-Scoped Securables and Permissions;529
24.4;Schema-Scoped Securables and Permissions;534
24.5;Object Permissions;539
24.6;Managing Permissions Across Securable Scopes;542
24.7;Auditing SQL Instance and Database-Level Activity of Principals Against Securables;549
25;Encryption;566
25.1;Encryption by Passphrase;566
25.2;Master Keys;569
25.3;Asymmetric Key Encryption;574
25.4;Symmetric Key Encryption;579
25.5;Certificate Encryption;586
25.6;Transparent Data Encryption;594
26;Service Broker;598
26.1;Example Scenario: Online Bookstore;599
26.2;Creating a Basic Service Broker Application;599
26.3;Creating a Stored Procedure to Process Messages;617
26.4;Remote-Server Service Broker Implementations;620
26.5;Event Notifications;631
27;Configuring and Viewing SQL Server Options;634
28;Creating and Configuring Databases;639
28.1;Creating, Altering, and Dropping Databases;639
28.2;Configuring Database Options;653
28.3;Controlling Database Access and Ownership;669
28.4;Managing Database Files and Filegroups;672
28.5;Viewing and Managing Database Space Usage;681
29;Database Integrity and Optimization;687
29.1;Database Integrity Checking;687
29.2;Tables and Constraints;692
29.3;Index Maintenance;700
30;Maintaining Database Objects and Object Dependencies;705
30.1;Database Object Maintenance;705
30.2;Object Dependencies;708
31;Database Mirroring;714
31.1;Database Mirroring in Context;714
31.2;Database Mirroring Architecture;715
31.3;Setting Up Database Mirroring;717
31.4;Setup Summary;726
31.5;Operating Database Mirroring;727
31.6;Monitoring and Configuring Options;731
32;Database Snapshots;734
32.1;Snapshot Basics;734
33;Linked Servers and Distributed Queries;740
33.1;Linked Server Basics;740
33.2;Linked Server Logins;745
33.3;Executing Distributed Queries;747
34;Query Performance Tuning;756
34.1;Query Performance Tips;757
34.2;Capturing and Evaluating Query Performance;759
34.3;Index Tuning;771
34.4;Statistics;777
34.5;Miscellaneous Techniques;783
35;Backup and Recovery;805
35.1;Creating a Backup and Recovery Plan;805
35.2;Making Backups;807
35.3;Restoring a Database;826
36;Index;839