Transact-Structured Query Language

T-SQL is Microsoft's implementation of a standard established by the American National Standards Institute (ANSI) for the Structured Query Language (SQL). SQL was first developed by researchers at IBM. They called their first pre-release version of SQL "SEQUEL," which stood for Structured English QUEry Language. The first release version was renamed to SQL, dropping the English part but retaining the pronunciation to identify it with its predecessor. Today, several implementations of SQL by different stakeholders are in the database marketplace, and as you sojourn through the sometimes-mystifying lands of database technology you will undoubtedly encounter these different varieties of SQL. What makes them all similar is the ANSI standard to which IBM, more than any other vendor, adheres to with tenacious rigidity. However, what differentiate the many implementations of SQL are the customized programming objects and extensions to the language that make it unique to that particular platform. Microsoft SQL Server 2000 implements ANSI-92, or the 1992 standard as set by ANSI. SQL Server 2005 implements ANSI-99. The term "implements" is of significance. T-SQL is not fully compliant with ANSI standards in its 2000 or 2005 implementation; neither is Oracle's P/L SQL, Sybase's SQLAnywhere, or the open-source MySQL. Each implementation has custom extensions and variations that deviate from the established standard. ANSI has three levels of compliance: Entry, Intermediate, and Full. T-SQL is certified at the entry level of ANSI compliance. If you strictly adhere to the features that are ANSI-compliant, the same code you write for Microsoft SQL Server should work on any ANSI-compliant platform; that's the theory, anyway. If you find that you are writing cross-platform queries, you will most certainly need to take extra care to ensure that the syntax is perfectly suited for all the platforms it affects. Really, the simple reality of this issue is that very few people will need to write queries to work on multiple database platforms. These standards serve as a guideline to help keep query languages focused on working with data, rather than other forms of programming, perhaps slowing the evolution of relational databases just enough to keep us sane.

T-SQL: Programming Language or Query Language?

T-SQL was not really developed to be a full-fledged programming language. Over the years the ANSI standard has been expanded to incorporate more and more procedural language elements, but it still lacks the power and flexibility of a true programming language. Antoine, a talented programmer and friend of mine, refers to SQL as "Visual Basic on Quaaludes." I share this bit of information not because I agree with it, but because I think it is funny. I also think it is indicative of many application developers' view of this versatile language.

The Structured Query Language was designed with the exclusive purpose of data retrieval and data manipulation. Microsoft's T-SQL implementation of SQL was specifically designed for use in Microsoft's Relational Database Management System (RDBMS), SQL Server. Although T-SQL, like its ANSI sibling, can be used for many programming-like operations, its effectiveness at these tasks varies from excellent to abysmal. That being said, I am still more than happy to call T-SQL a programming language if only to avoid someone calling me a SQL "Queryer" instead of a SQL Programmer. However, the undeniable fact still remains; as a programming language, T-SQL falls short. The good news is that as a data retrieval and set manipulation language it is exceptional. When T-SQL programmers try to use T-SQL like a programming language they invariably run afoul of the best practices that ensure the efficient processing and execution of the code. Because T-SQL is at its best when manipulating sets of data, try to keep that fact foremost in your thoughts during the process of developing T-SQL code.

Performing multiple recursive row operations or complex mathematical computations is quite possible with T-SQL, but so is writing a .NET application with Notepad. Antoine was fond of responding to these discussions with, "Yes, you can do that. You can also crawl around the Pentagon on your hands and knees if you want to." His sentiments were the same as my father's when I was growing up; he used to make a point of telling me that "Just because you can do something doesn't mean you should." The point here is that oftentimes SQL programmers will resort to creating custom objects in their code that are inefficient as far as memory and CPU consumption are concerned. They do this because it is the easiest and quickest way to finish the code. I agree that there are times when a quick solution is the best, but future performance must always be taken into account. This book tries to show you the best way to write T-SQL so that you can avoid writing code that will bring your server to its knees, begging for mercy.

What's New in SQL Server 2005

Several books and hundreds of web sites have already been published that are devoted to the topic of "What's New in SQL Server 2005," so I won't spend a great deal of time describing all the changes that come with this new release. Instead, throughout the book I will identify those changes that are applicable to the subject being described. However, in this introductory chapter I want to spend a little time discussing one of the most significant changes and how it will impact the SQL programmer. This change is the incorporation of the .NET Framework with SQL Server.

T-SQL and the .NET Framework

The integration of SQL Server with Microsoft's .NET Framework is an awesome leap forward in database programming possibilities. It is also a significant source of misunderstanding and trepidation, especially by traditional infrastructure database administrators.

This new feature, among other things, allows developers to use programming languages to write stored procedures and functions that access and manipulate data with object-oriented code, rather than SQL statements.

Kiss T-SQL Goodbye?

Any reports of T-SQL's demise are premature and highly exaggerated. The ability to create database programming objects in managed code instead of SQL does not mean that T-SQL is in danger of becoming extinct. A marketing-minded executive at one of Microsoft's partner companies came up with a cool tagline about SQL Server 2005 and the .NET Framework that said "SQL Server 2005 and .NET; Kiss SQL Good-bye." He was quickly dissuaded by his team when presented with the facts. However, the executive wasn't completely wrong. What his catchy tagline could say and be accurate is "SQL Server 2005 and .NET; Kiss SQL Cursors Good-bye." It could also have said the same thing about complex T-SQL aggregations or a number of T-SQL solutions presently used that will quickly become obsolete with the release of SQL Server 2005.

Transact-SQL cursors are covered in detail in Chapter 10, so for the time being, suffice it to say that they are generally a bad thing and should be avoided. Cursors are all about recursive operations with single or row values. They consume a disproportionate amount of memory and CPU resources compared to set operations.

With the integration of the .NET Framework and SQL Server, expensive cursor operations can be replaced by efficient, compiled assemblies, but that is just the beginning. A whole book could be written about the possibilities created with SQL Server's direct access to the .NET Framework. Complex data types, custom aggregations, powerful functions, and even managed code triggers can be added to a database to exponentially increase the flexibility and power of the database application. Among other things, one of the chief advantages of the .NET Framework's integration is the ability of T-SQL developers to have complete access to the entire .NET object model and operating system application programming interface (API) library without the use of custom extended stored procedures. Extended stored procedures and especially custom extended stored procedures, which are almost always implemented through unmanaged code, have typically been the source of a majority of the security and reliability issues involving SQL Server. By replacing extended stored procedures, which can only exist at the server level, with managed assemblies that exist at the database level, all kinds of security and scalability issues virtually disappear.

Database Management System (DBMS)

A DBMS is a set of programs that are designed to store and maintain data. The role of the DBMS is to manage the data so that the consistency and integrity of the data is maintained above all else. Quite a few types and implementations of Database Management Systems exist:

• Hierarchical Database Management Systems (HDBMS) — Hierarchical databases have been around for a long time and are perhaps the oldest of all databases. It was (and in some cases still is) used to manage hierarchical data. It has several limitations such as only being able to manage single trees of hierarchical data and the inability to efficiently prevent erroneous and duplicate data. HDBMS implementations are getting increasingly rare and are constrained to specialized, and typically, non-commercial applications.

• Network Database Management System (NDBMS) — The NDBMS has been largely abandoned. In the past, large organizational database systems were implemented as network or hierarchical systems. The network systems did not suffer from the data inconsistencies of the hierarchical model but they did suffer from a very complex and rigid structure that made changes to the database or its hosted applications very difficult.

• Relational Database Management System (RDBMS) — An RDBMS is a software application used to store data in multiple related tables using SQL as the tool for creating, managing, and modifying both the data and the data structures. An RDBMS maintains data by storing it in tables that represent single entities and storing information about the relationship of these tables to each other in yet more tables. The concept of a relational database was first described by E.F. Codd, an IBM scientist who defined the relational model in 1970. Relational databases are optimized for recording transactions and the resultant transactional data. Most commercial software applications use an RDBMS as their data store. Because SQL was designed specifically for use with an RDBMS, I will spend a little extra time covering the basic structures of an RDBMS later in this chapter.

• Object-Oriented Database Management System (ODBMS) — The ODBMS emerged a few years ago as a system where data was stored as objects in a database. ODBMS supports multiple classes of objects and inheritance of classes along with other aspects of object orientation. Currently, no international standard exists that specifies exactly what an ODBMS is and what it isn't. Because ODBMS applications store objects instead of related entities, it makes the system very efficient when dealing with complex data objects and object-oriented programming (OOP) languages such as the new .NET languages from Microsoft as well as C and Java. When ODBMS solutions were first released they were quickly touted as the ultimate database system and predicted to make all other database systems obsolete. However, they never achieved the wide acceptance that was predicted. They do have a very valid position in the database market, but it is a niche market held mostly within the Computer-Aided Design (CAD) and telecommunications industries.

• Object-Relational Database Management System (ORDBMS) — The ORDBMS emerged from existing RDBMS solutions when the vendors who produced the relational systems realized that the ability to store objects was becoming more important. They incorporated mechanisms to be able to store classes and objects in the relational model. ORDBMS implementations have, for the most part, usurped the market that the ODBMS vendors were targeting for a variety of reasons that I won't expound on here. However, Microsoft's SQL Server 2005, with its XML data type and incorporation of the .NET Framework, could arguably be labeled an ORDBMS.