Idola Blogs

CROSS APPLY

 

Here is another nice one (CROSS APPLY) and also note the key features here:

Create Function GetOrderDetails(@OrderId Int, @count Int)

      returns table

As

RETURN

      Select top(@count) * from [Order Details]

      Where OrderId = @OrderId

In the above example note the usage of @count in top. I just wanted to show the output of the following statement using the CROSS APPLY statement:

Select * from Orders

Inner join [Order Details] on Orders.OrderId = [order details].OrderId

Check this:

Select O.OrderId, OrderDate, ProductId, UnitPrice, Quantity from orders O

CROSS APPLY

      dbo.GetOrderDetails(O.OrderID, 3) AS D

ORDER BY

      O.OrderId Asc

 

This statement returns all the Orders and only the first three detail records. The APPLY clause acts like a JOIN without the ON clause. Refer to both CROSS APPLY and OUTER Apply here.

 

Event Notification and DDL Triggers

 

This function called Event Notifications is a feature that can be used by applications using SQL Server. This Event notifications executes in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.

A sample event will be like this:

 

CREATE EVENT NOTIFICATION log_ddl1

   ON SERVER

   FOR ALTER_TABLE

   TO SERVICE ‘//northwind.com/archiveservice’ , ‘current database’;

 

In the above example, ALTER_TABLE is a DDL statement. Refer to DDL Events for Use with Event Notifications for a complete list of DDL Events.

 

Similarly DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. The old DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views. DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

USE Northwind;

GO

IF EXISTS (SELECT * FROM sys.triggers

    WHERE parent_class = 0 AND name = ‘safety’)

DROP TRIGGER safety

ON DATABASE;

GO

CREATE TRIGGER safety

ON DATABASE

FOR DROP_SYNONYM

AS

   RAISERROR (‘You must disable Trigger “safety” to drop synonyms!’,10, 1)

   ROLLBACK

GO

DROP TRIGGER safety

ON DATABASE;

GO

 

 

 

 

 

Error Handling

 Simply anything can be handled with the TRY/CATCH:

BEGIN TRY

Select 1/0

Select Cast(‘a’ as DateTime)

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() as ErrorNumber,

        ERROR_MESSAGE() as ErrorMessage;

END CATCH;

 

 

These statements will show the following errors.

 

 

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() as ErrorState,

        ERROR_LINE () as ErrorLine,

        ERROR_PROCEDURE() as ErrorProcedure,

        ERROR_MESSAGE() as ErrorMessage;

 

 

 

 

May be I’m writing this very late, but will be useful for someone who is looking for features that are introduced in 2005. The content may not be properly formatted as it was prepared in a hurry.

Common Table Expression

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Sample (Northwind database):

WITH TopCustomers (City, Cnt)

AS

(

      SELECT City, count(*) FROM customers

      GROUP BY City

      HAVING count(*) > 1

)

SELECT * FROM

      Customers C inner join TopCustomers on C.City = TopCustomers.City

 

TOP function

SQL2005 allows TOP within the sub query also:

— Simple TOP query

SELECT TOP(5) *

FROM Employees;

— Nested TOP query

SELECT *

FROM (SELECT TOP(5) *

      FROM Employees

      ORDER BY [EmployeeId])

AS E

Order by EmployeeId Desc

Pivot Table

Another feature in 2005 is the Pivot table:

 

SELECT ProductId, Sum(Quantity*UnitPrice)

FROM [Order Details]

GROUP BY ProductId;

 

 

The same query as a Pivot table:

WITH Sales(ProductId, SalesAmt)

AS

      (SELECT ProductId, Quantity*UnitPrice

            FROM [Order Details])

SELECT ‘Sales’ AS SalesByProduct, [23], [46], [69], [15], [3]

FROM

      (SELECT ProductId, SalesAmt FROM Sales) AS SourceTable

PIVOT

      (

      SUM(SalesAmt)

      FOR ProductId IN ([23], [46], [69], [15], [3])

      ) AS PivotTable;

 

 

Though this doesn’t look very flexible, there will be instances where this can be applicable.