Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

SQL Server 2000

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

posted @ Wednesday, January 18, 2012 1:01 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 Denali ]

Avoid stupid mistakes

Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is SELECT COUNT(*) OfflineData Do you see the bug? Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case? It returns 1. So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes? An...

posted @ Thursday, September 22, 2011 8:38 AM | Feedback (3) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 Miscellaneous Denali ]

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

posted @ Wednesday, September 14, 2011 6:30 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 Denali ]

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...

posted @ Sunday, February 06, 2011 12:10 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

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 ]

The internal storage of a DATETIME value

SELECT  [Now],         BinaryFormat,         SUBSTRING(BinaryFormat, 1, 4) AS DayPart,         SUBSTRING(BinaryFormat, 5, 4) AS TimePart,         CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days],         DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today],         CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks],         DATEADD(MILLISECOND, 1000.E / 300.E * CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT), 0) AS Peso FROM    (             SELECT  GETDATE() AS [Now],                     CAST(GETDATE() AS BINARY(8)) AS BinaryFormat         ) AS d

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

Fast easter day function

CREATE FUNCTION dbo.fnGetEasterDate (     @Year SMALLINT ) RETURNS DATE AS BEGIN     RETURN (                 SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6)                 FROM    (                             SELECT CASE @Year % 19                                         WHEN  0 THEN '0415'                                         WHEN  1 THEN '0404'                                         WHEN  2 THEN '0324'                                         WHEN  3 THEN '0412'                                         WHEN  4 THEN '0401'                                         WHEN  5 THEN '0419'                                         WHEN  6 THEN '0409'                                         WHEN  7 THEN '0329'                                         WHEN  8 THEN '0417'                                         WHEN  9 THEN '0406'                                         WHEN 10 THEN '0326'                                         WHEN 11 THEN '0414'                                         WHEN 12 THEN '0403'                                         WHEN 13 THEN '0323'                                         WHEN 14 THEN '0411'                                         WHEN 15 THEN '0331'                                         WHEN 16 THEN '0418'                                         WHEN...

posted @ Wednesday, September 08, 2010 3:30 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Proper Relational Division With Sets

I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data. With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor. Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and...

posted @ Friday, July 02, 2010 12:48 AM | Feedback (36) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Relational division

I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem. The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be some day. For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx But for now there is no such operator, so we as developers have to find our own ways. First prepare and populate some sample data -- Prepare sample data DECLARE @Sample TABLE         (             ParentID INT NOT NULL,             Keyword VARCHAR(25) NOT NULL,             UNIQUE (ParentID, Keyword)         ) -- Populate sample data INSERT ...

posted @ Wednesday, June 30, 2010 9:41 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Full SQL Server 2000 Archive

Powered by:
Powered By Subtext Powered By ASP.NET