Peter Larsson Blog

Patron Saint of Lost Yaks

Remove all Extended Properties in a database

During my tests to port several databases to SQL Azure, one of the recurring things that fails export is the Extended Properties. So I just wanted to remove them. This is a simple wayh to list all Extended Properties and the corresponding delete statement. Read more →

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) ;WITHcteSource(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 ) SELECTSUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)] FROMcteSource Legacy Comments dmSQL 2012-10-24 re: How to calculate the covariance in T-SQL Thanks. Read more →

Convert UTF-8 string to ANSI

CREATEFUNCTION 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 Read more →