# Thinking outside the box

Patron Saint of Lost Yaks

## Algorithms

##### ISO week calculation for all years 1-9999 without dependencies

CREATE FUNCTION dbo.fnISOWEEK ( @Year SMALLINT, @Month TINYINT, @Day TINYINT ) RETURNS TINYINT AS BEGIN RETURN ( SELECT CASE WHEN nextYearStart <= theDate THEN 0 WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7 ELSE (theDate - prevYearStart) / 7 END + 1 FROM ( SELECT (currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart, currJan4 / 7 * 7 AS currYearStart, (currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart, CASE @Month WHEN 1 THEN @Day WHEN 2 THEN 31 + @Day WHEN 3 THEN 59 + @Day + currLeapYear WHEN 4 THEN 90 + @Day + currLeapYear WHEN 5 THEN 120 + @Day + currLeapYear WHEN 6 THEN 151 + @Day + currLeapYear WHEN 7 THEN 181 + @Day + currLeapYear WHEN 8...

posted @ Saturday, July 27, 2013 10:07 AM | Feedback (0) | Filed Under [ Algorithms ]

##### 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) ;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

##### Convert UTF-8 string to ANSI

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

##### Code Audit - The Beginning

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...

posted @ Thursday, July 21, 2011 8:44 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

##### A tale from a Stalker

A tale from a Stalker who licked his wounds and got back 9 months later...

posted @ Wednesday, April 27, 2011 4:16 PM | Feedback (5) | Filed Under [ Algorithms Miscellaneous ]

##### Simple function to get beginning or end of month

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

posted @ Thursday, April 14, 2011 1:40 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Unified Relational Division algorithm

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...

##### The internal storage of a DATETIMEOFFSET value

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)),           ...

posted @ Wednesday, December 15, 2010 10:44 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms ]

##### The internal storage of a DATETIME2 value

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...

posted @ Wednesday, December 15, 2010 10:05 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

##### The internal storage of a SMALLDATETIME value

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

posted @ Friday, November 05, 2010 3:14 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### The internal storage of a DATETIME value

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

posted @ Friday, November 05, 2010 3:10 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Sweet and simple "first and last" weekday calculation

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

posted @ Thursday, October 28, 2010 9:25 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. -- Prepare staging table for all DBCC outputs DECLARE @Sample TABLE         (             Col1 VARCHAR(MAX) NOT NULL,             Col2 VARCHAR(MAX) NOT NULL,             Col3 VARCHAR(MAX) NOT NULL,             Col4 VARCHAR(MAX) NOT NULL,             Col5 VARCHAR(MAX)         )   -- Some intermediate variables for controlling loop DECLARE @FileNum BIGINT = 1,         @PageNum BIGINT = 6,         @SQL VARCHAR(100),         @Error INT,         @DatabaseName SYSNAME = 'Yoda'   -- Loop all files to the very end WHILE 1 = 1     BEGIN         BEGIN TRY             -- Build the...

posted @ Thursday, October 21, 2010 4:34 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration ]

##### Fast easter day function

CREATE FUNCTION dbo.fnGetEasterDate (     @Year SMALLINT ) RETURNS DATE AS BEGIN     RETURN (                 SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6)                 FROM    (                             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'                                         WHEN...

##### Excel XIRR function

First create a table type like this CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL ) GO And then you create a function like this CREATE FUNCTION dbo.XIRR ( @Sample MyXirrTable READONLY, @Rate DECIMAL(19, 9) = 0.1 ) RETURNS DECIMAL(38, 9) AS BEGIN DECLARE @LastRate DECIMAL(19, 9), @RateStep DECIMAL(19, 9) = 0.1, @Residual DECIMAL(19, 9) = 10, @LastResidual DECIMAL(19, 9) = 1, @i TINYINT = 0 IF @Rate IS NULL SET @Rate = 0.1 SET @LastRate = @Rate WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001 BEGIN SELECT @LastResidual = @Residual, @Residual = 0 SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E) FROM ( SELECT theValue, DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta FROM @Sample ) AS d SET @LastRate = @Rate If @Residual >= 0 SET...

posted @ Thursday, August 19, 2010 9:46 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

##### CHECKSUM weakness explained

The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation. Today, I wanted to see how often a collision (or false positive) can occur. Let's take a very simple CHECKSUM value, for example 123. Decimal 123 is "01111011" in binary representation. Since CHECKSUM function rotates the iterative checksum value 4 bits to the left (same thing as multiplying by 16), how many permutations of two characters returns the same CHECKSUM value of 123? The answer is 16 permutations. Let's investigate by writing down the solution of this...

posted @ Thursday, August 19, 2010 4:15 PM | Feedback (0) | Filed Under [ Algorithms Miscellaneous ]

##### Joe Celko's Puzzles and Answers - The Restaurant seat assignment Problem

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 INSERT  dbo.Restaurant          (             Seats         ) SELECT  0x GO -- Create procedure for handling seat assignment CREATE...

posted @ Saturday, July 31, 2010 1:20 AM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 Celko Puzzle ]

##### String concatenation and entitization

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         (             ID INT,             Data VARCHAR(100)         )   INSERT  @Sample VALUES  (1, 'Peso & Performance SQL'),         (1, 'MVP'),         (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. SELECT      i.ID,             STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '') FROM        (                 SELECT      ID                 FROM        @Sample                 GROUP...

posted @ Sunday, July 04, 2010 11:59 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Proper Relational Division With Sets

I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data. With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor. Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and...

posted @ Friday, July 02, 2010 12:48 AM | Feedback (36) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Relational division

I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem. The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be some day. For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx But for now there is no such operator, so we as developers have to find our own ways. First prepare and populate some sample data -- Prepare sample data DECLARE @Sample TABLE         (             ParentID INT NOT NULL,             Keyword VARCHAR(25) NOT NULL,             UNIQUE (ParentID, Keyword)         ) -- Populate sample data INSERT ...

posted @ Wednesday, June 30, 2010 9:41 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Another bin-packaging algorithm using recursion and XML

This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE @Sample TABLE         (             RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,             Expense SMALLMONEY NOT NULL         )   -- Populate sample data INSERT  @Sample         (             Expense         ) VALUES  (12.51),         (45.63),         (66.35),         (92.66),         (65.46),         (54.01),         (32.23),         (27.16),         (78.92),         (14.58)   Next, we need to create a variable to hold the user's wanted total sum. -- Prepare user supplied parameter DECLARE @WantedSUM SMALLMONEY = 111.09 And we also need to create a temporary staging table to hold the valid combinations   -- Prepare temporary staging table DECLARE @Temp TABLE         (             CombID INT...

posted @ Wednesday, June 30, 2010 1:44 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### The Excel Column Name assigment problem

Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel. DECLARE @Value BIGINT = 8839,         @Base TINYINT = 26   ;WITH cteSequence(Position, Value, Chr) AS (     SELECT  CAST(LOG(@Value - @Value / @Base) / LOG(@Base) AS INT),             CAST(@Value - 1 AS BIGINT),             CAST(CHAR(65 +(@Value - 1) % @Base) AS VARCHAR(MAX))       UNION ALL       SELECT  Position - 1,             Value / @Base - 1,             CHAR(65 +(Value / @Base - 1) % @Base) + Chr     FROM    cteSequence     WHERE   Position > 0 ) SELECT  Chr FROM    cteSequence WHERE   Position = 0

posted @ Wednesday, April 28, 2010 3:45 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Convert FILETIME to SYSTEM time using T-SQL

Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900. But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from...

posted @ Monday, February 08, 2010 5:55 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Get date from ISO week number

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                                                ...

posted @ Tuesday, January 26, 2010 10:57 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Celko Stumper - The Class Scheduling Problem

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...

##### Collapse date range, with safety date range

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       ...

posted @ Friday, January 08, 2010 1:07 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Create permutations, a simple way

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)

posted @ Saturday, January 02, 2010 8:15 AM | Feedback (11) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to get a date from Year, week and weekday

This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below. And according to ISO, Monday is 1 and Sunday is 7. CREATE FUNCTION dbo.fnGetDateFromYearWeekWeekday (     @Year INT,     @Week INT,     @Weekday INT ) RETURNS DATETIME AS BEGIN     RETURN CASE                 WHEN @Year < 1900 OR @Year > 9999 THEN NULL                 WHEN @Week < 1 OR @Week > 53 THEN NULL                 WHEN @Weekday < 1 OR @Weekday > 7 THEN NULL                 WHEN @Year = 9999 AND @Week = 52 And @Weekday > 5 THEN NULL                 WHEN DATEPART(YEAR, DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7...

##### Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate. CREATE FUNCTION    dbo.fnWeekdayOfPeriod (     @FromDate AS DATETIME,     @ToDate AS DATETIME,     @WeekDay TINYINT,     @Nth INT ) RETURNS DATETIME AS BEGIN     RETURN  (             SELECT  DATEADD(DAY,                     CASE                         WHEN @Weekday < theFrom %...

##### Superfast sequence generators!

Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs. Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records. My rewrite is two-part 1) The functions accepts a Starting point and an Ending point. 2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day? Here are the functions. Have fun with them! I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast. CREATE FUNCTION dbo.fnGetNumbers (     @FromNum INT,     @ToNum...

posted @ Tuesday, November 03, 2009 11:38 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### New article series going on by Itzik Ben-Gan

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed. First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734 //Peso

posted @ Monday, October 26, 2009 11:34 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Median and weighted median

Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code. Joe had a feeling an elegant query existed but was not seeing it. Anyway, since Celko's books have helped me in the past, I thought I should help him now. Consider this sample data DECLARE @Foo TABLE         (             x INT NOT NULL         ) INSERT  @Foo VALUES  (1),         (2),         (2),         (3),         (3),         (3) The most common approach to calculate the median value I have seen is SELECT  AVG(1.0E * x) FROM    (             SELECT  x,                     ROW_NUMBER() OVER (ORDER BY x DESC) AS a,                     ROW_NUMBER() OVER (ORDER BY x) AS...

posted @ Wednesday, September 16, 2009 9:56 PM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Statistical SQLCLR for prediction analysis now in RC1

For more information about my SQLCLR, see http://www.developerworkshop.net/software.html The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later. //Peso

posted @ Thursday, September 03, 2009 9:45 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms ]

##### NOT IN not equal to <>

Hi! I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum. The original poster was amazed that Col1 <> 'Some value' didn't return same records as Col1 NOT IN ('Some value') See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting. declare @sample table         (             d varchar(200)         ) Insert  @sample select  'cancelled' union all select  null -- Try 1 set ansi_nulls off select  d as [Try 1, <>, ANSI_NULLS off] from    @sample where   d <> 'cancelled' select  d as [Try 1, NOT IN, ANSI_NULLS off] from    @sample where   d not in ('cancelled') -- Try 2 set ansi_nulls on select  d as [Try...

posted @ Wednesday, September 02, 2009 4:07 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Recursive Fibonacci number calculation

The answer to your question is "Yes, I am having a slow day today." ;WITH Fibonacci(n, f, f1) AS (     SELECT  CAST(1 AS BIGINT),             CAST(0 AS BIGINT),             CAST(1 AS BIGINT)       UNION ALL       SELECT  n + 1,             f + f1,             f     FROM    Fibonacci     WHERE   n < 93 ) SELECT  n,         f AS Number FROM    Fibonacci

posted @ Wednesday, August 26, 2009 12:33 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### SQLCLR aggregate function

Phew! Now it's finally done. I haven't coded outside SQL Server since 2007 and that was with VB version 6.0. Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location Regression.dll http://regression.developerworkshop.net/dws.Regression.dll Install.sql http://regression.developerworkshop.net/Install.sql Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression) Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression) Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression) Update: * I have created a homepage for this SQLCLR function http://regression.developerworkshop.net For the Sample2 above, the result for c: drive on Server1 look like this <dws bestfit="linear" r2="0.99" type="least square...

posted @ Wednesday, August 26, 2009 12:22 PM | Feedback (5) | Filed Under [ Algorithms Miscellaneous ]

##### Third running streak

declare @t table (Id int, dt datetime, value int) set dateformat 'dmy' insert into @t   select 1, '10/12/2008', 10 union all select 1, '11/12/2008', 10 union all select 1, '12/12/2008', 10 union all select 1, '13/12/2008', 9 union all select 1, '14/12/2008', 10 union all select 1, '15/12/2008', 10 union all select 1, '16/12/2008', 10 union all select 1, '17/12/2008', 10 union all select 2, '05/03/2008', 8 union all select 2, '06/03/2008', 6 union all select 2, '07/03/2008', 8 union all select 2, '08/03/2008', 8 union all select 2, '09/03/2008', 8 union all select 2, '20/03/2008', 8   SELECT      Id,             MIN(dt) AS Startdt,             MAX(dt) AS Enddt,             MIN(value) AS Value FROM        (                 SELECT Id,                         dt,                         value,                         ROW_NUMBER() OVER (PARTITION BY Id,...

posted @ Friday, August 21, 2009 11:19 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Complement of ISNUMERIC function

Today I was involved in an interesting topic about how to check if a text string really is integer or not. This is what I finally suggested. CREATE FUNCTION dbo.fnIsINT (     @Data NVARCHAR(11) ) RETURNS INT AS BEGIN     RETURN  CASE                 WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN @Data IN('-', '+') THEN NULL                 WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL                 ELSE CAST(@Data AS INT)             END END And the BIGINT alternative CREATE FUNCTION dbo.fnIsBIGINT (     @Data NVARCHAR(20) ) RETURNS BIGINT AS BEGIN     RETURN  CASE                 WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN SUBSTRING(@Data, 1, 1) NOT LIKE...

posted @ Friday, August 14, 2009 12:18 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do. But this will work, and work fast! DECLARE  @Sample TABLE         (             Col1 INT,             Col2 INT,             Col3 INT,             Col4 INT,             Col5 INT,             Col6 INT,             Col7 INT,             Col8 DATETIME         ) INSERT  @Sample SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0,...

posted @ Wednesday, August 12, 2009 7:24 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Another running streaks algorithm

It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx will be better for you? Below, I am using the same sample data as the original. DECLARE @Sample TABLE         (             GameDate DATETIME,             Result CHAR(1)         ) INSERT @Sample SELECT '1/1/2000', 'W' UNION ALL SELECT '1/12/2000', 'L' UNION ALL SELECT '1/15/2000', 'W' UNION ALL SELECT '1/17/2000', 'W' UNION ALL SELECT '1/22/2000', 'W' UNION ALL SELECT '2/1/2000', 'L' UNION ALL SELECT '2/5/2000', 'W' UNION ALL SELECT '2/8/2000', 'L' UNION ALL SELECT '2/16/2000', 'W' UNION ALL SELECT '2/19/2000', 'L' UNION ALL SELECT '2/25/2000', 'L' UNION ALL SELECT '2/28/2000', 'L' UNION ALL SELECT '3/15/2000', 'L' UNION ALL SELECT '3/19/2000', 'W' UNION ALL SELECT '3/25/2000', 'W' For this to...

posted @ Wednesday, August 12, 2009 12:49 AM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Fast and Simple Prime Number Factorization

This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2..2,147,483,647} you only need the first 4,792 primes {2..46,337} DECLARE @Number BIGINT   SET     @Number = 2020208534430421   SELECT  Prime AS Number,         CAST(1 AS TINYINT) AS Items INTO    #Temp FROM    Primes WHERE   Prime <= SQRT(@Number)         AND @Number % Prime = 0 SELECT  @Number = @Number / Number FROM    #Temp WHILE @@ROWCOUNT > 0     UPDATE  #Temp     SET     Items = Items + 1,             @Number = @Number / Number     WHERE   @Number % Number = 0 SELECT  Number,         Items FROM    #Temp UNION ALL SELECT  @Number,         1 WHERE   @Number >...

posted @ Sunday, August 02, 2009 2:12 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to insert into two tables in one statement

Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement. Right now, I can't see a practical use, but that might change in the future. As long as I remember this blog post exists... DECLARE @Source TABLE         (             a INT,             b INT         )   INSERT  @Source SELECT  11, 21 UNION ALL SELECT  12, 22   DECLARE @PrimaryTarget TABLE         (             a INT         )   DECLARE @SecondaryTarget TABLE         (             b INT         )   MERGE   @PrimaryTarget AS pt USING   @Source AS s ON 1 = 1 WHEN    NOT MATCHED BY TARGET         THEN    INSERT  (                             a                         )                 VALUES  (                             s.a                         ) OUTPUT  s.b INTO    @SecondaryTarget;   SELECT  'Source' AS TableName,         a,         b FROM   ...

posted @ Wednesday, July 29, 2009 6:16 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms ]

##### Simple base converter

Here is a simple base converter that manages [from and to] base 2 to 16. CREATE FUNCTION    dbo.fnBaseConvert (     @Value VARCHAR(8),     @FromBase TINYINT,     @ToBase TINYINT ) RETURNS VARCHAR(32) AS BEGIN     RETURN (                 SELECT     SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)                 FROM        (                                 SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue                                 FROM    master..spt_values                                 WHERE   Type = 'P'                                         AND Number < LEN(@Value)                                 HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0                                         AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase                             ) AS x                 INNER JOIN  master..spt_values AS v ON v.Type = 'P'                 WHERE       x.theValue >= 0                             AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue,...

posted @ Tuesday, July 21, 2009 1:18 AM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to get a batch of identity values without OUTPUT

Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch. Well, since OUTPUT is not an option for SQL Server 2000, and if you don't want to include temp tables or other means, this is one way how to solve this issue. The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved. CREATE TABLE    #Sample                 (                    ...

posted @ Thursday, July 16, 2009 11:37 PM | Feedback (4) | Filed Under [ Algorithms SQL Server 2000 ]

##### EXISTS is my new favorite tool

Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative. Well, today I have. Recently I blogged here http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx about finding records in one table which are missing in another table. In that blog post, I noticed that NOT EXISTS were the better choice for all scenarios, and have learnt to adopt the NOT EXISTS approach. Why haven't I extended that knowledge to the "Certain status" scenario? I can't tell. And that is a good thing. Because being active on a good forum now and then makes you rethink your positions. As I did today. Here is the test case from which...

posted @ Monday, July 13, 2009 9:36 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to calculate the number of weekdays for any given period

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year. This code below calculates the number of weekdays for any given period. CREATE FUNCTION dbo.fnPeriodWeekdays (     @StartDate DATETIME,     @EndDate DATETIME ) RETURNS INT AS BEGIN     RETURN  (                 DATEDIFF(DAY, @StartDate, @EndDate) + 1                 + (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2                 - (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2                 - CASE DATEDIFF(DAY, '17530101', @StartDate) % 7                       WHEN 6 THEN 1                       ELSE 0                   END                 - CASE DATEDIFF(DAY, '17530101', @EndDate) % 7                       WHEN 5 THEN 1                       ELSE 0                   END         ) END

posted @ Sunday, July 12, 2009 2:29 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to calculate the number of weekdays in a month

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnMonthWeekDays (     @Year SMALLINT,     @Month TINYINT ) RETURNS TINYINT AS BEGIN     RETURN (                 SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                 FROM    (                             SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 31) = 1                               UNION ALL                               SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 30) = 1                               UNION ALL                               SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29)                                         WHEN 1 THEN DATEADD(MONTH,...

posted @ Sunday, July 12, 2009 1:50 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to calculate number of weekdays in a year

This function calculates the number of weeksdays in a year, and has error-checking for invalid years. It is also language independant. Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnYearWeekDays (     @Year SMALLINT ) RETURNS SMALLINT AS BEGIN         RETURN  (                     SELECT  260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                     FROM    (                                 SELECT  DATEADD(YEAR, @Year - 1899, -1) AS dt                                   UNION ALL                                                                                                     SELECT  DATEADD(YEAR, @Year - 1899, -2)                                 WHERE   ISDATE(10000 * @Year + 229) = 1                             ) AS d                     WHERE   @Year BETWEEN 1753 AND 9999                 ) END

posted @ Sunday, July 12, 2009 12:31 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### JDEdwards date conversion

It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year. So how to convert JDEdwards to DATETIME? DECLARE @jde NUMERIC(6, 0) SET     @jde = 107299 SELECT @jde,         DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))   And how to go from DATETIME to JDEdwards? DECLARE @Date DATETIME SET     @Date = '20041117' SELECT @Date,         1000 * DATEDIFF(YEAR, 0, @Date) + DATEPART(DAYOFYEAR, @Date)

posted @ Friday, July 10, 2009 4:23 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function. CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT,     @theType CHAR(1) ) RETURNS DATETIME BEGIN     RETURN (                 SELECT theDate                 FROM    (                             SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate                             FROM    (                                         SELECT CASE UPPER(@theType)                                                     WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')                                                     WHEN 'Q'...

posted @ Thursday, June 18, 2009 11:34 AM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to get the Weekday and Nth from a date

You call this function with a date. The function returns a table with one record and 3 columns. First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7. Second column is number of occurencies of that date since beginning of selected period type. Third columns is number of occurencies left of that period type. CREATE FUNCTION dbo.fnGetWeekdayAndNths (     @theDate DATETIME,     @theType CHAR(1) ) RETURNS TABLE AS RETURN (   SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,                     1 +(theDelta - 1) / 7 AS Beginning,                     DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1...

posted @ Thursday, June 18, 2009 2:28 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to get the Nth weekday of a month

You call this function with three parameters: 1. Any date of the month in question 2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7 3. The choice of weekday count; a positive number means from the beginning of month and a negative number means from the end of month If a valid date cannot be calculated, NULL is returned. For an extended version which, besides month, also handles quarter and year, see new blog post here http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT ) RETURNS DATETIME BEGIN     RETURN  (                 SELECT  theDate                 FROM    (                             SELECT  DATEADD(DAY, 7 * @theNth...

posted @ Wednesday, June 17, 2009 9:05 PM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Timings of different techniques for finding missing records

I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler. Number of reads are equal between methods but different depending how many record in #TableB. If there are other methods I haven't included, please let me know. Method      TableA   TableB   CPU  Duration ----------  -------  -------  ---  -------- GROUP BY    1000000  1000000  748       754 LEFT JOIN   1000000  1000000  328       321 NOT EXISTS  1000000  1000000  265       288 NOT IN      1000000  1000000  296       293 EXCEPT      1000000  1000000  312       288 GROUP BY    1000000   500000  577      2984 LEFT JOIN   1000000   500000...

posted @ Friday, June 12, 2009 3:22 PM | Feedback (11) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Extract XML structure automatically, part 2

For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet. However, the previous algorithm relied on a WHILE loop here http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx but this new algorithm doesn't. It's all xml internal thingies going on. DECLARE     @Nodes TABLE             (                          NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                          ParentNodeName NVARCHAR(64),                          NodeName NVARCHAR(64)             ) DECLARE @Data XML SET @Data = ' <root>         <elementGroup>                         <element>                                      <stuff>                                                  <comment>Stuff comment</comment>                                      </stuff>                                      <comment>Element comment</comment>                         </element>                         <comment>Element group comment</comment>             </elementGroup>             <comment>Root comment</comment> </root>' INSERT      @Nodes             (                          ParentNodeName,                          NodeName             ) SELECT      e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,            ...

posted @ Thursday, June 04, 2009 1:30 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to check Danish personal number

CREATE FUNCTION dbo.fnCheckDanSSN (             @SSN CHAR(10) ) RETURNS BIT AS BEGIN     IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'         RETURN 0       DECLARE @x CHAR(6)         SET     @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2)       IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2)...

posted @ Friday, May 29, 2009 2:26 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to calculate Finnish personal number

CREATE FUNCTION dbo.fnCalculateFinSSN (             @SSN CHAR(10) ) RETURNS CHAR(11) AS BEGIN     IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9]'         RETURN NULL       IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN NULL       IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       DECLARE @Digits INT       SET     @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31       RETURN @@SSN + SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) END

posted @ Friday, May 29, 2009 2:07 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to check Finnish personal number

CREATE FUNCTION dbo.fnCheckFinSSN (             @SSN CHAR(11) ) RETURNS BIT AS BEGIN     IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9][0-9a-y]'         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       DECLARE @Digits INT       SET     @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31       IF SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) <> LOWER(RIGHT(@SSN, 1))         SET @Digits = -1       RETURN @Digits + 1 END

posted @ Friday, May 29, 2009 1:46 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Get this group but not that group

Wow! This seems to be blog post number 100. Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped". Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11. This can be done in a number of ways, but I wanted to write an efficiently running query. DECLARE     @Sample TABLE             (                         MeetingID INT,                          TicketID INT             )   INSERT      @Sample SELECT      1, 11 UNION ALL SELECT      1, 22 UNION ALL SELECT      1, 33 UNION ALL SELECT      2, 22 UNION ALL SELECT      2, 33 UNION ALL SELECT      3, 11 UNION ALL SELECT     ...

posted @ Friday, May 29, 2009 10:57 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Extended ISO week function

CREATE FUNCTION    dbo.fnISOWEEK (     @theDate DATETIME ) RETURNS TINYINT AS BEGIN     RETURN (                 SELECT CASE                             WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7                             WHEN @theDate >= '99990101' THEN 52                             WHEN NextYear <= @theDate THEN 0                             WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7                             ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7                         END + 1                 FROM    (                             SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,                                     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, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4                                     ) AS x                         )...

posted @ Wednesday, May 27, 2009 9:52 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Double duty

I was browsing some of my older solutions, and I stumbled across this interesting piece. The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'. DECLARE     @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1)) INSERT      @Roles SELECT      'Smith', 'O' UNION ALL SELECT      'Smith', 'D' UNION ALL SELECT      'Jones', 'O' UNION ALL SELECT      'White', 'D' UNION ALL SELECT      'Brown', 'X' SELECT      Person,             SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty FROM        @Roles WHERE       [Role] IN ('D', 'O') GROUP BY    Person

posted @ Tuesday, April 21, 2009 3:24 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Greatest Common Divisor function

This approach is not dependent on the 32-level recursion as most other algorithms are for this problem. CREATE FUNCTION dbo.fnGCD (             @a INT,             @b INT ) RETURNS INT AS BEGIN             DECLARE     @c INT               IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)                          RETURN NULL               IF @a = 0 OR @b = 0                          RETURN ABS(@a) + ABS(@b)               IF ABS(@a) < ABS(@b)                          SELECT      @c = ABS(@a),                                      @a = ABS(@b),                                      @b = @c             ELSE                          SELECT      @a = ABS(@a),                                      @b = ABS(@b)               SET         @c = @a % @b               WHILE @c > 0                          SELECT      @a = @b,                                      @b = @c,                                      @c = @a %...

posted @ Wednesday, April 15, 2009 10:17 AM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### One way to calculate decimal year

Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent. The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that. First, set up a test environment by declaring and setting two datetime variables like this declare @f datetime...

posted @ Wednesday, April 01, 2009 10:09 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this: DECLARE     @String VARCHAR(MAX),             @Size TINYINT SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ',             @String = @String + 'right now this is how the application i am working ',             @String = @String + 'with displays data.',             @Size = 32 SELECT      1 + Number AS Part,              SUBSTRING(@String, Number * @Size, @Size) FROM        master..spt_values WHERE       Type = 'P'...

posted @ Wednesday, March 18, 2009 11:11 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Excel DAYS360 clone

CREATE FUNCTION dbo.DATEDIFF360 (     @source DATETIME,     @target DATETIME,     @style BIT = 0 ) RETURNS INT AS BEGIN         RETURN  CASE @style                     -- European style                     WHEN 1 THEN CASE                                     WHEN DATEPART(DAY, @target) = 31...

posted @ Sunday, March 15, 2009 4:12 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Excel ERF clone for two variables

I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables. CREATE FUNCTION dbo.fnErf (             @z1 FLOAT,             @z2 FLOAT,             @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN             IF @z1 IS NULL OR @z2 IS NULL                          RETURN      NULL               DECLARE     @n TINYINT,                          @s1 FLOAT,                          @s2 FLOAT,                          @p1 FLOAT,                          @p2 FLOAT,                          @a1 FLOAT,                          @a2 FLOAT               SELECT      @n = 1,                          @p1 = 1,                          @p2 = 1,                          @a1 = @z1,                          @a2 = @z2,                          @MaxIterations = COALESCE(ABS(@MaxIterations), 10)               WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations                          SELECT      @s1 = @z1 /(2.0E * @n + 1.0E),                                      @s2 = @z2 /(2.0E *...

posted @ Wednesday, March 11, 2009 2:55 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Excel ERF clone

This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with SELECT  dbo.fnErf(0.35, DEFAULT) However, if you feel the need to more accuracy, replace second parameter with a value of your choice. Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables. CREATE FUNCTION dbo.fnErf (         @z FLOAT,         @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN         IF @z IS NULL                 RETURN      NULL         DECLARE @n TINYINT,                 @s FLOAT,                 @p FLOAT,                 @a FLOAT         SELECT  @n = 1,                 @p = 1,                 @a = @z,                 @MaxIterations = COALESCE(ABS(@MaxIterations), 10)         WHILE @p <> 0.0E AND @n <= @MaxIterations                 SELECT  @s = @z /(2.0E * @n...

posted @ Wednesday, March 11, 2009 1:59 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to efficiently reuse gaps in identity column

Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values. I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used. That method is not efficient. What if you have 1 million records and there is only 1 gap at...

posted @ Tuesday, March 10, 2009 9:06 AM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Extract XML structure automatically

Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file. I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table. See comment in code to understand what happens CREATE PROCEDURE dbo.uspGetFileStructureXML (     @FileName NVARCHAR(256) ) AS -- Prevent unwanted resultsets back to client SET NOCOUNT ON -- Initialize...

posted @ Thursday, March 05, 2009 3:06 PM | Feedback (9) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Fastest LeapYear checker?

CREATE FUNCTION dbo.fnIsLeapYear (             @Year SMALLINT ) RETURNS BIT AS BEGIN             RETURN      CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229'))                                      WHEN 29 THEN 1                                      ELSE 0                          END END

##### Improved anniversary calculation (better datediff)

Some time ago, I wrote this article about how DATEDIFF works. http://www.sqlteam.com/article/datediff-function-demystified At the end I suggested two functions to calculate the number of months according to how human mind works. At the discussion later, a person notified me that it calculated the wrong number of months if you tried January 29th 2009 to February 28th 2009. The day is still greater, but you cannot have more days in february 2009 than 28. These are improved functions that also deals with those situations. CREATE FUNCTION [dbo].[fnMonthsApart] (     @FromDate DATETIME,     @ToDate DATETIME ) RETURNS INT AS BEGIN         RETURN  CASE                     WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1                     ELSE DATEDIFF(MONTH,...

##### Alternative approach to calculate most used time interval

In the past I have given the advice to break down all date intervals into the smallest part, most often minutes. Then OP should group by the minute. -- Prepare sample data DECLARE     @Data TABLE             (                          RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                          CreateDate DATETIME,                          DeleteDate DATETIME             ) -- Populate sample data INSERT      @Data             (                          CreateDate,                          DeleteDate             ) SELECT      '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL SELECT      '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL SELECT      '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL SELECT      '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT      '2009-01-14 22:38', '2009-01-14 22:38' UNION...

posted @ Friday, January 30, 2009 9:03 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Create another nested XML hierarchy

-- Prepare sample data DECLARE     @Sample TABLE             (                          GalaxyID INT,                          ObjectID VARCHAR(16),                          ObjectType VARCHAR(5),                          ObjectTitle VARCHAR(200)             ) INSERT      @Sample SELECT      1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL SELECT      1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL SELECT      1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL SELECT      1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL SELECT      2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL SELECT      2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL SELECT      2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL SELECT      2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL SELECT      3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL SELECT      3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL SELECT      3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL SELECT      3, 'T011820001524538', 'Topic', 'Drivers...

posted @ Tuesday, January 20, 2009 9:50 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Easy script for calculating weekday or weekend for a date

SELECT d.theDate,         DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,         1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday FROM    (                 SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL                 SELECT '20081125' UNION ALL                 SELECT '20081126' UNION ALL                 SELECT '20081127' UNION ALL                 SELECT '20081128' UNION ALL                 SELECT '20081129' UNION ALL                 SELECT '20081130'         ) AS d

posted @ Wednesday, January 14, 2009 2:03 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### One way to resolve and calculate fractional strings

CREATE FUNCTION dbo.fnResolveFractionals (             @data VARCHAR(20) ) RETURNS FLOAT AS BEGIN             RETURN      CASE                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)                                      ELSE NULL                          END END GO   DECLARE     @Sample TABLE             (                          data VARCHAR(20)             )   INSERT      @Sample SELECT      '5 3/16' UNION ALL SELECT      '7' UNION ALL SELECT      '2 /' UNION ALL SELECT     ...

posted @ Monday, December 15, 2008 3:01 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Find popular combos

DECLARE     @Sample TABLE             (                  StudentID INT,                  Class VARCHAR(20)             )   INSERT      @Sample SELECT      1, 'Maths' UNION ALL SELECT      1, 'English' UNION ALL SELECT      1, 'Science' UNION ALL SELECT      2, 'Maths' UNION ALL SELECT      2, 'English' UNION ALL SELECT      2, 'Science' UNION ALL SELECT      2, 'History' UNION ALL SELECT      3, 'English' UNION ALL SELECT      3, 'Maths' UNION ALL SELECT      3, 'Science' UNION ALL SELECT      3, 'RE'  UNION ALL SELECT      4, 'Science' UNION ALL SELECT      4, 'Maths' UNION ALL SELECT      4, 'English' UNION ALL SELECT      4, 'History' UNION ALL SELECT      4, 'French'   ;WITH Yak(ClassName, ClassPath, Combinations) AS (             SELECT      Class,                         CAST(Class AS VARCHAR(MAX)),                         CAST(1 AS INT)             FROM        @Sample             GROUP BY    Class               UNION ALL               SELECT     s.Class,                        y.ClassPath + '-' + s.Class,                        y.Combinations + 1             FROM       Yak AS y             INNER JOIN @Sample AS s ON s.Class > y.ClassName            ...

posted @ Tuesday, December 09, 2008 4:16 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### An alternative to IDENTITY column

Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources. And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%. If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...

##### Expand network using CTE without circular reference

Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference. See topic here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290 You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.   Here is the result.   DECLARE @Stations TABLE       (             stationID INT,             name VARCHAR(255)       )   INSERT      @Stations SELECT      1, 'Glasgow' UNION ALL SELECT      2, 'Edinburgh' UNION ALL SELECT      3, 'York' UNION ALL SELECT      4, 'London' UNION ALL SELECT      5, 'Aberdeen' UNION ALL SELECT      6, 'Bjuv'   DECLARE @Links TABLE      ...

posted @ Thursday, November 27, 2008 4:00 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Run jobs synchronously

If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished? Try this CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job (        @jobName SYSNAME ) AS   SET NOCOUNT ON   DECLARE       @jobID UNIQUEIDENTIFIER,        @maxID INT,        @status INT,        @rc INT   IF @jobName IS NULL       BEGIN             RAISERROR('Parameter @jobName have no value.', 16, 1)             RETURN -100       END   SELECT @jobID = job_id FROM   msdb..sysjobs WHERE name = @jobName   IF @@ERROR <> 0       BEGIN             RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)             RETURN -110       END   IF @jobID IS NULL       BEGIN             RAISERROR('Job %s does not exist.', 16, 1, @jobName)             RETURN -120       END   SELECT @maxID...

##### Half Hour Impacts

Yesterday I came across this question on another forum.   I am trying to come up with a way to identify the half hour impact from several exceptions across multiple days. I have access to SQL 2000 and SQL 2005. The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM), stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact. When I have one exception with a start time of 7:15 and an end time of 8:20, I would like to see...

posted @ Thursday, November 27, 2008 10:50 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Finding streaks in data

A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx And this is an alternative way to find your streaks -- Prepare sample data SET NOCOUNT ON   DECLARE       @GameResults TABLE        (               gameID INT,               homeScore INT,               awayScore INT        )   INSERT @GameResults SELECT 1, 2, 1 UNION ALL SELECT 2, 4, 1 UNION ALL SELECT 3, 4, 3   DECLARE       @Program TABLE        (               gameID INT,               gameDate DATETIME,               homeID INT,               awayID INT        )   INSERT @Program SELECT 1, '2008-05-12', 101, 102 UNION ALL SELECT 2, '2008-05-20', 106, 101 UNION ALL SELECT 3, '2008-05-14', 107, 101   -- Prepare staging data DECLARE       @Stage TABLE        (               teamID INT,               gameDate DATETIME,               outcome CHAR(3),               streak INT,              ...

posted @ Wednesday, November 26, 2008 1:25 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Easy sorted numeric pivoting with maximum three columns

-- Prepare sample data DECLARE       @Sample TABLE        (               ID INT,               col INT        )   INSERT @Sample SELECT 0, 1 UNION ALL SELECT 0, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 6 UNION ALL SELECT 3, 6 UNION ALL SELECT 5, 8 UNION ALL SELECT 5, 9 UNION ALL SELECT 4, 7   -- Pivot the source data SELECT        ID,               MIN(col) AS col1,               CASE COUNT(*)                      WHEN 1 THEN NULL                      WHEN 2 THEN MAX(col)                      ELSE SUM(col) - MIN(col) - MAX(col)               END AS col2,               CASE COUNT(*)                      WHEN 3 THEN MAX(col)                     ...

##### Datetime manipulation - Time only by Itzik Ben-Gan

See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html  And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html

posted @ Monday, November 24, 2008 5:17 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Bin packaging

With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm.   This new algorithm is about finding all possible sums and how many combinations you have of each sum. Have fun!   DECLARE       @Data TABLE        (               faceValue MONEY,               maxItems INT,               permCount INT        ) INSERT        @Data               (                      faceValue,                      maxItems               ) SELECT        faceValue,               1 + COUNT(*) FROM          (                      SELECT 899 AS faceValue UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION...

posted @ Sunday, November 23, 2008 3:32 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to get the productsum from a table

IF EXISTS(SELECT * FROM YourTable WHERE Number = 0)     SELECT 0.0E ELSE     SELECT CASE IsNegativeProduct                WHEN 1 THEN -EXP(theSum)                ELSE EXP(theSum)            END     FROM   (                SELECT SUM(LOG(ABS(Number))) AS theSum,                       SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct                FROM   YourTable            ) AS d

posted @ Wednesday, November 19, 2008 2:13 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

SELECT        * FROM          ::fn_dblog(DEFAULT, DEFAULT) AS l INNER JOIN    sysobjects AS so ON so.name = l.[transaction name] SELECT        so.name AS ObjectName,               so.type AS ObjectType,               MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM          ::fn_dblog(DEFAULT, DEFAULT) l inner join    sysobjects so on so.name = l.[transaction name] --where              so.type = 'u' GROUP BY      so.name,               so.type ORDER BY      so.name,               so.type

posted @ Thursday, November 13, 2008 9:49 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Stripping out all non-numeric characters from a string

DECLARE @Value NVARCHAR(200) SET     @Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE @Value LIKE '%[^0-9]%'         SET     @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT  @Value

##### Getting date or time only from a Datetime value

SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly

##### Bayesian Estimate and Root Mean Square

Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item. And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking. Well, have you looked a Bayesian Estimate? DECLARE       @Sample TABLE        (               userID INT,               vote INT        ) INSERT @Sample SELECT 3, 40 UNION ALL SELECT 3, 60 UNION ALL SELECT 0, 100 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 90 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 90 UNION ALL SELECT 1, 100 UNION ALL SELECT...

posted @ Monday, October 27, 2008 11:14 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Sequencies in string

DECLARE @s VARCHAR(100)   SET    @s = 'aardddvaaaarrkkkk'   -- Highest frequency of same character SELECT TOP 1 WITH TIES               [char],               COUNT(*) AS cnt FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               ) AS q GROUP BY      [char] ORDER BY      COUNT(*) DESC   -- Longest sequence of same character SELECT TOP 1 WITH TIES               [char],               CASE [seq]                      WHEN 0 THEN DATALENGTH(@s) - Number                      ELSE [seq]               END AS [seq] FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char],                            Number,                            PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               )...

posted @ Monday, October 27, 2008 8:32 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

SELECT        number,               CASE                      WHEN number % 100 IN (11, 12, 13) THEN 'th'                      WHEN number % 10 = 1 THEN 'st'                      WHEN number % 10 = 2 THEN 'nd'                      WHEN number % 10 = 3 THEN 'rd'                      ELSE 'th'               END AS Ordinal FROM          master..spt_values WHERE         type = 'p' ORDER BY      number

posted @ Monday, October 27, 2008 8:29 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Delete all subset records

Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record. See this sample data DECLARE       @Sample TABLE        (               recID INT IDENTITY(1, 1),               col1 VARCHAR(1),               col2 VARCHAR(2),               col3 VARCHAR(3),               userID INT        ) INSERT @Sample SELECT 'A', 'B', 'C', 1 UNION ALL SELECT 'A', 'B', ' ', 1 UNION ALL SELECT 'A', ' ', 'C', 1 UNION ALL SELECT 'F', ' ', 'C', 1 UNION ALL SELECT ' ', 'M', ' ', 2 UNION ALL SELECT 'T', 'M', 'O', 2 UNION ALL SELECT ' ', 'M', 'O', 2 UNION ALL SELECT 'X', 'M', 'O', 2...

##### Keep track of all your jobs schedules

This is a piece of code I use to create a resultset from and display in Outlook calendar. CREATE PROCEDURE dbo.uspGetScheduleTimes (        @startDate DATETIME,        @endDate DATETIME ) AS /*     This code is blogged here     http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx */ SET NOCOUNT ON   -- Create a tally table. If you already have one of your own please use that instead. CREATE TABLE #tallyNumbers               (                      num SMALLINT PRIMARY KEY CLUSTERED               )   DECLARE       @index SMALLINT   SET    @index = 1   WHILE @index <= 8640        BEGIN               INSERT #tallyNumbers                      (                            num                      )               VALUES (                            @index                      )                 SET    @index = @index + 1        END   -- Create a staging table for jobschedules CREATE TABLE #jobSchedules               (                      rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                      serverName SYSNAME NOT NULL,                     ...

##### Excerpt from The Compression Session

This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to. With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...

posted @ Thursday, October 09, 2008 8:39 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration ]

##### SUM and JOIN together

DECLARE    @a TABLE (pk INT) DECLARE    @b TABLE (fk INT, i INT) DECLARE    @c TABLE (fk INT, j INT) INSERT    @a SELECT    1 UNION ALL SELECT    2 UNION ALL SELECT    3 INSERT    @b SELECT    1, 1 UNION ALL SELECT    1, 3 UNION ALL SELECT    2, 4 UNION ALL SELECT    2, 8 UNION ALL SELECT    2, 10 UNION ALL SELECT    3, 1 INSERT    @c SELECT    1, 11 UNION ALL SELECT    1, 13 UNION ALL SELECT    2, 14 UNION ALL SELECT    2, 18 UNION ALL SELECT    2, 60 UNION ALL SELECT    3, 11 -- Wrong way SELECT        a.pk,         SUM(b.i) AS SumAct,...

posted @ Wednesday, October 01, 2008 3:48 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

CREATE PROCEDURE dbo.uspCreatePassword (     @UpperCaseItems SMALLINT,     @LowerCaseItems SMALLINT,     @NumberItems SMALLINT,     @SpecialItems SMALLINT ) AS SET NOCOUNT ON -- Initialize some variables DECLARE    @UpperCase VARCHAR(26),     @LowerCase VARCHAR(26),     @Numbers VARCHAR(10),     @Special...

posted @ Monday, September 29, 2008 2:35 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Simple FAQ search algorithm

-- Prepare sample data DECLARE       @Keywords TABLE        (               FaqID INT,               Keyword VARCHAR(200)        ) INSERT @Keywords SELECT 1, 'help' UNION ALL SELECT 1, 'resolve' UNION ALL SELECT 1, 'issue' UNION ALL ...

##### Extract UK postcode

CREATE FUNCTION dbo.fnExtractPostCodeUK (        @Data VARCHAR(8000) ) RETURNS VARCHAR(8) AS BEGIN         RETURN        COALESCE(                            -- AANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- AANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- ANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- AAN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- ANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            --   AN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' '...

posted @ Wednesday, August 13, 2008 2:32 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Validate UK postcode

CREATE FUNCTION dbo.fnValidatePostCodeUK (        @PostCode VARCHAR(8) ) RETURNS BIT AS BEGIN        RETURN CASE                      --   Special case GIR 0AA                      WHEN @PostCode LIKE 'GIR 0AA' THEN 1                      -- Current postcode prefixes                      WHEN   LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP',...

posted @ Wednesday, August 13, 2008 1:46 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to sum up an unknown number of records

-- Initialize the search parameter DECLARE       @WantedValue INT   SET    @WantedValue = 221   -- Stage the source data DECLARE       @Data TABLE        (               RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,               MaxItems INT,               CurrentItems INT DEFAULT 0,               FaceValue INT,               BestUnder INT DEFAULT 0,               BestOver INT DEFAULT 1        )   -- Aggregate the source data INSERT        @Data               (                      MaxItems,                      FaceValue               ) SELECT        COUNT(*),               Qty FROM          (                      SELECT 899 AS Qty UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                     ...

posted @ Tuesday, August 12, 2008 5:06 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Another sequencing algorithm

This problem originated here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y and I post the solution here for two reasons. 1) The forum above does not support code tags 2) The common interest is high enough -- Prepare sample data DECLARE @Sample TABLE         (              HoleID CHAR(8),              mFrom SMALLMONEY,              mTo SMALLMONEY,              Result SMALLMONEY,              PRIMARY KEY CLUSTERED              (                  HoleID,                  mFrom              ),              Seq INT          ) INSERT  @Sample          (              HoleID,              mFrom,              mTo,              Result          ) SELECT  'TWDD0004',   1   ,   2   ,  0.86 UNION ALL SELECT  'TWDD0004',   3   ,   4   ,  8.93 UNION ALL SELECT  'TWDD0004',   4   ,   5   ,  2.78 UNION ALL SELECT  'TWDD0004',   8   ,  ...

posted @ Tuesday, August 12, 2008 2:24 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to tell if a number is a "POWER of 2"-number

SELECT  Number,         1 - SIGN(Number & (Number - 1)) FROM    master..spt_values WHERE   Type = 'P'         AND Number > 0   Other way is   CREATE FUNCTION dbo.isPowerOf2 (       @i INT ) RETURNS BIT AS BEGIN       DECLARE @x FLOAT          SET @x = LOG(Number) / LOG(2)       RETURN      CASE                   WHEN FLOOR(@x) = CEILING(@x) THEN 1                   ELSE 0             END END

posted @ Saturday, August 09, 2008 1:22 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### PIVOT conundrum

I started out with typing SELECT @@VERSION and got the result as Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86)         Dec 8 2007 18:51:32         Copyright (c) 1988-2005 Microsoft Corporation        Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) So far so good. Now I created some sample data like this -- Prepare sample data DECLARE     @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9)) INSERT      @Sample SELECT      1, 10, '7/5/2008', 'Opened' UNION ALL SELECT      2, 11, '7/5/2008', 'Closed' UNION ALL SELECT      3, 12, '7/5/2008', 'Closed' UNION ALL SELECT      4, 13, '7/6/2008', 'Opened' UNION ALL SELECT      4, 14, '7/6/2008', 'Opened' UNION ALL SELECT      4, 15, '7/6/2008', 'Closed' UNION ALL SELECT      1, 16, '7/7/2008',...

posted @ Tuesday, July 15, 2008 4:00 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### How to get authentication mode in SQL Server

CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNS INT AS   BEGIN       DECLARE @InstanceName NVARCHAR(1000),             @Key NVARCHAR(4000),             @LoginMode INT         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',                         N'MSSQLSERVER',                         @InstanceName OUTPUT         IF @@ERROR <> 0 OR @InstanceName IS NULL             RETURN NULL         SET   @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         @Key,                         N'LoginMode',                         @LoginMode OUTPUT         RETURN @LoginMode END

posted @ Thursday, June 19, 2008 5:10 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

##### How to change authentication mode in SQL Server

posted @ Thursday, June 19, 2008 5:04 PM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

##### Some SQL Server network properties

DECLARE       @Stage TABLE               (                      RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,                      Data VARCHAR(90),                      Section INT               )   INSERT @Stage               (                      Data               ) EXEC   xp_cmdshell 'ipconfig /all'   DECLARE       @Section INT   SET    @Section = 0   UPDATE @Stage SET    @Section = Section = CASE WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE @Section END   SELECT        MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,               MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,               MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,               MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...

posted @ Thursday, June 19, 2008 3:57 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

##### Lightning fast collapsed date ranges and missing date ranges

The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs. Naturally I posted the link to the Script Library topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422 Traditional T-SQL proved to be very inefficient. Even when using the CTE approch which proved to be second fastest and still 100 to 1,600 times slower! I started out with creating 1,000 date pairs with following code -- Prepare sample data CREATE TABLE #ProcessCellAllocation               (                      AllocationID INT IDENTITY(1, 1) NOT NULL,                      ProcessCell VARCHAR(50) NOT NULL,                      DateFrom DATETIME NOT NULL,                      DateTo DATETIME,                      Seq INT               ) INSERT        #ProcessCellAllocation                 (                     ...

posted @ Tuesday, May 13, 2008 4:16 PM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Some XML search approaches

I just played around with some different techniques to fetch relevant data from XML content.   DECLARE       @XMLString XML,        @Search VARCHAR(50)   SELECT @XMLString = '                            <Customers>                                   <Customer>                                          <FirstName>Kevin</FirstName>                                          <LastName>Goff</LastName>                                          <City type="aca">Camp Hill</City>                                   </Customer>                                   <Customer>                                          <FirstName>Steve</FirstName>                                          <LastName>Goff</LastName>                                          <City type="acb">Philadelphia</City>                                   </Customer>                            </Customers>',               @Search = 'Camp Hill'   -- Get all customers living in Camp Hill SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1   -- Get all customers living in a City containing the text "adel" SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1   -- Get...

posted @ Wednesday, March 26, 2008 11:17 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Search all code for specific keyword

This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.ID) AS RoutineName, (SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body FROM SYSOBJECTS AS so WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X') ) AS p WHERE p.Body LIKE '%YourKeyWordHere%' The types are C = CHECK constraint D = Default or DEFAULT constraint FN = Scalar function IF = In-lined table-function...

posted @ Friday, March 14, 2008 2:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 ]

##### Efficient pagination for large set of data?

This is what I pondered about today. Maybe I also will have some time to test it.   CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550

posted @ Tuesday, February 19, 2008 4:12 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Finding table reference levels and simulating cascading deletes

I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454   The general idea is to have a generic purge functionality.

##### Curiosity found, the wrap

After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint. It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes. DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY) INSERT  @Items SELECT  1 UNION ALL SELECT  2 SELECT  * FROM    @Items INSERT  @Items SELECT  1 INSERT  @Items SELECT  2 INSERT  @Items SELECT  3 SELECT  * FROM    @Items

##### Algorithm to sort strings mixed with Alpha and Numeric values.

CREATE FUNCTION dbo.fnSplitType (         @Data VARCHAR(200),         @PartSize TINYINT ) RETURNS VARCHAR(8000) AS BEGIN         DECLARE @Result VARCHAR(8000),                 @Alpha TINYINT,                 @OldPosition SMALLINT,                 @NewPosition SMALLINT         SELECT  @Result = '',                 @Alpha = 1,                 @OldPosition = 1,                 @NewPosition = 1         IF @Data LIKE '[0-9]%'                 SELECT  @Result = REPLICATE(' ', @PartSize),                         @Alpha = 0         WHILE @NewPosition < LEN(@Data)                 SELECT  @NewPosition =  CASE @Alpha                                                 WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))                                                 ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))                                         END,                         @NewPosition =  CASE @NewPosition                                                 WHEN 0 THEN LEN(@Data)                                                 ELSE @OldPosition + @NewPosition - 2                                         END,                         @Result = @Result +     CASE @Alpha                                                         WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)                                                         ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)                                                 END,                         @Alpha = 1 - @Alpha,                         @OldPosition = @NewPosition + 1         RETURN  RTRIM(@Result) END Here is...

posted @ Wednesday, December 19, 2007 2:20 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Cursor is really faster than set-based solution for weighted moving average?

Today, I was involved in an interesting discussion. Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow. Then Jezemine come up with a CURSOR solution that looked fine and fast. After some trial-and-errors I finally posted a set-based solution that seems to be the fastest solution yet. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 So it still seems that [proper implemented] set-based solutions are the fastest, compared to CURSORs.  Below is also an implementation of a SQL Server 2005 approach.   DECLARE     @Sample TABLE (dt DATETIME, Rate FLOAT)   INSERT      @Sample SELECT      CURRENT_TIMESTAMP - 10, 1 UNION ALL SELECT      CURRENT_TIMESTAMP - 9, 2 UNION ALL SELECT      CURRENT_TIMESTAMP - 8, 4...

posted @ Monday, December 10, 2007 8:20 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Getting errors when working with Excel and SQL Server

In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET. But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods. The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors. -- Using this code for a file with no appropriate permissions throws a general error SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1\$a1:q50]') If you have some error and you don't understand why, try using MSDASQL provider temporarily. SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls', ...

##### Simulating cross apply with CSV-records in SQL Server 2000

DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200)) INSERT @Sample SELECT '123', '125,124,126' UNION ALL SELECT '124', '127,21,245' --SELECT         Col1, --                Data --FROM            @Sample --CROSS APPLY     fnParseList(',', Col3) SELECT          a.Col1,                 SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value] FROM            @Sample AS a INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ',' WHERE           n.Type = 'p'                 AND n.Number > 0                  AND n.Number < LEN(',' + a.Col3 + ',')

posted @ Monday, October 15, 2007 8:25 AM | Feedback (7) | Filed Under [ Algorithms SQL Server 2000 ]

##### Sum up a tree hierachy in SQL Server 2005

-- Prepare sample data DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT  @Accounts SELECT  '100-000-000', NULL          UNION ALL SELECT  '100-001-000', '100-000-000' UNION ALL SELECT  '100-002-000', '100-000-000' UNION ALL SELECT  '100-002-001', '100-002-000' UNION ALL SELECT  '100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT  @Transactions SELECT  '100-001-000', 1000.00 UNION ALL SELECT  '100-002-001',  500.00 UNION ALL SELECT  '100-002-002',  300.00 -- Setup staging expression ;WITH Yak (AccountNumber, Amount) AS (         SELECT          AccountNumber,                         SUM(Amount) AS Amount         FROM            @Transactions         GROUP BY        AccountNumber         UNION ALL         SELECT          a.ParentAccountNumber,                         y.Amount         FROM            @Accounts AS a         INNER JOIN      Yak AS y ON y.AccountNumber = a.AccountNumber ) -- Show the expected resultset SELECT          COALESCE(AccountNumber, 'All accounts') AS AccountNumber,                 SUM(Amount) AS Amount FROM            Yak WHERE           AccountNumber IS NOT NULL GROUP BY        AccountNumber ORDER BY        CASE                         WHEN AccountNumber...

posted @ Thursday, October 04, 2007 10:48 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### New SQL Server 2005 OUTPUT operator

Let’s play with the new OUTPUT operator! -- Setup TableA & TableB CREATE TABLE          #TableA                       (                                  i INT                       )   CREATE TABLE          #TableB                       (                                  i INT                       )   CREATE TABLE          #TableC                       (                                  iOld INT,                                  iNew INT                       )   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Insert into TableA INSERT     #TableA OUTPUT     inserted.i INTO       #TableB SELECT     1 UNION ALL SELECT     2 UNION ALL SELECT     3   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Delete from TableA DELETE     a OUTPUT     10 * deleted.i + 49 INTO       #TableB FROM       #TableA AS a WHERE      i = 2   -- Check TableA and TableB SELECT 'A'...

##### Save some time and key-typing

Sometimes you have a denormalized table with several BIT columns used as flags. Say you want to select every row that has at least one flag set. That's easy. SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1... But how to easy select all records where all flags are not set? SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0... That can be the way you normally write, and it can get very long! But if you write like this instead to get all records where at least one flag is set SELECT * FROM...

posted @ Friday, September 28, 2007 12:24 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### SQL Server 2005 too smart?

I was involved in a discussion today about the ISNUMERIC() function Someone proposed a nested solution like this SELECT Column_Name from    (               SELECT Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' as Column_Name union all                                select '12d1' as Column_Name union all                                select '45e0' as Column_Name union all                                select '\$123.45' as Column_Name union all                                select '\$12,345' as Column_Name                         ) as Table_Name                WHERE   ISNUMERIC(Column_Name) = 1         ) as d where    CAST(Column_Name AS INT) <= 1000000 But is does not work. I suggested an alternative method that seems to work. SELECT d.Column_Name from    (                SELECT x.Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' union all                                select '12d1' union...

posted @ Thursday, September 27, 2007 3:53 PM | Feedback (6) | Filed Under [ Algorithms Administration ]

##### Clever way to get the records you want with certain number of a given character

Consider this test data CREATE TABLE #Temp (ID INT, Directory TEXT) INSERT  #Temp SELECT  1, 'Sports' UNION ALL SELECT  2, 'Sports/Football' UNION ALL SELECT  3, 'Sports/Football/American' UNION ALL SELECT  4, 'Sports/Football/American/College_and_University' UNION ALL SELECT  5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL SELECT  6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL SELECT  7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL SELECT  8, 'Sports/Darts' UNION ALL SELECT  9, 'Sports/Darts/Organizations' UNION ALL SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama' Say you want to return all records that are at least three directories down, ie having at least 2 dividers. This is the most common way I have encountered SELECT ID,        Directory FROM   #Temp WHERE  LEN(Directory) - LEN(REPLACE(Directory, '/', '')) >= 2 This works but have one drawback. It does not work on TEXT columns! This is what I came up with today in this topic Is this possible? SELECT...

posted @ Thursday, September 27, 2007 10:41 AM | Feedback (5) | Filed Under [ Algorithms ]