Common table expressions Using scalar subqueries Using subqueries as lists Using subqueries as tables Row constructors All, some, and any Correlated Subqueries Correlating in the where clause Correlating a derived table using apply Relational Division Relational division with a remainder Exact relational division Composable SQL Aggregating Data Simple Aggregations Basic aggregations Aggregates, averages, and nulls Using aggregate functions within the Query Designer Beginning statistics Grouping within a Result Set Simple groupings Grouping sets Filtering grouped results Aggravating Queries Including group by descriptions Including all group by values Nesting aggregations Including detail descriptions OLAP in the Park Rollup subtotals Cube queries Building Crosstab Queries Pivot method Case expression method Dynamic crosstab queries Unpivot Cumulative Totals Running Sums Correlated subquery solution T-SQL cursor solution Multiple assignment variable solution Windowing and Ranking Windowing The Over clause Partitioning within the window Ranking Functions Row number function Ntile function Aggregate Functions Projecting Data Through Views Why Use Views?
The Basic View Creating views using the Query Designer Creating views with DDL code Executing views Altering and dropping a view A Broader Point of View Column aliases Order by and views View restrictions Nesting views Updating through views Views and performance Locking Down the View Unchecked data Protecting the data Protecting the view Encrypting the view's select statement Application metadata Using Synonyms Modifying Data Inserting Data Inserting simple rows of values Inserting a result set from select Inserting the result set from a stored procedure Creating a default row Creating a table while inserting data Updating Data Updating a single table Performing global search and replace Referencing multiple tables while updating data Deleting Data Referencing multiple data sources while deleting Cascading deletes Alternatives to physically deleting data Merging Data Returning Modified Data Returning data from an insert Returning data from an update Returning data from a delete Returning data from a merge Returning data into a table Modification Obstacles Primary Key Constraint and Unique Constraint Identity columns Globally unique identifiers GUIDs Deleting Duplicate Rows Deleting duplicate rows using windowing Deleting duplicate rows using a surrogate key Deleting duplicate rows using select distant into Foreign Key Constraints Null and Default Constraints Check Constraints Instead of Triggers After Triggers Non-Updateable Views Views With Check Option Calculated Columns Security Constraints Summary III.
Beyond Relational Traversing Hierarchies Adjacency List Pattern Single-level queries Subtree queries Recursive CTE down the hierarchy User-defined function down the hierarchy Recursive CTE looking up the hierarchy Searching up the hierarchy with a user-defined function Is the node an ancestor?
Microsoft SQL Server Bible | Board4All
Determining the node's level Reparenting the adjacency list Indexing an adjacency list Cyclic errors Adjacency list variations Adjacency list pros and cons The Materialized-Path Pattern Searching down the hierarchy with materialized path Searching up the hierarchy with materialized path Is the node in the subtree? Determining the node level Reparenting the materialized path Indexing the materialized path Materialized path pros and cons Using the New HierarchyID Selecting a single node Scanning for ancestors Performing a subtree search Single-level searches Inserting new nodes Performance HierarchyID pros and cons Manipulating XML Data Generating XML documents Querying XML documents Validating XML documents Sample Tables and Data XML Data Type Typed and untyped XML Creating and using XML columns Declaring and using XML variables Using XML parameters and return values Limitations of the XML data type XPath Joining XML nodes with relational tables Using variables and filters in XQuery expressions Accessing the parent node TYPE directive Generating XML Schema information Generating XML namespaces Simple queries FLWOR operation Understanding XQuery Functions String functions Numeric and aggregate functions Other functions Insert operation Update operation Delete operation Handling Namespaces Creating an XML Schema collection Creating typed XML columns and variables Performing validation Altering XML Schema collections What's in the "collection"?
Understanding XML Indexes XML Best Practices Using Integrated Full-Text Search Configuring Full-Text Search Catalogs Creating a catalog with the wizard Creating a catalog with T-SQL code Pushing data to the full-text index Maintaining a catalog with Management Studio Maintaining a catalog in T-SQL code Word Searches The Contains function The ContainsTable function Advanced Search Options Multiple-word searches Searches with wildcards Phrase searches Word-proximity searches Word-inflection searches Thesaurus searches Variable-word-weight searches Fuzzy Searches Freetext FreetextTable Summary IV.
Developing with SQL Server Creating the Physical Database Schema Designing the Physical Database Schema Logical to physical options Refining the data patterns Designing for performance Responsible denormalization Designing for extensibility Creating the Database The Create DDL command Database-file concepts Configuring file growth Using multiple files Creating a database with multiple files Modifying the files of an existing database Planning multiple filegroups Creating a database with filegroups Modifying filegroups Dropping a database Creating Tables Designing tables using Management Studio Working with SQL scripts Schemas Column names Filegroups Creating Keys Primary keys The surrogate debate: pros and cons Database design layers Creating primary keys Identity column surrogate primary keys Using uniqueidentifier surrogate primary keys Creating foreign keys Declarative referential integrity Optional foreign keys Cascading deletes and updates Creating User-Data Columns Column data types Character data types Numeric data types Other data types Calculated columns Sparse columns Column constraints and defaults Column nullability Creating Indexes Composite indexes Filegroup location Index options Unique indexes Include columns Filtered indexes Index fill factor and pad index Limiting index locks and parallelism Index sort order The Ignore Dup Key index option The Drop Existing index option The Statistics Norecompute index option Sort in tempdb Disabling an index Programming with T-SQL Transact-SQL Fundamentals T-SQL batches Terminating a batch DDL commands Switching databases Executing batches T-SQL formatting Statement termination Line continuation Comments Variables Variable default and scope Using the set and select commands Incrementing variables Conditional select Using variables within SQL queries Multiple assignment variables Procedural Flow If If exists While Goto Dynamic Management Views System functions Temporary Tables and Table Variables Local temporary tables Global temporary tables Table variables Kill the Cursor!
Anatomy of a Cursor The five steps to cursoring Managing the cursor Watching the cursor Cursor options Update cursor Cursor scope Cursors and transactions Cursor Strategies Refactoring Complex-Logic Cursors Update query with user-defined function Multiple queries Query with case expression Legacy Error Handling Raiserror The simple raiserror form The improved raiserror form Error severity Adding variable parameters to messages Stored messages Logging the error SQL Server log Catch Catch block Developing Stored Procedures Managing Stored Procedures Create, alter, and drop Executing a stored procedure Returning a record set Compiling stored procedures Stored procedure encryption System stored procedures Using stored procedures within queries Executing remote stored procedures Passing Data to Stored Procedures Input parameters Parameter defaults Table-valued parameters Returning Data from Stored Procedures Output parameters Using the Return Command Path and scope of returning data Building User-Defined Functions Scalar Functions Limitations Creating a scalar function Calling a scalar function Inline Table-Valued Functions Creating an inline table-valued function Calling an inline table-valued function Using parameters Correlated user-defined functions Creating functions with schema binding Multi-Statement Table-Valued Functions Creating a multi-statement table-valued function Calling the function Creating DML Triggers Trigger Basics Transaction flow Creating triggers After triggers Instead of triggers Trigger limitations Disabling triggers Listing triggers Triggers and security Working with the Transaction Determining the updated columns Inserted and deleted logical tables Developing multi-row-enabled triggers Multiple-Trigger Interaction Trigger organization Nested triggers Recursive triggers Instead of and after triggers Multiple after triggers Transaction-Aggregation Handling The inventory-transaction trigger The inventory trigger DDL Triggers Managing DDL Triggers Creating and altering DDL triggers Trigger scope DDL triggers and security Enabling and disabling DDL triggers Removing DDL triggers Developing DDL Triggers EventData Preventing database object changes Building Out the Data Abstraction Layer Google-Style Search Procedure Executing Dynamic SQL Parameterized queries Developing dynamic SQL code Code generation Preventing SQL Injection Appending malicious code What password?
Preventing SQL Server injection attacks Summary V. Data Connectivity Bulk Operations Bulk Insert Bulk Insert Options BCP Executing Distributed Queries Distributed Query Concepts Linking to External Data Sources Selecting the server Configuring the logins Configuring the options Establishing the link Distributed security and logins Linked server options Linking with non—SQL Server data sources Linking to Excel Linking to MS Access Developing Distributed Queries Distributed queries and Management Studio Distributed views Local-distributed queries Using the four-part name OpenDataSource Pass-through distributed queries OpenQuery OpenRowSet Distributed Transactions Distributed Transaction Coordinator Developing distributed transactions Monitoring distributed transactions Programming with ADO.
NET 3. An Overview of ADO.
NET ADO OLEDB ADODB primary interop assembly The ADO object model OLEDB data providers Mapping data types ADO and scripting The ADO. NET object model Managed providers SQL Native Client Data types DataAdapters and DataSets NET in Visual Studio Server Explorer Debugging ADO. Application tracing Application Building Basics Connecting to SQL Server What's new in ADO.
LINQ Stored procedures vs. Data adapters DataReaders and Recordsets Streams Asynchronous execution Using a single database value Data modification Binding to controls Sync Framework Sync Framework example Sync Framework overview Sync architecture Sync knowledge Sync fundamentals Sync services for ADO.
SQL Server 2008 Bible
What's new in Sync Services 2. Offline scenarios Collaboration scenarios LINQ Overview Standard Query Operators Query expression syntax Query syntax and method syntax Choosing a query syntax Example 1: Manually applying the mappings Example 2: The easy way Traversing XML LINQ to Entities Creating and querying entities using LINQ Asynchronous Messaging with Service Broker Configuring a Message Queue Working with Conversations Sending a message to the queue Receiving a message Monitoring Service Broker Replicating Data Replication Concepts Types of replication Replication agents Transactional consistency Configuring Replication Creating a publisher and distributor Creating a peer-to-peer topology Creating a merge publication Web synchronization Design Environment Connection managers Variable usage Expressions Configuring elements Control flow Control flow precedence Data flow Event handlers Executing a package in development Integration Services Package Elements Database Microsoft Learning contracted with Paul to design some of the Microsoft database certification tests and Microsoft official courseware.
- Strong Convictions: An Emmett Strong Western (Emmett Strong Westerns, Book 1)!
- Microsoft® SQL Server® Bible [Book].
Paul is the author, co-author, editor, or contributor to 8 technology books including his best-selling SQL Server Bible. Paul is active in the Colorado entrepreneurial eco-system and is on the Board of Peak Startup, a non-profit focused on encouraging the entrepreneurial and investment community in Colorado Springs.
Paul was previously widowed and is now married to his beautiful wife, Edie. They have a year-old daughter who loves to ride horses and 2 adult children, and live in Colorado Springs, Colorado. For relaxation, Paul reads voraciously and plays a Taylor ce acoustic guitar for relaxation. Recent activities.