List all object dependencies on which the Stored Procedure depends

In order to lists the database objects on which the given stored procedure depends, use the following query:

USE Adventureworks
EXEC sp_depends @objname = N'uspGetManagerEmployees'

sp_depends displays information about database object dependencies.

sp_depends gives out two result sets. One that shows the objects on which the 'given object' depends and the other, shows the objects that depend on the 'given object'

Tutorial available on Microsoft SQL Server 2008 Reporting Services

There is a new training available on Sql Server 2008 Reporting Services on the Microsoft Learning site.

You can check it out over here:

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services

As given on the site, the online course addresses several of the new features and functionality of Microsoft SQL Server 2008 Reporting Services:

- It includes the new Microsoft Report Designer
- A look at the innovate tablix data region
- Enhancements in visualization through improved charting and gauges
- Exploration of new administration and management features are also explored

SQL Server 2008: Merge Statement

SQL Server 2008 introduced the Merge statement which is commonly also called as Upsert (Update / Insert). It can be used as Insert if the row does not exists and update, if it exists. It can be used as ETL process while fetching data. This feature is useful for developers. It is not required to give any particular join (inner, left outer etc).

Let’s see some advantages for using Merge statement.

With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.

The syntax of Merge statement is as follows :
MERGE source
ON criteria

Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).

FullName nvarchar(35))

FullName nvarchar(35))

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')

With following statement only the update is done if there is match :

MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
THEN UPDATE SET src.FullName=t.FullName;

SELECT * FROM source
SELECT * FROM [target]

The following will be the actual values in both the tables

Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :

DELETE source
DELETE [target]

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')

SELECT * FROM source
SELECT * FROM [target]

The select query result will look as follows :

After using Merge with Insert and Delete

MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
THEN UPDATE SET src.FullName=t.FullName

SELECT * FROM source
SELECT * FROM [target]

The result of the Merge statement will give following output :

Now let’s try all three conditions and see the result. Let’s start fresh with the records again :

DELETE source
DELETE [target]

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')

SELECT * FROM source
SELECT * FROM [target]

The result will be as follows :

Let’s use Merge and see the result

We no longer see record for ID =3 as it gets deleted with this merge statement.

Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.

Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.

Delete a column in all tables using SQL Server 2005

If you are looking out to delete a column across multiple tables, use the query mentioned over here. This query would not work if there are constraints on the column. Just replace 'SomeName' with the column you wish to delete.

DECLARE @TblNames Table
tbl_name nvarchar(100) NULL
DECLARE @str nvarchar(100)
DECLARE @SQL varchar(200);
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT OBJECT_NAME(obj.object_id) as [Name]
FROM sys.objects obj inner join sys.columns col
ON col.object_id = obj.object_id
WHERE col.NAME = 'SomeName'
WHILE @I <= @ROWCOUNT BEGIN --SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I SET @SQL = 'ALTER TABLE ' + @str + ' DROP COLUMN SomeName' --Print @SQL EXEC (@SQL) SET @I = @I +1 END

SQL Server 2008 introduces Sparse Columns

SQL Server 2008 introduces a new concept of sparse columns with a table, for providing no physical usage for a column which may have NULL data.

Let’s see what benefit this feature offers us with respect to the memory required.

If there is a single table with thousands of rows and we have a single filed with some sort of flag which might have value only for some 100 records, it is quite memory consuming, even if we take bit as the data type. With sparse columns there can be multiple columns with a single table with no physical memory taken if they are declared as sparse.

To explain this further, we will take an example where Sparse Column can be implemented. Let us take a case of Registration Data for a website. While many people may register, not many would like to subscribe to some newsletter we may want to send. For those who would like to receive such newsletter, we keep a flag. Since the population of such people will be less, the flag may not hold any value for majority of the records but is still going to consume space in each record.

Let’s see some code snippets how this feature can be accomplished. This is with reference to Feb CTP SQL 2008 (CTP 6)

Sparse Columns can be created with CREATE TABLE or ALTER TABLE statements. It is like an ordinary column with SPARSE as the keyword. Sparse column can not be associated with some data types like text, NText or image. It also can not have properties like ROWGUID, IDENTITY or FILESTREAM.

Following is the code snippet to create a table with sparse columns in it

CREATE TABLE SparseColumns


Col1 int SPARSE,

Col2 int SPARSE,

Col3 int SPARSE,

Col4 int SPARSE,

Col5 int SPARSE,

Col6 int SPARSE,

Col7 int SPARSE,

Col8 int SPARSE,

Col9 int SPARSE,

Col10 int SPARSE)

INSERT INTO Sparsecolumns

(col1, col4)

VALUES (1,4)

SELECT * FROM SparseColumns will give a result with all columns displayed.

If there are a lot of sparse columns with a single table, the output can be really messy. In order to avoid it you can use Columns Sets associated with Sparse Columns. A column set is an untyped XML representation which combines all sparse columns together to form a better output. Once a column set added, it can not be changed. The table needs to be dropped and re- created. After adding column set, the output of select query will not return individual columns but will return XML representation.

A column set can be added to a table with CREATE TABLE statement and can not be done with ALTER TABLE

CREATE TABLE SparseColumns

(ID int identity primary key,


Col1 int Sparse,

Col2 int Sparse)


Using sparse columns is a good way to reduce physical storage. This feature will be useful in following scenarios

  • Storage space is a critical criteria (with sparse columns the disk space can be saved from 20 to 40 %)
  • If there are a lot of columns in a table which are likely to have NULL values (more than 50%)

SQL Server 2008 -Change Data Capture (CDC)

Exploring Change Data Capture (CDC) in SQL Server 2008

SQL Server 2008 has introduced ability to track changes to a table with CDC (Change Data Capture). With this, the changes to a table can be logged in another table without writing any trigger or any such mechanism. The changes to a table like insert, update and delete can be logged.

Advantages of using CDC

With SQL 2008 and 2005 the jobs could be done by writing update or after delete triggers. With CDC the logging can be done without writing triggers. Once a source table has been enabled for CDC immediately after the data is written to a transaction log (typical of any database) the capture process reads this log and puts the details in the log. This will help ETL process to query the change by using CDC functions. The change metadata is also written along with the actual change.

Let’s take an example

Following are some code snippets how CDC can be implemented. This code is with respect to Feb CTP of SQL Server 2008 (CTP 6) . Let’s see a small example by creating a database and enabling CDC for a table in it. Before CDC is enabled for a table it needs to be enabled for database.


USE MyCDCDatabase

EXECUTE sys.sp_cdc_enable_db;

To cross check if CDC is enables query system database
SELECT [name], is_cdc_enabled from sys.databases

The result will show value 1 for the newly created database.

After enabling CDC there are 2 jobs done. A user with the name of cdc is created for the database and a schema with the name cdc is added. If there is already use with cdc enabling CDC can not be done.

Name nvarchar(20) NOT NULL)

Ensure that SQL Server agent is running. If specified role doesn’t exist it automatically gets created. Running...

EXEC sys.sp_cdc_enable_table
gives following message :

Job 'cdc.myCDCDatabase_capture' started successfully.

Job 'cdc.myCDCDatabase_cleanup' started successfully.

A list of tables get created in the database which will track all changes. It can be verified by giving the query :

SELECT name, is_tracked_by_cdc from sys.tables


SELECT * fROM dbo.MyTableCDC
SELECT * FROM cdc.dbo_MyTableCDC_CT

After deleting a row from MyTableCDC, the table will be left with 3 rows but the captured table will now have 5 rows,

DELETE from dbo.MyTableCDC where Id=4

SELECT * fROM dbo.MyTableCDC
SELECT * FROM cdc.dbo_MyTableCDC_CT

Now let’s try update for the table :

UPDATE dbo.MyTableCDC SET Name='Gouri S' Where ID=1

SELECT * fROM dbo.MyTableCDC
SELECT * FROM cdc.dbo_MyTableCDC_CT

The result now shows 7 rows in the tracked table. First 4 rows from Insert, 1 from delete and update has 2 rows. One previous to update and one after update.

The functions available to capture the changed data are as follows. The second function gets created if the net changes are set to 1.

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_MyTableCDC');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_MyTableCDC(@from_lsn, @to_lsn, 'all')
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_MyTableCDC(@from_lsn,@to_lsn,'all')

Get all changes returns all the changes within a range and get net changes returns only the final changes within row.

CDC can be used with ETL operation for pulling data. Be careful with enabling, as depending upon the net changes, a lot many number of records will be added to the tracked table. Depending upon the tables tracked, the number of tracked tables will also be increased.

Determine when was Statistics last updated on your Table

The STATS_DATE gives back the date specifying when was the statistics for the specified index last updated.

You can use the following query to get the last updated date of Statistics for the object

USE Northwind
SELECT [name] AS 'Statistic',
STATS_DATE(object_id, index_id) AS 'Last Updated Statistics Date'
FROM sys.indexes
WHERE object_id = OBJECT_ID('Customers');

The query returns NULL in case of an error or if the caller lacks permissions to view the object.

If you need to study more on Statistics, check this link

Find out all the views in a database using SQL Server 2005

In order to find out all the views in a database, use this query :

USE AdventureWorks
SELECT [name] AS ViewName
,create_date as CreationDate
,modify_date as ModificationDate
FROM sys.views

You can also get various other info like SchemaName from sys.views using the query:

SELECT [name] AS ViewName, SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.views

Search for a column in your SQL Server database

If you have a requirement where you need to determine the tables and views where the column exists, here's the query for you:

USE AdventureWorks;
SELECT OBJECT_NAME(obj.object_id) as [Name], type_desc as [Type]
FROM sys.objects obj inner join sys.columns col
ON col.object_id = obj.object_id
WHERE col.NAME = 'ProductID'

Find all the User Defined functions in a database using SQL Server

In this short snippet, we will be using the Catalog Views to query and display a list of UDF's created on a database.

Use this query :

USE AdventureWorks
SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects

-- Displays a list of all the UDF's on the AdventureWorks database along with the creation date, modification date and function type.

Note: User-defined functions are either scalar-valued or table-valued. Table-valued functions are further classified as inline or multi-statement.

What is the difference between Scalar-Valued, Inline Table-Valued and Multi-Statement Table-Valued functions?

Scalar functions return a single data value using the RETURNS statement. Inline table-valued functions return the result set of a single SELECT statement whereas Multistatement table-valued functions return a table.

References :

Localize the ReportViewer control User Interface in SSRS

To provide custom localization of the ReportViewer control user interface, implement the IReportViewerMessages interface. All that needs to be done is add a setting tag to your web.config file using the key “ReportViewerMessages”.

For example:

namespace MyNamespace
public class MyClass : IReportViewerMessages
// implement interface

<add key="ReportViewerMessages"
value="MyNamespace.MyClass, MyClass" />

where value = Class, Assembly

Note: value = "" will display the default values for your ReportViewer UI.

Get an entire list over here :

Install Sql Server Express 64-bit On 64-Bit Windows Vista

There was a discussion recently at the MSDN forums where I was participating in a thread that discussed about installing 64-bit SQL Express Editions on Vista 64-bit. You can check the thread over here. I thought to record the important points in this blog. There is also a similar thread which discusses about the issues while installing SQL Express 64-bit over here.

SQLEXPR.EXE or SQLEXPR_ADV.EXE, are the full installers that support x64 systems. In order to download 64bit versions of SQL Express, check these links:

For the 64-bit install of SQL Server 2005 Express Edition, click here
For the 64-bit install of SQL Server Management Studio Express, click here

Steps to install SQL Express 64-bit on a Vista 64-bit machine:

1. Go to Add/Remove programs and check for an entry for SQL Native client. If there exists one, uninstall.
2. Install the 64-bit of SQL native client from over here X64 Package
3. Then download the Express Editions and Management Studio from the links given at the top.