Chapter 10: Transact-SQL Primer

Overview

No pocketful of chapters makes a treatise on Transact-SQL or T-SQL, the SQLese of the SQL Server 2005 DBMS. The subject is so extensive it can easily fill a 10,000-page book, and there are several such tomes available (well, not exactly 10,000 pages), not to mention the venerable SQL Server Books Online. But you have to start somewhere. This chapter is both a primer to the language and an introduction to the new T-SQL features and enhancements supported by SQL Server 2005. To test many of the examples in this chapter, I suggest installing the old favorite demo database, Northwind, from Microsoft

This chapter is a concise overview of T-SQL’s building blocks. It was tackled with newcomers to SQL Server in mind, as well as battleaxes needing either a refresher or a leg up on the new elements of the language. Also, this chapter should be considered as a “briefing” or, better, an introduction to the extensive reference to T-SQL in SQL Server Books Online and the advanced features discussed in later chapters.

No matter what API or environment you use, communication between client and server is via T-SQL. Knowing and using T-SQL is independent of the type of software you are running on the clients, be they fat, thin, thick, or rich. T-SQL is also the language you use to manage the DBMS, which is discussed in later chapters.

General office applications-line-of-business applications, report generators, SQL Server Management Studio, and so on-do not require a deep knowledge of T-SQL, because the code is usually embedded in the application. However, some applications need to support an end-user ability to query SQL Server databases, for instance, to find all accounts 30 days past due. This usually requires a user to know some menial T-SQL query syntax, but you usually allow your end user to visually construct a SQL query, which creates a statement under-the-hood and on-the-fly, so to speak.

SQL Server tools, such as the SQL Server Management Studio and the OSQL tool, require a deep knowledge of T-SQL because they accept direct T-SQL syntax for transmission to the server. The server returns results direct by to these tools, usually in the form of a result set or as tabulated data displayed in a grid or text window. As you learned in the earlier chapters SQL Server Management Studio is the essential tool for building T-SQL statements.

Application developers programming access to SQL Server data need a thorough understanding of T-SQL. They need to know as much as possible about the language, which requires comprehensive study and a lot of practice (beyond the scope of this book). This chapter covers the basics, such as syntax style, operators, and data types, Later chapters cover the more complex subject matter: stored procedure programming, triggers, user-defined functions, and so on.

T-SQL knowledge is essential in Internet applications or services. Even if you are going to latch onto XML, T-SQL is essentially still the facilitator for XML, especially when it comes to returning result set data as XML documents or inserting XML data into SQL Server databases as XML documents. T-SQL statements or queries can also be transmitted to SQL Server using URLs. However, most of the discussion in the next seven chapters relates to native use of T-SQL.