Introduction
Many database programers confronted with the fact that the database contain date in different format: TIMESTAMP, LOCAL or UTC timezone and need some transformation between them. An another old SQL-kind problem is splitting the strings. By using this addon you can easily perform the above operations without losing any performance.
Background
Splitting the string is a CPU cost operation in T-SQL, moreover not any built-in function exists for it.
Using CLR and .Net is an obivious solution since there are many built-in functions for string operations.
Date transformations is also supported in .NET, so do not need to create any complicated date-transformation related functions in T-SQL (I meet with these and mostly they were wrong because of the daylight saving time).
Deploying the CLR
Basically you may deploy the CLR by running the Install.sql script or following the below steps. First enable the CLR, then simply create the assembly:
use [master]
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
CREATE ASSEMBLY [Sql.Addons]
FROM 'c:\SQLCLR\Sql.Addons.dll'
WITH PERMISSION_SET = SAFE
GO
Split - functions
I'm using this functions more targeted like usually because in this way I can eliminate the unnecessary CAST operation if I use the function in the join condition (CAST is also needed from NVARCHAR to VARCHAR).
CREATE FUNCTION dbo.clr_split2bigint(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item bigint)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Bigint]
GO
grant select on dbo.clr_split2bigint to [public]
go
CREATE FUNCTION dbo.clr_split2int(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item int)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Int]
GO
grant select on dbo.clr_split2int to [public]
go
CREATE FUNCTION dbo.clr_split2smallint(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item smallint)
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Smallint]
GO
grant select on dbo.clr_split2smallint to [public]
go
CREATE FUNCTION dbo.clr_split2string(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item nvarchar(4000))
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2String]
GO
grant select on dbo.clr_split2string to [public]
go
CREATE FUNCTION dbo.clr_split2max(@str nvarchar(max), @delimiter nvarchar(10))
RETURNS table (ID int, Item nvarchar(max))
AS
EXTERNAL NAME [Sql.Addons].[Split].[Split2Max]
GO
grant select on dbo.clr_split2max to [public]
go
Example:
select * from dbo.clr_split2bigint('1,2,3,4, 1000000000000', ',')
select * from dbo.clr_split2int('1,2,3,4, 1000000000', ',')
select * from dbo.clr_split2smallint('1,2,3,4, 5', ',')
select * from dbo.clr_split2string('1,2,3,4, 5', ',')
select * from dbo.clr_split2max('1,2,3,4, 5', ',') DATE- functions
Below listed many transformations between local-time, utc-time, timestamp and timetick.
* --> UTC
CREATE FUNCTION dbo.clr_date_localtime2utc(@date datetime)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToUtc]
GO
grant execute on dbo.clr_date_localtime2utc to [public]
go
CREATE FUNCTION dbo.clr_date_utcts2utctime(@timestamp bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TimeStampToUtcTime]
GO
grant execute on dbo.clr_date_utcts2utctime to [public]
go
CREATE FUNCTION dbo.clr_date_localticks2utctime(@ticks bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TicksToUtcTime]
GO
grant execute on dbo.clr_date_localticks2utctime to [public]
go
CREATE FUNCTION dbo.clr_date_localtime2utcts(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToTimeStamp]
GO
grant execute on dbo.clr_date_localtime2utcts to [public]
go
CREATE FUNCTION dbo.clr_date_utctime2utcts(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcTimeToTimeStamp]
GO
grant execute on dbo.clr_date_utctime2utcts to [public]
go
CREATE FUNCTION dbo.clr_date_utcticks()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[GetUtcTicks]
GO
grant execute on dbo.clr_date_utcticks to [public]
go
CREATE FUNCTION dbo.clr_date_localtime2utcticks(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcTimeToTicks]
GO
grant execute on dbo.clr_date_localtime2utcticks to [public]
go
* --> Local-Time
CREATE FUNCTION dbo.clr_date_utc2localtime(@date datetime)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[UtcToLocalTime]
GO
grant execute on dbo.clr_date_utc2localtime to [public]
go
CREATE FUNCTION dbo.clr_date_utcts2localtime(@timestamp bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TimeStampToLocalTime]
GO
grant execute on dbo.clr_date_utcts2localtime to [public]
go
CREATE FUNCTION dbo.clr_date_utcticks2localtime(@ticks bigint)
RETURNS datetime
AS
EXTERNAL NAME [Sql.Addons].[Date].[TicksToLocalTime]
GO
grant execute on dbo.clr_date_utcticks2localtime to [public]
go
CREATE FUNCTION dbo.clr_date_localticks()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[GetLocalTimeTicks]
GO
grant execute on dbo.clr_date_localticks to [public]
go
CREATE FUNCTION dbo.clr_date_utctime2localticks(@date datetime)
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Date].[LocalTimeToTicks]
GO
grant execute on dbo.clr_date_utctime2localticks to [public]
go
Date --> String
CREATE FUNCTION dbo.clr_date_date2string(@date datetime)
RETURNS nvarchar(18)
AS
EXTERNAL NAME [Sql.Addons].[Date].[DateToString]
GO
grant execute on dbo.clr_date_date2string to [public]
go
Examples :
select 'clr_date_localtime2utc' func, dbo.clr_date_localtime2utc(getdate()) utc_time
union
select 'clr_date_utcts2utctime', dbo.clr_date_utcts2utctime(dbo.clr_date_utctime2utcts(getutcdate())) utc_time
union
select 'clr_date_localticks2utctime', dbo.clr_date_localticks2utctime(dbo.clr_date_localticks()) utc_time
select 'clr_date_localtime2utcts' func, dbo.clr_date_localtime2utcts(getdate()) utc_timestamp
union
select 'clr_date_utctime2utcts', dbo.clr_date_utctime2utcts(getutcdate()) utc_timestamp
select 'clr_date_utcticks' func, dbo.clr_date_utcticks() utc_timetick
union
select 'clr_date_localtime2utcticks', dbo.clr_date_localtime2utcticks(getdate())
select 'clr_date_utc2localtime' func, dbo.clr_date_utc2localtime(getutcdate()) local_time
union
select 'clr_date_utcts2localtime', dbo.clr_date_utcts2localtime(dbo.clr_date_utctime2utcts(getutcdate())) local_time
union
select 'clr_date_utcticks2localtime', dbo.clr_date_utcticks2localtime(dbo.clr_date_utcticks()) local_time
select 'clr_date_localticks' func, dbo.clr_date_localticks() local_timetick
union
select 'clr_date_utctime2localticks', dbo.clr_date_utctime2localticks(getutcdate())
select 'clr_date_date2string' func, dbo.clr_date_date2string(getdate()) date_string
Memory Usage
Use this method to force the system to try to reclaim the maximum amount of available memory and after that will returns the number of bytes allocated.
CREATE FUNCTION dbo.clr_addons_memory_usage()
RETURNS bigint
AS
EXTERNAL NAME [Sql.Addons].[Monitor].[MonitorMemoryUsage]
GO
grant select on dbo.clr_addons_memory_usageto [public]
go
Example
select dbo.clr_addons_memory_usage() bytes
2010-2012 - DB. Architect & Senior DB. Developer - www.livejasmin.com
2008-2010 - Senior DB. Developer - www.logmein.com
2006-2008 - DBA & DB. Developer - www.eurotaxglass.co.uk
Submit an article or tip
