Get week start dates of any month

DECLARE @startDate DATETIME
SET @StartDate = '20130101'
;WITH DayCounter(n) AS
( SELECT 0 UNION ALL SELECT n+1 FROM DayCounter WHERE n<30)
SELECT DISTINCT DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n) FROM DayCounter
WHERE MONTH(DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n)) = MONTH(@StartDate)

Get week end dates of any month

DECLARE @StartDate DATETIME
SET @StartDate = '20130101'
;WITH DayCounter(n) AS
( SELECT 0 UNION ALL SELECT n+1 FROM DayCounter WHERE n<30)
SELECT DISTINCT DateAdd(dd,7-(DATEPART(DW,@StartDate+n)),@StartDate+n) FROM DayCounter
WHERE MONTH(DateAdd(dd,7-(DATEPART(DW,@StartDate+n)-1),@StartDate+n)) = MONTH(@StartDate)

SQL Server 2012 brings some of the most desired enhancements in the Management Studio to simplify manageability.

(1) The new SQL Server Management Studio is built on Visual Studio 2012. As it opens, you soon recognize the familiar dark blue theme of Visual Studio 2010.

(2) You can drag and move out Query Window from the main work area:

It also has dual monitor support. You can move out a Query Window to another monitor or outside the main IDE.

(3) You can even drag out the ToolBox from the main IDE and place it elsewhere on the desktop or on another monitor.

(4) Being based on Visual Studio, SSMS brings the same Clipboard Buffer feature of Visual Studio 2010. You can cycle through buffer text by holding down the SHIFT key and pressing CTRL+V.

(5) Another interesting feature is Insert Snippet which you can find on menu via Edit -> IntelliSense. It allows you to inject variety of code snippets onto your Query Window:

Above I selected Create Procedure Basic Template to inject stored procedure code. It even provides you with two parameters to start with. You can always remove or add parameters according to your need.

Similarly, you can inject many other type of code snippets like: Function, Index, Table, View etc. There are even more enhancements which are outside the scope of this post.

Technorati : SQL Server, SQL Server 2012, SSMS
Del.icio.us : SQL Server, SQL Server 2012, SSMS

If you are coming from Crystal Reports background then you must be aware of a property called Suppress if 0 in Crystal Reports. By default, this property is disabled resulting in zero values being displayed on your report. When checked, all the zero values are suppressed by replacing 0 with a blank space.

SQL Server Reporting Services (SSRS) is quite young when compared to Crystal Reports. It is not as powerful as Crystal Reports but it definitely bundles simplicity and seamless integration with SQL Server data and Visual Studio.

Here I show you how to implement Suppress if 0 functionality in SSRS. In my example I have used SQL Server 2008 Reporting Services. Before starting let's see how the report looks when opened in browser before suppressing zero values:

To suppress zero values, go to Business Intelligence Development Studio and open the report design. My report design looks like this: More...

Sometimes you may want to execute a command against all the databases in an instance or all the tables in any database. SQL Server (2005 and above) contains two undocumented stored procedures, viz. sp_MSforeachdb and sp_MSforeacheable. To illustrate their use, I am taking a simple example to display all the databases and tables with the total space occupied.

What is the job of sp_MSforeachdb and sp_MSforeachtable?

Both the stored procedures are designed for iteration. With each iteration you can execute a command.

sp_MSforeachdb iterates through all the databases in the current instance, while sp_MSforeachtable iterates through all the tables in a database to perform a command.

You can view the details of each stored procedure by executing:

sp_help <name of system stored procedure>

from the query window. Below is the output of each:

Script to display all databases with sizes in an instance using: sp_MSforeachdb

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

An alternate approach is by ignoring the system databases from the list. In that case:

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
BACKUP DATABASE ? TO DISK = ''D:?.bak, WITH INIT'''

Script to display all the tables with total number of rows in a database using: sp_MSforeachtable

CREATE TABLE #t 
	( 
		Table_Name VARCHAR(80),
		Total_Rows CHAR(11),
		Reserved_Space VARCHAR(18), 
		Data_Size VARCHAR(18), 
		Index_Size VARCHAR(18),
		Unused_Space VARCHAR(18)
	) 

	INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

	SELECT * FROM #t ORDER BY Data_Size DESC

	DROP TABLE #t /* Drop Temp Table */

Note the ? used in all the code snippets above. It represents the name of the database or table returned at each iteration of the loop.

Disadvantages:

Both the stored procedures discussed above have a number of disadvantages. 

1. Iteration uses Loop which is not recommended because Loops are harder to manage in databases and are slower than SET-based constructs.
2. It is found that sp_MSforeachdb skips few databases from the list. Aaron Bertrand (Blog | twitter) writes in his blog about these issues. He also suggests a more reliable stored procedure to use instead of sp_MSforeachdb discussed above.

In some situations you may need to retrieve rows as independent columns using a single T-SQL Query.

For instance, I have a table (Schemes) in my SQL Server database:

CREATE TABLE [dbo].[Schemes](

	[ID] [int] NOT NULL,

	[City] [varchar](30) NOT NULL,

	[Scheme] [varchar](10) NOT NULL,

	[SchemeStatus] [varchar](10) NOT NULL,

 CONSTRAINT [PK_Schemes] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

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

) ON [PRIMARY]

This table holds City, Scheme and Scheme Status. Each City is running various schemes at various locations and keeps record of the Status of the scheme. 

The Scheme Status of any Scheme can be from any of the following:

• To be started
• Running
• Complete
• Discarded

Now, we add a couple of records in the table [Schemes]:

More...