Efnisyfirlit

• Cover
• Contents
• Title page
• Copyright
• Dedication
• Credits
• About the Authors
• About the Technical Editor
• Acknowledgments
• Introduction
• 1 RDBMS Basics: What Makes Up a SQL Server Database?
• An Overview of Database Objects
• SQL Server Data Types
• SQL Server Identifiers for Objects
• Summary
• 2 Learning the Tools of the Trade
• Getting Help with Books Online
• SQL Server Configuration Manager
• SQL Server Management Studio
• SQL Server Data Tools (formerly BIDS)
• SQL Server Integration Services (SSIS)
• SQL Server Reporting Services (SSRS)
• SQL Server Analysis Services (SSAS)
• Bulk Copy Program (BCP)
• SQL Server Profiler
• sqlcmd
• PowerShell
• Summary
• 3 The Foundation Statements of T-SQL
• Getting Started with a Basic SELECT Statement
• Adding Data with the INSERT Statement
• Changing What You’ve Got with the UPDATE Statement
• The DELETE Statement
• Summary
• 4 Joins
• Combining Table Data with JOINs
• Selecting Matching Rows with INNER JOIN
• Retrieving More Data with OUTER JOIN
• Seeing Both Sides with FULL JOINs
• Understanding CROSS JOINs
• Exploring Alternative Syntax for Joins
• Stacking Results with UNION
• Summary
• 5 Creating and Altering Tables
• Object Names in SQL Server
• The CREATE Statement
• The ALTER Statement
• The DROP Statement
• Using the GUI Tool
• Summary
• 6 Keys and Constraints
• Types of Constraints
• Constraint Naming
• Key Constraints
• CHECK Constraints
• DEFAULT Constraints
• Disabling Constraints
• Rules and Defaults — Cousins of Constraints
• Triggers for Data Integrity
• Choosing What to Use
• Summary
• 7 Adding More to Your Queries
• What Is a Subquery?
• Building Correlated Subqueries
• Derived Tables
• Using Common Table Expressions (CTEs)
• Using the EXISTS Operator
• Mixing Data Types: CAST and CONVERT
• Synchronizing Data with the MERGE Command
• Gathering Affected Rows with the OUTPUT Clause
• Through the Looking Glass: Windowing Functions
• One Chunk at a Time: Ad Hoc Query Paging
• Performance Considerations
• Summary
• 8 Being Normal: Normalization and Other Basic Design Issues
• Understanding Tables
• Keeping Your Data “Normal”
• Understanding Relationships
• Diagramming Databases
• Denormalization
• Beyond Normalization
• Drawing Up a Quick Example
• Summary
• 9 SQL Server Storage and Index Structures
• SQL Server Storage
• Understanding Indexes
• Creating, Altering, and Dropping Indexes
• Choosing Wisely: Deciding Which Index Goes Where and When
• Maintaining Your Indexes
• Summary
• 10 Views
• Creating Simple Views
• Editing Views with T-SQL
• Dropping Views
• Creating and Editing Views in the Management Studio
• Auditing: Displaying Existing Code
• Protecting Code: Encrypting Views
• About Schema Binding
• Making Your View Look Like a Table with VIEW_METADATA
• Indexed (Materialized) Views
• Indexing an Aggregate View
• Summary
• 11 Writing Scripts and Batches
• Understanding Script Basics
• Grouping Statements into Batches
• Running from the Command Prompt: sqlcmd
• Dynamic SQL: Using the EXEC Command
• Using Control-of-Flow Statements
• Summary
• 12 Stored Procedures
• Creating the Sproc: Basic Syntax
• Changing Stored Procedures with ALTER
• Dropping Sprocs
• Parameterizing Sprocs
• More on Dealing with Errors
• What a Sproc Offers
• Extended Stored Procedures (XPs)
• A Brief Look at Recursion
• Debugging
• Understanding .NET Assemblies
• When to Use Stored Procedures
• Summary
• 13 User-Defined Functions
• What a UDF Is
• UDFs Returning a Scalar Value
• UDFs That Return a Table
• Debugging User-Defined Functions
• Using .NET in a Database World
• Summary
• 14 Transactions and Locks
• Understanding Transactions
• How the SQL Server Log Works
• Understanding Locks and Concurrency
• Setting the Isolation Level
• Dealing with Deadlocks (aka “a 1205”)
• Summary
• 15 Triggers
• What Is a Trigger?
• Using Triggers for Data Integrity Rules
• Other Common Uses for Triggers
• Other Trigger Issues
• Instead of Triggers
• Performance Considerations
• Dropping Triggers
• Debugging Triggers
• Summary
• 16 A Brief XML Primer
• XML Basics
• What SQL Server Brings to the Party
• A Brief Word on XSLT
• Summary
• 17 Business Intelligence Fundamentals
• What Is Business Intelligence?
• Those Who Forget History: The Data Warehouse
• Dimensional Modeling: Why Be Normal?
• ETLs
• Making Your Data Actionable: BI Reporting Techniques
• Summary
• 18 BI Storage and Reporting Resources
• BI in T-SQL
• SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube
• Building Your First Cube
• Self-Service BI: User Tools
• Summary
• 19 Reporting for Duty, Sir! A Look at Reporting Services
• Reporting Services 101
• Understanding the SSRS Report Lifecycle
• Understanding the Reporting Services Architecture
• Building Simple Report Models
• Report Server Projects
• Summary
• 20 Getting Integrated with Integration Services
• Understanding the Problem
• Using the Import/Export Wizard to Generate Basic Packages
• Examining Package Basics
• Executing Packages
• A Final Word on Packages
• Summary
• 21 Playing Administrator
• Scheduling Jobs
• Logins and Users
• Backup and Recovery
• Index Maintenance
• Policy Based Management
• Automating Administration Tasks with PowerShell
• Summary
• Appendix: Answers to Exercises
• Bonus Appendices
• Bonus Appendix 1: System Functions
• System Statistical Functions
• Aggregate Functions
• Analytic Functions
• Configuration Functions
• Conversion Functions
• Cryptographic Functions
• Cursor Functions
• Date and Time Functions
• Hierarchy Functions
• Mathematical Functions
• Basic Metadata Functions
• Ranking Functions
• Rowset Functions
• Security Functions
• String Functions
• System Functions
• Text and Image Functions
• Bonus Appendix 2: Very Simple Connectivity Examples
• Some General Concepts
• Connectivity Examples
• A Brief Word on LINQ
• Advertisement
• End User License Agreement