# Thinking outside the box

Patron Saint of Lost Yaks

## Algorithms

##### ISO week calculation for all years 1-9999 without dependencies

CREATE FUNCTION dbo.fnISOWEEK ( @Year SMALLINT, @Month TINYINT, @Day TINYINT ) RETURNS TINYINT AS BEGIN RETURN ( SELECT CASE WHEN nextYearStart <= theDate THEN 0 WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7 ELSE (theDate - prevYearStart) / 7 END + 1 FROM ( SELECT (currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart, currJan4 / 7 * 7 AS currYearStart, (currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart, CASE @Month WHEN 1 THEN @Day WHEN 2 THEN 31 + @Day WHEN 3 THEN 59 + @Day + currLeapYear WHEN 4 THEN 90 + @Day + currLeapYear WHEN 5 THEN 120 + @Day + currLeapYear WHEN 6 THEN 151 + @Day + currLeapYear WHEN 7 THEN 181 + @Day + currLeapYear WHEN 8...

posted @ Saturday, July 27, 2013 10:07 AM | Feedback (0) | Filed Under [ Algorithms ]

##### How to calculate the covariance in T-SQL

DECLARE @Sample TABLE         (             x INT NOT NULL,             y INT NOT NULL         ) INSERT  @Sample VALUES  (3, 9),         (2, 7),         (4, 12),         (5, 15),         (6, 17) ;WITH cteSource(x, xAvg, y, yAvg, n) AS (         SELECT  1E * x,                 AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),                 1E * y,                 AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),                 COUNT(*) OVER (PARTITION BY (SELECT NULL))         FROM    @Sample ) SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)] FROM    cteSource

##### Convert UTF-8 string to ANSI

CREATE FUNCTION dbo.fnConvertUtf8Ansi (     @Source VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN     DECLARE @Value SMALLINT = 160,             @Utf8 CHAR(2),             @Ansi CHAR(1)     IF @Source NOT LIKE '%[ÂÃ]%'         RETURN  @Source     WHILE @Value <= 255         BEGIN             SELECT  @Utf8 = CASE                                 WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value)                                 WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64)                                 ELSE NULL                             END,                     @Ansi = CHAR(@Value)             WHILE CHARINDEX(@Source, @Utf8) > 0                 SET    @Source = REPLACE(@Source, @Utf8, @Ansi)             SET    @Value += 1         END     RETURN  @Source END

##### Code Audit - The Beginning

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden. My part will be the technical details of the forecasting application now when our former DBA has left our company. Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these. One function I stumled across is very simple. All it does is to add a timepart from current execution time to...

posted @ Thursday, July 21, 2011 8:44 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

##### A tale from a Stalker

A tale from a Stalker who licked his wounds and got back 9 months later...

posted @ Wednesday, April 27, 2011 4:16 PM | Feedback (5) | Filed Under [ Algorithms Miscellaneous ]

##### Simple function to get beginning or end of month

CREATE FUNCTION dbo.fnIsOnMonthEdge (     @theDate DATETIME ) RETURNS SMALLINT AS BEGIN     RETURN CASE @theDate                 WHEN '99991231' THEN 1                 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate))             END +             CASE @theDate                 WHEN '17530101' THEN -1                 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate))             END END

posted @ Thursday, April 14, 2011 1:40 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Unified Relational Division algorithm

Finally! Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder. Optionally it should work with single and multiple divisor sets. That's 16 permutations of relational division kinds. A bonus point is that the algorithm I've found work across multiple platforms with standard SQL language elements. Also, I have performance tested the algorithm with the sample data from Mr Celko here. For such small sample set, my algorithm is in the top queries, but the real performance kicks in when you...

##### The internal storage of a DATETIMEOFFSET value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. This is because SQL Server add one byte that holds the precision for the datetime2 value. Start with this very simple repro declare    @now datetimeoffset(7) = '2010-12-15 21:04:03.6934231 +03:30'   select     cast(cast(@now as datetimeoffset(0)) as binary(9)),            cast(cast(@now as datetimeoffset(1)) as binary(9)),            cast(cast(@now as datetimeoffset(2)) as binary(9)),            cast(cast(@now as datetimeoffset(3)) as binary(10)),            cast(cast(@now as datetimeoffset(4)) as binary(10)),            cast(cast(@now as datetimeoffset(5)) as binary(11)),            cast(cast(@now as datetimeoffset(6)) as binary(11)),           ...

posted @ Wednesday, December 15, 2010 10:44 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms ]

##### The internal storage of a DATETIME2 value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. This is because SQL Server add one byte that holds the precision for the datetime2 value. Start with this very simple repro declare @now datetime2(7) = '2010-12-15 21:04:03.6934231'   select  cast(cast(@now as datetime2(0)) as binary(7)),         cast(cast(@now as datetime2(1)) as binary(7)),         cast(cast(@now as datetime2(2)) as binary(7)),         cast(cast(@now as datetime2(3)) as binary(8)),         cast(cast(@now as datetime2(4)) as binary(8)),         cast(cast(@now as datetime2(5)) as binary(9)),         cast(cast(@now as datetime2(6)) as binary(9)),         cast(cast(@now...

posted @ Wednesday, December 15, 2010 10:05 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

##### The internal storage of a SMALLDATETIME value

SELECT  [Now],         BinaryFormat,         SUBSTRING(BinaryFormat, 1, 2) AS DayPart,         SUBSTRING(BinaryFormat, 3, 2) AS TimePart,         CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT) AS [Days],         DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT), 0) AS [Today],         SUBSTRING(BinaryFormat, 3, 2) AS [Ticks],         DATEADD(MINUTE, CAST(SUBSTRING(BinaryFormat, 3, 2) AS SMALLINT), 0) AS Peso FROM    (             SELECT  CAST(GETDATE() AS SMALLDATETIME) AS [Now],                     CAST(CAST(GETDATE() AS SMALLDATETIME) AS BINARY(4)) AS BinaryFormat         ) AS d

posted @ Friday, November 05, 2010 3:14 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]