T - SQL Language - T-SQL

I have mentioned to my colleagues and anyone else who might have been listening that one day I was going to write a version of Parker Brother's Trivial Pursuit entitled "Trivial Pursuit: Geek Edition.” This section gives you some background on the T-SQL language and provides the information you need to get the orange history wedge on the topic of "Database History” in Trivial Pursuit: Geek Edition.
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 is a pseudo-acronym 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. As of the release of SQL Server 2008, several implementations of SQL by different stakeholders are in the database marketplace. 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 makes the many implementations of SQL different are the customized programming objects and extensions to the language that make it unique to that particular platform.
Microsoft SQL Server 2008 implements the 2003 ANSI standard. The term implements” is of significance. T-SQL is not fully compliant with ANSI standards in any of its implementations; neither is Oracle's P/L SQL, Sybase's SQL Anywhere, 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. The simple reality of this issue is that very few people will need to write queries to work on multiple database platforms. The standards serve as a guideline to help keep query languages focused on working with data, rather than other forms of programming. This may slow the evolution of relational databases just enough to keep us sane.

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.

T-SQL was designed with the exclusive purpose of data retrieval and data manipulation. 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 "queryers.” 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.

With the release of SQL Server 2005, Microsoft muddied the waters a bit with the ability to write calls to the database in a programming language like C# or VB.NET, rather than in pure SQL. SQL Server 2008 also supports this very flexible capability, but use caution! Although this is a very exciting innovation in data access, the truth of the matter is that almost all calls to the database engine must still be manipulated so that they appear to be T-SQL based.

Performing multiple recursive row operations or complex mathematical computations is quite possible with T-SQL, but so is writing a .NET application with Notepad. When I was growing up my father 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.

One of the systems I am currently working on is a perfect example of this problem. The database started out very small, with a small development team and a small number of customers using the database. It worked great. However, the database didn't stay small, and as more and more customers started using the system, the number of transactions and code executions increased exponentially. It wasn't long before inefficient code began to consume all the available CPU resources. This is the trap of writing expedient code instead of efficient code. Another of my father's favorite sayings is "Why is there never enough time to do the job right, but plenty of time to do it twice?” 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. Don't give in to the temptation to write sloppy code just because it is a "one time deal.” I have seen far too many times when that one-off ad-hoc query became a central piece of an application's business logic.

What’s New in SQL Server 2008

When SQL Server 2005 was released, it had been five years since the previous release and the changes to the product since the release of SQL Server 2000 were myriad and significant. Several books and hundreds of websites were published that were devoted to the topic of "What's New in SQL Server 2005.” With the release of SQL Server 2008, however, there is much less buzz and not such a dramatic change to the platform. However, the changes in the 2008 release are still very exciting and introduce many changes that T-SQL and application developers have been clamoring for. Since these changes are sprinkled throughout the capabilities of SQL Server, I won't spend a great deal of time describing all the changes here. Instead, throughout the book I will identify those changes that are applicable to the subject being described. In this introductory chapter I want to quickly mention two of the significant changes to SQL that will invariably have an impact on the SQL programmer: the incorporation of the .NET Framework with SQL Server and the introduction of Microsoft Language Integrated Query (LINQ).

Kiss T-SQL Goodbye?

I have been hearing for years that T-SQL and its ANSI counterpart, SQL, were antiquated languages and would soon be phased out. However, every database vendor, both small and large, has devoted millions of dollars to improving their version of this versatile language. Why would they do that if it were a dead language? The simple fact of the matter is that databases are built and optimized for the set-based operations that the SQL language offers. Is there a better way to access and manipulate data? Probably so, but with every major industry storing their data in relational databases, the reign of SQL is far from over.

I worked for a great guy at a Microsoft partner company who was contracted by Microsoft to develop and deliver a number of SQL Server and Visual Studio evangelism presentations. Having a background in radio sales and marketing, he came up with a cool tagline about SQL Server and the .NET Framework that said "SQL Server and .NET — Kiss T-SQL Goodbye.” He was quickly dissuaded by his team when presented with the facts. However, Todd wasn't completely wrong. What his catchy tagline could have said and been accurate was "SQL Server and .NET — Kiss Inefficient, CPU-Hogging T-SQL Code Goodbye. ”

Two significant improvements in data access over the last two releases of SQL Server have offered fuel for the "SQL is dead” fire. As I mentioned briefly before, these are the incorporation of the .NET Framework and the development of LINQ. LINQ is Microsoft's latest application data-access technology. It enables Visual Basic and C# applications to use set-oriented queries that are developed in C# or VB, rather than requiring that the queries be written in T-SQL. Building in the .NET Framework to the SQL Server engine enables developers to create SQL Server programming objects such as stored procedures, functions, and aggregates using any .NET language and compiling them into Common Language Runtime (CLR) assemblies that can be referenced directly by the database engine.

So with the introduction of LINQ in SQL Server 2008 and CLR integration in SQL Server 2005, is T-SQL on its death bed? No, not really. 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. Likewise, the ability to create set-oriented queries in C# and VB does not sound the death knell for T-SQL. SQL Server's native language is still T-SQL. LINQ will help in the rapid development of database applications, but it remains to be seen if this technology will match the performance of native T-SQL code run from the server. This is because LINQ data access still must be translated from the application layer to the database layer, but T-SQL does not. It's a fantastic and flexible access layer for smaller database applications, but for large, enterprise-class applications, LINQ, like embedded SQL code in applications before it, falls short of pure T-SQL in terms of performance.

What was true then is true now. T-SQL will continue to be the core language for applications that need to add, extract, and manipulate data stored on SQL Server. Until the data engine is completely re-engineered (and that day will inevitably come), T-SQL will be at the heart of SQL Server.

Database Management Systems

A database management system (DBMS) is a set of programs 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. They were (and in some cases still are) used to manage hierarchical data. They have several limitations, such as being able to manage only single trees of hierarchical data and the inability to efficiently prevent erroneous or 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, such as "Customer” and "Sale” and storing information about the relationship of these tables to each other in yet more tables managed by the system which define the relationship between the Sale table and the Customer table. 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, they make the system very efficient when dealing with complex data objects and object-oriented programming (OOP) languages such as the .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, with its xml data type, the incorporation of the .NET Framework, and the new file stream data type introduced with SQL Server 2008, could arguably be labeled an ORDBMS. The file stream data type is discussed in more detail later in this chapter and in Appendix E.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics