January 2010 Blog Posts
PASS Scania is a new PASS chapter in Sweden which will promote the interest, networking and knowledge for professional SQL Server-users such as developers, DBA's and BI-specialist in Skåne. The Chapter also has a business alliance with an existing usergroup SQLUG.
All developers who use SQL Server platform in their work is welcome to participate in the seminars with start february 4th 2010. Both consultants and employees are welcome.
Membership and seminars are free and activities are mainly sponsored events.
First meeting is held in feburary where new members have the opportunity to present themself, we will present the agenda for...
With SQL Server 2008, we can easily use
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
SELECT CONVERT(VARCHAR(MAX), @bin, 2)
But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.
So, here is how you can do the conversion with SQL Server 2005 by using XML.
-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
-- Display the results
SELECT @bin AS OriginalValue,
CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString
This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx.
The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx.
CREATE FUNCTION dbo.fnISOMonday
(
@theYear SMALLINT,
@theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
FROM (
SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
FROM (
SELECT DATEADD(YEAR, @theYear - 1900, 3) AS Jan4
WHERE @theYear BETWEEN 1900 AND 9999
...
Joe Celko has posted a new Stumper - The Class Scheduling Problem
here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/
Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes.
-- Initialize and find the valid combinations
DECLARE @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils.
DECLARE @Source TABLE
(
room_nbr CHAR(2),
class_nbr CHAR(2),
recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
)
INSERT @Source
(
room_nbr,
class_nbr
)
SELECT r.room_nbr,
c.class_nbr
FROM dbo.Rooms AS r
INNER JOIN dbo.Classes AS...
This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.
DECLARE @Interval INT = 7
;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
SELECT ProductID,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
FROM Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
SELECT s.ProductID,
s.TransactionDate AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM ...
This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple.
DECLARE @Word VARCHAR(10) = 'Peter'
;WITH cteYak(Word, Letters)
AS (
SELECT CAST(SUBSTRING(@Word, Number, 1) AS VARCHAR(10)) AS Word,
STUFF(@Word, Number, 1, '') AS Letters
FROM dbo.TallyNumbers
WHERE Number BETWEEN 1 AND LEN(@Word)
UNION ALL
SELECT CAST(Word + SUBSTRING(y.Letters, n.Number, 1) AS VARCHAR(10)) AS Word,
STUFF(y.Letters, n.Number, 1, '') AS Letters
FROM cteYak AS y
INNER JOIN dbo.TallyNumbers AS n ON n.Number BETWEEN 1 AND LEN(y.Letters)
)
SELECT DISTINCT
Word
FROM cteYak
WHERE LEN(Word) = LEN(@Word)