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