Counter of Festivals

Ashok Blog for SQL Lovers

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

















Monday, 13 April 2015

How to get Reference or Referenced Objects through sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities in SQL 2008?

Result
Object does not reference any object, and no objects reference it.
Object does not reference any object, and no objects reference it.
Both the sp_depends statements in the above script are not returning the referencing/referenced objects list. For example the stored procedure dbo.GetEmployeeDetails is referring the Employee table but sp_dpends is not providing this dependency information. Reason for this is: Stored procedure dbo.GetEmployeeDetails which is refereeing to this Employee table is created first and then the employee table in other words we call it as deferred resolution.
Solution to this problem is to use the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. Now let us check whether we are able to get the expected results using these DMV’s:
SPDependsAlternative
Note: These two DMV’s are introduced as a part of Sql Server 2008. So this alternate solution works for Sql Server version 2008 and above.

Wednesday, 8 April 2015

How to get tables with/without Index and Non Clustered Index on Database?

How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

--List all the Tables with NO Non-Clustered Indexes
SELECT Name 'Tables without any Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 0
        AND Type = 'U'
We can write a query like below to get all the Tables with Non-Clustered indexes:
--List all the Tables that have Non-Clustered Indexes
SELECT Name 'Tables with Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 1
        AND Type = 'U'
Let us understand this with example:
CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Clustered
and Non-Clustered Indexes.*/
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY (1, 1)
             PRIMARY KEY CLUSTERED NOT NULL,
    FirstName Varchar(50),
    LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_Customers
    ON dbo.Customers(FirstName,LastName)
GO
/*Let us create Orders Table with Clustered indexe only.*/
CREATE TABLE dbo.Orders (
    OrderId int IDENTITY (1, 1)
          PRIMARY KEY CLUSTERED NOT NULL,
    CustomerId int NOT NULL ,
    CreationDT DATETIME NOT NULL)
GO
/*Let us create OrderItems Table without any indexes.*/
CREATE TABLE dbo.OrderItems (
    OrderItemId int IDENTITY (1, 1),
    OrderId int NOT NULL,
    Qty int NOT NUll)
GO
Now let us run the queries to get the list of all Tables with or without Non-Clustered indexes and verify the result:
List_All_Tables_With_OR_Without_NonClustered_Indexes

How to find all the tables with no indexes at all in Sql Server?

We can write a query like below to get all the Tables in the Database that don’t have any indexes:
SELECT Name 'Tables without any Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0
Let us understand this with an example:
CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Clustered
and Non-Clustered Indexes.*/
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY (1, 1)
             PRIMARY KEY CLUSTERED NOT NULL ,
    FirstName Varchar(50),
    LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_Customers
    ON dbo.Customers(FirstName,LastName)
GO
 
/*Let us create Orders Table without any indexes.*/
CREATE TABLE dbo.Orders (
    OrderId int IDENTITY (1, 1) NOT NULL ,
    CustomerId int NOT NULL ,
    CreationDT DATETIME NOT NULL)
GO
Now let us run the query to get the list of all the tables with no indexes at all and verify the result:
List_All_Tables_Without_Any_Indexes