Dynamic Pivotting + Dynamic Aggregation = Dynamic T-SQL

I’m recently approached by one of the developers asking how to swing the data listed in the rows onto the columns, but he does not know these columns ahead of time. I right away think of “Dynamic Pivoting” article written by Itzik Ben-Gan on SQL Server Magazine. So here’s the codes:

-- Populate table variable with list of order years
SET NOCOUNT ON
DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY)
INSERT INTO @T SELECT DISTINCT YEAR(orderdate) FROM dbo.Orders

-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
DECLARE @cols AS nvarchar(MAX), @y AS int
SET @y = (SELECT MIN(y) FROM @T)
SET @cols = N''
WHILE @y IS NOT NULL
BEGIN
  SET @cols = @cols + N',['+CAST(@y AS nvarchar(10))+N']'
  SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

If you don’t care about the order of columns in @cols, then use this shorter version of codes:

-- Construct the column list for the IN clause
DECLARE @cols AS nvarchar(max);
select @cols = stuff((select distinct ',' + '[' + YEAR(orderdate) + ']' as [text()] from dbo.Orders for xml path('')), 1, 1, '')

-- Construct the full T-SQL statement and execute it dynamically.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

Here’s another challenge!

My developer then asks if he can aggregate this dynamic pivoted data as well…. So here’s what I come up with:

- Insert this dynamic pivoted data into a tempdb.dbo.someTempTable

-  Construct the statements for aggregating the pivot data

- Construct the main query using dynamic t-sql and concatenate with the statements built above

select @sql_aggr_pivot = @sql_aggr_pivot + ', SUM(pivot_tbl.[' +  rtaaa_col + '])' from @T;
SET @sql_aggr_pivot = SUBSTRING(@sql_aggr_pivot, 3, LEN(@sql_aggr_pivot));

set @sql_result = 'select .....' + @sql_aggr_pivot + ' from TableA join tempdb.dbo.someTempTable as pivot_tbl on TableA.ID=pivot_tbl.ID where this = that group by this that'

EXEC (@sql_result);

In conclusion, I guess when dealing with ‘Dynamic Pivotting’ and ‘Dynamic Aggregation’ at the same time, the only solution I can come with is to use ‘Dynamic T-SQL’

Implement sql server 2012 Failover Clustering on Windows 2008R2 cluster

been awhile since last blogged...good to be back and jog down whatever in my head... and as usual i not care much about spelling or grammar... it's been a very challenging experiences. I definitely learn a lot. I'd done clustering back in windows 2003 and sql server 2005. With win2008R2/sql2012 combination, oh lots of changes... all in good way. We only have 2-node cluster and will be active/active config, but we almost ran out of drive letters. Then we decided to use mounted volumnes rather than drive letter. Tempdb will be on shared disk as well as other cluster volumnes. One improvement in sql 2012 is now you can put tempdb on local disk if you wish. This helps a lot in performance when you have tempdb on some sort of local faster drives (eg. SSD). I have to admit, in clustering world, we have many ip addresses along with associated names. To name a few: - physical node name and their ip - windows cluster name and ip - sql server cluster virtual name and ip (1 pair for 1 sql instance) - sql server alias for app and ip (1 pair for 1 sql instance) - cluster msdtc and ip (1 pair for each sql instance) I have 2-node with active/active: look like i got 9 "name" and 9 ip these first 2-node cluster will be our OLTP. Once this up and running, my next project would set up 2nd 2-node cluster for some sort of OLAP ... kind of late... be back next time.

Using T-SQL to Verify Tables Row Counts in Transactional Replication - Continue

Last year, I wrote an article (Using T-SQL to Verify Tables Row Counts in Transactional Replication) about Transactional Replication and published it on on SQLServerCentral (http://www.sqlservercentral.com/articles/Replication/70545/)

At my new company, we're using a separated Distributor server, so I modified my script. Uhmm... not sure if I try to submit it again to SQL Server Magazine, they would take it or not. Anyhow, here's the script:

/*==================================================================
Originally created by: Thanh Ngay Nguyen (DBA in Minneapolis, MN)
Created date: 7/22/2010
Modified date: 6/11/2011
--------------------------------------------------------------------
This script verifies the Subscriber(s) have the same number of rows of
replicated data as the Publisher in transactional replication

This script must be run at the Distributor (where it resides on separate
server from the Publisher)
--------------------------------------------------------------------
Feel free to modify this script to fit your needs. All I ask is to
keep the original author name intact. Thanks.
--------------------------------------------------------------------
Modified by: You
Modified date: xx/yy/zzzz
--------------------------------------------------------------------
System tables that I use throughout the script:

SELECT * FROM distribution.dbo.MSarticles
SELECT * from distribution.dbo.MSpublications
SELECT * from distribution.dbo.MSsubscriptions
select * from master.sys.servers

USE [myDB]
SELECT * from dbo.sysindexes
SELECT * from sys.objects

--Instead of using deprecated dbo.sysindexes, sys.partitions can be used to obtain the rowcount

SELECT ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName,
p.rows AS RowCnt
FROM sys.partitions AS p
INNER JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND p.index_id IN ( 0, 1 )
====================================================================*/


/********************************************************************
BEGINING OF SCRIPT
*********************************************************************/

USE [Distribution]
GO

--------------------------------------------------------------------
--STEP 1: Gather information about current transactional replication
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempTransReplication') IS NOT NULL
DROP TABLE #tempTransReplication

CREATE TABLE #tempTransReplication
(
publisher_id INT,
publisher_srv VARCHAR(255),
publisher_db VARCHAR(255),
publication VARCHAR(255),
subscriber_id INT,
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
object_type VARCHAR(255),
source_owner VARCHAR(255),
source_object VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
rowcount_publisher INT,
rowcount_subscriber INT,
rowcount_diff INT
)

INSERT INTO #tempTransReplication
SELECT s.publisher_id,
ss2.data_source,
a.publisher_db,
p.publication,
s.subscriber_id,
ss.data_source,
s.subscriber_db,
NULL,
a.source_owner,
a.source_object,
ISNULL(a.destination_owner, a.source_owner), -- if NULL, schema name remains same at subscriber side
a.destination_object,
NULL,
NULL,
NULL
FROM distribution.dbo.MSarticles AS a
INNER JOIN distribution.dbo.MSsubscriptions AS s
ON a.publication_id = s.publication_id
AND a.article_id = s.article_id
INNER JOIN [master].sys.servers AS ss
ON s.subscriber_id = ss.server_id
INNER JOIN distribution.dbo.MSpublications AS p
ON s.publication_id = p.publication_id
LEFT OUTER JOIN [master].sys.servers AS ss2
ON p.publisher_id = ss2.server_id
WHERE s.subscriber_db <> 'virtual'

--SELECT * FROM #tempTransReplication



--------------------------------------------------------------------
-- STEP 2: Gather rowcount at Publisher side
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempPublishedArticles') IS NOT NULL
DROP TABLE #tempPublishedArticles

CREATE TABLE #tempPublishedArticles
(
publisher_srv VARCHAR(255),
publisher_db VARCHAR(255),
source_owner VARCHAR(255),
source_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_publisher INT
)

DECLARE @pub_srv VARCHAR(255),
@pub_db VARCHAR(255),
@strSQL_P VARCHAR(4000)

DECLARE db_cursor_p CURSOR
FOR SELECT DISTINCT
publisher_srv,
publisher_db
FROM #tempTransReplication

OPEN db_cursor_p
FETCH NEXT FROM db_cursor_p INTO @pub_srv, @pub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_P = 'SELECT ' + '''' + @pub_srv + ''''
+ ' AS publisher_srv, ' + '''' + @pub_db + ''''
+ ' AS publisher_db, s.name AS source_owner, o.name AS source_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_publisher
FROM ' + @pub_srv + '.' + @pub_db + '.sys.objects AS o
INNER JOIN ' + @pub_srv + '.' + @pub_db + '.sys.schemas AS s
on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @pub_srv + '.' + @pub_db + '.dbo.sysindexes AS i
on o.object_id = i.id
WHERE ' + '''' + @pub_srv + '.' + @pub_db + ''''
+ ' + ' + '''' + '.' + '''' + ' + s.name'
+ ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT publisher_srv + ' + '''' + '.' + ''''
+ ' + publisher_db + ' + '''' + '.' + ''''
+ ' + source_owner + ' + '''' + '.' + ''''
+ ' + source_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempPublishedArticles
EXEC ( @strSQL_P
)

FETCH NEXT FROM db_cursor_p INTO @pub_srv, @pub_db
END
CLOSE db_cursor_p
DEALLOCATE db_cursor_p

--SELECT * FROM #tempPublishedArticles



--------------------------------------------------------------------
-- STEP 3: Gather rowcount at Subscriber(s) side
--------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL
DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles
(
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_subscriber INT
)

DECLARE @sub_srv VARCHAR(255),
@sub_db VARCHAR(255),
@strSQL_S VARCHAR(4000)

DECLARE db_cursor_s CURSOR
FOR SELECT DISTINCT
subscriber_srv,
subscriber_db
FROM #tempTransReplication

OPEN db_cursor_s
FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + ''''
+ ' AS subscriber_srv, ' + '''' + @sub_db + ''''
+ ' AS subscriber_db, '
+ 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber
FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o
INNER JOIN ' + @sub_srv + '.' + @sub_db
+ '.sys.schemas AS s on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db
+ '.dbo.sysindexes AS i on o.object_id = i.id
WHERE ' + '''' + @sub_srv + '.' + @sub_db + ''''
+ ' + ' + '''' + '.' + ''''
+ ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT subscriber_srv + ' + '''' + '.' + ''''
+ ' + subscriber_db + ' + '''' + '.' + ''''
+ ' + destination_owner + ' + '''' + '.' + ''''
+ ' + destination_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempSubscribedArticles
EXEC ( @strSQL_S
)

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db
END
CLOSE db_cursor_s
DEALLOCATE db_cursor_s

--SELECT * FROM #tempSubscribedArticles



--------------------------------------------------------------------
-- STEP 4: Update table #tempTransReplication with rowcount
--------------------------------------------------------------------

UPDATE t
SET rowcount_publisher = p.rowcount_publisher,
object_type = p.object_type
FROM #tempTransReplication AS t
INNER JOIN #tempPublishedArticles AS p ON t.publisher_db = p.publisher_db
AND t.source_owner = p.source_owner
AND t.source_object = p.source_object

UPDATE t
SET rowcount_subscriber = s.rowcount_subscriber
FROM #tempTransReplication AS t
INNER JOIN #tempSubscribedArticles AS s ON t.subscriber_srv = s.subscriber_srv
AND t.subscriber_db = s.subscriber_db
AND t.destination_owner = s.destination_owner
AND t.destination_object = s.destination_object

UPDATE #tempTransReplication
SET rowcount_diff = ABS(rowcount_publisher - rowcount_subscriber)

--SELECT * FROM #tempTransReplication



--------------------------------------------------------------------
-- STEP 5: Display final results
--------------------------------------------------------------------

-- rowcount result by replicated database
SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those databases which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by publication
SELECT publisher_srv,
publisher_db,
publication,
subscriber_srv,
subscriber_db,
sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv,
publisher_db,
publication,
subscriber_srv,
subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those publications which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by table
SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
( source_owner + '.' + source_object ) AS source_objectname,
( destination_owner + '.' + destination_object ) AS destination_objectname,
rowcount_diff AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
AND rowcount_diff > 0 -- only show those tables which fall behind
ORDER BY rowcount_diff DESC



--------------------------------------------------------------------
-- information about current transactional replication setup
--------------------------------------------------------------------

SELECT publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
( source_owner + '.' + source_object ) AS source_objectname,
( destination_owner + '.' + destination_object ) AS destination_objectname,
rowcount_publisher
FROM #tempTransReplication
ORDER BY publisher_srv,
publisher_db,
subscriber_srv,
subscriber_db,
publication,
object_type,
source_objectname,
rowcount_publisher


/********************************************************************
END OF SCRIPT
*********************************************************************/

Snapshot Isolation

I’m currently making use of the SQL Server Change Tracking (new feature in 2008) to incrementally load our data warehouse. Per Microsoft, it's recommended to turn on Allow_Snapshot_Isolation at server level to make sure the consistency of data when using Change Tracking. I googled about this snapshot isolation stuff and ran into some very interesting discussions/blogs/articles. See below:

Hugo Kornelis, SQL Server MVP:
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/21/Snapshot-and-integrity-part-1.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot_and_integrity_part_2.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/08/25/174.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot_and_integrity_part_4.aspx

Paul Alcon:
http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level

microsoft.public.sqlserver.server:
http://www.sqlnewsgroups.net/sqlserver/t11164-performance-sql2k5-with-snapshot-isolation-level-turned.aspx

Sunil Agarwal:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/overhead-of-row-versioning.aspx

Database Growth Tracker Tool

Today, while I was googling for some t-sql scripts to collecting database stats from central mgmt server, I ran into CLR tool written by Tara Kizer (http://weblogs.sqlteam.com/tarad/archive/2010/07/09/Database-Growth-Tracker-Tool-ndash-New-Version.aspx). What a nice job she did without the hassle of setting up linked-server.

Well, while I was inside her blog, I pass through some very interesting things that she mentioned, e.g. missing indexes DMV, Max Degree of Parallelism, etc… You know what? I discover everyday that more and more things that I need to learn…

SQL Server Community’s Blogs

Aaron Bertrand's Blog
Brad McGehee's Blog
Chad Boyd's Blog
Greg Low's Blog
Jonathan Kehayias's Blog
Kevin Kline's Blog
Kimberly Trip's Blog
Paul Randal's Blog
Pinal Dave's Blog
Satya SK Jayanty's Blog
Simple Talk
SQL Server Magazine
TechNet Magazine
Tibor Karaszi's Blog
Tara Kizer - Ramblings of a DBA
Kevin Cox - SQLCAT Team
Glenn Berry
The SQLCAT Team
SQL Server CSS Team
Thomas LaRock
Michael J. Swart
Jamie Thompson
Adam Machanic (author of sp_WhoIsActive)
Jonathan Kehayias | The Rambling DBA

SQL Server Magazine

Well today I got email from SQL Server Magazine Editor saying that they wanted to publish my article in print on monthly SQL Server Magazine. For some reason, my article that submitted back in June, got buried in a backlog and just got surfaced recently. I was super duper excited about it. I told the editor that I didn’t get response from the company for couple months, so I submitted to SQLServerCentral and got posted/published there in August. The editor said since my article already got published elsewhere, so they can’t publish it in their magazine…. Dang…Bumper…Oh well… next time then…