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.

Changing Rows to Columns Using PIVOT - SQL Server by serverku

During working with one logic, I got a chance to work with PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as a column.

Before going ahead to run the script of Pivot, we will create a database and table objects.

CREATE DATABASE DEMO
GO

USE DEMO
GO

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

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)
GO

-- Inerting some sample records
INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
GO

Now we will check the original table data and aggregated data using Pivot. So we will run both scripts for the same.

-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN (Red, Blue, Green)
) AS pv
GO

You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.

Backup Statistics and History - SQL Server by serverku

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at the other place and using for the database restore which used for reporting purposes or used in log shipping purpose. Because database backups are the most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loss.

You can read my earlier posts Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like to share the script which helps us to show the database backup status, history of theirs when they are done based on schedule, at where are taking and when, backup types, backups, physical device and the size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO
 
SELECT
 bs.server_name AS Server, -- Server name
 bs.database_name AS DatabseName , -- Database name
 CASE bs.compatibility_level 
   WHEN 80 THEN 'SQL Server 2000'
   WHEN 90 THEN 'SQL Server 2005 '
   WHEN 100 THEN 'SQL Server 2008'
   WHEN 110 THEN 'SQL Server 2012'
 END AS CompatibilityLevel , -- Return backup compatibility level
 recovery_model AS Recoverymodel , -- Database recovery model
 CASE bs.type 
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file'
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 CASE device_type
   WHEN 2 THEN 'Disk - Temporary'
   WHEN 102  THEN 'Disk - Permanent'
   WHEN 5  THEN 'Tape - Temporary'
   WHEN 105  THEN 'Tape - Temporary'
   ELSE 'Other Device'
 END AS DeviceType, -- Device type
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 bs.compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
 ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC
 
GO

(Click on image to enlarge)

Hope you liked this post.

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server by serverku

When we created a new database, it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from a table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other options other than this. But I am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK, we need to put this hint on table level, so it is required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written on top of the query or stored procedure. Let us look on small demo to elaborate it. First checking here database default isolation level,

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating a database and table objects.

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

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1,

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updated in above session, which are not committed yet in another session. It will be going on waiting to release the lock held by session 1,

We are not closing this transaction here, and created a new session and run following scripts having a NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1,
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008 by serverku

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of the instances of registered SQL Server instances.

There you can see I have registered one, shared SQL Server 2011 instance, under CMS and named it Denali. Under Shared instance, I have registered SQL Server 2008 and same SQL Server instance of Denali which I already registered. Here SQL server 2008 successfully registered but SQL Server 2011 has encountered an error. But how I have registered it which I am going to explain here.

This is because the same SQL instance already registered as shared SQL server.

How can i register same SQL Server Denali or 2011 instance again?

I have changed the port and applied static port as follows from SQL Server TCP/IP properties which we will available on the SQL Server Configuration Monitor.

Then please see the screenshot below as I have registered SQL Server Denali instance with port.

Now both SQL Server registered successfully.

Did you get it earlier? How did you resolve?

Multi Monitor Support of SSMS - A new feature of SQL Server Denali by serverku

I have drafted all the new features and enhancements introduced by SQL Server Denali CTP1 and CTP3.  Multi Monitor Support is the new feature among them.

What can we do?
1. Using this feature we can use multi screen of query analyzer or editor.
2. You can monitor separately it and run the output individually.
3. You can resize all the screens.
4. You can drag and drop at the place you want.
5. You can use Registered servers and object explorer and it's details with multi screens.

For more idea, You can see the below screen shows which I have captured during enjoyed with it.

1. This screen shot has multi screens of query analyzer with SSMS.

2. This screen shot captured during docking/undocking the screens.

Hope you like this post.

"The database owner SID recorded in the master database differs from the database owner SID recorded in database" - SQL CLR DLL Register error in SQL Server by serverku

Recently, while working with SQL CLR functionality and created DLL for the SQL CLR. But while registering this DLL in the database I got one surprised error. Let's show you the script so you have more idea. We have a script to register the DLL as following,

SP_CONFIGURE 'clr enabled',1
GO
RECONFIGURE
GO

USE SQLCLRDb 
GO

CREATE ASSEMBLY [SQLCLR_ASSEMBLY] 
FROM 'C:\SQLCLR_ASSEMBLY.dll' WITH permission_set = UNSAFE 

GO 

The error is,

The database owner SID recorded in the master database differs from the database owner SID recorded in database.
You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement.

The solution for this issue is which we have the script below. This script will change dbowner of the running database and make it trustworthy on.

USE SQLCLRDb 
GO

ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
go

EXEC SP_CHANGEDBOWNER 'UserName'
GO

After running above query, I come out of the issue and registered SQL CLR DLL successfully. I think you also suffered same or different issues with SQL CLR. Please comment your issues and the solution for the same.

Multi server Query with Central Management Servers - SQL Server 2008 by serverku

Before SQL Server 2008, when we need to gather all information and details related to a server or database level, we must run the script individually by connecting each SQL Server instance. But SQL Server 2008 came up and easy our work for that. It has introduced a new feature - Central Management Servers (CMS).

With Central Management Servers we can configure and register SQL Server instances with shared SQL Server instances. Then we run the query against all the SQL instance and get the details for all instances. Let's you demonstrate the same in details here.

1. How to open Central Management Servers?

Go to View --> Registered Servers 
or
press Ctrl + Alt + G.

2. How can register SQL Server instances in CMS?

Expand Database engine from Registered Servers. Right click on the CMS and click on Register SQL Server Management. A new screen will appear below,

In the above, I have registered SQL Server Denali instance, which will be shared SQL Server instance. Now I am creating a new SQL Server Group under CMS and then register SQL Server 2008 and SQL Server 2011 by right click on the group and then go to the link of registration and then go on the same way as I did for SQL Server Denali instance.

3. How can we perform multi server query against all SQL Server ?

Go on right click on Shared SQL Server instance under CMS and click on New Query.

Let's do here same and execute the query and see what will be the result?

This feature very help us to run the script against all the registered SQL Server instances. Hope you liked this post.

New Logical functions coming in SQL Server Denali CTP3 by serverku

As I have started to learn new functions arrived by SQL Server Denali CTP3 version and I have posted some of them. You can learn Analytical functions , Conversion functions and String functions. You can also get the list of all the features coming in SQL Server 2011 CTP3.

Now diverting on this post for the new Logical functions which are following,

1. IIF : It returns one of two arguments, depending on the evaluation of expression. It has require three arguments, first is condition, second and third are the values. Depending on the evaluation of first condition second or third values will be returned, means if the first condition is true, then it will return second value and if it is false then it will return third value. It returns the data type with the highest precedence from the types in true_value and false_value. Let us evaluate it to know it better.

DECLARE @IsDone bit
SET @IsDone = 1
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Above code returns "Success". Now les us assign the NULL value to variable then verify.

DECLARE @IsDone bit
SET @IsDone = NULL
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Here it returns "Failed" as output. Now elaborate it with more examples.

DECLARE @IsDone bit
DECLARE @FirstVal varchar(10)
DECLARE @SecondVal varchar(10)
SET @IsDone = 1
SET @FirstVal = NULL
SET @SecondVal = NULL

SELECT iif(@IsDone = 1,@FirstVal, @SecondVal)
GO

Above code returns NULL as resulted output. What happen if we pass directly NULL in both first and second values?

DECLARE @IsDone bit
SET @IsDone = 1

SELECT iif(@IsDone = 1,NULL, NULL)
GO

It comes with following error,

"Msg 8133, Level 16, State 1, Line 4
At lease one of the result expressions in a CASE specification must be an expression other than the NULL constant."

Now second turns come for CHOOSE function.

2. CHOOSE : It returns the value at the specified index from among the lists. It has require the first argument as the Index and hen we can pass multiple parameters for the values. It returns the data type with the highest precedence from the set of types passed to the function. Let elaborate it with sample examples.

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,'First','Second','Third')
GO

It returns "Second" value because it belongs to the second index.

DECLARE @Index int
SET @Index = 0

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Above query returns NULL as output.

DECLARE @Index int
SET @Index = NULL

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Same as earlier query it also returns NULL as resulted output. What happen if we pass all the values with NULL?

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,NULL,NULL,NULL)
GO

It's also come up with a NULL. Hope you liked these functions. Stay tuned for more posts.