SQL Server Health Reports — Working on Version 2

Late last year and early this year, I created a collection of report in SSRS that I called SQL Server Health reports.

Some of the things that the Server Health report evaluates are the following

But…. I have found it very challenging to distribute the SSRS reports in a way that they are easy to use for anyone anywhere with any SQL Server database environment.

So… The solution is to take the work done in the reports and port it into an application that can replace this collection of SSRS reports.  I started on this project today, and think that over the next couple of months, I should be able to replace the existing SQL Server Health reports in SSRS with an application that hosts all the queries and makes it easier than it is now.

I am just getting started, but over the next couple of months I will be releasing the SQL Server Health reports as an application.

TSQL Pivot Table

Here is a quick sample of how to implement a pivot table in TSQL for SQL Server. The example below creates a database called pivot, you probably already have your own database to work in. Then it creates a table called REVENUE and fills it in with department revenue for just over a 10 year period. Then you see a couple simple select statements followed by a SELECT statement that pivots the data.


USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'pivot')
BEGIN
ALTER DATABASE [pivot] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [pivot];
END
CREATE DATABASE [pivot];
GO

USE [pivot];

-- Table to be used for demo
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);
USE [pivot];
-- first lets look at the REVENUE table

SELECT *
FROM Revenue;
SELECT DepartmentId, Year, Revenue
FROM Revenue;

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

Just to recap, here is the actual pivot code, and the output that it produces.

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

I hope you find this useful as an example of working with PIVOT tables in SQL Server using the select statement in TSQL.

Working on my Lean Software Development presentation today.

Working on a 1h hour introduction to LEAN Software development entitled Applying Lean Manufacturing Principles to Software Development.

Its a little bit of agile, a bit of lean, and a ton of common sense.

Here is a link to the presentation:   Lean Software Development  its not done yet, just a start.

Common Table Expression – links

Here are a few links to some of the other CTE examples used during my Common Table Expressions presentation.

Just a quick summary of the samples.

You can also find more CTE examples on the TSQL Wiki.

Home after Seattle (Redmond) Code Camp 2012

What a great day at  Code Camp today.

Click here to download the slides and sample code from the presentations.

The first presentation that I attended was  Accelerating your Development Workflow presentation by Ian Davis.  What a great presentation, it touched on the lean and agile development processes, it was very well done.

The next one was a PowerShell presentation that was okay, then I presented the following three sessions.

What’s New in TSQL 2012

Unleashing Common Table Expressions in SQL Server

The CTE presentation is currently my most polished presentation, it was first presented at SQLSaturday in Redmond a few months back.

SQL Server Performance for Developers

I hope that everyone who attended had a great time, and learned something new!

Code camp 2012.

image

Code Camp Schedule… The photo is pretty bad, but if you could see it you would see my three sessions.