Introduction

From the very first Hitchhiker's Guide, I assumed that my readers understood SQL Server as well as the route they take to work every morning. They knew every shortcut and back alley to beat the traffic and how to bypass that speed-trap over by the lake. Based on questions from the newsgroups and those attending my conference sessions, it's clear that some of you would like to know, or need to know, a lot more about SQL Server's inner workings. In order to get the best performance out of any engine, you really should know what makes it tick and purr. No, I don't plan to get into the intimate details of the query engine and exactly how it chooses one index over another or how the file system writes data to the drive, but I do plan to provide you with the knowledge you need to get the most out of the query engine and optimize what gets written to disk.

Since SQL Server has captured about 50% of the market, a number of developers (perhaps you) are in the process of migrating from Access/JET, Foxpro, MySQL, or one of the other database management systems that you've outgrown. You might also be transitioning from one of the "enterprise"-class DBMSs, like Oracle, DB2, or Informix, or simply want to improve your SQL Server skillset. On the other hand, you might simply be ready to migrate from SQL Server 2000 or, if you work for the government, perhaps SQL Server Version 7.0 or earlier. This chapter outlines how to choose an appropriate version of SQL Server, how the server handles connections and security, how (in some detail) SQL Server processes the queries you submit, and how the data is returned. The information I provide here should help prevent you from being surprised at SQL Server's behavior, performance, or lack thereof as your application puts it through its paces. I can't hope to duplicate SQL Server's voluminous documentationI don't plan to try. I do hope that the points I raise make you aware of the base features afforded by SQL Serverespecially the new features exposed by SQL Server 2005.