OTHER DML and T-SQL functions

Often developers have the need to have a command that will enable them to insert data while updating a table. The Merge command can now be used to do this. The command specifies the table to which the data will be updated while USING will specify the table from which the data is being input. An ON clause is used to determine which fields will be used for matching and execution of the WHEN MATCHED THEN or WHEN NOT MATCHED THEN. The whole process is still very cumbersome and slows down performance considerably.

The TOP clause has been transformed to be used with other SQL statements such as INSERT, DELETE and UPDATE.

TABLESAMPLE

This is a new feature of the FROM clause that allows users select samples of data rows. It takes the number of rows to be sampled as a parameter. The syntax is as under:

SELECT * FROM Student.CourseSales TABLESAMPLE(10 ROWS)

Exception Handling

The lack of exception handling in SQL Server has been bemoaned by developers for years. SQL Server 2005 alleviates many of the problems. Now data errors can be managed and some of the issues can be resolved by the server before the results are displayed on the client machine. Structure exception handling integrated into SQL Server through the .NET Framework enables exception handling in transactional situations—such as in a stored procedure. The TRY…CATCH blocks implement structured exception handling. While the TRY block contains the transactional code that could potentially fail the CATCH block contains code that executes if an error occurs. This reduces the amount of code required to be written and the @@error check can be dispensed with.

A precondition to the structured exception handling is that the Automatic roll back of transactions must be enabled with SET XACT_ABORT ON. All errors, are then raised within the TRY block to the level of transaction abort errors and the CATCH block then handles the errors. At this point, the transaction become doomed and is in a limbo till the developer uses the ROLLBACK TRANSACTION statement in the CATCH block to release the transaction resources. To create an exception in the TRY block the RAISERROR statement has to be used and the severity level (11-20) must be specified in order to invoke the CATCH block.

The syntax would be as under:

BEGIN TRY
---BEGIN TRAN
---------Perform INSERT,UPDATE, or DELETE statements
---COMMIT TRAN
END TRY

The syntax for the CATCH statement would be as under:

BEGIN CATCH
---DECLARE @err int
SET@err=@@error
ROLLBACK TRAN
--log the @err variable in an error log table
END CATCH

Procedure for using Structured Exception handling

1. Start SQL Server Management Studio. When prompted to connect to a server, click Cancel.

2. On the standard menu, click New Query, and then click New SQL Server Query.

3. In the Connect to SQL Server dialog box, connect to localhost by using Windows authentication.

4. In the new query page, type the following Transact-SQL code: USE tempdb GO CREATE TABLE dbo.DataTable (ColA int PRIMARY KEY, ColB int) CREATE TABLE dbo.ErrorLog (ColA int, ColB int, error int, date datetime) GO

5. On the Query menu, click Execute.

6. Type the following Transact-SQL code: CREATE PROCEDURE dbo.AddData @a int, @b int AS SET XACT_ABORT ON BEGIN TRY BEGIN TRAN INSERT INTO dbo.DataTable VALUES (@a, @b) COMMIT TRAN END TRY

7. Add the following Transact-SQL code: BEGIN CATCH DECLARE @err int SET @err = @@error ROLLBACK TRAN INSERT INTO dbo.ErrorLog VALUES(@a, @b, @err, GETDATE()) END CATCH GO

8. On the Query menu, click Execute.

9. Create a new query with the following code: USE tempdb EXEC dbo.AddData 1, 1 EXEC dbo.AddData 2, 2 EXEC dbo.AddData 1, 3 GO SELECT * FROM dbo.DataTable SELECT * FROM dbo.ErrorLog GO

10. On the Query menu, click Execute.

While a number of new features have been added to SQL Server 2005 T-SQL, several features have been marked for deprecation. The SET ROWCOUNT is being deprecated in favor of TOP statement. The original JOIN syntax is also on its way out. It may be better to use the ANSI join syntax in its place till a final decision is taken. The next one for the axe is the COMPUTE statement. The ROLLUP or CUBE statements will have to be used instead.

In the next section of this tutorial we will briefly examine the Developer tools that are being made available to the developer.

First Page: Data Manipulation Language (DML) in SQL Server 2005