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

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

-- 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

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

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

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

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:

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

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