Have you experienced the flexibility of the new T-SQL features in SQL Server 2005? If not, take a look because you'll want to become familiar with four especially noteworthy development features: error handling, ROW_NUMBER Function, DDL triggers and CLR.
Error handling has always been a tricky part of T-SQL development. You once had to manually check for errors after running each statement and handle them on a case-by-case basis. Transaction management was also a chore because you had to figure out the state of your transactions and roll them back or commit them as appropriate. With SQL Server 2005, Microsoft has provided us with newer, more robust error handling capabilities by adding Try...Catch blocks to T-SQL.
Exception handling is simple really; when something unplanned occurs during the execution of your code, i.e., an exception, you need to have a routine to handle that exception. How you handle the exception is really up to you as the developer. In order to add exception handling to your T-SQL code, use Try…Catch blocks.
If you have used Visual Studio recently, you are probably familiar with the concept of Try…Catch blocks. The implementation in T-SQL is similar, but in T-SQL, it actually has fewer options than the full blown implementation you would encounter in Visual Studio. To use Try…Catch, put the T-SQL you want to run inside of a Try block. If an error with a severity of 10 or higher occurs during the execution of the Try code, execution is passed to exception handling code in a Catch block. The basic construct is shown below.
ROW_NUMBER function
SQL Server 2005 introduces us to the ROW_NUMBER function. Have you ever had the need to number the rows of a result set in a query? Every now and again, you will find that being able to number the rows is a useful thing. Previously, you had to do something tricky, like create a temp table with an identity column and then insert the resul...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ts of your SELECT into the temp table. Now, using the ROW_NUMBER function, you can have row numbers added to an additional column of your result set. In order to get row numbers, you simply include the ROW_NUMBER function as one of the columns in your SELECT statement. You must also include the OVER statement in order to tell SQL Server how you want to append the row number.
RESULTS
Row Number
employee_id
Firstname
Lastname
soc_sec
1
5623222
Tim
Jones
123-65-8745
2
5632111
Rob
Kinkad
456-69-8754
3
6365666
Jim
Miller
236-56-8989
4
7563333
Joe
Roberts
564-89-5555
This query will return all employees and a number that indicates which row each record is in. The OVER statement forces SQL Server to add the row number based on the employee_id column. In other words, the row number is generated as though data were sorted by employee_id. This is an important point, as you can still change the sort order of the SELECT. Take the following query as an example:
RESULTS
Row Number
employee_id
Firstname
Lastname
soc_sec
1
5623222
Tim
Jones
123-65-8745
3
6365666
Jim
Miller
236-56-8989
2
5632111
Rob
Kinkad
456-69-8754
4
7563333
Joe
Roberts
564-89-5555
Notice in the second result set that the data is sorted by the social security number, but the row number is still built as though the data were sorted by employee_id.
Data Definition Language (DDL) triggers
Triggers have long been a part of T-SQL features and now their usefulness has been expanded. In SQL Server 2005, Microsoft included Data Definition Language (DDL) triggers. DDL triggers, like the name implies, can be configured to fire when DDL actions take place in SQL Server. DDL statements consist of any command you would issue to modify, create or delete objects in a SQL Server database. Generally, most DDL statements begin with CREATE, ALTER or DROP. Using DDL triggers, you could choose to run a script any time a user issues a DROP TABLE statement.
A fairly common practice in older versions of SQL Server was to create schema binding views on all your tables in production to prevent accidentally dropping a production table. Since the view was schema bound, drops would fail. This required administrators to take an extra step in order to drop a table; they first had to drop a view. Now, you can accomplish the same goal using DDL triggers and the solution is less of a workaround. You can set up a DDL trigger first on ALTER or DROP TABLE statements, which prevents the statement from completing. This, again, adds the extra layer of protection, since the trigger needs to be dropped or disabled before changes are made.
DDL triggers also have some very obvious auditing uses. Triggers can be used to log the details any time a table, stored procedure or view is added, modified or dropped. No more guesswork around who added a new object. Really, the sky is the limit when it comes to things you can do with DDL triggers.
Common Language Runtime integration
SQL Server 2005's new T-SQL features are complete with the capability to write and use Common Language runtime (CLR) modules that can be integrated into your database. CLR integrations allow it to write triggers, stored procedures, functions, aggregate functions and types in a .NET language. Before you go crazy and start rewriting everything in CLR, be warned: Take some time to carefully consider which objects should use CLR. Certain tasks lend themselves well to CLR modules and others don't. If you need to interact with the OS, Common Language Runtime is probably the way to go. Additionally, complex algorithms for string parsing or sorting will likely perform better if done in CLR. However, standard T-SQL statements, such as INSERT, UPDATE and DELETE, are better left in T-SQL. Just use common sense and performance test anything you write.
The complexities of writing CLR are outside the scope of this tip, but I will give you the ten thousand foot view. You need to use Visual Studio to write stored procedures in the .NET language of your choice. Once you write it, you attach the code as an assembly and then create the stored procedure, function, trigger or type – and then point it at the assembly. Beyond that, it can be called or used just as traditional T-SQL objects would be used. Get an overview of the differences between CLR routines vs. T-SQL stored procedures in our expert answer section.
There have been a good number of changes made to SQL Server 2005, and not just in the area of T-SQL. We certainly haven't covered everything in this short tip, but these have been some of the most hyped new additions. Take some time and explore these new features -- and you had better hurry. The next version of SQL Server is just around the corner and I am sure it to will be packed with more new toys to play with.
ABOUT THE AUTHOR:
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is a co-founder of Consortio Services and the primary Database Technologies Consultant. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. He has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. Eric is active in the local SQL Server Community, serving as the president of the Colorado Springs SQL Server Users Group.
Copyright 2007 TechTarget
