Pearls: SQL Comments

When you are writing SQL code, very often you run parts in isolation by commenting out what you don't need and uncommenting portion that is to be executed.

A very simple approach of writing SQL comments coupled with SSMS(studio) shortcuts for commenting/uncommenting sections of code can help you do your unit testing efficiently.

First you need to know these SSMS short cuts:

Cntrl+K followed by Cntrl+C :

This comments ​current SQL line

Cntrl+K followed by Cntrl+U:

This uncomments the current SQL line

​​

--/* booklist section starts
SELECT * FROM BookList
--*/
--/*Book section starts
SELECT * FROM Books
--*/​

​Now all you need to do to comment Booklist section is press cntrl+K &cntrl-U on line #1 . To uncomment back the section , you'll need to comment out the line#1 by pressing cntrl+K&cntrl+C

The hidden Stored Procedures in SQL-II

Just like sp_MSforeachtable  you also have a stored procedure which runs for each DB.

It is sp_MSforeachdb

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachdb @command1,

     @replacechar,

     @command2,

     @command3,

     @precommand,

     @postcommand

where:

@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"

@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")

@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2

@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table

@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

Some example usages-

• To check space used by each DB

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

• Change all DB owners to sa

EXEC sp_MSforeachdb @command1='use ?; exec sp_changedbowner ''sa'''

3 Simple ways to check if a table exists in DB

Many times we ought to search if a table exist in DB. At those times we typically search the object explorer and do a manual search. This is OK if we have like 1-50 tables; but for huge databases it is best to run a query and find the result.Below are 3 queries which use different methods to find the same thing

1.  Using Object_ID approach

SELECT CASE WHEN OBJECT_ID (N'books', N'U') IS NOT NULL THEN  'Yes' ELSE  'No' END AS [DoesTableExists?]

2.  Using Information_Schema approach

SELECT CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='books') THEN 'yes'  ELSE 'no'  END AS [DoesTableExists?];

3.  Using Sys.tables approach

select case when exists(select 1 from sys.tables where name like 'books' )

then 'yes' else 'no' end as [DoestableExists?]

The hidden Stored Procedures in SQL

Not all SQL stored procedures have been documented. Some of these hidden/undocumented SP are quite useful. We’ll begin this series of blog by discussing some of these, one at a time.

Today we’ll discuss about the hidden SP - sp_MSforeachtable.

This very versatile SP works on all the tables in your databases. As such it can be used to run same query/code on multiple tables in a database. It is present in the master database.

It also supports one or more commands as arguments and we can use wild card character ? in the command queries  to take care of different tables.

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachtable @command1,

     @replacechar,

     @command2,

     @command3,

     @whereand,

     @precommand,

     @postcommand

where:

@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"

@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")

@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2

@whereand - can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)

@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table

@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

Some example usages are below-

·                                                         ·         Run DBCC check on all tables.

EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])'

·         Count rows in each table

CREATE TABLE #counts ( TABLE_NAME varchar(255), ROW_COUNT int) 

EXEC sp_MSForEachTable 

@command1=

'INSERT #counts(table_name,row_count) SELECT ''?'',COUNT(*) FROM ?'

SELECT TABLE_NAME,ROW_COUNT

FROM #counts

ORDER BY TABLE_NAME,ROW_COUNT DESC

·         Disable triggers in all tables

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

Dynamic SQL Pivot

DYNAMIC SQL PIVOT

One problem with the PIVOT query is that you have to specify all values inside the IN selection if you want to see them as columns.

A quick way to circumvent this problem is to create a dynamic IN selection making your PIVOT dynamic.

DECLARE @query VARCHAR(4000)

DECLARE @languages VARCHAR(2000)

SELECT @languages =

        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]

        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'

SET @query=

'SELECT * FROM

  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks

   FROM BookList) sourceData

PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)

PIVOT query in MS SQL

USING SQL PIVOT

This is perhaps one of the best things that I’ve come across in MS SQL while working on OGC. When you need to quickly convert something from rows to column or column to rows, this is the numero uno solution. Without ado, let me quickly introduce you to the syntax and most common use of a SQL PIVOT keyword.

SYNTAX

The syntax for PIVOT in most simplest expression format as

SELECT *

FROM [SOURCEDATA] PIVOT( SUM([ColumnToBePivotedOn]) FOR [ColumnWhoseRowsWillBeMadeIntoColumns] IN (NewColumn1, NewColumn2, NewColumn3, NewColumn4,…)) AS PivotTableName

 NOTES

1. We can use any aggregate function in lieu of SUM() like MAX(), MIN() etc.
2. [SourceData] can be replaced with a complex source like a JOIN between two table or an inner select query
3. If we need to add more columns to the PIVOT result it can be done provided these are not to be pivoted and will be used for aggregation of pivot data i.e. drilling down further.

EXAMPLE

Imagine a table Books in a Bookstore’s database. For simplicity we assume that the table is quite denormalised and has following columns

• BookId (Primary Key Column)
• Name
• Language
• NumberOfPages
• EditionNumber
• YearOfPrint
• YearBoughtIntoStore
• ISBN
• AuthorName
• Price

NumberOfUnitsSold

CREATE TABLE [dbo].[BookList](

      [BookId] [int] NOT NULL,

      [Name] [nvarchar](100) NULL,

      [Language] [nvarchar](100) NULL,

      [NumberOfPages] [int] NULL,

      [EditionNumber] [nvarchar](10) NULL,

      [YearOfPrint] [int] NULL,

      [YearBoughtIntoStore] [int] NULL,

[NumberOfBooks] [int] NULL,

[ISBN] [nvarchar](30) NULL,

      [AuthorName] [nvarchar](200) NULL,

      [Price] [money] NULL,

      [NumberOfUnitsSold] [int] NULL,

 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED

(

      [BookId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now if we need to query on the database and figure out number of books in English, Russian, German, Hindi, Latin languages bought into the bookstore every year and present our output in a small report format, we can use PIVOT query like this

SELECT * FROM

  (SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks

   FROM BookList) sourceData PIVOT(SUM(NumberOfBooks)

                                   FOR [Language] IN (English, Russian, German, Hindi, Latin)) pivotrReport

This will give a report like

Year Bought	English	Russian	German	Hindi	Latin
2009	24	NULL	50	23	NULL
2010	12	13	NULL	NULL	12
2011	30	NULL	NULL	44	NULL
2012	NULL	NULL	28	NULL	NULL
2013	45	NULL	10	NULL	NULL

We’ll see some more details on this in later blogs. Hope this was helpful.