Microsoft SQL Server 2008 Bible

At the Rocky Mountain Tech Tri-Fecta 2009 SQL keynote, I walked through the major SQL Server 2008 new features and asked the question, "Cool or Kool-Aid?"

I've worked with SQL Server since version 6.5 and I'm excited about this newest iteration because it reflects a natural evolution and maturing of the product. I believe it's the best release of SQL Server so far. There's no Kool-Aid here it's all way cool.

SQL Server is a vast product and I don't know any sane person who claims to know all of it in depth. In fact, SQL Server is used by so many different types of professions to accomplish so many different types of tasks, it can be difficult to concisely define it, but here goes:

SQL Server 2008: Microsoft's enterprise client-server relational database product, with T-SQL as its primary programming language.

However, SQL Server is more than ust a relational database engine:

• Connecting to SQL Server is made easy with a host of data connectivity options and a variety of technologies to import and export data, such as Tabular Data Stream (TDS), XML, Integration Services, bulk copy, SQL Native Connectivity, OLE DB, ODBC, and distributed query with Distributed Transaction Coordinator, to name a few.
• The engine works well with data (XML, spatial, words within text, and blob data).
• SQL Server has a full suite of OLAP/BI components and tools to work with multidimensional data, analyze data, build cubes, and mine data.
• SQL Server includes a complete reporting solution that serves up great-looking reports, enables users to create reports, and tracks who saw what when.
• SQL Server exposes an impressive level of diagnostic detail with Performance Studio, SQL Trace/Profiler, and Database Management Views and Functions.
• SQL Server includes several options for high availability with varying degrees of latency, performance, number of nines, physical distance, and synchronization.
• SQL Server can be managed declaratively using Policy-Based Management.
• SQL Server's Management Studio is a mature UI for both the database developer and the DBA.
• SQL Server is available in several different scalable editions in 32-bit and 64-bit for scaling up and out.

All of these components are included with SQL Server (at no additional cost or per-component cost), and together in concert, you can use them to build a data solution within a data architecture environment that was difficult or impossible a few years ago. SQL Server 2008 truly is an enterprise database for today.

A Great Choice

There are other good database engines, but SQL Server is a great choice for several reasons. I'll leave the marketing hype for Microsoft; here are my personal ten reasons for choosing SQL Server for my career:

• Set-based SQL purity: As a set-based data architect type of guy, I find SQL Server fun to work with. It's designed to function in a set-based manner. Great SQL Server code has little reason to include iterative cursors. SQL Server is pure set-based SQL.
• Scalable performance: SQL Server performance scales well — I've developed code on my notebook that runs great on a server with 32-and 64-bit dual-core CPUs and 48GB of RAM. I've yet to find a database application that doesn't run well with SQL Server given a good design and the right hardware.
  People sometimes write to the newsgroups that their database is huge — "over a gig!" —but SQL Server regularly runs databases in the terabyte size. I'd say that over a petabyte is huge, over a terabyte is large, 100 GB is normal, under 10 GB is small, and under 1 GB is tiny.
• Scalable experience: The SQL Server experience scales from nearly automated self-managed databases administered by the accidental DBA to finite control that enables expert DBAs to tune to their heart's content.
• Industry acceptance: SQL Server is a standard. I can find consulting work from small shops to the largest enterprises running SQL Server.
• Diverse technologies: SQL Server is broad enough to handle many types of problems and applications. From BI to spatial, to heavy transactional OLTP, to XML, SQL Server has a technology to address the problem.
• SQL in the Cloud: There are a number of options to host a SQL database in the cloud with great stability, availability, and performance.
• Financial stability: It's going to be here for a nice long time. When you choose SQL Server, you're not risking that your database vendor will be gone next year.
• Ongoing development: I know that Microsoft is investing heavily in the future of SQL Server, and new versions will keep up the pace of new cool features. I can promise you that SQL 11 will rock!
• Fun community: There's an active culture around SQL Server, including a lot of user groups, books, blogs, websites, code camps, conferences, and so on. Last year I presented 22 sessions at nine conferences, so it's easy to find answers and get plugged in. In fact, I recently read a blog comparing SQL Server and Oracle and the key differentiator is enthusiasm of the community and the copious amount of information it publishes. It's true: the SQL community is a fun place to be.
• Affordable: SQL Server is more affordable than the other enterprise database options, and the Developer Edition costs less than $50 on Amazon. Tclient/server

The Client/Server Database Model

Technically, the term refers to any two cooperating processes. The client process requests a service from the server process, which in turn handles the request for the client. The client process and the server process may be on different computers or on the same computer: It's the cooperation between the processes that is significant, not the physical location.

For a client/server database, the client application be it a front end, an ETL process, a middle tier, or a report) prepares a SQL request — ust a small text message or remote procedure call (RPC) — and sends it to the database server, which in turn reads and processes the request. Inside the server, the security is checked, the indexes are searched, the data is retrieved or manipulated, any server-side code is executed, and the final results are sent back to the client. All the database work is performed within the database server. The actual data and indexes never leave the server.

In contrast, desktop file-based databases (such as Microsoft Access), may share a common file, but the desktop application does all the work as the data file is shared across the network.

The client/server database model offers several benefits over the desktop database model:

• Reliability is improved because the data is not spread across the network and several applications. Only one process handles the data.
• Data integrity constraints and business rules can be enforced at the server level, resulting in a more thorough implementation of the rules.
• Security is improved because the database keeps the data within a single server. Hacking into a data file that's protected within the database server is much more difficult than hacking into a data file on a workstation. It's also harder to steal a physical storage device connected to a server, as most server rooms are adequately protected against intruders.
• Performance is improved and better balanced among workstations because the majority of the workload, the database processing, is being handled by the server; the workstations handle only the user-interface portion.
• Because the database server process has direct access to the data files, and much of the data is already cached in memory, database operations are much faster at the server than in a multi-user desktop-database environment. A database server is serving every user operating a database application; therefore, it's easier to ustify the cost of a beefier server. For applications that require database access and heavy computational work, the computational work can be handled by the application, further balancing the load.
• Network traffic is greatly reduced. Compared to a desktop database's rush-hour traffic, client/server traffic is like a single motorcyclist carrying a slip of paper with all 10 lanes to himself. This is no exaggeration! Upgrading a heavily used desktop database to a well-designed client/server database will reduce database-related network traffic by more than 95 percent.
• A by-product of reducing network traffic is that well-designed client/server applications perform well in a distributed environment — even when using slower communications. So little traffic is required that even a 56KB dial-up line should be indistinguishable from a 100baseT Ethernet connection for a .NET-rich client application connected to a SQL Server database.

Client/server SQL Server: a Boeing 777. Desktop databases: a toy red wagon.

SQL Server Database Engine

SQL Server components can be divided into two broad categories: those within the engine, and external tools (e.g., user interfaces and components), as illustrated in Figure 1-1. Because the relational Database Engine is the core of SQL Server, I'll start there.

Database Engine

The SQL Server Database Engine, sometimes called the Relational Engine, is the core of SQL Server. It is the component that handles all the relational database work. SQL is a descriptive language, meaning it describes only the question to the engine; the engine takes over from there.

Within the Relational Engine are several key processes and components, including the following:

• Algebrizer: Checks the syntax and transforms a query to an internal representation that is used by the following components.
• Query Optimizer: SQL Server's Query Optimizer determines how to best process the query based on the costs of different types of query-execution operations. The estimated and actual query-execution plans may be viewed graphically, or in XML, using Management Studio or SQL Profiler.
• Query Engine, or Query Processor: Executes the queries according to the plan generated by the Query Optimizer.
• Storage Engine: Works for the Query Engine and handles the actual reading from and writing to the disk.
• The Buffer Manager: Analyzes the data pages being used and pre-fetches data from the data file(s) into memory, thus reducing the dependency on disk I/O performance.
• Checkpoint: Process that writes dirty data pages (modified pages) from memory to the data file.
• Resource Monitor: Optimizes the query plan cache by responding to memory pressure and intelligently removing older query plans from the cache.
• Lock Manager: Dynamically manages the scope of locks to balance the number of required locks with the size of the lock.
• SQLOS: SQL Server eats resources for lunch, and for this reason it needs direct control of the available resources (memory, threads, I/O request, etc.). Simply leaving the resource management to Windows isn't sophisticated enough for SQL Server. SQL Server includes its own OS layer, SQLOS, which manages all of its internal resources.

SQL Server 2008 supports installation of up to 16 (Workgroup Edition) or 50 (Standard or Enterprise Edition) instances of the Relational Engine on a physical server. Although they share some components, each instance functions as a complete separate installation of SQL Server.

ACID and SQL Server's Transaction Log

QL Server's Transaction Log is more than an optional appendix to the engine. It's integral to SQL Server's reputation for data integrity and robustness. Here's why:

Data integrity is defined by the acronym ACID, meaning transactions must be Atomic (one action — all or nothing), Consistent (the database must begin and end the transaction in a consistent state), Isolated transaction should affect another transaction), and Durable once committed, always committed).

The transaction log is vital to the ACID capabilities of SQL Server. SQL Server writes to the transaction log as the first step of writing any change to the data pages (in memory), which is why it is sometimes called the write-ahead transaction log.

Every DML statement Select, Insert, Update, Delete) is a complete transaction, and the transaction log. ensures that the entire set-based operation takes place, thereby ensuring the atomicity of the transaction.

SQL Server can use the transaction log to roll back, or complete a transaction regardless of hardware failure, which is key to both the consistency and durability of the transaction.

CROSS REF: Chapter 40, "Policy-Based Management," goes into more detail about transactions.

Transact-SQL

SQL Server is based on the SQL standard, with some Microsoft-specific extensions. SQL was invented by E. F. Codd while he was working at the IBM research labs in San Jose in 1971. SQL Server is entry-level (Level 1) compliant with the ANSI SQL 92 standard. (The complete specifications for the ANSI SQL standard are found in five documents that can be purchased from www.techstreet.com/ncits.html. I doubt if anyone who doesn't know exactly what to look for will find these documents.) But it also includes many features defined in later versions of the standard (SQL-1999, SQL-2003).

While the ANSI SQL definition is excellent for the common data-selection and data-definition commands, it does not include commands for controlling SQL Server properties, or provide the level of logical control within batches required to develop a SQL Server—specific application. Therefore, the Microsoft SQL Server team has extended the ANSI definition with several enhancements and new commands, and has left out a few commands because SQL Server implemented them differently. The result is Transact-SQL, or T-SQL — the dialect of SQL understood by SQL Server.

Missing from T-SQL are very few ANSI SQL commands, primarily because Microsoft implemented the functionality in other ways. T-SQL, by default, also handles nulls, quotes, and padding differently than the ANSI standard, although that behavior can be modified. Based on my own development experience, I can say that none of these differences affect the process of developing a database application using SQL Server. T-SQL adds significantly more to ANSI SQL than it lacks.

Understanding SQL Server requires understanding T-SQL. The native language of the SQL Server engine is Transact-SQL. Every command sent to SQL Server must be a valid T-SQL command. Batches of stored T-SQL commands may be executed within the server as stored procedures. Other tools, such as Management Studio, which provide graphical user interfaces with which to control SQL Server, are at some level converting most of those mouse clicks to T-SQL for processing by the engine.

SQL and T-SQL commands are divided into the following three categories:

• Data Manipulation Language (DML): Includes the common SQL SELECT, INSERT, UPDATE,and DELETE commands. DML is sometimes mistakenly referred to as Data Modification Language; this is misleading, because the SELECT statement does not modify data. It does, however, manipulate the data returned.
• Data Definition Language (DDL): Commands that CREATE, ALTER,or DROP data tables, constraints, indexes, and other database objects.
• Data Control Language (DCL): Security commands such as GRANT, REVOKE,and DENY that control how a principal (user or role) can access a securable (object or data.)