Wednesday, November 30, 2011

0 TVF and Apply

TVF full name is User-defined functions that return a table data. It is also alternative of the view. While views are limited to a single SELECT statement,TVF can contain additional statements that allow more powerful logic than is possible in views.

Create View sample Code

  1. use AdventureWorks;
  2.  
  3. CREATE VIEW vSalesCount(ProductID,CountProduct)
  4. AS
  5. SELECT ProductID,COUNT(ProductID) FROM sales.SalesOrderDetail GROUP BY ProductID having ProductID > 800
  6. GO

Table Value Function

  1. use AdventureWorks;
  2. IF OBJECT_ID(N'dbo.ufnGetSalesCount', N'TF') IS NOT NULL
  3.     DROP FUNCTION dbo.ufnGetSalesCount;
  4. GO
  5. CREATE FUNCTION dbo.ufnGetSalesCount(@checkProductID int)
  6. RETURNS TABLE
  7. AS
  8. RETURN (
  9.        SELECT ProductID,COUNT(ProductID) AS NumCount FROM sales.SalesOrderDetail
  10.        GROUP BY ProductID having ProductID > @checkProductID
  11.     );
  12. GO

Lets query the TVF

  1. SELECT ProductID,NumCount
  2. FROM dbo.ufnGetSalesCount(900);

image

TVF return result is just like a table. We can use APPLY to join it. APPLY is similar to join. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY is more like the inner join. OUTER APPLY is more like the outer join.

To simplify the testing, I change the TVF as below

  1. CREATE FUNCTION dbo.ufnGetSalesCount(@checkProductID int)
  2. RETURNS TABLE
  3. AS
  4. RETURN (
  5.        SELECT ProductID,COUNT(ProductID) AS NumCount FROM sales.SalesOrderDetail
  6.        GROUP BY ProductID having ProductID =@checkProductID
  7.     );
  8. GO

Then run the below query

  1. SELECT Production.Product.ProductID,Product.Name, SE.NumCount
  2. FROM Production.Product  CROSS APPLY dbo.ufnGetSalesCount( Production.Product.ProductID) AS SE

When Query engine execute the query, it take the ProductID from Product table and retrieve the data from TVF , then combine with the Product table.

image

If TVF does not return data, then CROSS APPLY wont show the dataset from the Product table. When we replace the CROSS APPLY with OUTER APPLY, even the TVF does not return data, It will still show the result set.

Confirm there is no sales for the Product ID 1

  1. SELECT ProductID,NumCount
  2. FROM dbo.ufnGetSalesCount(1);

Lets change it to the OUTER APPLY

  1. SELECT Production.Product.ProductID,Product.Name, SE.NumCount
  2. FROM Production.Product  OUTER APPLY dbo.ufnGetSalesCount( Production.Product.ProductID) AS SE

image

As you can see the Product ID 1 does not show in the result set when we use CROSS APPLY but shows in the result set when we use OUTER APPLY.

How to resolve CREATE FUNCTION failed because a column name is not specified for column 2.

If any of the column in the User defined function does not have explicit name, you will get the below error message

Msg 4514, Level 16, State 1, Procedure ufnGetSalesCount2, Line 5
CREATE FUNCTION failed because a column name is not specified for column 2.

In the example above, If we do not provide the alias for the COUNT(ProductID), then we will get that error.

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates