Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Cover
Contents
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 5: Creating and Altering Tables
Object Names in SQL Server
The CREATE Statement
The ALTER Statement
The DROP Statement
Using the GUI Tool
Summary
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 16: A Brief XML Primer
XML Basics
What SQL Server Brings to the Party
A Brief Word on XSLT
Summary
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Introduction
Advertisement
← Prev
Back
Next →
← Prev
Back
Next →