Some useful Addins for SQL Server Management Studio 2005 and 2008

Here are some useful addins I found for SSMS 2005/2008 on CodePlex and thought of sharing it with my readers.

Internals Viewer for SQL Server - Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

DataScripter - This Addin for SQL Server Management Studio 2008 allows you to generate INSERT statements for all values of a table easily. Just select Script Data as from the context menu of the table and choose one of the options

Fulltext Management for SQL Server - This Addin for SQL Server Management Studio allows you to manage your fulltext catalogs easily. It even works for SQL Server Express editions, so now you can use a nice GUI instead of unhandy SQL commands.

SQL Compact data and schema script utility - This console app and SQL Server 2008 Management Studio add-in helps you better manage your SQL Compact development efforts. If allows you to script schema and data to a .sql file, which can be used in any context. It also scripts DML for use in SSMS scripts

SQL Server 2008 Extended Events SSMS Addin - SQL 2008 Extended Events are a powerful new way of troubleshooting problems with SQL Server. One short coming is that there is no UI support for this new feature. The Extended Events Manager is a SSMS Addin to bridge this gap.

These were just a few of the many addins created for SQL Server Management Studio. If you know of any other addins you have used, please share it in the comments section.

Find Primary Key of a SQL Server Table

Here’s a quick way to find the primary key of a SQL Server 2005/2008 Table using TABLE_CONSTRAINTS

USE Northwind
go
SELECT
ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION

Primary Key SQL Server

Another way that I know of is to make a join between sys.indexes, sys.index_columns and sys.columns and get the desired results.

GROUPING SETS in SQL Server 2008

With the new GROUPING SETS operator included in SQL Server 2008, you have more control over what is aggregated. Here’s a practical example of using GROUPING SETS in SQL Server 2008

SAMPLE DATA

--DROP TABLE #Student

CREATE TABLE #Student
(
StudentID int ,
CourseYear smallint,
Semester smallint,
Marks float
)

INSERT INTO #Student VALUES (1, 2008, 1, 5.6)
INSERT INTO #Student VALUES (1, 2008, 2, 6.5)
INSERT INTO #Student VALUES (1, 2008, 3, 8.9)
INSERT INTO #Student VALUES (1, 2008, 4, 9.1)
INSERT INTO #Student VALUES (1, 2009, 1, 4.4)
INSERT INTO #Student VALUES (1, 2009, 2, 7.9)
INSERT INTO #Student VALUES (1, 2009, 3, 8.5)
INSERT INTO #Student VALUES (1, 2009, 4, 8.7)
INSERT INTO #Student VALUES (2, 2008, 1, 5.4)
INSERT INTO #Student VALUES (2, 2008, 2, 9.9)
INSERT INTO #Student VALUES (2, 2008, 3, 8.5)
INSERT INTO #Student VALUES (2, 2008, 4, 4.7)
INSERT INTO #Student VALUES (2, 2009, 1, 6.4)
INSERT INTO #Student VALUES (2, 2009, 2, 7.9)
INSERT INTO #Student VALUES (2, 2009, 3, 7.4)
INSERT INTO #Student VALUES (2, 2009, 4, 9.7)

Let’s do some common GROUPBY operations

-- Calculate Total Marks obtained by student each year
SELECT StudentID, CourseYear, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, CourseYear
ORDER BY StudentID

GROUPING SETS SQLServer

-- Calculate Total Marks obtained by student
-- grouped by Semester for both years
SELECT StudentID, Semester, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, Semester
ORDER BY StudentID

GROUPING SETS SQLServer

However with the GROUPING SETS operator, you can define different aggregate groups in a single query as shown below

SELECT StudentID, CourseYear, Semester, SUM(Marks) AS TotMarks
FROM #Student
GROUP BY GROUPING SETS((StudentID, CourseYear), (StudentID, Semester))
ORDER BY StudentID, Semester

GROUPING SETS SQLServer

Here’s a very nice article by Craig on GROUPING SETS in SQL Server 2008

Convert Binary to String in SQL Server

I recently found an undocumented function in the Master database to convert Binary to String in SQL Server. It’s called fn_sqlvarbasetostr and the code for this function is as shown below:

create function sys.fn_sqlvarbasetostr (
@ssvar sql_variant
)
returns nvarchar(max)
as
begin
declare @pstrout nvarchar(max)
,@basetype sysname
select @basetype = lower(cast(SQL_VARIANT_PROPERTY ( @ssvar, 'BaseType' ) as sysname) collate database_default)
if (@ssvar IS NOT NULL and @basetype IS NOT NULL)
begin
if (@basetype = N'varchar')
select @pstrout = N'''' + REPLACE(CAST(@ssvar as nvarchar(max)), '''', '''''') + N''''
else if (@basetype in (N'nvarchar', N'xml'))
select @pstrout = N'N''' + REPLACE(CAST(@ssvar as nvarchar(max)), '''', '''''') + N''''
else if (@basetype = N'char')
select @pstrout = N'''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(max))), '''', '''''') + N''''
else if (@basetype = N'nchar')
select @pstrout = N'N''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(max))), '''', '''''') + N''''
else if (@basetype in (N'binary',N'varbinary'))
select @pstrout = sys.fn_varbintohexsubstring(1, CAST(@ssvar as varbinary(max)), 1, 0)
else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
select @pstrout = CAST(@ssvar as nvarchar(40))
else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
select @pstrout = CONVERT(nvarchar(60), @ssvar, 2)
else if (@basetype in (N'money',N'smallmoney'))
select @pstrout = CONVERT(nvarchar(40), @ssvar, 2)
else if (@basetype = N'uniqueidentifier')
select @pstrout = N'''' + CAST(@ssvar as nvarchar(40)) + N''''
else if (@basetype in (N'datetime',N'smalldatetime'))
select @pstrout = N'''' + CONVERT(nvarchar(40), @ssvar, 112) + N' ' + CONVERT(nvarchar(40), @ssvar, 114) + N''''
else if (@basetype in (N'date',N'time',N'datetime2',N'datetimeoffset'))
select @pstrout = N'''' + CONVERT(nvarchar(40), @ssvar, 121) + N''''
else
select @pstrout = N'''Invalid Datatype' + @basetype + N'(' + CAST(@ssvar as nvarchar(max)) + N')'''
end
-- All done
return @pstrout
end

USAGE

SELECT sys.fn_sqlvarbasetostr(yourcolname) AS StringValue from YOURTABLE
Note: While searching for existing material on the subject, I came across a nice article by SQL Server MVP Peter Larsson - Convert Binary Value to String Value

Create an Index on a Partition Table using Page and Row Compression in SQL Server 2008

SQL Server 2008 provides an index option which has the ability to compress the contents of the index based on the row or the page. Here’s the syntax for doing it.

I assume that you have already created a Partition Table

-- DATA_COMPRESSION = ROW
CREATE CLUSTERED INDEX IX_RowCompr
ON YourPartitionTable (ColumnA)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

-- DATA_COMPRESSION = PAGE
CREATE CLUSTERED INDEX IX_PageCompr
ON YourPartitionTable (ColumnA)
WITH ( DATA_COMPRESSION = PAGE ) ;
GO

-- DATA_COMPRESSION = ROW/PAGE
CREATE CLUSTERED INDEX IX_PageRowCompr
ON YourPartitionTable (ColumnA)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1 To 3),
DATA_COMPRESSION = ROW ON PARTITIONS (4) ) ;
GO

Additional Reading

Here are are some useful articles on Page and Row Compression I read recently

Creating Compressed Tables and Indexes

Page compression in SQL Server 2008

Row compression in SQL Server 2008

Profiler for SQL Server 2005/2008 Express Edition

During an interaction session with one of my clients, they popped up a question – Is it possible to use a profiler in SQL Server Express Editions to see how queries are being resolved? I had no answer as I knew that SQL Server profiler was not supported on Express Edition

On doing some research, I found a nice tool that lets you profile SQL Express Databases. It’s called “SQL Server 2005/2008 Express Profiler”. SQL Server Express Edition Profiler provides most of the functionality a standard profiler does, such as choosing events to profile, setting filters, etc.

I hope you find the tool useful!

What’s a SQL Server Profiler?

SQL Server Profiler shows how SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets.

TOP 5 costly Stored Procedures in a SQL Server Database

I was recently doing a query to find out the Stored Procedures which took maximum time to execute. Here’s the query (thanks to gbn) I executed on a sample database (AdventureWorks) using SQL Server 2005/2008:

SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY max_logical_reads DESC

OUTPUT

image

If you know of a better way, please share it in the comments section.

Search Inside a SQL Server Stored Procedure Definition

I found myself in a strange situation recently. A couple of months ago, I had written a couple of Stored Procedure in SQL Server 2005/2008. A week ago, when the time came to use it, I could barely remember its name or any of the columns used in the SP. What I did remember was the name of a variable I had declared inside the SP. If you fall in a similar situation and want to search inside a SQL Server Stored Procedure, here’s how to do it.

I am using the AdventureWorks database as an example. I will search for all stored procedures that have @ManagerID declared as a parameter in their definition. Here’s the query:

SELECT obj.name AS SP_NAME,
sqlmod.definition AS SP_DEFINITION
FROM sys.sql_modules AS sqlmod
INNER JOIN sys.objects AS obj
ON sqlmod.object_id = obj.object_id
WHERE sqlmod.definition LIKE '%@ManagerID%'
and type = 'P'

OUTPUT

Search Stored Procedure

SQL Server 2008 R2 Developers Training Kit – March 2010 Update

The SQL Server 2008 R2 Update for Developers Training Kit – March 2010 Update was released a couple of days ago.

As given in the release notes - The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.


The training kit offers the following benefits:

  • Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
  • Provides a comprehensive set of videos, presentations, demos and hands-on labs
  • Contains new content for developers who are new to SQL Server.
  • Contains new content for SQL Server 2008 R2.
  • Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
  • Easy to download and install.

Download the Training Kit over here

3 Different Ways to display VIEW definition using SQL Server 2008 Management Studio

Here are 3 different ways to display VIEW definitions:

Method 1: Use sp_helptext

USE Northwind
GO
EXEC
sp_helptext Invoices

Method 2: Use SQL Server 2008 Management Studio

Open SSMS 2008. Choose the database and expand the ‘Views’ node. Right click on it > Script View as > CREATE To > Choose different options as shown below:

View Definition SSMS 2008

Method 3: If you want to display the name and definitions of all view in a database, use this query:

SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views

OUTPUT

View Definition

Alternatively, you can also do a join between sys.views and sys.sql_modules to obtain the same result

Temporary Tables VS Table Variables

I was having a good discussion with a fellow developer around SQL Server Temporary Tables and Table Variables. Here are some points from the discussion:

Temporary tables are like regular tables but stored in the tempdb database. These tables get dropped after they have been used. Temporary tables are of two types,

Local Temporary table - defined using a pound sign (#) and accessible only within the session that created it

Global Temporary table - defined using double pound signs (##) and visible to all users.

Table Variables is a data type that looks similar to a table but has a tighter lifetime scope (i.e within a function, stored procedure or batch that it is declared in) and should be used with small datasets.

Here are some differences between Temporary Table and Table Variables

Temporary Tables

Table Variables

Created and stored in tempdb databaseCreated in Memory (although it can write to tempdb)
Transaction logs are recorded for temporary tablesTransaction logs are not recorded for the table variables
They are bound to transactions.They are not bound to any transactions. So no effect of transaction rollbacks
Can participate in parallel operationsCannot participate in parallel operations
The log activity remains till it is manually cleared or the server restartsTable variable log activity is truncated immediately
SQL Server creates statistics for temporary tablesSQL Server does not create statistics for table variables
Stored procedure containing temporary tables cannot be pre-compiledStored procedures containing table variables can be pre-compiled
You can drop a Temporary TableYou cannot manually drop a table variable
You can create indexes on themYou cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

For further reading, http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html and Table Variables

Calculate Total Hours, Minutes and Seconds using T-SQL

I had recently posted about Convert Number to Hours and Minutes in SQL Server. I was writing a query where the table had three columns for Hours, Minutes and Seconds. I had to calculate the total hours, minutes and seconds for all the rows. The query had to also take care of rounding off every 60 minutes to an hour and every 60 seconds to a minute. Here’s the query if you have a similar requirement:

DECLARE @TT table
(
SNo int,
HoursSpent smallint,
MinutesSpent smallint,
SecondsSpent smallint
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 5, 20, 5);
INSERT INTO @TT VALUES ( 2, 2, 40, 22);
INSERT INTO @TT VALUES ( 3, 3, 33, 31);
INSERT INTO @TT VALUES ( 4, 6, 28, 52);
INSERT INTO @TT VALUES ( 5, 5, 1, 22);

SELECT TotMin / 3600 as TotalHours,
TotMin % 3600/60 as TotalMinutes,
TotMin % 60 as TotalSeconds
FROM
(
SELECT SUM(HoursSpent) * 3600 + SUM(MinutesSpent) * 60
+ SUM(SecondsSpent) as TotMin
FROM @TT
) temp

OUTPUT

Hours, minutes, seconds in T-SQL

Generate a Start and End Date Range using T-SQL

I was generating a date range by specifying a Start and End Date and an interval. I wanted the following output with an interval of 3 days (including the start date):

StartDate EndDate

2010-01-01 00:00:00.000 2010-01-03 00:00:00.000
2010-01-04 00:00:00.000 2010-01-06 00:00:00.000

and so on..

Here’s the query (thanks toTGBraitman) to generate the range:

DECLARE
@StartDate datetime = '2010-01-01',
@EndDate datetime = '2010-03-01',
@interval smallint = 3

;WITH CTE as
(
SELECT @StartDate as StDt
UNION ALL
SELECT DATEADD(day, @interval, StDt)
FROM CTE
WHERE DATEADD(day, @interval, StDt) <= @EndDate
)

SELECT StDt as StartDate,
DATEADD(day, @interval-1, StDt) as EndDate
FROM CTE

OUTPUT

Generate Date Range T-SQL

Add Error Handling to an Existing Stored Procedure

A lot of developers find Error Handling to be a tedious task, and in some cases it is. As a result, a lot of them skip error handling assuming the code would run just fine. However in a practical scenario, assuming that the code works fine is a myth.

Here’s a technique of how to add Error Handling to an Existing Stored Procedure without changing the code of the stored procedure. This technique was shared by a colleague of mine called Henry Perkins and I am thankful to him for the same. So let us say there is a stored procedure called uspSampleProc. To add error handling to this stored proc, use the following code. I am using the Northwind database to create this stored procedure with an exception

USE Northwind
GO
CREATE PROCEDURE
uspSampleProc
AS
INSERT INTO
Customers(CustomerID, CompanyName)
VALUES('ALFKI','TEST');
GO
BEGIN TRY
EXEC
uspSampleProc
END TRY
-- Catch the error
BEGIN CATCH
SELECT
ERROR_Number() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as InStoredProcedure,
ERROR_STATE() as ErrorState,
ERROR_SEVERITY() as ErrorSeverity,
ERROR_LINE() as ErrorLine
END CATCH

Since ALFKI already exists in the Customer table, there is a violation of primary key constraint which is caught by our Error Handling code. The output of running this code is as follows:

ErrorHandling StoredProcedure

Update a SQL Server Column with Random Numbers within a Range

Some time back, I had shared some code to Update a Column with Random Numbers in SQL Server

A SqlServerCurry reader mailed back asking me if it was possible to generate random numbers between a minimum and maximum range. Here’s a sample query that does that:

Test Data

DECLARE @TT table
(
ID smallint,
StudentID smallint,
DayAlloted smallint
)
-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 0);
INSERT INTO @TT VALUES ( 102, 2, 0);
INSERT INTO @TT VALUES ( 103, 3, 0);
INSERT INTO @TT VALUES ( 104, 4, 0);
INSERT INTO @TT VALUES ( 105, 5, 0);
INSERT INTO @TT VALUES ( 106, 6, 0);
INSERT INTO @TT VALUES ( 107, 7, 0);
INSERT INTO @TT VALUES ( 108, 8, 0);
INSERT INTO @TT VALUES ( 109, 9, 0);
INSERT INTO @TT VALUES ( 110, 10, 0);

UPDATE @TT
SET DayAlloted = ABS(CHECKSUM(NEWID())) % 10 + 1

SELECT * FROM @TT

The query generates a random number between 1 and 10.

Random Number SQL Server

Similarly to generate a random number between 24 and 123, use this query:

UPDATE @TT
SET DayAlloted = ABS(CHECKSUM(NEWID())) % 100 + 24
Random Number SQL Server 
There are other ways to generate the random number within a range and this is just one of them. If you too have a script, please share it in the comments section.

Resolving “Please create a master key in the database or open a master key in the session before performing this operation” error

While creating a backup or using the back up of an encrypted database, you may encounter the error - “Please create a master key in the database or open a master key in the session before performing this operation”

You can resolve this error by using:

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘thepassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

You can even backup the master key and then restore it to avoid this error. Here are some additional links to help you resolve this error:

Master Key Closed After Database Copy

Question on restoring encrypted databases on different servers

Getting: The key is not encrypted using the specified decryptor after DB restore