Up-Line and Down-Line with HierarchyId Data type - SQL Server 2008 by serverku

I have already given HierarchyId datatype overview in my earlier post. Also explained some of HierarchyId functions in post with details as well. Please go through to the overview and some function details which I explained in my previous posts. In this article I am going to demonstrate following items.

1. How to get the up-line nodes?
2. How to get down-line nodes?

Hierarchies functions will give you the answer to all above questions. Let's demonstrate the answers with examples. Before going ahead, I would like to create a hierarchy data structure by following a script.

-- Create database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

The structure of HierarchyId data looks as follows,

(Clink on image to enlarge)

Now we have time to demonstrate the answers one by one here.

1. How to get the up-line nodes?

GetAncestor(n) : This function will help us to get up-line of the particular hierarchy node. Looking at the script, which will find first up-line node of all hierarchy nodes.

-- GetAncestor(n)
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetAncestor(1).ToString() AS ParentNode,
-- Here 1 is used to get 1st u-line node
-- You can use n to get nth up-line node
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

Please change value to 2 as GetAncestor(n) in place of 1 in script and get the output, You will get more idea.

2. How to get down-line nodes?

IsDescendantOf() : This function will give us the down-line noes of a particular node. Let us run the script and clear our answer. We are viewing a downline node of hierarchy node 1 by script.

-- IsDescendantOf()
SELECT  
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(HIERARCHYID::Parse('/1/')) = 1
-- Here we have filtered condition to get down-line of node 1.
GO

I hope you are very clear now after demonstrate to how get up-line and down-line of hierarchy node.

The row was not found at the Subscriber when applying the replicated command - Replication error in SQL Server by serverku

Earlier I wrote a post for the same error at Review of some replication issues and workaround, but it is just overview with some other replication errors. Please read it if you have not visited those posts and hope you will enjoy and help you much. Let me elaborate the error with proper example. For example, I have already created objects and configured replication, just need to use those objects. Let me introduce them,

• Primary database : Test
• Secondary database : Test1
• Replicated table : dbo.sample1

I am using the same server in the example as publisher, distributor and subscriber. First need to check records in tables of both databases.

-- Selecting  records from table of publisher database 
SELECT * 
FROM   test.dbo.sample1 (nolock) 

-- Selecting  records from table of subscriber database 
SELECT * 
FROM   test1.dbo.sample1 (nolock) 

How to raise an error manually?
To raise mention error in tile I will remove one record from a table in subscriber database to create inconsistency, then will update same deleted records and insert one more record in table of publisher database. Let us see what will happen then,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4' 

After running above script we will review replication monitor window and you will see error because it is trying update row at the subscriber side, but it is not exist and therefore it will raise an error. Due to this, new inserted records (id = 4) will not populate at subscriber side.

Solution 
Now we have to do some workaround to get it resolved, but first we need to get missing row details which was deleted at subscriber side. You can see sequence number in the above image from which we can get the same information. Run following script in distribution database and see the output,

USE distribution 

go 

SELECT * 
FROM   dbo.msarticles m 
WHERE  EXISTS (SELECT mc.article_id 
               FROM   msrepl_commands mc 
               WHERE  mc.xact_seqno = 0x0000002200000048000300000000 
                      AND mc.article_id = m.article_id) 

EXEC Sp_browsereplcmds 
  @xact_seqno_start = '0x0000002200000048000300000000', 
  @xact_seqno_end = '0x0000002200000048000300000000' 

(Click on image to enlarge)
The result is clearly showing article and missing row details. Let me apply that missing row at subscriber side and then review replication monitor again.

-- Inserting missing record in table in subscriber database 
INSERT test1.dbo.sample1 
SELECT 2, 
       'test2' 

After inserting above record the issue will get resolved, which you can see in the image below. Missing record and the newly inserted record (id = 4) also applied,

 Hope you enjoyed this post and it will be fine you put your innovative ideas here for any alternative solution and opinion

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up - Issue in SQL Server Replication by serverku

Hope you read my earlier post of "Replication components are not installed on this server" issue, you may like it. One day suddenly replication went to high latency and I clicked it during monitoring. I opened the replication monitor, during analysis I found one message which was showing latency from publisher to distributor due to some issue. The message is as follows,

“Replicated transactions are waiting for next Log backup or for mirroring partner to catch up”.

As per message i checked the transaction log backups were happened or not, checked it and log backups were happening . Finally one option is pending to review and it is mirroring. I checked the status of mirroring  and see the  principal database went to synchronizing mode. May be mirroring went in synchronizing mode due to heavy or so many transactions in the route to apply at mirror database. So replication is waiting  to be synchronized status of principal database. I exactly do not know why replication went on waiting even transaction log backups were happening. I checked online solution and received some of the solutions from here and it suggests following,

1. EXEC sp_replicationdboption 'PublisherDB','sync with backup',false
This means that not need to backed up of all transactions before being delivered to the distribution database. Please visit this option here, which sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

2. Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here.

I never applied these suggestions. Replication and mirroring both are using transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize the mirror database. So I turned off mirroring and monitored replication status. Finally replication was succeeded to remove the delay and applied all pending commands. Then I configured to mirror again after doing with replication sync.

Conclusion : We have two options, either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depends on priority and importance. It will be better to go for correct solution to avoid such issue. But question is here, why replication went on waiting for a synchronized of mirroring even transactions log backups happened while? I would like you to share if received such issue and solution or any opinion which you applied to resolve it. This may help to me and all people facing the same issue.

Script to get table size statistics - SQL Server by serverku

Recently we have seen the post for database size information and the script used for the same. Hope you liked that post. Sometime we need to have details like database size for growth and disk planning we viewed in an earlier post, the same way I am repeating this post in details like earlier size statistics for database and now it is for tables. You have seen in an earlier post we used sp_spaceused to get database size information, but what about if we pass the table name as parameter, I will return for particular table size statistics in one user database,

USE [AdventureWorksLT2008]
GO
EXEC sp_spaceused '[SalesLT].[Product]'; 

/* Output :

name     rows  reserved  data    index_size  unused
-------- ----- --------- ------- ----------- ------
Product  295   928 KB    808 KB  96 KB       24 KB

*/

The same information can be achieved for all tables using a loop (while loop or cursor ) or sp_msforeachtable system procedure,

USE [AdventureWorksLT2008]
GO
CREATE TABLE #tablesizestatistics 
  ( 
     Tablename  VARCHAR(100), 
     [rows]     VARCHAR(100), 
     reserved   VARCHAR(50), 
     data       VARCHAR(50), 
     index_size VARCHAR(50), 
     unused     VARCHAR(50) 
  ) 

EXEC Sp_msforeachtable 
  @command1='INSERT INTO #TableSizeStatistics exec sp_spaceused ''?''' 

SELECT * 
FROM   #tablesizestatistics 

/* Output :

Tablename                        rows   reserved  data    index_size  unused
-------------------------------- ------ --------- ------- ----------- -------
BuildVersion                     1      16 KB     8 KB    8 KB        0 KB
Address                          450    336 KB    72 KB   160 KB      104 KB
Customer                         847    512 KB    272 KB  136 KB      104 KB
CustomerAddress                  417    80 KB     32 KB   48 KB       0 KB
Product                          295    928 KB    808 KB  96 KB       24 KB
ProductCategory                  41     48 KB     8 KB    40 KB       0 KB
ProductDescription               762    248 KB    144 KB  56 KB       48 KB
ProductModel                     128    248 KB    56 KB   104 KB      88 KB
ProductModelProductDescription   762    112 KB    48 KB   64 KB       0 KB
SalesOrderDetail                 542    120 KB    40 KB   80 KB       0 KB
SalesOrderHeader                 32     64 KB     8 KB    56 KB       0 KB
ErrorLog                         0      0 KB      0 KB    0 KB        0 KB

*/

You may use a loop instead of above method and run sp_spaceused inside it to get for each table. But without a loop and sp_msforeachtable and sp_spaceused we have another script which help us to get each tables size statistic,

USE [AdventureWorksLT2008]
GO
SELECT Schema_name(t.schema_id) + '.' + t.name        AS TableName, 
       p.rows                                         AS Rows, 
       Sum(a.total_pages) * 8                         AS TotalSpace_KB, 
       Sum(a.used_pages) * 8                          AS UsedSpace_KB, 
       (Sum(a.total_pages) - Sum(a.used_pages)) * 8 AS UnusedSpace_KB 
FROM   sys.tables t 
       INNER JOIN sys.indexes i 
               ON t.object_id = i.object_id 
       INNER JOIN sys.partitions p 
               ON i.object_id = p.object_id 
                  AND i.index_id = p.index_id 
       INNER JOIN sys.allocation_units a 
               ON p.partition_id = a.container_id 
GROUP  BY Schema_name(t.schema_id) + '.' + t.name, 
          p.rows 
ORDER  BY totalspace_kb DESC

/* Output :

TableName                                Rows       TotalSpace_KB  UsedSpace_KB  UnusedSpace_KB
---------------------------------------- ---------- -------------- ------------- --------------
SalesLT.Product                          295        928            904           24
SalesLT.Customer                         847        512            408           104
SalesLT.Address                          450        336            232           104
SalesLT.ProductDescription               762        248            200           48
SalesLT.SalesOrderDetail                 542        120            120           0
SalesLT.ProductModelProductDescription   762        112            112           0
SalesLT.ProductModel                     128        112            104           8
SalesLT.CustomerAddress                  417        80             80            0
SalesLT.SalesOrderHeader                 32         64             64            0
SalesLT.ProductCategory                  41         48             48            0
dbo.BuildVersion                         1          16             16            0
dbo.ErrorLog                             0          0              0             0

*/

Hope you enjoyed this post and this may help you to get same table statistics. I would like you to share if we can have some other scripts or some additional information for tables.

SQL Server is unable to complete the New Publication Wizard - Replication Error by serverku

The last time we saw the issue we faced during setup of replication and it was due to original server name change. We came out from one issue But today I am writing for another issue which I faced in the same for replication configuration. I think this is something bad happen during workaround for the solution earlier, by mistake, something went wrong while old server drop and it were used in replication. Even we will look for the solution for this too. So let us move on the error which I get on initial stage of replication configuration while adding publication using Publication Wizard,

“TITLE: New Publication Wizard
------------------------------
SQL Server is unable to complete the New Publication Wizard.

------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Invalid object name 'msdb.dbo.MSdistributiondbs'. (Microsoft SQL Server, Error: 208)”

It seems these objects getting destroyed while workaround for earlier error. Finally, I got solution online as how to create those objects in msdb database,

USE MSDB
GO

CREATE TABLE [MSdistributiondbs] (
 [name] [sysname] NOT NULL 
 ,[min_distretention] [int] NOT NULL
 ,[max_distretention] [int] NOT NULL
 ,[history_retention] [int] NOT NULL
) 
GO

CREATE TABLE [dbo].[MSdistpublishers] (
 [name] [sysname] NOT NULL 
 ,[distribution_db] [sysname] NOT NULL 
 ,[working_directory] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
 ,[security_mode] [int] NOT NULL 
 ,[login] [sysname] NOT NULL 
 ,[password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
 ,[active] [bit] NOT NULL 
 ,[trusted] [bit] NOT NULL 
 ,[thirdparty_flag] [bit] NOT NULL 
) 
GO

Above objects created and finally I was succeeding to go ahead for my next steps!

Changing Columns to Rows Using UNPIVOT - SQL Server by serverku

I have already posted the article previously with my PIVOT. As PIVOT same, but working reversely and it is UNPIVOT. I do not want to miss the post using UNPIVOT. With PIVOT we can get aggregate data with Rows as Columns. With UNPIVOT operation, we can change Columns to Rows without aggregation.

Let me prepare small example and explain as how how can we use UNPIVOT and changing column as Rows. Before go ahead to run the script of UnPivot, we will create a database and table objects.

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblUnPivot','U') > 0)
DROP TABLE TblUnPivot


CREATE TABLE TblUnPivot
(
 ItemCode int,
 ItemName varchar(100),
 Red int,
 Green int,
 Blue int
)

GO

-- Inserting some sample records

INSERT INTO TblUnPivot
SELECT 1,'Samsung Mobile', 1, 1, 1
UNION ALL
SELECT 2,'Nokia Mobile', 1, 2, 3
UNION ALL
SELECT 3,'Motorola Mobile', 2, 3, 2

GO

Now we will check the original table data and also using UnPivot as well. So we will run both scripts for the same.

-- Getting table data
select 
 ItemCode,
 ItemName,
 Red,
 Green,
 Blue
from TblUnPivot

GO

-- Getting  Pivot and changing columns to rows
SELECT
 ItemCode,
 ItemName,
 ItemColour,
 ItemCount
FROM
 (
    SELECT 
      ItemCode,
      ItemName,
      Red,
      Green,
      Blue
    FROM TblUnPivot
 ) AS UP
UNPIVOT
(
  ItemCount FOR ItemColour IN (Red, Green, Blue)
) AS upv

GO

You can review here and see how the UNPIVOT is working. Let me share your experience with UNPIVOT.

Replication components are not installed on this server - Error while adding subscriber in replication by serverku

Recently, when I was working on replication task. It has been to just add new subscribers to an existing publication. Some of their added successfully but some of failed due to replication component is missing. Before you go ahead into details I would like to read some other tips and posts related to replication,

1. Review of some replication issues and workaround
2. Finding objects replicated schema/data or schema only published in replication
3. Get Publications, Articles, Distributors and Subscribers for Replication
4. Finding Stored Procedures, Functions and Views published in replication
5. Find the details of publications, subscribers and articles at once in replication

Now moving here to go ahead with this post and the issue which I faced during adding new subscribers to publication. The issue is as the following which was raised as the message,

“Replication components are not installed on this server. Run SQL Server setup again and select the option to install replication.”

This message gives complete and clear information about exact issue and give suggestion too, as what need to do to complete this task. Just need to add that missing component for replication. Install replication component from SQL Server installation and select that component,

Adding feature to an existing instance,

After completing above steps of installation, I was succeeding to add subscribers to a publication. This is the common post, but hope you liked it.

Database size information - SQL Server by serverku

This is common information which DBA required on a daily basis for monitoring or reporting purposes. This is basic details which help us to get the database growth statistics on daily, weekly and monthly basis so we can have an idea for the disk space management. We can dump daily database statistics and make database size and growth reports. You all know about the script and you may use too, even I would like to share. System stored procedures help us to get the same database information, e.g sp_helpdb stored procedure give details,

EXEC sp_helpdb 'DEMO';

/* Output :

name       db_size   owner  dbid   created     status           compatibility_level
------------------------ ------ ------ ----------- ---------------- -------------------
DEMO       13.25 MB  sa     19     Apr 28 2015 Status=ONLINE,...   110

 
name       fileid filename                                                                      filegroup size     maxsize        growth  usage
-------   -------- ------------------------------------------------------------------------------ --------- ------- ------------   -------- -------
DEMO       1  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf     PRIMARY   9280 KB  Unlimited      1024 KB data only
DEMO_log   2  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf NULL      4288 KB  2147483648 KB  10%     log only

*/

This is all information for database which returned by sp_helpdb by passing the database name as a parameter. If you do not wish to pass the database name, then it will return all databases without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing.

USE DEMO
GO
EXEC sp_spaceused;

/* Output :

database_name  database_size      unallocated space
-------------- ------------------ ------------------
DEMO           13.25 MB           3.52 MB

reserved      data               index_size         unused
------------- ------------------ ------------------ ---------
5680 KB       3064 KB            2024 KB            592 KB


*/

Now I would like to get it with some other scripts which give details for each database and their physical files.

USE master 
GO 

SELECT db.[name]                  AS 'DBName', 
       af.name                    AS 'LogicalName', 
       af.[filename]              AS 'PhysicalName', 
       ( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 1024 ) 
              AS 
                NUMERIC(18, 2)) ) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 

/* Output :

DBName                LogicalName               PhysicalName                                                                                FileSize_MB
------------------- --------------------------- ---------------------------------------------------------------------------------------- ---------------
master                master                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\master.mdf                 4.88
master                mastlog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\mastlog.ldf                1.75
tempdb                tempdev                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\tempdb.mdf                 8.00
tempdb                templog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\templog.ldf                0.50
model                 modeldev                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\model.mdf                  4.06
model                 modellog                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\modellog.ldf               1.00
msdb                  MSDBData                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBData.mdf               22.31
msdb                  MSDBLog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBLog.ldf                19.63
test                  test                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test.mdf                   4.06
test                  test_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test_log.ldf               1.31
test1                 test1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1.mdf                  8.00
test1                 test1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1_log.ldf              19.63
Repl1                 Repl1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1.mdf                  23.00
Repl1                 Repl1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1_log.ldf              2.50
SBExternal            SBExternal                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal.mdf             4.06
SBExternal            SBExternal_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal_log.ldf         1.02
SBExternalReceiver    SBExternalReceiver        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver.mdf     4.06
SBExternalReceiver    SBExternalReceiver_log    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver_log.ldf 1.02
importdb              importdb                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb .mdf              4.06
importdb              importdb _log             C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb _log.ldf          1.02
import                import                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import.mdf                 9.06
import                import_log                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import_log.ldf             2.81
SchemaSync            SchemaSync                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync.mdf             4.06
SchemaSync            SchemaSync_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_log.ldf         1.02
SchemaSync_New        SchemaSync_New            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New.mdf         4.06
SchemaSync_New        SchemaSync_New_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New_log.ldf     1.02
hMailServer           hMailServer               C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer.mdf            4.06
hMailServer           hMailServer_log           C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer_log.ldf        1.02
AdventureWorksLT2008  AdventureWorksLT2008_Data D:\Data\AdventureWorksLT2008_Data.mdf                                                       8.31
AdventureWorksLT2008  AdventureWorksLT2008_Log  D:\Data\AdventureWorksLT2008_Log.ldf                                                        18.00
powershelldb          powershelldb              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb.mdf           4.06
powershelldb          powershelldb_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb_log.ldf       1.02
SMOSimple_DB          SMOSimple_DB              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB.mdf           4.06
SMOSimple_DB          SMOSimple_DB_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB_log.ldf       1.02
Merge_Repl_Sub        Merge_Repl_Sub            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub.mdf         9.00
Merge_Repl_Sub        Merge_Repl_Sub_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub_log.ldf     3.75
DEMO                  DEMO                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf                   9.06
DEMO                  DEMO_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf               4.19

*/

This is details returned by each physical file of all databases, but now the same script with minor changes return whole database size details without physical files detail.

USE master 
GO 

SELECT db.[name]                           AS 'DBName', 
       Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS 
                        NUMERIC(18, 2)) )) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] 

/* Output :

DBName                FileSize_MB
--------------------- -------------
master                6.63
tempdb                8.50
model                 5.06
msdb                  41.94
test                  5.37
test1                 27.63
Repl1                 25.50
SBExternal            5.08
SBExternalReceiver    5.08
importdb              5.08
import                11.87
SchemaSync            5.08
SchemaSync_New        5.08
hMailServer           5.08
AdventureWorksLT2008  26.31
powershelldb          5.08
SMOSimple_DB          5.08
Merge_Repl_Sub        12.75
DEMO                  13.25

*/

These are the scripts which I want to share with you which may help you and I would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.

SQL Server is unable to connect to server 'ServerName' - Error while setup replication by serverku

One day when I was setting up replication in my local machine, when I clicked to create a new publication from replication tab, I faced one error and the details,

Error :
TITLE: New Publication Wizard
------------------------------
SQL Server is unable to connect to server 'PARESH\MSSQLSERVER2012'.
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server.
Specify the actual server name, 'ADMIN\MSSQLSERVER2012'. (Replication.Utilities)

This is because of earlier my machine name was “ADMIN” and SQL server installed at that time. After some time I changed my machine name to “PARESH”. Currently SQL Server instance showing “PARESH\MSSQLSERVER2012” from SSMS and showing “"ADMIN\MSSQLSERVER2012” while running @@SERVERNAME from query analyzer. So this is the reason why I am facing above mentioned error. This will raise same error while trying to configure replication and the default SQL instance connected with “(local)” or “.” as a name.

Solution :
I have one option which we have to connect SQL Server with the actual server name and it will be done with change SQL server name to machine name\instance name first. You can change the SQL Server instance name with following script,

EXEC sp_dropserver 'ADMIN\MSSQLSERVER2012' -- <Old Name\Instance>
GO

EXEC sp_addserver 'PARESH\MSSQLSERVER2012','local' -- <New Name\Instance>
GO

When you run a first statement which in above mentioned script, you may face the following error too,

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'ADMIN\MSSQLSERVER2012'.

And we have solved too,

EXEC sp_dropremotelogin 'ADMIN\MSSQLSERVER2012'; -- <Old Name\Instance>
GO

After running all steps we can go ahead for the next step of replication. To check renamed server name with @@SERVERNAME. Among you also faced this issue also. Can we have the other options for the solution here?

Archive old database backup files using forfiles.exe and FOR /F by serverku

Before so many posts published I posted to the old database backup files archive using TSQL and using SSIS. Hope you visit that post and you liked them. I am repeating the same thing here, but it will be with two different methods, the first one with FOR /F command which we saw last time to copy database backups to another location too, second one with forfiles.exe. Let us move on the first method.

FOR /F :  As we discussed earlier post, it used same to traverse rows from generated file from SQLCMD command. Here we do not have values with delims (,) and we have only one values to grab and assign in variables.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb -Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date < DATEADD(Day,-7,GETDATE()) " -o "C:\BackupFile.txt"

FOR /F "tokens=1,1 skip=2 delims=," %%G IN (C:\BackupFile.txt) DO del %%G

Please make sure SQLCMD and FOR /F should be in single line individualized. Running above code and captured snapshot as follows,

forfiles.exe : It is used to delete files in giving directories and subdirectories with a specified day or date. Syntax of it is following, Here /P is a directory, /m for search criteria, /S for subdirectories search, /d for days or date and /C command to fire. Please visit this site for more detail.

forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]

Let us run it and capture snap again with the method,

Hope you have some other methods to archive old database backups. Request to share here!

Copy database backup files using SQLCMD and FOR /F commands by serverku

Recently we have done with the conversion of the same topic to copy database backup files to an external drive using XCOPY only. That script copy database backup files created on current day from source to destination, So we can schedule that code to run one time only per day to avoid duplicate file copy, otherwise duplicate files may copy for further run.So I used another alternative solution which we can schedule recursive and no chance for duplicate file copy, So I am sharing the same here.

In this method I will use backupset and backupmediafamily system tables from msdb database to get database backup details for a particular period and and process them for a copy.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb 
-Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name +','+'\\ExternalDrivePath\DBBackup\'+
HOST_NAME()+'\'+ bs.Database_name + '\' +
 CASE WHEN BS.TYPE = 'D' then 'FULL' WHEN BS.TYPE = 'I' then 'DIFF' else 'TRN' End + '\'  as BackupFiles
FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf 
on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date > DATEADD(HOUR,-6,GETDATE()) "
 -o "D:\Batchfiles\BackupFile.txt"

FOR /F "tokens=1,2 skip=2 delims=," %%G IN (C:\DatabaseBackup\BackupFile.txt) DO xcopy /Y %%G %%H

I have created above code in the batch, Please make sure SQLCMD and FOR /F should be in single line individualized.

SQLCMD will generate a text file having source full file path and destination path with comma separated. Destination path I made a dynamically with ServerName + DatabaseName + Backup Type for pattern. If the destination path doesn't exist, then it will be created by a process.

FOR /F will traverse each row in the generated text file and grab the values to process for copy. The options which I used are tokens to read first and second columns separated by comma, skip option to skip first two lines (skip header and line), delims (here comma) to separate values and variables to grab values. XCOPY used to copy files from source to destination. XCOPY will create the destination path if not exists dynamically, so I used XCOPY instead of the COPY command.

Let's run the batch file and checking for database backups created taken in last 6 hours, review generated text files and copy process,
Text file (BackupFile.txt)

BackupFiles
----------------------------------------------------------------------------------------------------------------
C:\DatabaseBackup\DemoDB\Full\DemoDB_20121019_234500.bak,\\ExternalDrivePath\DBBackup\PARESH\DemoDB\FULL\
C:\DatabaseBackup\SampleDB\Full\SampleDB_20121019_133000.bak,\\ExternalDrivePath\DBBackup\PARESH\SampleDB\FULL\
C:\DatabaseBackup\DemoDB\Trn\DemoDB_20121019_051500.trn,\\ExternalDrivePath\DBBackup\PARESH\DemoDB\TRN\

The above file is generated by the SQLCMD command with backup details which need to process through FOR /F command as a further step after it immediately. Which will skip first two lines and process rows one by one till the end of file, split values with comma, assign them to source and destination variables and process them for a copy.

This routine copy database all backup files created last in 6 hours, so we can schedule this routine to every 6 hours every day. I shared two methods here, first was in an earlier post and second on today. Let me know if we have another method. We can use xp_cmdshell command in SQL server for the same, we should avoid it for security.

Alerts for SQL Server security events by serverku

Security is the main thing for servers and SQL servers and we need to trace it for security management. Have to need track who is using it, what they are doing and when they performed. For SQL Server 2005 and later versions we have some DDL events which can help us to achieve this. So let us catch it up. But before going ahead, please visit DDL Trigger and DDL Auditing in SQL Server 2005 and Logon trigger in SQL Server for more information about DDL trigger.
I am presenting here for DDL triggers fire for security events. We can also log all the event data in the table, but I want to describe this post to get al alert for this security event's occurrence. To implement I am creating a DDL trigger in the master database and evaluate for some scenario like login creation, Add server roles and drop logins. You can find the trigger created for the same as following,

CREATE TRIGGER [Trg_TrackLoginManagement]
ON ALL SERVER
FOR 
 DDL_SERVER_SECURITY_EVENTS
AS
BEGIN
SET NOCOUNT ON

DECLARE @data xml,
              @EventType varchar(100),
              @EventTime datetime,
              @ServerName varchar(100),
              @AffectedLoginName varchar(100),
              @WhoDidIt varchar(100),
              @EmailSubject varchar(500),
              @EmailBody varchar(800),
              @EmailRecipients varchar(300),
              @TSQL varchar(4000)
 
SET @EmailRecipients = 'prajapatipareshm@gmail.com'
 
SET @data = EVENTDATA()
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(4000)')

SET @EmailSubject =  @EventType + ' occured by ' + @WhoDidIt + ' on ' +
      @ServerName + ' occured at: ' + convert(Varchar, @EventTime) 
SET @EmailBody = @TSQL 

EXEC msdb.dbo.sp_send_dbmail  
    @recipients = @EmailRecipients
  , @subject = @EmailSubject  
  , @body = @EmailBody  
  , @profile_name = '<ProfileName>' -- Put profile name here
  , @body_format = 'HTML' ;  

END

We are capturing some data which is full information of login name , the events occurred and the date on which it occurred and who did. I collected some snaps after performing and testing some scenario for creating a login, assigning server roles and finally deleting created login after testing, Let me share here.

Above are the alert emails which I received for the events happened to create login, server role assignment and after all deleting the login. If we do not want to continue receiving the alerts for change, then it DDL trigger on the server can be disabled with following statement.

DISABLE TRIGGER [Trg_TrackLoginManagement] ON ALL SERVER
GO

Did you configure any alerts for such DDL events? Share your thoughts here.

SQL Server Logical Reads - What's it? by serverku

Recently, when I was working on the tuning of stored procedures and I experienced with one performance issue, that was high Logical Reads.

Logical Reads?
"Number of pages read from the data cache" - It occurs every time when the database engine request a page from buffer cache, otherwise physical reads occurs if current page is not available in buffer cache. Let us go, through the sample demo and get experience for the logical reads. First, we need require objects, so we are creating database and tables inside it.

USE DEMO
GO

-- Creating a table
IF (OBJECT_ID('TblLogicalReads','U') > 0)
  DROP TABLE TblLogicalReads
GO

CREATE TABLE TblLogicalReads
(
 TranId INT,
 TrnData VARCHAR(100),
 TrnDate DATETIME
)

GO

After creating objects, 49999 records will be inserted by following scripts.

DECLARE @cnt BIGINT

SET @cnt  = 1

WHILE (@cnt < 50000)
BEGIN

INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END

GO

Now we are checking logical reads from the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

/* Output :

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 8 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 59 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 4 ms.
   
*/

You can see here, logical reads are high.

How can we reduce it?
There are many factors to reduce it as it depends on But here, for example, we need to create some required indexes on columns which are used in the queries.

-- Creating indexes on tables 
CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO

CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Finally we are on the stage where we need to review logical reads after creating indexes on tables

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

/* Output : 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/

Hope you understood the logical reads difference and performance impact from the examples given above. You can share your knowledge for the same.

Using force index hint - SQL Server by serverku

I wrote about my previous just learned tips about covering index and will publish my future post for the detail understanding it. We should generate index statistics and index usage report periodically, so we can have more idea of the index utilization. Sometime we require different indexes other than the query optimizer used for the execution for the best performance.

How can we use different indexes other that query optimizer used for execution? You can force the indexes to be used with Index Hint. Here is the small demonstration by example. Please look on below script and created objects for demo.

-- Creating objects
USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblForceIndexHint','U') > 0)
DROP TABLE TblForceIndexHint

CREATE TABLE TblForceIndexHint
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

INSERT INTO TblForceIndexHint
SELECT 
object_id,
name,
create_date,
type_Desc  
FROM SYS.OBJECTS

GO

Now creating one clustered index, Normal non-clustered index and an additional non-clustered index with include means covering index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblForceIndexHint (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblForceIndexHint (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblForceIndexHint (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

To review how force Indexes hint works, We need to get the data with CreateDate filtered. So let us start with there.

-- Keep query optimizer to decides and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE CreateDate >= GETDATE() - 50

Here you see the first query (by Query optimizer) and last query (forcing index hint) have used same index. Reviewing same index hints, but the query will get the data on ObjectId filter from where condition.

-- Keep query optimizer to decide and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE ObjectId >= 1000000

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE ObjectId >= 1000000

Here you see the first query (by Query optimizer) and third query (forcing index hint) have used same index. Please share your experience if you ever used force indexes hint.

Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server by serverku

We have seen example of Changing Rows to Columns Using PIVOT. Hope you liked that post and you enjoyed a lot also. As we know that we need to hard-code the values for pivoting which we have made for color values. For the values updates in table or whenever values changed in the table for the column which we use in pivot then we have to update the script as per that otherwise it won't work or do not give correct information. Also, I got the same comment from a user in the post which you can reach from the above link.

I am going to explain the same scenario and the resolution for the same, but you need to read original post properly. So requesting you to read it first if you did not read. So whenever we add new color values in the table, the same pivot query does not return information about the new values. So let us add new value and prepare the query and run.

-- Inserting one more record in existing table created in earlier post
INSERT INTO TblPivot
(
 ItemCode,
 ItemName,
 ItemColour
)
SELECT 
 6,
 'Samsung Mobile',
 'Silver'

After adding above new value, the pivot query which used in earlier post does not return right result set. Here's the solution for such type of issue we need to make the query with dynamic columns as following, We have got distinct values and embedded as comma separated using ‘[‘ and ‘]’. To make dynamic we can use COALESCE or FOR XML PATH.

DECLARE @ColourColumn varchar(200)
DECLARE @sql varchar(1000)

CREATE TABLE #Colours
(
Colour varchar(500)
)

INSERT INTO #Colours (Colour)
SELECT 
 DISTINCT '[' + ItemColour + ']' 
FROM TblPivot

-- Creating Column Names for Pivot
SELECT @ColourColumn = COALESCE(@ColourColumn + ',', '') +
Colour
FROM #Colours

/*
--OR
SET @ColourColumn = (SELECT  SUBSTRING(
 (SELECT DISTINCT ',' + Colour
  FROM #Colours
  FOR XML PATH('')),2,200000) 
 )
*/

DROP TABLE #Colours

SET @sql = 
'
 SELECT
 *
  FROM
  (
  SELECT 
   ItemCode, 
   ItemName, 
   ItemColour
  FROM TblPivot
  ) AS P
 PIVOT
 (
   Count(ItemName) FOR ItemColour IN ('+@ColourColumn+')
 ) AS pv
'
EXEC (@sql)

I want you to share your ideas for such issues which you faced and the resolutions for the same.

Pivoting on multiple columns - SQL Server by serverku

Last time I have written about pivoting and the next after that post describes for dynamic columns which used for pivoting. We have seen the example and the same example implemented with dynamic columns. You can read both posts which are following.

1. SQL Server - Changing Rows to Columns Using PIVOT
2. Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server

In an earlier post I have applied pivoting on one column name ItemColour but here I would like to introduce pivoting on more than one column. So let us jump on example and implement it for multiple columns. Here below you can find the script to create the required objects for demo.

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
Seq int not null identity(1,1),
ItemCode int,
ItemModel varchar(25),
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
(
 ItemCode,
 ItemModel,
 ItemName,
 ItemColour
)
SELECT 1,'S1024','Samsung Mobile','Red'
UNION ALL
SELECT 2,'N1465','Nokia Mobile','Blue'
UNION ALL
SELECT 3,'N1689','Nokia Mobile','Green'
UNION ALL
SELECT 4,'M1642','Motorola Mobile','Red'
UNION ALL
SELECT 5,'S2358','Samsung Mobile','Green'
UNION ALL
SELECT 2,'N2376','Nokia Mobile','Blue'
UNION ALL
SELECT 1,'S3245','Samsung Mobile','Red'
UNION ALL
SELECT 2,'N3421','Nokia Mobile','Blue'

GO

These are the just sample records in the demo. Now here we are applying pivoting on multiple columns named ItemColour and ItemName. Following queries you can use for the same. So let’s run it and check result set.

SELECT
 Seq, 
 ItemCode,
 ItemModel, 
 ItemColour,
 ItemName
FROM TblPivot

-- Applying pivoting on multiple columns
SELECT
*
 FROM
 (
    SELECT 
  Seq,
  ItemCode,
  ItemModel, 
  ItemName, 
  ItemColour
 FROM TblPivot
 ) AS P

 -- For ItemColour
PIVOT
(
  Count(ItemCode) FOR ItemColour IN ([Red], [Blue], [Green])
) AS pv1

-- For ItemName
PIVOT
(
  Count(ItemModel) FOR ItemName IN ([Samsung Mobile], [Nokia Mobile], [Motorola Mobile])
) AS pv2

GO

You can also implement the above script dynamic as previous post. Let me know your comments if you have something for pivoting.

CTE within CTE - SQL Server by serverku

As per requirement in custom logic, we need to require CTE (Common Table Expression) something like for the hierarchy, to find duplicate and remove data or for some other stuff. Recently i have used CTE within CTE for one logic and maxrecursion option as well.  So we will look at how we can use CTE inside CTE or multiple CTEs. Let us create one requirement. The requirement is we need to find the first and second objects by object types in the database and it should be in ascending order. 

The sample data will be created from the script below. Let us create it first, then we will demonstrate for the logic that need to be created as per requirement.

USE DEMO
GO

-- Creating sample table
IF(OBJECT_ID('TblCTEwithCTE','U') > 0)
  DROP TABLE TblCTEwithCTE

CREATE TABLE TblCTEwithCTE
(
 ObjectNumber INT ,
 ObjectType VARCHAR(50),
 ObjectName VARCHAR(100),
 ObjectCreateDate DATETIME
)

GO

-- Inserting sample records created above
INSERT INTO TBLCTEWITHCTE
(
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
)
SELECT 
 ROW_NUMBER() OVER(PARTITION BY TYPE_DESC ORDER BY TYPE_DESC,CREATE_DATE) as ObjectNumber,
 TYPE_DESC,
 NAME,
 CREATE_DATE 
FROM SYS.OBJECTS

Here we are creating first CTE to get only objects with creating first based or created date by object type categories.

-- Fetching first created objects
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 

SELECT 
 * 
FROM FirstCreatedObjectsCTE

GO

Now we have completed work for the first created objects by object type categories. And it is time to have the second created objects using first CTE and another second CTE to finally come out with an output which having both first created and next created objects. This first created and next created object by object type categories will be shown as column as follows.

-- Original table data.
SELECT 
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
FROM TBLCTEWITHCTE
GO

-- Fetching first created objects in first CTE and using in second CTE for the second created objects.
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 
,

SecondCreatedObjectsCTE
AS
(
 SELECT 
  t.ObjectType as ObjectType,
  c.FirstCreatedObject as FirstCreatedObject,
  t.ObjectName as SecondCreatedObject
 FROM TBLCTEWITHCTE t 
  RIGHT OUTER JOIN 
 FirstCreatedObjectsCTE c
  ON (c.ObjectType = t.ObjectType and t.ObjectNumber = c.ObjectNumber + 1)
)

SELECT 
 * 
FROM SecondCreatedObjectsCTE

GO

Hope you like this, stay tuned from more.