SQL Server’s query functionality can be extended by using T-SQL. It can encapsulate common tasks and make it easier to maintain and perform them. It in effect allows you to write SQL batches that perform common tasks and store those batches directly in SQL Server for later reuse.
Much of the functionality associated with SQL Server does not come from the SQL programming language; it comes from extensions that Microsoft has added to SQL Server using T-SQL, its proprietary language for writing imperative code.
There is, for example, a Microsoft-written stored procedure to add a new user to a database called sp_adduser. This stored procedure is deprecated in SQL Server 2005 but is still supported so that existing scripts from previous versions of SQL Server can be used on SQL Server 2005. It has been replaced by the CREATE USER expression. Both of these work. This stored procedure inserts the parameters you pass inlogin name and usernameinto appropriate database tables. If you use sp_adduser to add a user to the database you do not need to know the details of what happens inside the database. In fact, Microsoft could completely change how SQL Server maintains users in a database, and it would not affect the way you add users to a database.
Prior to SQL Server 2005, the only ways to extend SQL Server were to use T-SQL, or write an extended stored procedure or a COM component. T-SQL required you to know the T-SQL language. For many, this meant learning an alternative programming language that they used much less than their primary language. For a Visual Basic 2005 programmer, this might have meant stumbling through something like “Dim id. Whoops, no; Declare id. Whoops, no; Declare @id int.” Similar relearn-by-syntax-error journeys await programmers from other languages whenever they attempt to write a T-SQLbased stored procedure.
Extended stored procedures require a rather tedious DLL to be created. C++ programmers, however, can use a wizard in Visual Studio to create this DLL and just fill in the functionality they choose. Likewise, Visual Basic 6 programmers can create a COM component and use it in SQL Server through the sp_OACreate stored procedure. This allows C++ or Visual Basic 6 programmers to use a familiar programming environment to extend SQL Server. Extended stored procedures and COM components have capabilities that T-SQL does not, because they can access system services that are outside SQL Server. The extension to SQL Server that allows it to send e-mail, for example, is an extended stored procedure. It could not have been written in T-SQL.
Extended stored procedures have their own issues. Although it is possible to write extended stored procedures that are secure and reliable, the languages used to create them make this very difficult to do. In general, an extended stored procedure or a COM component must stand a much higher level of scrutiny than a T-SQLbased stored procedure and in some cases cannot match the performance of T-SQL.
SQL Server 2005 changes all this. Any CLR language can extend SQL Server. The CLR is part of the .NET Framework. Extensions running in the CLR can be as safe and reliable as T-SQL and as flexible as an extended stored procedure or a COM component. This means that nonT-SQL developers can use a familiar development environment to extend the functionality of SQL Server.
In addition, there are some tasks for which the CLR is just better suited. Typically, the CLR is a better choice for operations that involve numeric computations or string manipulation.
If you hear anyone say, “Now that SQL Server 2005 uses the CLR, every Visual Basic and C# programmer is a database programmer!”, run away quickly. The CLR is not better suited for doing set operations; SQL is the clear winner here. However, the CLR can execute SQL expressions, just as T-SQL can, and with about the same efficiency. Being able to write code in a CLR language will not be a substitute for knowing how to write SELECT DISTINCT A.au_fname, A.au_lname FROM authors A JOIN titleauthors T ON A.au_id = T.au_id when you need to find all the authors who have publications.
