Summary

By now you should have a good understanding about what the Transact-SQL language is used for and how it is implemented with Microsoft SQL Server. You learned that Transact-SQL is a dialect of the Structured Query Language, based on the industry-wide ANSI SQL standard.

Three categories of statements within SQL are used to define and manage a database and the objects contained therein, to control access to data and database functionality, and to manage the data in a database. Data Definition Language (DDL) encompasses the CREATE and ALTER statements, used to define database objects. Data Control Language (DCL) is used to manage security access and user privileges to data and database objects. Finally, Data Manipulation Language (DML) is the subset of SQL you will typically use most often. DML contains the SELECT, INSERT, UPDATE, and DELETE statements and several variations of these statements that you will use to populate tables with records, modify, remove, and read data values. The SELECT statement has several modifiers and additional commands and clauses you will use to do useful things with, and make sense of, the data stored in a database.

The SQL Server database engine uses intelligent logic to process queries as efficiently as possible. The query parser and optimizer translate a SQL query into distinct operations, which are then compiled into low-level machine instructions. This compiled execution plan is cached in memory and can be stored permanently within the database with database programming objects to run more efficiently.

You also learned about the proper way to write SQL script, using comments and naming standards. Script can be saved in script files for safekeeping and templates can be used to save time and effort when writing new queries.

This chapter, along with the first three chapters, is the foundation upon which more specific topics are based. As you move forward, you will be using the scripting techniques discussed here and the tools you learned to use in Chapter 2. The rest of the book focuses on specific types of queries and objects.