Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Cover
Title Page
Copyright
Dedication
About the Authors
Credits
Acknowledgments
Introduction
Version Issues
Who This Book Is For
What This Book Covers
How This Book Is Structured
Conventions
Source Code
Errata
p2p.wrox.com
www.professionalsql.com
Chapter 1: Being Objective: Re-Examining Objects in SQL Server
So, What Exactly Do We Have Here?
An Overview of Database Objects
SQL Server Data Types
SQL Server Identifiers for Objects
Summary
Chapter 2: Tool Time
Books Online
The SQL Server Configuration Manager
The SQL Server Management Studio
SQL Server Business Intelligence Development Studio
SQL Server Integration Services (SSIS)
Reporting Services
Bulk Copy Program (bcp)
SQL Server Profiler
sqlcmd
Summary
Chapter 3: Asking a Better Question: Advanced Queries
A Quick Review of Subqueries
Building a Nested Subquery
Correlated Subqueries
Derived Tables
The EXISTS Operator
The INTERSECT and EXCEPT Operators
Common Table Expressions (CTEs)
Recursive Queries
MERGE
Using External Calls to Perform Complex Actions
Performance Considerations
Summary
Chapter 4: XML Integration
The XML Data Type
Retrieving Relational Data in XML Format
A Quick Heads Up Regarding XML Indexes
A Brief Word on Hierarchical Data
Summary
Chapter 5: Daring to Design
Normalization 201
Relationships
Diagramming
Logical versus Physical Design
Dealing with File-Based Information Via Classic BLOBs
Subcategories
Database Reuse
De-Normalization
Partitioning for Scalability
The SQL Server Diagramming Tools
Regarding Date Columns
Summary
Chapter 6: Core Storage and Index Structure
SQL Server Storage
Understanding Indexes
Creating, Altering, and Dropping Indexes
Choosing Wisely: Deciding What Index Goes Where and When
Maintaining Your Indexes
Summary
Chapter 7: More Advanced Index Structures
XML Indexes
User-Defined Data Types
Hierarchical Data
Spatial Data
Filestreams
Enabling Filestreaming
Table Compression
Summary
Chapter 8: Views
Reviewing View Syntax
More Complex Views
Editing Views with T-SQL
Dropping Views
Auditing: Displaying Existing Code
Protecting Code: Encrypting Views
About Schema Binding
Making Your View Look Like a Table with VIEW_METADATA
Indexed (Materialized) Views
Partitioned Views
Summary
Chapter 9: Scripts and Batches
Script Basics
Batches
SQLCMD
Dynamic SQL: Generating Your Code on the Fly with the EXEC Command
Control-of-Flow Statements
Summary
Chapter 10: Advanced Programmability
A More Advanced Look At Stored Procedures
Table-Valued Parameters (TVPs)
Debugging
Understanding the SQLCLR and .NET Programming in SQL Server
Creating Aggregate Functions
Custom Data Types
Summary
Chapter 11: Transactions and Locks
Transactions
How the SQL Server Log Works
Locks and Concurrency
Setting the Isolation Level
Dealing with Deadlocks (a.k.a. “A 1205”)
Summary
Chapter 12: Triggers
What Is a Trigger?
Using Triggers for Data Integrity Rules
Other Common Uses for Triggers
Other Trigger Issues
INSTEAD OF Triggers
IF UPDATE() and COLUMNS_UPDATED()
Performance Considerations
Dropping Triggers
Summary
Chapter 13: SQL Cursors
What Is a Cursor?
The Life Span of a Cursor
Types of Cursors and Extended Declaration Syntax
Navigating the Cursor: The FETCH Statement
Altering Data within Your Cursor
Summary
Chapter 14: Reporting Services
A Quick Look at Reports as a Concept
Reporting Services 101
Report Server Projects
A Brief Note on RDL
Summary
Chapter 15: Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations
bcp Utility
Format Files
BULK INSERT
OPENROWSET (BULK)
Summary
Chapter 16: Getting Integrated
Understanding the Problem
An Overview of Packages
Building a Simple Package
Executing Packages
Summary
Chapter 17: Replication
Replication Basics
Replication Models
Replication Topology
Planning for Replication
Setting Up Replication in Management Studio
Replication Management Objects (RMO)
Summary
Chapter 18: Looking at Things in Full: Full-Text Search
Full-Text Search Architecture
Setting Up Full-Text Indexes and Catalogs
More on Index Population
Full-Text Query Syntax
Stop Words
Summary
Chapter 19: Feeling Secure
Security Basics
Security Options
User Permissions
Server and Database Roles
Application Roles
More Advanced Security
Certificates and Asymmetric Keys
Summary
Chapter 20: A Grand Performance: Designing a Database That Performs Well
When to Tune
Index Choices
Client vs. Server-Side Processing
Strategic De-Normalization
Organizing Your Sprocs Well
Uses for Temporary Tables
Update Your Code In a Timely Fashion
Sometimes, It's the Little Things
Hardware Considerations
Summary
Chapter 21: What Comes After: Forensic Performance Tuning
When to Tune (Mark Two)
Routine Maintenance
Troubleshooting
Summary
Chapter 22: Administration
Scheduling Jobs
Backup and Recovery
Index Maintenance
Archiving of Data
PowerShell
Policy-Based Management
Summary
Chapter 23: SMO: SQL Management Objects
The History of SQL Server Management Object Models
The SMO Object Model
Walking through Some Examples
Dropping a Database
Backing Up a Database
Scripting
Pulling It All Together
Summary
Chapter 24: Data Warehousing
Considering Differing Requirements
Dimensional Databases
Data Warehouse Concepts
SQL Server Integration Services
Creating an Analysis Services Solution
Accessing a Cube
Summary
Chapter 25: Being Well Connected
Appendix A: System Functions
Legacy System Functions (a.k.a. Global Variables)
Aggregate Functions
Configuration Functions
Cryptographic Functions
Cursor Functions
Date and Time Functions
Mathematical Functions
Basic Metadata Functions
Rowset Functions
Security Functions
String Functions
System Functions
Text and Image Functions
Appendix B: Going Meta: Getting Data About Your Data
System Views
Dynamic Management Views
Appendix C: The Basis
Everything to Do with Queries
The INSERT Statement
The UPDATE Statement
The DELETE Statement
Exploring Alternative Syntax for Joins
UNION
Summary
Index
Advertisement
← Prev
Back
Next →
← Prev
Back
Next →