This week I was invited my Microsoft to give two presentations in Slovenia. My presentations went well and I had good energy and the audience was interacting with me.
When I had some time over from networking and partying, I attended a few other presentations. At least the ones who where held in English. One of these was "SQL Server Integration Services 2012 - All the News, and More", given by Davide Mauri, a fellow co-worker from SolidQ.
We started to talk and soon came into the details of the new things in SSIS 2012. All of the official things Davide talked...
DECLARE @Sample TABLE
x INT NOT NULL,
y INT NOT NULL
VALUES (3, 9),
;WITH cteSource(x, xAvg, y, yAvg, n)
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))
SELECT SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
CREATE FUNCTION dbo.fnConvertUtf8Ansi
DECLARE @Value SMALLINT = 160,
IF @Source NOT LIKE '%[ÂÃ]%'
WHILE @Value <= 255
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)
@Ansi = CHAR(@Value)
WHILE CHARINDEX(@Source, @Utf8) > 0
SET @Source = REPLACE(@Source, @Utf8, @Ansi)
SET @Value += 1
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
RETURN CASE @theDate
WHEN '99991231' THEN 1
ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate))
WHEN '17530101' THEN -1
ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate))
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...
DECLARE @Year SMALLINT = 2011,
@NumberOfYears TINYINT = 3
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth
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
CREATE FUNCTION dbo.fnGetEasterDate
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6)
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'
This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant.
I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage.
Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat.
-- Setup sample data
CREATE TABLE dbo.Restaurant
Seats BINARY(125) NOT NULL
-- Initialize an empty restaurant
-- Create procedure for handling seat assignment
This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.
Consider this sample data
DECLARE @Sample TABLE
VALUES (1, 'Peso & Performance SQL'),
(2, 'Need help <? /> -- '),
(2, 'With XML string concatenation ?')
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '')
Full Optimization Archive