Friday, October 31, 2014

T-SQL Tips: Modify All Default Constraints in a Database Based on a Common Value


Context

This blog discusses a method that can be applied for doing the modification of default constraints on multiple fields within a database which currently points to one value

Scenario

Recently in one of my projects there was a need for changing default values linked to all the date fields to make them return GMT date time rather than the local date time. This was for bringing on a standardization between the various in-house systems which were at different geographic locations before bringing the data to the centralized Enterprise Datawarehouse (DWH) for deriving various analytic reports.

Solution 

The solution I proposed was a simple query as below

DECLARE @SQL varchar(max)

 SET @SQL = (select 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' DROP CONSTRAINT ' + name + '; ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' ADD CONSTRAINT ' + name + ' DEFAULT getutcdate() FOR ' + COL_NAME(parent_object_id,parent_column_id) + ';'
 from sys.default_constraints WHERE definition = '(getdate())' FOR XML PATH(''),TYPE).value('.','varchar(MAX)')

 EXEC( @SQL)

Explanation 

The above solution utilizes the catalog view sys.default_constraints which will contain the details of all the default constraints defined in a database. We are utilizing the below columns in this view for our purpose

parent_object_id - This will hold the object id of the table in which constraint is defined. We will apply OBJECT_NAME function to get back the table name
name - This will give you the name of the default constraint
parent_column_id - This will give the column id of column within the table on which DEFAULT constraint is tied. We apply COL_NAME function to return column name from it
definition - This contains the actual definition of the default constraint. In our case we use this to specifically filter for default utilizing GETDATE function alone to modify them to be based on GETUTCDATE instead.

Now the query explanation
What it does is to generate a SQL string using the information from the above columns to drop and recreate each of the constraints in the database utilizing GETDATE() value and recreate them to point to GETUTCDATE() function to capture GMT date time instead

The generated statement would look like below

ALTER TABLE Table1 DROP CONSTRAINT DefaultConstraint1; ALTER TABLE Table1 ADD CONSTRAINT DefaultConstraint1 DEFAULT getutcdate() FOR column1;
....

Then we make use of EXEC function to dynamically execute the above string to make sure modified constraints are created.
Once this is done you can use the below query to check if changes have been applied to the database correctly.

select count(*) from sys.default_constraints WHERE definition = '(getdate())'

select * from sys.default_constraints WHERE definition = '(getutcdate())'


The first query should return 0 count and second one will return full list of constraints which were previously linked to the getdate function.


Summary

As explained above you can use similar logic to generate the query and execute it so as modify all the default constraints based on a common value in a single pass.

References

sys.default_constraints

http://msdn.microsoft.com/en-us/library/ms173758.aspx

Friday, October 24, 2014

IIf function T-SQL vs SSRS Behavioral Difference

This blog explains on the behavioral difference of IIf function between Reporting Services and in Transact SQL.
IIf function was a new addition in SQL Server 2012 which provides a simplified way to implement conditionally logic. Though we have a similar function available in SSRS 2005 onwards there is a small difference in the functionality between IIf function in SSRS and that in T-SQL.

I've explained this difference in the below MSDN article using few scenarios

http://social.technet.microsoft.com/wiki/contents/articles/28000.behavioral-difference-of-iif-function-in-t-sql-compared-to-ssrs.aspx

Feel free to go through this and let me know your valuable feedback

Saturday, October 11, 2014

Know Your DMVs : sys.dm_exec_procedure_stats


Context 

The impetus behind this post is a technical conversation I had with a fellow team member couple of days back. He was doing the performance analysis of a few procedures in a datawarehouse and was using the DMV sys.dm_exec_procedure_stats for the purpose. He had some concerns over the results he was getting and came to discuss with me on the same.
His major issues were the below
  1. The names of the procedures returned by the DMV are not always correct
  2. When trying to link to the sys.procedures view to get definition some of the rows are getting missed in the output.
He shared with me the below query which he was using and having issues with

select OBJECT_NAME(s.object_id) AS ProcName,
last_execution_time,
execution_count,
last_elapsed_time,
last_execution_time,
last_logical_reads,
last_physical_reads,
last_worker_time
from sys.dm_exec_procedure_stats s

Illustration

I thought of analyzing this myself and setup a sample database with few procedures and executed them. Then I executed the above query to capture their stats and got the below output


As specified earlier I could also get NULL values for few objects. 
I tried adding join to sys.procedures and got the following result
Which was also the same as what my colleague specified

Solution

I understood there is something fundamentally wrong we are doing in the query. 
I analysed the DMV carefully and understood the issue
Just do a select * on the DMV and you'll see the below result
Which clearly indicates the below
  • The DMV is always executed from master db context and returns the details of all procedures in the cache from all the databases within the server instance. The database_id field indicates the database to which the procedure belongs. As such joining with catalog views like sys.procedures will only return matches for the objects which belong to the database from which query is executed. Also usage of functions like OBJECT_NAME will also return values for objects based on current database scope  itself  by default so if there's another object with same id in the executing database it will get returned as the object name instead of the actual object name.
  • As shown by the above result the query also retrieves the details on extended stored procedures so if we want to see details of procedures alone we need to add a filter on type or type_desc fields
Based on these observations I tweaked the query as below and executed

select OBJECT_NAME(s.object_id) AS ProcName,
st.text AS ProcDefinition,
last_execution_time,
execution_count,
last_elapsed_time,
last_execution_time,
last_logical_reads,
last_physical_reads,
last_worker_time
from sys.dm_exec_procedure_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
WHERE database_id = DB_ID()
AND Type = 'P'
This would cause the query to retrieve only details of the cached procedures which are in the current database scope. Hence you will get the correct object name listed. It would also cause extended procedures to get filtered out as they will have NULL values for database_id column.
You will get the result as below

If you want full list of procedures to be returned for the instance you need to do a slight tweak in above query to pass database_id also as an argument for OBJECT_NAME function to return the value from the correct database scope as shown below

More Info

Now If you analyze this DMV carefully you will understand that this is a very useful DMV which gives useful details on performance statistics of cached procedures like details on execution count,execution time,worker threads, logical / physical reads etc. One thing to notice is that the result will contain the details of a procedure only until it is cached. Remove the procedure from the cache and it will get excluded from the result as shown.


As you see from the above the procedure got excluded from the result
Hope this article will help you in understanding more on the DMV sys.dm_exec_procedure_stats and its usage and would help you in avoiding issues like above caused due to its incorrect usage.

Referrences





Friday, September 26, 2014

T-SQL Tips: Multifaceted MERGE Statement

MERGE statement was a very useful addition which was introduced from SQL 2008. It is very useful in lots of day to day scenarios like normal UPSERT (UPDATE + INSERT) operations, slowly changing dimension processing etc.
This blog aims at explaining two not so common scenarios where MERGE statement comes handy.

1. Generating an "Incremental" Data Population Script
In a typical project development there will be a need to create data scripts for populating generic values like configurations, master data entry, audit/ control table entries etc. During the course of the project the data scripts keeps on adding new values. In normal cases this will be handled by adding new INSERT scripts each time. So at the project end whilst doing the final migration to the production we will have a set of INSERT statements which may even span multiple files.
This can be replaced by a single MERGE statement to which we can always keep on adding values and executed in database to apply only the incremental changes.
The statement would look like below

MERGE INTO Table  AS d
USING (VALUES('Value1','Value2',value3)) AS s (Col1, Col2, Col3)
ON s.Col1 = d.Col1
WHEN MATCHED
AND (
s.Col2 <> d.Col2
OR s.Col3 <> d.Col3
)
THEN 
UPDATE SET Col2 = s.Col2, Col3 = s.Col3
WHEN NOT MATCHED
THEN 
INSERT (Col1,Col2,Col3)
VALUES(s.Col1,s.Col2,s.Col3);
Illustration
Consider the code below

declare @t table
(
id int identity(1,1),
col1 varchar(50),
col2 varchar(50),
col3 int
)

MERGE @t AS d
USING (VALUES('Value1','Cat1',100),
('Value2','Cat1',150),
('Value1','Cat2',300),
('Value2','Cat2',225),
('Value3','Cat2',430),
('Value1','Cat3',520)
) AS s(col1,col2,col3)
ON s.col1 = d.col1
WHEN MATCHED
AND (
COALESCE(s.col2,'') <> COALESCE(d.col2,'')
OR COALESCE(s.col3,'') <> COALESCE(d.col3,'')
)
THEN 
UPDATE 
SET col2 = s.col2,col3= s.col3
WHEN NOT MATCHED BY TARGET
THEN
INSERT (col1,col2,col3)
VALUES(s.col1,s.col2,s.col3);

SELECT * FROM @t

Now suppose if you want to add three more values you can just extend that within same MERGE statement as below

declare @t table
(
id int identity(1,1),
col1 varchar(50),
col2 varchar(50),
col3 int
)

MERGE @t AS d
USING (VALUES('Value1','Cat1',100),
('Value2','Cat1',150),
('Value1','Cat2',300),
('Value2','Cat2',225),
('Value3','Cat2',430),
('Value1','Cat3',550),
('Value2','Cat3',610),
('Value1','Cat4',735),
('Value2','Cat4',821)
) AS s(col1,col2,col3)
ON s.col1 = d.col1
WHEN MATCHED
AND (
COALESCE(s.col2,'') <> COALESCE(d.col2,'')
OR COALESCE(s.col3,'') <> COALESCE(d.col3,'')
)
THEN 
UPDATE 
SET col2 = s.col2,col3= s.col3
WHEN NOT MATCHED BY TARGET
THEN
INSERT (col1,col2,col3)
VALUES(s.col1,s.col2,s.col3);


SELECT * FROM @t

I've marked the changed rows in green. I've added three new rows and changed the value of an existing row. Executing this you can see that only the incremental changes ie new rows + modified ones will get applied to table.You can keep on extending script like this and finally you'll just have a single MERGE statement in a single file with all values which you need to migrate to your production environment rather than a set of small small incremental INSERT scripts.

2. Populating Master Child Tables

Another major application of the MERGE statement comes in the cases where you want to populate a master child table with some external data. I had previously blogged about a similar scenario here

http://visakhm.blogspot.in/2010/04/using-xml-to-batch-load-master-child.html

Now lets see how MERGE statement can be used for achieving the same requirement. MERGE would provide a much more compact way of doing this without the need of any intermediate temporary table.

The XML looks like as in below file
https://drive.google.com/file/d/0B4ZDNhljf8tQbHdfZUFteXNRcDg/edit?usp=sharing

The tables can be given as below
declare @order table
(
OrderID int  IDENTITY(100000,1),
OrderDesc varchar(1000),
OrderDate datetime,
CustomerDesc varchar(1000),
ShippedDate datetime,
WebOrder bit
)
declare @OrderItems table
(
OrderItemID int IDENTITY(1000,1),
OrderID int,
ItemDesc varchar(100),
Qty int,
ItemPrice decimal(15,2)
)

For illustration I've given it as table variables but in the actual case it would be permanent tables.
Nows lets see the illustration of how MERGE can be applied in the above scenario

Illustration
The code will look like below


INSERT INTO @OrderItems
(
OrderID,
ItemDesc,
Qty,
ItemPrice
)
SELECT *
FROM
(
MERGE INTO @order AS d
USING (SELECT t.u.value('OrderDesc[1]','varchar(100)') AS OrderDesc,
       t.u.value('OrderDate[1]','datetime') AS OrderDate,
       t.u.value('CustomerDesc[1]','varchar(1000)') AS CustomerDesc,
       t.u.value('ShippedDate[1]','datetime') AS ShippedDate,
       t.u.value('WebOrder[1]','bit') AS WebOrder,
       m.n.value('ItemDesc[1]','varchar(100)') AS ItemDesc,
       m.n.value('Qty[1]','int') AS Qty,
       m.n.value('ItemPrice[1]','decimal(15,2)') AS ItemPrice  
       FROM @x.nodes('/Orders/Order')t(u)
       CROSS APPLY u.nodes('OrderItem')m(n)) AS s
 ON d.OrderDesc = s.OrderDesc
 AND d.OrderDate = s.OrderDate
 WHEN NOT MATCHED
 THEN
 INSERT ( 
 OrderDesc ,
OrderDate ,
CustomerDesc ,
ShippedDate ,
WebOrder
)
VALUES
(
s.OrderDesc ,
s.OrderDate ,
s.CustomerDesc ,
s.ShippedDate ,
s.WebOrder
)
OUTPUT inserted.OrderID,
s.ItemDesc,
s.Qty,
s.ItemPrice
)t (OrderID,
ItemDesc,
Qty,
ItemPrice)

Now check the output and you can see the below



As seen above the data will get populated to the master and the child table preserving the referential integrity. This is made possible through the use of OUTPUT clause with MERGE. The OUTPUT clause when used with MERGE exposes the columns of source table/query in addition to the INSERTED and DELETED table columns which enables us to capture the generated id values from the parent table as well as all the required column values for the child table from the source query within the same statement. This is then used in a nested INSERT for populating the child table. This is a scenario where MERGE can come really handy to us.
Hope you enjoyed this article speaking about two not so common practical scenarios where MERGE can be applied. As always feel free to revert with your clarifications/comments.
Will be back with a new T-SQL tip soon!

Wednesday, September 17, 2014

T-SQL Tips: Fun with date FORMATing

FORMAT was a new function introduced in SQL 2012 which can be use to format date,numeric values to a variety of different formats and styles.
This blog discusses on some of the cool date value formatting tips that can be done using the FORMAT function.

1. Display the date value based on a language/locale culture

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'d','en-GB') AS BritishFormat,FORMAT(@dt,'D','en-US') AS UsFormat,
FORMAT(@dt,'D','zh-cn') AS ChineseFormat,FORMAT(@dt,'D','ta-IN') AS TamilIndiaFormat,
FORMAT(@dt,'D','ml-IN') AS MalayalamIndiaFormat



2. Display date as per any of the custom formats

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'dd/MM/yyyy hh:mm:ss tt') AS DateWithTime12h,
FORMAT(@dt,'dd MMM yyyy HH:mm:ss') AS DateWithTime24h,
FORMAT(@dt,'HH:mm:ss.fff') AS TimeAloneWithMillisec,
FORMAT(@dt,'yyyy-MM-dd HH:mm:ss.fff zz') AS DateTimeWithOffset,
FORMAT(@dt,'dddd dd MMMM yyyy gg') AS DayNameWithDateEra


3. Display component parts of a date value based on custom formats

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'yyyy\MM') AS Period,
FORMAT(@dt,'hh:mm:ss tt') AS Time12h,
FORMAT(@dt,'HH:mm:ss') AS Time24h,
FORMAT(@dt,'dddd dd\t\h MMMM') AS DayMonthDate,
FORMAT(@dt,'HH \h mm \min ss \sec') AS TimeElapsedSinceDayStart,
FORMAT(@dt,'yyyy-MM-ddTHH:mm:sszzz') AS XMLDateFormat,
FORMAT(@dt,'yyyy-MM-ddTHH:mm:ssZ') AS XMLDateFormatUTC


From the above we can see that FORMAT is a very useful function which provides the ability of generating flexible presentation formats from a given date value.