|
|
Algorithms
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
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
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...
A tale from a Stalker who licked his wounds and got back 9 months later...
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
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...
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)),
...
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...
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
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
Full Algorithms Archive
|