Saturday, December 20, 2014

SSMS Table Designer Not Recognizing Rowversion Datatype

This blog is the result of a recent discussion I had with my fellow colleagues. 
One of my colleagues asked me why rowversion is not recognized as a valid datatype with SQLServer. I was surprised at this statement and told that I don't believe that is correct. He replied that he can illustrate it and showed the below on his system.
He launched an instance of SQLServer Management Studio (SSMS) which belonged to SQL 2014 Express Edition and tried creating a table using the table designer. The moment he tried to add a column of rowversion datatype and save it he got the below error message


I checked the datatype listing within the designer and found that its not having rowversion included as a type within which was a bit surprising!
This was the reason why it errored out.So the only solution seems to be to select timestamp as the datatype. But the irony is that timestamp is deprecated from SQL 2008 onwards. I've check this in 2012 and 2008 R2 versions of SSMS and there also the behavior is the same. 
I told him that this is a limitation of the designer and you can very well create rowversion field if you use T-SQL CREATE TABLE statement. He tried it out and confirmed that it indeed worked. But here also if you try to script out the table from SSMS it will still show column as of timestamp type only as it seems systypes still does not have the change implemented as per the connect item below


Apparently rowversion and timestamp are synonyms of one and the same thing. However ANSI recommends using rowversion and timestamp has been deprecated for use in all fresh development activities.
I've never been a fan of the table designer in SSMS. I prefer using T-SQL scripts for all DDL activities. This just adds one more reason for my preference.
Hope this blog will give a word of caution to anyone trying to create tables using the designer and want to declare a rowversion type field. I would suggest strongly considering T-SQL scripts for activities like table creation,constraint addition etc.

Tuesday, December 16, 2014

T-SQL Tips: Customizing CDC for Oracle Service Implementation in SQLServer

The purpose of this blog is to explain a quick tip which you can implement to customize the default functionality of CDC for Oracle service in SQLServer to help you overcome the limitation posed by the default deployment script.

Business Case
Recently in one of projects CDC for Oracle was implemented and over a weekend there was some failures with our ETL processes due to some spurious data coming from the source. This prompted us to clear the residual data for the week and rerun the jobs. We had CDC as the first layer which would pull data from the source Oracle instances to the SQLServer  instance where datawarehouse resided. Due to some data issues some of ETL had failed over the weekend so we were trying for a rerun of the jobs. To our surprise we were not getting any records for the jobs which were rerun. On analysis we found CDC tables to be empty. This was not certainly not expected by us.

Analysis
On analysis the reason was found as follows.
By default the data retention period for CDC was set as 3 days by the deployment script which is why the data was getting purged by the CDC cleanup job so soon. In our case this retention period was less as in case of a long weekend by the time we realize the job failure we may not have data available in CDC tables for the failed days due to the short retention period

Solution
The obvious solution to the above issue was to increase the data retention period. There's a system stored procedure which can be used for this purpose which is sp_cdc_Change_job

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

The procedure has a parameter called retention which can be passed to override the default value set by the deployment script. The parameter designates the value in minutes that would be set for the data retention period. So you can set value as per your requirement. So in our case I set it to 7200 (5 * 24 * 60)  to make CDC retain the change data for 5 days. For this the complete sql command would like below

EXEC sys.sp_cdc_change_job @job_type= 'cleanup',@retention = 7200

sp_cdc_change_job
Now some details on sp_cdc_change_job sp. The procedure has a set of parameters which can be used to override the default settings of CDC jobs. Some of the parameter we may be interested in addition to the above are

@pollinginterval - This would indicate the interval at which the Oracle log has to be polled to capture the changes. This has to be used in conjunction with the @continuos parameter ie only when @continuos = 1 then polling interval can be set as this indicates the polling has to be done continuously rather than as a one time activity

@threshold - This will indicate the number of entries that can be deleted using a given statement

@maxtrans - This indicates the maximum number of transactions that can be processed in a single cycle.

All these parameters can be utilized to override default settings of CDC service as per your requirement

The current configured values for the above parameters for CDC instance can be viewed by querying the 
dbo.cdc_jobs table

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

Saturday, November 15, 2014

T-SQL Tips: UDF To Swap The Parts Of A Datetime Value

Context

There was a requirement recently which required changing only the year portion of one of the date fields.
The business scenario was to extend validity of insurance details on a table.
I've also come across similar requests in forums also asking whether its possible to swap one or more parts of a datetime value without affecting the other parts of the date

Solution

This article provides a solution for scenarios like above.
The logic for these type of scenarios uses date function DATEADD to do an integer arithmetic over datetime values to swap the required parts. Since dates are represented internally as numbers in SQL Server its very easy to do integer operations with date values.

Illustration

Consider the below illustration

declare @date datetime = '20141020 16:35'

Now let the requirement be that we need to swap the year part of the date with 2020. We can do it by taking the offset between current year and new year value and add it to the date value ie 2020-2014

Translating it using DATEADD we can use

SELECT DATEADD(yy,@Year-YEAR(@date),@date) AS YearSwappedDate

Now see the result


As you see the year part gets swapped correctly for the passed value keeping the other parts unchanged

Now lets try for year and month parts together. Going again by the same theory we can use an expression as below

declare @date datetime = '20141020 16:35'
declare @Year int = 2020
declare @Month int = 9
SELECT DATEADD(mm,@Month -MONTH(@date)+ ((@Year-YEAR(@date))*12),@date) AS YearMonthSwappedDate

What we're doing here is take the offset between month and year parts and doing addition with that. I'm reducing year also to month level by multiplying 12 so that we can just use a single DATEADD call to do the addition. The result for this looks like below



Now lets go one more step ahead and do replacement of day part also. Again extending the logic we can write expression as below

declare @date datetime = '20141020 16:35'
declare @Year int = 2020
declare @Month int = 9
declare @Day int = 12

SELECT DATEADD(mm,@Month -MONTH(@date)+ ((@Year-YEAR(@date))*12),@date) + @Day - DAY(@date)

This provides us with the results as below


Generic Function

Lets convert this into a generic UDF. I've also extended it to add the hour minute and second parts.
The UDF would look like below

CREATE Function SwapDateParts
(
@InputDate datetime,
@SwapYear int = NULL,
@SwapMonth int = NULL,
@SwapDay int = NULL,
@SwapHour int = NULL,
@SwapMinute int = NULL,
@SwapSecond int = NULL
)
RETURNS datetime
AS
BEGIN
DECLARE @SwappedDate datetime
SET @SwappedDate = DATEADD(ss,(COALESCE(@SwapHour-DATEPART(HOUR,@InputDate),0)*60*60) + (COALESCE(@SwapMinute-DATEPART(MINUTE,@InputDate),0)*60)+COALESCE(@SwapSecond-DATEPART(SECOND,@InputDate),0),DATEADD(mm,COALESCE(@SwapMonth -MONTH(@InputDate),0)+ (COALESCE((@SwapYear-YEAR(@InputDate))*12,0)),@InputDate)) + COALESCE(@SwapDay - DAY(@InputDate),0)
RETURN @SwappedDate
END

Try running it for different set of inputs and see the result

SELECT dbo.SwapDateParts('20140516 16:33:22',DEFAULT,1,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS MonthSwapped,
dbo.SwapDateParts('20140516 16:33:22',DEFAULT,6,11,DEFAULT,DEFAULT,DEFAULT) AS MonthDaySwapped,
dbo.SwapDateParts('20140516 16:33:22',1980,12,30,DEFAULT,DEFAULT,DEFAULT) AS YearMonthDaySwapped,
dbo.SwapDateParts('20140516 16:33:22',2054,DEFAULT,30,DEFAULT,56,17) AS YearDayMinuteSecondSwapped

See the output which would be the below



Summary
 This is a very good utility function that can be applied in similar scenarios to swap out one or more parts of a given datetime value

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