Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

SQL Server Reference Guide Overview

Last updated Mar 28, 2003.

If you’re looking for more up-to-date information on this topic, please visit our SQL Server article, podcast, and store pages.

This InformIT SQL Server Reference Guide intends to be your "one-stop shop" into the world of Microsoft’s SQL Server, providing you with the information you need to administer and program SQL Server. You’ll find hundreds of tutorials here on SQL Server, and we’re adding new SQL Server content every week, so make sure you check in often.

We also provide an easy way to find out when the guide is updated. If you're using an RSS reader, you can subscribe to these notifications here. We also have a blog you can subscribe to for even more up-to-date information.

The sections in this Guide are designed to allow quick access to what you need. The tutorials and overviews can be read in just a few minutes, and many contain useful scripts and hands-on guides to examples you can follow.

There’s so much here that you need a more than one navigation paradigm to find what you need. To begin, you can click the “Guide Contents” link in the left-hand top corner. That brings up a menu of the articles below the one you’re reading now. There’s another way to navigate the guide. You can use the “Search” box in the top right-hand side of the guide. There’s yet another way to get around, which you’re reading now. I’ll keep this section up to date each week, and add a description to each section so that you can quickly find what you want to read.

To help you navigate the Guide, let's take a look at a few of the sections you'll find here.

SQL Server Reference Guide Overview

You are here. This is a comprehensive “guide to the guide.” In this section you can read about each part of the guide to find exactly what you’re looking for.

Microsoft SQL Server Defined

This section describes Microsoft SQL Server and relational database engines. I compare SQL Server to other systems and show where it does well and where it doesn’t. I also describe the editions of SQL Server; having this information can make a big difference in licensing and hardware costs. If you’re new to SQL Server or databases in general, this is where you start

 

SQL Server Editions

 

SQL Server Access

Microsoft SQL Server Features

This section explains the feature set for SQL Server. Along with storing and retrieving data, SQL Server has a few other tricks up its sleeve. This section explains those tricks, for both the "newbie" and "guru" types. If you don’t know anything about the topic, I’ll sacrifice completeness for simplicity, and if you know a lot about it, I’ll use plenty of jargon and specifics to speed you along your way!

 

SQL Server Books Online

 

Clustering Services

 

Data Transformation Services (DTS) Overview

 

Replication Services

 

Database Mirroring

 

Natural Language Processing (NLP)

 

Analysis Services

 

Reporting Services

 

XML Overview

 

Notification Services for the DBA

 

Full-Text Search

 

SQL Server 2005 - Service Broker

 

SQL Server Web Services

 

SQL Server 2005 - Encryption

 

SQL Server 2008 Overview

Microsoft SQL Server Administration

From service packs to backups, this section covers everything the database administrator needs to do his or her job. If you’re a developer, there’s lots of good information in here for you as well.

 

DBA Survival Guide - 10 Minutes to SQL Server

 

Installing SQL Server

 

SQL Server 2000 Management Tools

 

SQL Server 2005 Management Tools

 

SQL Server 2008 Management Tools

 

Automating Tasks with SQL Server Agent

 

Storage - SQL Server I/O

 

Service Packs

 

Error Logs and Event Logging

 

Change Management

 

SQL Server Metadata, Part One

 

SQL Server Metadata, Part Two

 

Monitoring - SQL Server 2005 Dynamic Views and Functions

 

Monitoring - Performance Monitor

 

Unattended Performance Monitoring for SQL Server

 

Monitoring - User-Defined Performance Counters

 

Monitoring: SQL Server Activity Monitor

 

SQL Server Instances

 

DBCC Commands

 

SQL Server and Mail

 

Database Maintenance

 

The Maintenance Wizard

 

The Web Assistant Wizard

 

Creating Web Pages from SQL Server

 

SQL Server Security

 

Securing the Platform

 

SQL Server Security – Users (Principals)

 

SQL Server Security – Roles

 

SQL Server Security - Objects (Securables)

 

Security - Using The Command Line

 

SQL Server Security - Encrypting Connections

 

SQL Server Security - Encrypting Data

 

High Availability - SQL Server Clustering

 

SQL Server Configuration - Part 1

 

SQL Server Configuration - Part 2

 

Database Configuration Options

 

32-vs-64 Bit Computing for SQL Server

 

SQL Server and Memory

 

Performance Tuning: Introduction to Indexes

 

Statistical Indexes

 

Backup and Recovery

 

Backup and Recovery Examples, Part One

 

Backup and Recovery Examples, Part Two: Transferring Databases to Another System (even without backups)

 

SQL Profiler - Reverse Engineering An Application

 

SQL Trace

 

SQL Server Alerts

 

Files and Filegroups

 

Partitioning

 

Full-Text Indexes

 

Read-Only Data

 

SQL Server Locks

 

Monitoring Locking and Deadlocking

 

Controlling Locks in SQL Server

 

SQL Server Policy-Based Management, Part One

 

SQL Server Policy-Based Management, Part Two

 

SQL Server Policy-Based Management, Part Three

Microsoft SQL Server Programming

For those of you who are new to programming, this section explains the Transact-SQL programming language and methods for writing SQL Server applications.

 

An Outline for Development

 

Database

 

Database Services

 

Database Objects: Databases

 

Database Objects: Tables

 

Database Objects: Table Relationships

 

Database Objects: Keys

 

Database Objects: Constraints

 

Database Objects: Data Types

 

Database Objects: Views

 

Stored Database Objects: Stored Procedures

 

Database Objects: Indexes

 

Database Objects: User Defined Functions

 

Database Objects: Triggers

 

Database Design: Requirements, Entities, and Attributes

 

Database Design: Finalizing Requirements and Defining Relationships

 

Database Objects: Entity Relationship Diagrams

 

Database Design: The Logical ERD

 

Database Design: Adjusting the Model

 

Database Design: Normalizing the Model

 

Database Design: Creating the Physical Model

 

Database Design: Changing Attributes to Columns

 

Database Design: Creating the Physical Database

 

NULLs

 

The SQL Server Sample Databases

 

The SQL Server Sample Databases - pubs

 

The SQL Server Sample Databases - Northwind

 

The SQL Server Sample Databases - Adventureworks

 

Getting Started with Transact-SQL

 

Transact-SQL: Limiting Results

 

Transact-SQL: More Operators

 

Transact-SQL: Ordering and Aggregating Data

 

Transact-SQL: Subqueries

 

Transact-SQL: Joins

 

Transact-SQL: Complex Joins - Building a View with Multiple JOINs

 

Transact-SQL: Inserts, Updates and Deletes

 

An Introduction to the CLR in SQL Server 2005

 

Design Elements Part 1: Code Format and Comments

 

Design Elements Part 2: Controlling SQLs Scope

 

Design Elements Part 3: Error Handling

 

Design Elements Part 4: Variables

 

Design Elements Part 5: Where Does the Code Live?

 

Design Elements Part 6: Math Operators and Functions

 

Design Elements Part 7: Statistical Functions

 

Design Elements Part 8: Summarization Statistical Algorithms

 

Design Elements Part 9: Representing Data with Statistical Algorithms

 

Design Elements Part 10: Interpreting the Data - Regression

 

Design Elements Part 11: String Manipulation

 

Design Elements Part 12: Loops

 

Design Elements Part 13: Recursion

 

Design Elements Part 14: Arrays

 

Design Elements Part 15: Event-Driven Programming versus Scheduled Processes

 

Design Elements Part 16: Event-Driven Programming

 

Design Elements Part 17: Program Flow

 

Forming Queries Part 1: Design

 

Forming Queries Part 2: Query Basics

 

Forming Queries Part 3: Query Optimization

 

Forming Queries Part 4: SET Options

 

Forming Queries Part 5: Table Optmizer Hints

 

Using SQL Server Templates

 

Transact-SQL Unit Testing

 

Index Tuning Wizard

 

Unicode and SQL Server

 

SQL Server Development Tools

Performance Tuning

This section leads you down a systematic process for finding and fixing issues that slow down your installation. There's a lot of guidance on the web for tuning a server, but it's often not that simple. You need to understand how to follow the path through all your landscape to find the real causes of the slowdowns. I'll show you how.

 

Performance Tuning SQL Server - Tools and Processes

 

Creating a Performance Tuning Audit - Defining Components

 

Creating a Performance Tuning Audit - Evaluation Part One

 

Creating a Performance Tuning Audit - Evaluation Part Two

 

Creating a Performance Tuning Audit - Interpretation

 

Creating a Performance Tuning Audit - Developing an Action Plan

 

Understanding SQL Server Query Plans

 

Performance Tuning - Implementing Indexes

 

Performance Monitoring Tools - Windows System Monitor

 

Performance Monitoring Tools - Logging with System Monitor

 

Performance Monitoring Tools - User Defined Counters

 

General Transact-SQL (T-SQL) Performance Tuning, Part 1

 

General Transact-SQL (T-SQL) Performance Tuning, Part 2

 

Performance Monitoring Tools - An Introduction to SQL Profiler

 

Performance Tuning: Introduction to Indexes

 

Performance Monitoring Tools - SQL Server 2000 Index Tuning Wizard

 

Performance Monitoring Tools - SQL Server 2005 Database Tuning Advisor

 

Performance Monitoring Tools - SQL Server Management Studio Reports

Practical Applications

So what do you do with this thing, anyway? There’s more to SQL Server than managing databases. In this section I’ll explain real-world applications for SQL Server, from scripting to disaster recovery.

 

Choosing the Back End

 

The DBAs Toolbox (Part 1)

 

The DBAs Toolbox (Part 2)

 

Scripting Solutions for SQL Server

 

Building a SQL Server Lab

 

Using Graphic Files with SQL Server

 

Enterprise Resource Planning

 

Customer Relationship Management (CRM)

 

Building a Reporting Data Server

 

Building a Database Documenter - Part 1

 

Building a Database Documenter - Part 2

 

Data Management Objects

 

Data Management Objects: The Server Object

 

Data Management Objects: Server Object Methods

 

Data Management Objects: Collections and the Database Object

 

Data Management Objects: Database Information

 

Data Management Objects: Database Control

 

Data Management Objects: Database Maintenance

 

Data Management Objects: Logging the process

 

Data Management Objects: Running SQL Statements

 

Data Management Objects: Multiple Row Returns from a T-SQL Script File

 

Data Management Objects: Other Database Objects

 

Data Management Objects: Security

 

Data Management Objects: Scripting

 

Powershell and SQL Server - Overview

 

Powershell and SQL Server - Objects and Providers

 

Powershell and SQL Server - A Script Framework

 

Powershell and SQL Server - Logging the Process

 

Powershell and SQL Server - Reading a Control File

 

Powershell and SQL Server - SQL Server Access

 

Powershell and SQL Server - Web Pages from a SQL Query

 

Powershell and SQL Server - Scrubbing the Event Logs

 

SQL Server 2008 PowerShell Provider

 

SQL Server I/O: Importing and Exporting Data

 

SQL Server I/O: XML in Database Terms

 

SQL Server I/O: Creating XML Output

 

SQL Server I/O: Reading XML Documents

 

SQL Server I/O: Using XML Control Mechanisms

 

SQL Server I/O: Creating Hierarchies

 

SQL Server I/O: Using HTTP with SQL Server XML

 

SQL Server I/O: Using HTTP with SQL Server XML Templates

 

SQL Server I/O: Remote Queries

 

SQL Server I/O: Working with Text Files

 

Using Microsoft SQL Server on Handheld Devices

 

Front-Ends 101: Microsoft Access

 

Comparing Two SQL Server Databases

 

English Query - Part 1

 

English Query - Part 2

 

English Query - Part 3

 

English Query - Part 4

 

English Query - Part 5

 

RSS Feeds from SQL Server

 

Using SQL Server Agent to Monitor Backups

 

Reporting Services - Creating a Maintenance Report

 

SQL Server Chargeback Strategies, Part 1

 

SQL Server Chargeback Strategies, Part 2

 

SQL Server Replication Example

 

Creating a Master Agent and Alert Server

 

The SQL Server Central Management System: Definition

 

The SQL Server Central Management System: Base Tables

 

The SQL Server Central Management System: Execution of Server Information (Part 1)

 

The SQL Server Central Management System: Execution of Server Information (Part 2)

 

The SQL Server Central Management System: Collecting Performance Metrics

 

The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts

  The SQL Server Central Management System: Reporting the Data and Project Summary

Professional Development

If you’re already familiar with the technical side of databases, you’ll soon be tapped to do more by your organization. This section helps you "round out" your technical education.

 

Becoming a DBA

 

Certification

 

DBA Levels

 

Becoming a Data Professional

 

System Sizing

 

Creating a Disaster Recovery Plan

 

Database Troubleshooting

 

Conducting an Effective Code Review

 

Developing an Exit Strategy

 

Data Retention Strategy

 

Keeping Your DBA/Developer Job in Troubled Times

 

The SQL Server Run Book

 

Creating and Maintaining a SQL Server Configuration History – Part 1

 

Creating and Maintaining a SQL Server Configuration History – Part 2

 

Creating an Application Profile, Part 1

 

Creating an Application Profile, Part 2

Business Intelligence

Business Intelligence is a huge endeavor that pays off big for the strategy in your company. In this section I’ll guide you from the concepts to the practical application of everything you need to create for your system.

 

BI Explained

 

BI Security

 

Gathering BI Requirements

 

Source System Extracts and Transforms

 

ETL Mechanisms

 

Business Intelligence Landscapes

 

Business Intelligence Layouts and the Build or Buy Decision

 

A Single Version of the Truth

 

The Operational Data Store (ODS)

 

Data Marts - Combining, Transforming Data

 

Designing Data Elements

 

The Enterprise Data Warehouse - Aggregations and the Star Schema

 

On-Line Analytical Processing (OLAP)

 

Data Mining

 

Key Performance Indicators

 

BI Presentation - Client Tools

 

BI Presentation - Portals

 

Implementing ETL - Introduction to SQL Server 2005 Integration Services

 

Building a Business Intelligence Solution, Part 1

 

Building a Business Intelligence Solution, Part 2

 

Building a Business Intelligence Solution, Part 3

Tips and Troubleshooting

Lost a password? Need some help? This section has the Q and A.

 

SQL Server and Microsoft Excel Integration

 

Tips for the SQL Server Tools - SQL Server 2000

 

Tips for the SQL Server Tools - SQL Server 2005

 

Transaction Log Troubles

 

SQL Server Connection Problems

 

Orphaned Database Users

Tools and Downloads

Free, nearly free, and not free at all tools and scripts are located here. If you have an opinion on a tool, let me know and I’ll post your comments.

 

Utilities (Free)

 

Aqua Data Studio

 

Microsoft SQL Server Best Practices Analyzer

 

Utilities (Cost)

 

Quest - TOAD for SQL Server

 

Quest - Spotlight on SQL Server

 

Microsoft - Virtual PC 2004

 

Red Gate SQL Bundle

 

Microsofts Visio for Database Folks

 

Quest Capacity Manager

 

SQL Server Help

 

Visual Studio Team Edition for Database Professionals

 

Microsoft Assessment and Planning Solution Accelerator

InformIT Articles and Sample Chapters

SQL Server is used in conjunction with dozens of other programs and requires knowledge outside of the database world. There are several other Guides here at InformIT that you can use to learn even more.

Books and eBooks

The Database section of the InformIT store is home to hundreds of books, eBooks, and DVDs covering numerous database platforms including, of course, SQL Server.

If you haven’t checked out Safari yet, you just have to. It’s got loads of electronic books that you can subscribe to – many are specific to database topics.

Online Resources

For SQL Server, there is no better reference than the Microsoft Technical Network.