Pivot in SQL Server 2008

Definition: Pivot table is a T-SQL operator that converts one tabled-valued expression into another table.
It transforms the values of a column of a table into multiple columns producing Pivot table.Basically, It rotates the rows into columns and performs aggregation on the desired column.

Use: It is used to produce meaningful informtion from the table data easily and quickly. It converts one table format into another.It is great operator for producing summary tables. It is frequently used in Reports for the means of grouping and aggregation of Report Data.

Annotated Syntax:

SELECT
[first pivoted column] AS ,
[second pivoted column] AS ,
...
[last pivoted column] AS
FROM
(SELECT query that produces the data)
AS (alias for the source query)

PIVOT
(
(aggregation function(column being aggregated))
FOR
[column that contains the values that will become column headers]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS
optional ORDER BY clause

Examples:

Static Pivot Table Query:

                                     

























Dynamic PIVOT Table Query:

UNION vs UNION ALL

Whenever possible use UNION ALL instead of UNION. The difference is that UNION has a “side-effect” of eliminating all duplicate rows and sorting results, which UNION ALL doesn’t do. Selecting a distinct result requires building a temporary worktable, storing all rows in it and sorting before producing the output. In some cases that’s exactly what you need to do, then UNION is good to use. But if you don’t expect any duplicate rows in the result set, then use UNION ALL. It simply selects from one table or a join, and then selects from another, attaching results to the bottom of the first result set. UNION ALL requires no worktable and no sorting (unless other unrelated conditions cause that). In most cases it’s much more efficient. One more potential problem with UNION is the danger of flooding tempdb database with a huge worktable. It may happen if you expect a large result set from a UNION query.

Example

The following queries select ID for all stores in the sales table, and the ID for all stores in the big_sales table, a version of the sales table that has been populated with over 70,000 rows. The only difference between the two solutions is the use of UNION versus UNION ALL. But the addition of the ALL keyword makes a big difference in the query plan. The first solution requires stream aggregation and sorting the results before they are returned to the client. The second query is much more efficient, especially for large tables. In this example both queries return the same result set, though in a different order.

Although the result sets in this example are interchangeable, you can see that the UNION ALL statement consumed less than half of the resources that the UNION statement consumed (UNION is doing 463 logical reads on g_sales table which takes more resources of SQL Server. UNION ALL is doing only 224 logical read and makes it fast). So be sure to anticipate your result sets and in those that are already distinct, use the UNION ALL clause.

DMV 'Dynamic management views' in SQL Server

What are DMVs?

•Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance

•Introduced in SQL Server 2005 •Two Types

–Server-scoped

–Database-scoped

•Dynamic Management Objects (DMO)

–Dynamic Management Views (DMV)

•Can select like a view

–Dynamic Management Functions (DMF)

•Requires input parameters like a function

When and why use?

•Provides information that was not available in previous versions of SQL

•Provides a simpler way to query the data just like any other view versus using DBCC commands or system stored procedures.

Types of DMVs

•Change Data Capture

•Common Language Runtime

•Database Mirroring •Database

•Execution

•Full-Text Search

•I/O

•Index

•Object

•Query Notifications

•Replication

•Resource Governor

•Security

•Service Broker

•SQL Server Extended Events

•SQL Server Operating System

•Transaction

Get list of all DMOs

SELECT name, type_desc

FROM sys.all_objects

WHERE name LIKE 'dm%'

ORDER BY name

Permissions

•Server-scoped –VIEW SERVER STATE

•Database-scoped –VIEW DATABASE STATE

•DENY takes precedence –DENY state or –DENY SELECT on an object

GRANT / DENY Permissions

• GRANT VIEW SERVER STATE TO loginName

• GRANT VIEW DATABASE STATE TO user

• DENY VIEW SERVER STATE TO loginName

• DENY VIEW DATABASE STATE TO user

• Must create user in MASTER first

DENY SELECT ON sys.dm_os_sys_info TO loginName

Specific Types

•Database

• sys.dm_db_partition_stats

(page and row count information)

•Execution

• sys.dm_exec_sessions

(Information about all active user connections and internal tasks)

• sys.dm_exec_connections

(Information about the connections established)

• sys.dm_exec_requests

(Information about each request that is executing (includes all system

processes))

•Execution – Query Plans

• sys.dm_exec_sql_text (DMF)

(Returns text of SQL batch)

• sys.dm_exec_query_plan (DMF)

(Returns the showplan in XML format)

• sys.dm_exec_query_stats

(Statistics for cached query plans)

• sys.dm_exec_cached_plans

(Each query plan that is cached)

•IO

• sys.dm_io_pending_io_requests

(Shows pending I/O requests)

• sys.dm_io_virtual_file_stats

(Shows I/O stats for data and log files)

•Index

• sys.dm_db_index_operational_stats (DMF)

(Shows I/O, locking and access information such as inserts, deletes, updates…)

• sys.dm_db_index_physical_stats (DMF)

(Shows index storage and fragmentation information)

• sys.dm_db_index_usage_stats (DMV)

(Shows how often indexes are used and for what type of operation)

•Missing Indexes

– sys.dm_db_missing_index_details

– sys.dm_db_missing_index_groups

– sys.dm_db_missing_index_group_stats

– sys.dm_db_missing_index_columns

•SQL Operating System

• sys.dm_os_schedulers

(Information about processors)

• sys.dm_os_sys_info

(Information about the computer and about resources available to and consumed by SQL Server)

• sys.dm_os_sys_memory

(Memory information from the operating system)

• sys.dm_os_wait_stats

(Information about all the waits encountered by threads that executed)

•Clear Wait Stats

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

• sys.dm_os_buffer_descriptors

(Information about all the data pages that are currently in the SQL Server buffer pool)

I hope this information will be useful for you. Please write your comments and related question. I will be happy to answer them.

SQL Server 2008 MERGE Statement

MERGE Statement

What do you do when your application logic requires that you INSERT a record if the record doesn’t exist, or UPDATE the record if it does exist? In SQL Server 2008, It has been simplified the amount of T-SQL code it requires to perform INSERT/UPDATE logic, by implementing the MERGE statement.

You can do more with the MERGE statement then just inserting and updating records as mentioned above. You can also use it to delete records. Another thing you can do with the MERGE statement is to perform a specific number of merge actions based on a TOP clause. The MERGE statement can also generate output that will identify which records where inserted, updated, or deleted.

Following is an example of using MERGE to perform UPDATE and DELETE operations on a table in a single statement

Here I am using AdventureWorks sample database to show this example.

MERGE can be used to update the ProductInventory table in the AdventureWorks sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. The following MERGE statement updates the Quantity column of the ProductInventory table by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. If the number of orders for a product drops the product's inventory to 0, the row for that product is deleted from the ProductInventory table.

USE AdventureWorks;

GO

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;

GO

CREATE PROCEDURE Production.usp_UpdateInventory

@OrderDate datetime

AS

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,

Deleted.Quantity, Deleted.ModifiedDate;

GO

EXECUTE Production.usp_UpdateInventory '20030501'

Advantages of using MERGE statement

1 - With a single statement we can easily implement insert, update, and delete logic to handle criteria for maintaining a table.

2 - The MERGE statement handles all the joining of the source and target. This minimizes the amount of code you need to write to handle merge logic.

3 – It requires less SQL Server resources which make it fast and optimized.