Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Multi-Statement Table-Valued Function

Multi-Statement Table-Valued Function:

A Multi-Statement Table-Valued user-defined function returns a table variable as function result.

It can have one or more than one T-Sql statement.  Within the create function command you must define the table structure that is being returned

Example:

-- =============================================

-- Author:        NAGNATH

-- Create date: <Create Date,,>

-- Description:   Return total amount spent by user in web shop till date as a Table variable @TotalAmountSpentByUser

-- =============================================

CREATE FUNCTION GetAmountSpentByUser_FN

( @LoginName Varchar(50) )

RETURNS

@TotalAmountSpentByUser table (

 NAME VARCHAR(50),

 TotalAmount DECIMAL(20,10)

)

AS

BEGIN

 ---Return result as Table for user

INSERT INTO @TotalAmountSpentByUser

SELECT U.LoginName,SUM(O.total) TotalSpent

FROM [order] O INNER JOIN [User] U on O.UserID=U.Id WHERE U.LoginName=@LoginName GROUP BY U.LoginName

 

 --if no user Found return warning message as name

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @TotalAmountSpentByUser

VALUES ('User not found',0)

END

 

RETURN

END

GO

 

Use of function in From Clause for existing user:



Not existing users


Ranking Functions

Ranking Functions:

SQL server 2005+ has four ranking functions. Ranking functions return a ranking value for each row in a partition. Ranking functions are nondeterministic.

T-SQl provides the following four ranking functions:

·         RANK()

·         DENSE_RANK()

·         NTILE()

·         ROW_NUMBER()

 

RANK () function:

Returns  rank of each row within a partition of result set, starting at 1 for the first row in each partition.

      Return type for Rank() function is bigint. Partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied.

SYNTAX:

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Example:

SELECT  RANK() OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId


Note: if two or more rows tie for rank, in this case each tied row receives same rank.

 

DENSE RANK () function:

 Returns the sequential number of row or rank of each row within a partition of result set without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. Return type for Rank() function is bigint

Main difference between RANK() and DENSE_RANK() function is this will not consider any gaps in the ranking.

Syntax:

 

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example:

SELECT  DENSE_RANK() OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId

 


Note: For tied records it will consider same rank but for next record rank is tied record rank plus one.

 

NTILE() function:

This inbuilt function Distributes rows the rows into specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

      Parameter we are passing to NTILE(integer expression) Is a positive integer constant expression that specifies the number of groups into which each partition must be divided. integer_expression can be of type int, or bigint.

Syntax:

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Example:

SELECT  NTILE(3) OVER (ORDER BY P.CategoryId) as [No.],P.productName,P.unitPrice,C.CategoryName

FROM PRODUCTS P INNER JOIN Categories C on P.CategoryId=C.CategoryId

 

ROW_NUMBER Function:

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Return type for this function is bigint

Syntax:

ROW_NUMBER ( )

    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

 

Example:

-- Get total order amount in shop by month wise and year wise

-- ROW_NUMBER() function returns unique row number value within a partition --- of a result set

-- by OrderYear and OrderMonth

SELECT ROW_NUMBER() OVER (ORDER BY OrderSummery.OrderYear, OrderSummery.OrderMonth) AS [No.]

    , OrderSummery.OrderYear, OrderSummery.OrderMonth

    , OrderSummery.OrderAmountByMonth

FROM

(SELECT YEAR(OrderDate) OrderYear

    , MONTH(OrderDate) OrderMonth

    , SUM(Total) OrderAmountByMonth

FROM [Order]

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

) OrderSummery

 


 

Difference between UNION and UNION ALL

Difference between UNION and UNION ALL:

  1. UNION returns only distinct records removes duplicate records.

  2. UNION ALL returns all records including duplicate records.

  3. UNION has to perform distinct sort to remove duplicates, which makes it less fast than UNION ALL.

UNION (Merge Join) cost 46%, Union All

 

Union All:


 

Note:

·        While using UNION and UNION ALL operators, the number of columns must be same, and data columns have to be same and they have to in same order.

·        ORDER BY clause should be used in last SELECT statement in the UNION query.

·        Both statements should have some order, and equal number of columns.

INTERSECT Clause:

INTERSECT Clause the SQL INTERSECT clause/operator is used to combine two SELECT statements, INTERSECT returns only common rows returned by the two SELECT statements.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

INTERSECT

SELECT * FROM [Employee]


EXCEPT Clause:

EXCEPT Clause the SQL EXCEPT clause/operator is used to combine two SELECT statements EXCEPT returns only rows, which are not available in second SELECT statement.

Syntax:

SELECT column1 [, column2 ] ..

FROM table1 [, table2 ]

[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

Example:

SELECT * FROM [UserData]

Except

SELECT * FROM [Employee]