Category Archives: SQL Server 2008 What’s New

sp_depends results are not reliable

DMVS, Sql Server, SQL Server 2008 What's New, System Stored Procedures, Tips/Tricks, , , , ,

sp_depends System Stored procedure will give the list of referencing entities for a table/view and list of referenced entities by the stored procedure/view/function. As per MSDN this system stored procedure is going to be removed from the future versions of the Sql Server.

sp_depends results are not always reliable/accurate/correct. Good and reliable alternative for sp_depends are the DMV’s: sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities.

Let us see this with an example the incorrect results returned by the sp_depends system stored procedure:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
CREATE PROCEDURE dbo.GetEmployeeDetails
AS
BEGIN
	SELECT * FROM dbo.Employee
END

GO

CREATE TABLE dbo.Employee
(Id int IDENTITY(1,1), FirstName NVarchar(50), LastName  NVarchar(50))

GO
INSERT INTO dbo.Employee(FirstName, LastName)
	VALUES('BASAVARAJ','BIRADAR')

GO
-- Get the list of objects which are referring Employee table using
sp_depends [dbo.Employee]
GO
-- Get the list of objects referenced by the SP:GetEmployeeDetails
sp_depends [dbo.GetEmployeeDetails]
GO

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.

You may also like to read my other article: 

Please correct me if my understanding is not correct. Comments are always welcome.

Sparse Columns in SQL Server 2008

Sql Server, SQL Server 2008 What's New,

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

For example: As we know that storing a null/non-null value in a DATETIME column takes 8 bytes. On the other hand Sparse DATETIME column takes no space for storing null value but storing a non-null value will take 12 bytes i.e. 4 bytes extra then normal DATETIME column.

At this moment the obvious question which arises in our mind is: Why 0 bytes for null value and 4 bytes extra for storing non-null value in a sparse column? Reason for this is, sparse column’s value is not stored together with normal columns in a row, instead they are stored at the end of each row as special structure named Sparse Vector. Sparse vector structure contains:

 [List of non-null Sparse Column Id’s – It takes 2 Bytes for each non-null Sparse column] + [List of Column Offsets — It takes 2 bytes for each non-null Sparse column].

So, defining columns with high density of null value as Sparse will result in huge space saving. As explained previously non-null value in the sparse column is stored in a complex structure, so reading non-null sparse column value will have slight performance overhead.

Let us understand the Sparse Column concept with below example.

Example:  In this example we will create two identical tables. Only difference between them is, in one table two columns are marked as Sparse. In both of these tables insert some 25k records and check the space utilization by these tables.

CREATE DATABASE SPARSEDEMO
GO
USE SPARSEDEMO
GO
CREATE TABLE SPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime sparse,
 col3 int sparse
)
CREATE TABLE NONSPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime,
 col3 int
)
GO
INSERT INTO SPARSECOLUMNTABLE VALUES(NULL,NULL)
INSERT INTO NONSPARSECOLUMNTABLE VALUES(NULL,NULL)
GO 25000

 Now check the space used by these tables by executing the below statements:

EXEC SP_Spaceused SPARSECOLUMNTABLE 
EXEC SP_Spaceused NONSPARSECOLUMNTABLE

Result:

name              rows        reserved data   index_size unused
SPARSECOLUMNTABLE 25000       392 KB  344 KB 8 KB       40 KB

name                 rows        reserved data   index_size unused
NONSPARSECOLUMNTABLE 25000       712 KB  656 KB 8 KB       48 KB

So, with above example it is clear that defining a column with high density of null values result’s in huge space saving.

Please correct me, if my understanding is wrong. Comments are always welcome.

Merge Statement in Sql Server 2008

Sql Server, SQL Server 2008 What's New, ,

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

This feature is very useful in a scenario where we want to synchronize the data in the target table with source table data. In earlier versions of sql server to achieve this synchronization we would have scanned the source and target tables multiple times(i.e. ones for inserting the new records , second time for updating the matching records and third time for deleting the records in the destination table which are not present in the source table), but with Merge statement we can achieve all this with single statement and with only one time looking-up of the source and target tables.

Let us understand the Merge statement with a simple example.

First create a Source Table with Sample Data:

CREATE TABLE dbo.EmployeeSource(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeSource
VALUES(1,'Basavaraj Biradar') ,  
  (3,'Monty')
GO
SELECT * FROM dbo.EmployeeSource WITH(NOLOCK)
GO
--Source Table Data
Id Name
1  Basavaraj Biradar
3  Monty

Now create a Target Table with Sample Data:

CREATE TABLE dbo.EmployeeTarget(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeTarget
VALUES(1,'Basavaraj') ,
  (2,'Shashank')
GO
SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
 --Target Table Data
Id Name
1  Basavaraj
2  Shashank

Now Syncronize the target table with source table data using the below Merge statement:

MERGE dbo.EmployeeTarget AS T
USING dbo.EmployeeSource AS S
 ON T.Id = S.Id
WHEN MATCHED THEN -- Matching Employee record
  UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
-- Employee record presnet in Source but not in target
  INSERT (Id, Name)
  VALUES (S.Id, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
-- Employee record present in destination but not in source
   DELETE;

Target table data after executing the above Merge statement:

SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
 --Target Table Data
Id Name
1  Basavaraj Biradar
3  Monty