Saturday, January 1, 2011

Dynamic Management View

Brief Summary

  • Introduced in 2005
  • They are system views
  • Basically they are very useful to DBAs to get database / server level information in a most simplest and fastest way
  • Really helpful in monitoring for DBAs
  • Will be prefixed with dm_
  • Will be part of sys schema


Total 12 categories

  • DB Related
  • CLR Related
  • Full Text Search Related
  • Transaction Related
  • Index Related
  • OS Related
  • Replication Related
  • DB Mirroring Related
  • Execution Related
  • I/O Related
  • Query Notification Related
  • Service Brocker Related

Few Useful DMVs

  • sys.dm_db_index_physical_stats
  • sys.dm_os_performance_counters
  • sys.dm_exec_query_stats
0 comments

PIVOT and UNPIVOT


Overview
  • Introduced in SQL 2005
  • PIVOT transforms row level data to column level
  • UNPIVOT transforms column level data to row level data
  • Really useful in cross tab functionality


Example of PIVOT
  • Let's us take following example.

create table Income(EmpName nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

insert into Income values ('Nisarg', 'FRI', 100)
insert into Income values ('Nisarg', 'MON', 300)
insert into Income values ('Nisarg', 'WED', 500)
insert into Income values ('Nisarg', 'TUE', 200)
insert into Income values ('Swara', 'WED', 900)
insert into Income values ('Nisarg', 'FRI', 100)
insert into Income values ('Swara', 'MON', 300)
insert into Income values ('Swara', 'FRI', 300)
insert into Income values ('Megha', 'TUE', 500)
insert into Income values ('Megha', 'TUE', 200)
insert into Income values ('Nisarg', 'MON', 900)
insert into Income values ('Megha', 'FRI', 900)
insert into Income values ('Megha', 'MON', 500)
insert into Income values ('Nisarg', 'FRI', 300)
insert into Income values ('Nisarg', 'WED', 500)
insert into Income values ('Nisarg', 'FRI', 300)
insert into Income values ('Swara', 'THU', 800)
insert into Income values ('Nisarg', 'TUE', 100)
insert into Income values ('Nisarg', 'THU', 300)
insert into Income values ('Megha', 'WED', 500)
insert into Income values ('Megha', 'THU', 800)
insert into Income values ('Swara', 'TUE', 600)

  • Now the table is looking like this

EmpName
IncomeDay
IncomeAmount
Nisarg
FRI
100
Nisarg
MON
300
Nisarg
WED
500
Nisarg
TUE
200
Swara
WED
900
Nisarg
FRI
100
Swara
MON
300
Swara
FRI
300
Megha
TUE
500
Megha
TUE
200
Nisarg
MON
900
Megha
FRI
900
Megha
MON
500
Nisarg
FRI
300
Nisarg
WED
500
Nisarg
FRI
300
Swara
THU
800
Nisarg
TUE
100
Nisarg
THU
300
Megha
WED
500
Megha
THU
800
Swara
TUE
600

  • Now, we want result something like this. Basically we want total income of each person for each day as a matrix shown below.

EmpName
MON
TUE
WED
THU
FRI
Megha
500
700
500
800
900
Nisarg
1200
300
1000
300
800
Swara
300
600
900
800
300

  • Now, using PIVOT, we can achieve the same thing with very simple query

SELECT        EmpName, [MON],[TUE],[WED],[THU],[FRI]
FROM        Income
PIVOT (
SUM (IncomeAmount)
for IncomeDay in
(
[MON],[TUE],[WED],[THU],[FRI]
)
) as TotalIncomePerDay


  • Above query has basically 3 portions
    • SELECT
      • Contains column names. Basic fields (e.g. EmpName) and Pivoted values (Mon, Tue, etc.)
    • FROM
      • Which result set you want to PIVOT
      • In above example, we have used Income table, we can use some query as derived table also
    • PIVOT
      • Aggregation of a field from base table (mentioned in FROM clause)
        • Any aggregation (min, max, avg, sum, etc..) is required, otherwise it will throw an error
      • Pivot fields name (e.g. Incomeday)
      • Pivot Values ([MON], [TUE], etc..)
        • Brackets are not required, but if you would like to have white space in a name of field, then they are required
        • Even though a value does not exist in actual table records, still you can mention, it won't throw any error. e.g. Saturday is not part of any resultset, and if I include SAT as a column, it won't throw any error, and simply it will give me NULL in the final resultset

Example of UNPIVOT
  • Let's us take following example.
  • Assume that we have a table which is having TotalIncome of each employee for each working day as a matrix.
  • Following query will create that kind of data for us

SELECT        EmpName, [MON],[TUE],[WED],[THU],[FRI]
INTO        TotalSalary
FROM        Income
PIVOT (
SUM (IncomeAmount)
for IncomeDay in
(
[MON],[TUE],[WED],[THU],FRI
)
) as TotalIncomePerDay

  • So TotalSalary is looking something like this

EmpName
MON
TUE
WED
THU
FRI
Megha
500
700
500
800
900
Nisarg
1200
300
1000
300
800
Swara
300
600
900
800
300

  • Now we want to transform them into row based data
  • Following query will do the same

SELECT        EmpName, IncomeDay, Income
FROM        TotalSalary
UNPIVOT
(
Income
for IncomeDay in
(
[MON],[TUE],[WED],[THU],FRI
)
) as TotalIncomePerDay

  • Unlike PIVOT, if you put some wrong value in highlighted portion, it will throw an error. e.g. If we add SAT here, but SAT is not part of columns of Income table, so it will throw an error.
  • Once you PIVOT a resultset and then UNPIVOT the same, you won't get actual set of data

Example of Dynamic PIVOT
  • Assume that we have rows without static data and we would like to generate cross-tab report dynamically  based on data available at that time.
  • Let's assume that, we want to generate cross-tab report where rows will be Days and columns will be EmpName.
  • So basically , we are looking for result something like this

IncomeDay
Nisarg
Megha
Swara
FRI
800
900
300
MON
1200
500
300
THU
300
800
800
TUE
300
700
600
WED
1000
500
900

  • Now in that case, following query will give the same

SELECT        IncomeDay, [Nisarg], [Megha], [Swara]
FROM        Income
PIVOT (
SUM (IncomeAmount)
for EmpName in
(
[Nisarg], [Megha], [Swara]
)
) as TotalIncomePerDay

  • Now, let's say, 2 more employees are getting added to the table, in that case, our result will include 2 more columns, but we don't want to change our query. Currently EmpNames are hard-coded but now we want to pull them from a table and dynamically generate a result set of all available employees
  • So in this case, we have to go for dynamic PIVOT

-- Variable Declaration
DECLARE @Query NVARCHAR(MAX)
, @EmpNameXMLString NVARCHAR(MAX)
, @EmpNameString NVARCHAR(MAX)
, @ParmDefinition nvarchar(500);

SELECT @EmpNameXMLString = 'SELECT DISTINCT ''['' + EmpName  + ''],'' FROM Income FOR XML PATH('''')'
SELECT @Query = 'SELECT @EmpNameStringOUT = SUBSTRING((' + @EmpNameXMLString + '), 1,LEN((' + @EmpNameXMLString + '))-1)'
SET @ParmDefinition = N'@EmpNameStringOUT NVARCHAR(MAX) OUTPUT';

-- Building EmpName String
EXECUTE SP_EXECUTESQL @Query, @ParmDefinition, @EmpNameStringOUT=@EmpNameString OUTPUT
--SELECT @EmpNameString

SET @Query =
'
SELECT        IncomeDay, ' + @EmpNameString + '
FROM        Income
PIVOT (
SUM (IncomeAmount)
for EmpName in
(
' + @EmpNameString + '
)
) as TotalIncomePerDay'

EXECUTE SP_EXECUTESQL @Query        
0 comments

CTE (Common Table Expression)


Overview
  • It is called Common Table Expression, introduced in SQL 2005
  • It is kind of temporary result set with a scope of single DML operation (INSERT / UPDATE / DELETE)
  • It will be stored in memory
  • It will last only till the duration of the query
  • It can be self referenced which is the best usage of CTE
  • It is really useful in terms of recursion and deleting duplicate records

Advantages
  • Improved readability
  • Easy maintenance of complex queries
  • Can be defined in views also
  • Really works well for recursion and deleting duplicate records

Disadvantages
  • Above statement should be enclosed with ;
  • Cannot handle millions of records, #TEMP table will be a replacement in that scenario

Basic Syntax

WITH cte_name (optional column list)
AS
(
  Cte_query
)
  statement that uses the above CTE

Recursive Syntax

WITH cte_name (optional column list)
AS
(
  Cte_query --Anchor Member
Union All
Cte_query -- Recursive Member
)
  statement that uses the above CTE

Different Usages

  1. Basic use to replace Table variable or #TEMP table

-- Creating Table
CREATE TABLE Employee (EmployeeID INT, Name VARCHAR(50), ManagerID INT, DOB DATETIME)
-- Inserting Records
INSERT INTO Employee
VALUES
(1,'Kamlesh',NULL, '1960-01-01')
,(2,'Pratibha',1, '1960-05-01')
,(3,'Maulik',1 ,'1980-01-01')
,(4,'Nisarg',1,'1983-01-01')
,(5,'Dipa',3,'1982-01-01')
,(6,'Swara',5,'2008-01-01')
,(7,'Megha',4,'1986-01-01');

-- Query
WITH StartsWithM
AS
(
SELECT Name FROM Employee WHERE Name LIKE 'M%'
)
SELECT * FROM StartsWithM


  1. Using CTE for recurssion

-- Query  (Find out complete hierarchy of Swara)

WITH Hierarchy (EmployeeID, Name, ManagerID, Level)
AS
(
    -- Anchor Member Definition
    SELECT      EmployeeID, Name, ManagerID, 1 Level
    FROM  Employee
    WHERE EmployeeID = 6
    UNION ALL
    -- Recursive Member Definition
    SELECT      E.EmployeeID, E.Name, E.ManagerID, Level + 1
    FROM  Employee E
                INNER JOIN Hierarchy H ON H.ManagerID = E.EmployeeID
)
SELECT     
H.EmployeeID
, H.Name EmployeeName
, Level
, H.ManagerID
, ISNULL(E.Name,'<--No Manager-->') ManagerName
FROM        Hierarchy H
        LEFT JOIN Employee E ON H.ManagerID = E.EmployeeID
        

  1. CTE within CTE / CTE with multiple reference

/* Find out the employees with their managers who born prior to 1985
 We can write without using CTE in a much simpler way but this is just an example of using one CTE inside other CTE */

WITH PriorTo1985
AS
(
SELECT        *
FROM        Employee
WHERE        DOB < '1985-01-01'
),
Manager
AS
(
SELECT        E.Name, ISNULL(M.Name,'No Manager') ManagerMame
FROM        PriorTo1985 P
INNER JOIN        Employee E
ON P.EmployeeID = E.EmployeeID
LEFT JOIN Employee M
ON E.ManagerID = M.EmployeeID
)
SELECT * FROM Manager


  1. CTE within MAXRECURSION Hint

Maximum recursion is 100, if we want to go beyond that then we have to use MAXRECURSION Hint
If we don’t use it, by default it will take 100
We can give number from 0 to 32767

DECLARE        @startDate DATETIME,
@endDate DATETIME

SET                @startDate = '11/10/2011'
SET                @endDate = '03/25/2012';

WITH CTE AS
(
SELECT        YEAR(@startDate) AS 'yr',
MONTH(@startDate) AS 'mm',
DATENAME(mm, @startDate) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
@startDate 'new_date'
UNION ALL
SELECT
YEAR(new_date) AS 'yr',
MONTH(new_date) AS 'mm',
DATENAME(mm, new_date) AS 'mon',
DATEPART(d,@startDate) AS 'dd',
DATEADD(d,1,new_date) 'new_date'
FROM        CTE
WHERE        new_date < @endDate
)
SELECT        yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM        CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
0 comments
Subscribe to: Posts (Atom)