Important features in T-SQL Server 2005

Important features in T-SQL Server 2005

What are the new keywords in SQL Server 2005?

-          Replicate()

Repeat the characters into specified times.

Specifically this functionality is use for the padding and formatting the result.

SELECT REPLICATE ('JENA', 2) AS "MyLastName Twice"

-          Grouping()

This function use in the case of use POLLUP or CUBE that moment if this two operators are not return the result it will return 0 otherwise 1

-          Rollup

This operator help to generate the total and subtotal of the result set of query based on the specified column.

SELECT

   CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

        ELSE ISNULL(Item, 'UNKNOWN')

    END AS Item,

    CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

        ELSE ISNULL(Color, 'UNKNOWN')

    END AS Color,

    SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item,Color WITH ROLLUP

-          Cube

This operator is use to get the result in the cross tabulation of all the possible combination of dimension.

 SELECT Item, Color, SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

-          Compute

This clause provides the grand total of the result set of a query.

SELECT SalesOrderID, UnitPrice, UnitPriceDiscount

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

   COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount)

-          Compute By

This clause provides the grand total and subtotal of result set of a query based on the specified column.

SELECT SalesOrderID, UnitPrice, UnitPriceDiscount

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount) BY SalesOrderID

-          Row_ Number() over (Partition by  ... Order by  ...)

This method returns the sequential number of each records based on the order by column.

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'RowNumber',c.FirstName, c.LastName

FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID

JOIN Person.Address a ON a.AddressID = c.ContactID

-          Rank()  over (Partition by  ... Order by  ...)

This method return the rank of each row based on the partition and order by clause .If two rows having the same rank it will return the same number instead of two numbers. For the next rank number will come with the previous rank plus one number.

SELECT RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK,

i.ProductID, p.Name, i.LocationID, i.Quantity

FROM Production.ProductInventory i JOIN Production.Product p

ON i.ProductID = p.ProductID

ORDER BY p.Name