SQL Server - A Procedure to Display Columns as Rows

updated: Oct 12, 2012

As an Oracle DBA, I am used to using command line interface (CLI) for my many DBA tasks. When some long rows are output from a query, it is often very unconvenient to view in CLI windows due to text wrapping at certain lengh. Tom Kyte has developed a procedure in Oracle to display columns as rows. Here I try to accomolish the same thing in SQL Server.

My procedure is called usp_p, the source code can be downloaded here.

Below is an example of using this procedure.

First of all, here is the script (top10_ela.sql ) that displays top 10 sqls ordered by average execution time:

declare @sqltext varchar(4000)

set @sqltext = '
SELECT   top 10
sql_handle
, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
     ( (CASE WHEN statement_end_offset = -1
            THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
      ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
 , (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
 , max_elapsed_time/1000 AS [MaxExecTime in ms]
 , min_elapsed_time/1000 AS [MinExecTime in ms]
 , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, qs.execution_count AS NumberOfExecs
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, creation_time
, last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  s2
WHERE s2.objectid is null
ORDER BY [Avg Exec Time in ms] DESC
';



exec dbadmin.dbo.usp_p @sqltext

go

Below is a sample output from my test sql server instance:

$ sqlcmd -i  top10_ela.sql


OUTPUT COLUMNS AS ROWS - MISSING COLUMN NAME MEANS NULL VALUE

----------------------------------------------
sql_handle                    : 0x0200000027337b02ca2b54f71198517d10d457c2e93900b900000000000000000000000000000000
sql_statement                 : declare StmtCur cursor for
SELECT   top 10
sql_handle
, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
     ( (CASE WHEN statement_end_offset = -1
            THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
      ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
 , (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
 , max_elapsed_time/1000 AS [MaxExecTime in ms]
 , min_elapsed_time/1000 AS [MinExecTime in ms]
 , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, qs.execution_count AS NumberOfExecs
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, creation_time
, last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  s2
WHERE s2.objectid is null
ORDER BY [Avg Exec Time in ms] DESC

Avg Exec Time in ms           :        307
MaxExecTime in ms             :        307
MinExecTime in ms             :        307
Avg CPU Time in ms            :        307
NumberOfExecs                 :          1
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:46PM
last_execution_time           : Oct 12 2012  8:46PM
----------------------------------------------
sql_handle                    : 0x02000000cff2b929b1aebd642c8187892cf718f0921cf8bb00000000000000000000000000000000
sql_statement                 : select * from sys.dm_exec_query_stats;

Avg Exec Time in ms           :          0
MaxExecTime in ms             :          0
MinExecTime in ms             :          0
Avg CPU Time in ms            :          0
NumberOfExecs                 :          1
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:45PM
last_execution_time           : Oct 12 2012  8:45PM
----------------------------------------------
sql_handle                    : 0x020000004bee4622a78521352165a8a19fc021a4e00df3c900000000000000000000000000000000
sql_statement                 : select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [DBAdmin].[sys].[filetable_updates_2105058535] with (readpast) order by table_id
Avg Exec Time in ms           :          0
MaxExecTime in ms             :          0
MinExecTime in ms             :          0
Avg CPU Time in ms            :          0
NumberOfExecs                 :          2
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:46PM
last_execution_time           : Oct 12 2012  8:46PM
----------------------------------------------

Note: I did not verify that every data type can be shown properly as of now, I will probably have to modify the code as time goes.


Update: Oct 19,2012

Learned that SQL Server has a very simple way to turn the query output to XML format by appending "for xml path" at the end.  This has similar effect as output "columns as rows". For example, run: select top 2 * from sys.objects for xml path , it will generate the following XML format:

SQL Server - Create a Policy to Enfore Naming Convention on Stored Procedure

In SQL Server 2008, assuming we need to enforce a naming standard on stored procedures:
All stored procedures in the database (TestData) must start with "usp_" - ("usp" stands for user stored procedure).

Step 1 Create the Condition

1.  In Object Explorer, expand Management, expand Policy Management, expand Conditions, right-click the Conditions node and then click New Condition.
2.  On the Create New Condition, General page, in the Name box, type the name of the new condition: SP Naming
3. Select the Multipart Name facet in the Facet box.
4. In the Expression area, construct the condition expressions : @Name like 'usp_%'.
5. On the Description page, type a description and then click OK.

Step 2 Create the Policy

1. In Object Explorer, expand Management, expand Policy Management, right-click Policies, and then click New Policy.
2. On the Create New Policy, General page, in the Name box, type the name of the new policy: SP Naming Policy.
3. If you want the policy to be enabled as soon as it is created, select the Enabled check box.
4. If the evaluation mode is On demand, the Enabled check box is not available.
5. In the Check condition box, select the existing condition: SP Naming. To edit the condition, select the condition and then click the build (...) button.
6.  In the Against targets box, select the Every StoredProcedure target. Create a new condition TestData DB Only in the Database Node (Facet: Database, Condition: @Name='testdata').
7. In the Evaluation Mode box, select how this policy will behave: On Change: Prevent. Different conditions can have different valid evaluation modes.
8. To limit the policy to subset of the target types, in the Server restriction box, select from limiting conditions or create a new condition.
9. On the Description page, in the Category box, select an optional policy category or create a new one.
10. Type n optional description of the policy.
11.  Click OK.

When the policy is enabled, trying to create a procedure that violates the policy, the following message is received:

Changed database context to 'testdata'.
Policy 'SP Naming Policy' has been violated by 'SQLSERVER:\SQL\TUSNC012LKVT006\DEFAULT\Databases\testdata\StoredProcedures\dbo.HelloWorld3_proc'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp_%''
Policy description: ''
Additional help: '' : ''
Statement: '
create procedure HelloWorld3_proc
as
select 'HelloWorld'
'.
Msg 3609, Level 16, State 1, Server TUSNC012LKVT006, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

SQL Server - test case showing rows moved when creating a clustered index

Recently I have determined to study SQL Server. Of course, this is not the first time I attempt to do so. Just this time I decided not to spend time on Oracle voluntarily and to concentrate on SQL server instead. 

I have noticed that in SQL Server it is highly recommended that every table should have a clustered index. A table with a clustered index (CI) conceptually corresponds to the Index Organized Table (IOT) in Oracle. Basically, the leaf node of the index contains the data block (called B+ tree index). I also found a blog post that attempted to explain why SQL Server needs to use CI to achieve performance while IOT is not used in Oracle as widely as the CI in SQL Sever. But I am still not very clear about the reasons after reading the post.

If a table does not have a CI and we want to create  CI on it, the existing rows should be reorganized to occupy the pages in the sorted key order. This is what I try to confirm with the following test case.

Test case:

-- script: rowmove.sql
-- purpose: demonstrate table rows moved when creating clustered index
-- env    : SQL Server 2008
--

:setvar sqlcmdheaders            40     -- "page size"
:setvar sqlcmdcolwidth          132     -- line width
:setvar sqlcmdmaxfixedtypewidth  32     -- max column width (fixed length)
:setvar sqlcmdmaxvartypewidth    32     -- max column width (varying length)

USE testdata
go

IF OBJECT_ID ( 'mytable', 'U' ) IS NOT NULL 
DROP TABLE mytable;
GO


CREATE TABLE mytable
 (
  id  INT,
  col1 VARCHAR(2000),
  col2 VARCHAR(2000));

INSERT INTO mytable
SELECT 1, REPLICATE('a', 2000), REPLICATE('b', 2000);

INSERT INTO mytable
SELECT 4, REPLICATE('a', 2000), REPLICATE('b', 2000);


INSERT INTO mytable
SELECT 2, REPLICATE('a', 2000), REPLICATE('b', 2000);

INSERT INTO mytable
SELECT 3, REPLICATE('a', 2000), REPLICATE('b', 2000);


go

select 
       a.%%physloc%%                          AS Address,
       sys.fn_PhysLocFormatter(a.%%physloc%%) AS AddressText,
       a.id
FROM   mytable a
ORDER BY 2;
go

print " "
print "  ----  After created clustered index on ID ----"
print " "

go

create unique clustered index index01 
on mytable(id);
go

select 
       a.%%physloc%%                          AS Address,
       sys.fn_PhysLocFormatter(a.%%physloc%%) AS AddressText,
       a.id
FROM   mytable a
ORDER BY 2;
go

Test output:

Changed database context to 'testdata'.

(1 rows affected)
Address    AddressText                      id         
---------- -------------------------------- -----------
0xFA420000 (1:17146:0)                                1
0xFA420000 (1:17146:1)                                4
0xFD420000 (1:17149:0)                                2
0xFD420000 (1:17149:1)                                3

(4 rows affected)
 
  ----  After created clustered index on ID ----
 
Address    AddressText                      id         
---------- -------------------------------- -----------
0x99000000 (1:153:0)                                  3
0x99000000 (1:153:1)                                  4
0xFE420000 (1:17150:0)                                1
0xFE420000 (1:17150:1)                                2

(4 rows affected)

It can be seen that in this test case one page can hold maximum 2 table rows. At the begining, table rows with id=1 and 4 reside on page 17146, and with id=2,3 resides on page 17149. This is understandable as we inserted the rows in the order of 1,4,2,3. After create a CI on ID column, the rows with id=1,2 moved to page 153 and rows with id=3,4 moved to page 17150. So the rows occupies the pages in the sorted order of ID. It should be noted that the pages that belong to the table do not need to physically on the disk files closely to each other.