Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Learn SQL Server 2005 T-SQL Enhancements : Page 3

Microsoft has introduced a number of new features and enhancements in the T-SQL language in SQL Server 2005.


advertisement
DDL Triggers
DML (data manipulation language) triggers have always been an effective way to take action when changes are made to your data. DML permits you to take a number of actions such as updating an audit log of data updates. Triggers, though, are useless when it comes to tracking and auditing changes made to your database schema. For example, suppose one member of your development team adds a new field while another team member changes the size of one field from VARCHAR(20) to VARCHAR(30). In SQL Server 2000 there is no way to track these types of schema updates without the use of a third-party tool. SQL Server 2005 allows you to track such updates with the addition of DDL (data definition language) triggers. As an added bonus, the syntax for DDL triggers is very similar to the syntax for DML triggers. In Listing 16, a CREATE TRIGGER command creates a DDL trigger to prevent a table from being dropped. The event type (FOR DROP_TABLE) determines which database event this trigger is going to be associated with. To ensure the trigger runs on any DDL change, use DDL_DATABASE_LEVEL_EVENTS.

The EVENTDATA Function

In SQL Server 2000, there is no way to track these types of schema updates without the use of a third-party tool. This changes in SQL Server 2005 with the addition of DDL (data definition language) triggers.
DML triggers use the Inserted and/or Deleted virtual tables to determine the before and after values of an updated record. A DDL trigger uses the EVENTDATA() function to return information about the database event that caused the trigger to fire. EVENTDATA() returns an XML value and based on the type of event, the XML contains different data. There are four pieces of information that are contained in the XML regardless of the DDL command that fired the trigger.

  • The event type
  • The time of the event
  • The SPID of the connection
  • Login and user name information about the individual executing the DDL command
SQL Server Books Online contains more information regarding the data returned by EVENTDATA(). In Listing 17, a CREATE TABLE command creates the AuditLog table and then a CREATE TRIGGER command creates a trigger that adds a record to the AuditLog table whenever any database level event occurs.


Common Table Expressions (CTE)
A Common Table Expression (CTE) is a temporary result set created from a simple query. They are implemented by an expression that creates a table that can be referred to by name. If a CTE sounds like a view or possibly a derived table then you are on the right page. The syntax of a CTE is similar to a view. A SELECT statement based on data contained in the PurchaseOrderDetail table creates the CTE named PurchaseOrderCTE (see Listing 18). You can see that after the CTE is created another SELECT command draws its rows from the CTE.

Why would you want to use a CTE over a view or derived table? One of the features of a CTE is the ability to create recursive query expressions. Listing 19 uses a CTE to list a manager and all the employees that report to them. It loops through each employee reporting to the manager and then loops through each employee reporting to the current employee and then loops through each employee reporting to the current employee and then, OK, you get the idea. Using recursion, each employee is listed who has employees reporting to them. Recursive queries make working with hierarchal data, such as manager/employee relationships or a Bill of Materials much easier than prior versions of SQL Server.

Recursive queries make working with hierarchal data, such as manager/employee relationships or a Bill of Materials much easier than prior versions of SQL Server.
I hope this gives you a taste of some of the new and exciting T-SQL features being added to SQL Server 2005. I hope this article has piqued your curiosity sufficiently that you will go out and investigate what else SQL Server 2005 has to offer.

If you are interested in getting a jump on learning SQL Server 2005, then you owe it to yourself to pick up a copy of A First Look at SQL Server 2005 for Developers," by Bob Beauchemin, Niels Berglund, and Dan Sullivan.



Jim Duffy is founder and president of TakeNote Technologies, an award-winning training and software development company. He has a BS degree in Computer and Information Systems and over 19 years of programming and training experience. He is an energetic trainer, skilled developer, and has been published in leading developer-oriented publications. Jim, a Microsoft MVP award recipient, is a popular speaker at regional user groups and developer conferences. He is also a co-host of Computers 2K4, a call-in radio show on WRBZ (AM 850), 850 The Buzz, in Raleigh, NC. Jim is also the author of TakeNote's popular VFP database auditing product, FoxAudit. In addition to his duties with TakeNote, Jim is also the VP of Sales & Marketing for Red Matrix Technologies, a SQL Server tools vendor who's SQLAudit product adds complete audit trail support for SQL Server databases. Additional information about Jim, TakeNote Technologies, and Red Matrix Technologies can be found at www.takenote.com.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap