Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 14 April 2015

Important Things to know about UDF/Views in SQL Server with Real Time Examples

 Types of User-Defined functions in SQL Server 2000 and they are

1)Scalar
2)Inline Table-Valued
3)Multi-statement Table-valued.

For More/Details Information

http://sqltechi.blogspot.in/2012/02/user-defined-functions-types-and.html

  • you cant able to use DML operation inside Scalar Function


Create Function dbo.Scalar_DML(@OID int)

returns int

as
BEGIN
declare @ret int
select @ret=COUNT(*) from dbo.[Order Details]
WHERE Orderid=@OID

UPDATE [order details] set Orderid=Orderid+1 where Orderid=(SELECT TOP 1 @OID FROM [Order Details] where Orderid=@Oid)

return @ret
END

Error:
Msg 443, Level 16, State 15, Procedure Scalar_DML, Line 9
Invalid use of a side-effecting operator 'UPDATE' within a function.

  • Temporary Tables are not allowed in any User Defined Functions.
  • Table Variables can be used in User Defined Functions.


  • You can able to join UDF with other tables

SELECT * FROM dbo.Inline_OD(10273) AS IOD

JOIN dbo.Orders O On IOD.OrderID=O.OrderID



  • You can able to use it on cross join

declare @id int

set @id=10273

SELECT * FROM dbo.Inline_OD(@id) AS IOD
cross JOIN dbo.Orders O where IOD.OrderID=O.OrderID


VIEWs:

Ref:
http://sqlhints.com/category/sql-server/views/


Views are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables.
Benefits of Views:A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all the data). Views can do the following:
  • Restrict access to specific rows in a table
  • Restrict access to specific columns in a table
  • Join columns from multiple tables and present them as though they are part of a single table
  • Present aggregate information (such as the results of the COUNT function)
Lets checkout the basic syntax for creating a view:
CREATE VIEW <View_Name>
AS
<SELECT Statement>
  • You cant able to use DML operations inside View 

alter view vw_custinfo

as

update Custtemp set custtemp='TEst' where CustomerID in 
(SELECT  top 1 CustomerID
from dbo.Customers)

Msg 156, Level 15, State 1, Procedure vw_custinfo, Line 4
Incorrect syntax near the keyword 'update'.

  • you can able add new rows in view it reflected in table


  • Below is an example view where it returns the data from multiple tables by joining:

Create View vwGetCustomerOrders
AS
 SELECT C.FirstName,O.OrderId
 FROM Customers C
   INNER JOIN Orders O
    ON C.CustomerId = O.CustomerId
GO
Select * from vwGetCustomerOrders
  • Addition of New Column’s in the Underlying Table will not automatically reflect in the existing views you have to refresh view or drop and recreate it to reflect those column in view
Let us prove this behaviour by 
creating a view vwGetCustomers  which returns all customer details with all the columns in the customer table: 
Create View vwGetCustomers
AS
SELECT *
FROM Customers
GO
Select * FROM vwGetCustomers
Now add one more column Country to the Customers table:
ALTER Table Customers
ADD Country Varchar(30)
Execute the below statement and observe that the new column country added in the Customers table is not present in the result.
SELECT * From vwGetCustomers
following two way to reflect this new column in the view is to drop and create back the view as below
Drop View vwGetCustomers
or
if you use
Sp_refreshview ‘vwGetCustomers’
Then new column should be reflected in result for view.
  • When the View is created on Multi tables we can not delete from the View, though we can update it which will affect the corresponding table column value.

Create View vwGetCustomerOrders
AS
 SELECT C.CustomerId,O.OrderId
 FROM Customers C
   INNER JOIN Orders O
    ON C.CustomerId = O.CustomerId
GO
  • You can able to join view with other tables using join

Select * from vwGetCustomerOrders vwco
join Customers c on vwco.customerid=c.CustomerID 

  • you can able to use it on cross join

Select * from vwGetCustomerOrders vwco
cross join Customers c where vwco.customerid=c.CustomerID 

  • View can be able to create index but it have to create using with Schemabinding option 

The indexed view can be created with the WITH SCHEMA BINDING option while creating the view.
The indexed view has some restrictions like cannot use the TOP, DISTINCT, UNION, ORDER BY and aggregate functions.

It allows us to use the GROUP BY statement but we cannot use COUNT statement. Instead of that COUNT_BIG statement can be used.

  •         while you create Indexed view object name should be dbo.tablename if you omit dbo. before table name it throws error


Cannot schema bind view 'dbo.viewname' because name 'tablename' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
see below





  • while you create indexed view tables are belong to same database otherwise it throws error as below



Cannot schema bind view 'dbo.objectname' because name 'dbname.dbo.tablename' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So, After following above rule you can able to create view with index(indexed view) as shown below

ALTER View dbo.vwGetCustomerOrdersDetwithtrigex with Schemabinding 
AS
 SELECT C.CustomerId,O.OrderId,C.City,O.OrderDate
 FROM dbo.Customers C
   INNER JOIN dbo.Orders O
    ON C.CustomerId = O.CustomerId

GO



  • AFTER triggers cannot be defined on views. we can define instead of triggers on view kindly see below for your more details

INSTEAD OF Triggers on the Indexed View
Normally the triggers cannot be created on the view. But sql server 2005 onwards we can create the INSTEAD OF trigger on the indexed views.

USE
 [Northwind]
GO
IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL
BEGIN   
DROP VIEW [DBO].[VW_Trigger_Example]   
PRINT '[DBO].[VW_Trigger_Example view dropped..'
END
GO
CREATE VIEW [DBO].[VW_Trigger_Example]
WITH SCHEMABINDINGAS  
SELECT P.ProductID,P.ProductName,P.SupplierID,         OD.OrderID,OD.UnitPrice,OD.Quantity  
FROM [DBO].Products P  
INNER JOIN [DBO].[Order Details] OD ON OD.ProductID = P.ProductID
GO
IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL
BEGIN  
 PRINT '[DBO].[VW_Trigger_Example view created..'
END
GO
--SELECT * FROM VW_Trigger_Example
IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL
BEGIN  
DROP TRIGGER [DBO].Tr_Delete_TriggerExample 
 PRINT '[DBO].Tr_Delete_TriggerExample trigger dropped..'

END

GO

CREATE TRIGGER [DBO].Tr_Delete_TriggerExample

ON [DBO].VW_Trigger_Example

INSTEAD OF DELETEASBEGIN   

PRINT '----------------------------------------'  

PRINT 'This is an example of INSTEAD OF Trigger'  

PRINT '----------------------------------------' 

SELECT TOP 1 * FROM DELETED

END

GO
IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL
BEGIN  
PRINT '[DBO].Tr_Delete_TriggerExample trigger created..'
END