Tag Archives: Sql Server 2008

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.

INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ …

Errors, Sql Server, , , , , , , , ,

In this article we will discuss on when we get the errors like below in Sql Server and how to resolve them.

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

First to reproduce this scenario we will create a demo db, table, populate sample data in the table and create a stored procedure to add the data to table:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Employee(EmployeeId int identity(1,1),
                   FirstName VARCHAR(50),LastName  VARCHAR(50))
GO
--Insert 1k records using GO statement as below
INSERT INTO dbo.Employee(FirstName,LastName) VALUES(NEWID(),NEWID())
GO 1000

GO
--Create Stored Procedure to insert record in Employee Table with
--QUOTED_IDENTIFIER setting set to "OFF"

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Below statement to insert record in the Employee table, successfully executes and inserts a record in the employee table:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now try to create a filtered index (New Feature introduced in Sql Server 2008) on the employee table as shown below. To create filtered index, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON.

SET QUOTED_IDENTIFIER ON
GO
CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId
 ON Employee(EmployeeId) WHERE EmployeeId > 500
GO

After creating the above filtered index, try executing the below statement which was executed successfully prior to the creation of the filtered index.

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

This time the execution of the above statement returns the below error:

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Reason for this error is:  Employee table has filtered index and due to this any DML statement on this table which is executed with SET QUOTED_IDENTIFIER setting as OFF will result in failure. Here as the stored procedure AddEmployee is created with SET QUOTED_IDENTIFIER setting as OFF, so whenever this sp is executed it will use this setting stored in the meta data.

To solve this issue, we need to re-create the SP AddEmployee  with QUOTED_IDENTIFIER setting as ON as shown below:

DROP PROCEDURE dbo.AddEmployee
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Now, try executing the below statement:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now, the above statement executes successfully and inserts a record in the employee table 🙂

You would also like to gothrough the article SET Options with their setting values required while working with filtered index

Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. It is better practice to use SET QUOTED_IDENTIFIERS ON setting.

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

Note: All the examples in this article are tested on Sql Server 2008 version

[ALSO READ] Difference Between SET QUOTED_IDENTIFIER ON and OFF setting

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.