|
|
SQL Server 2005
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
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...
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...
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...
This bug has haunted me for a while, until today when I decided to not accept it anymore.
So I filed a bug over at connect.microsoft.com,
https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed.
Here is a very simple repro of the problem
DECLARE @Sample TABLE
(
a DECIMAL(38, 19),
b FLOAT
)
INSERT @Sample
(
a,
b
)
VALUES (1E / 7E, 1E / 7E)
SELECT *
FROM @Sample
Here is the actual output.
a b
--------------------------------------- ----------------------
0.1428571428571428400 0,142857142857143
I think that both columns should have the same decimal separator, don't you?
//Peter
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
DECLARE @Year SMALLINT = 2011,
@NumberOfYears TINYINT = 3
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth
FROM master..spt_values
WHERE TYPE = 'P'
AND number BETWEEN 1 AND 12 * @NumberOfYears
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROM cteCalendar
Full SQL Server 2005 Archive
|