# Thinking outside the box

Patron Saint of Lost Yaks

## Optimization

##### The one feature that would make me invest in SSIS 2012

This week I was invited my Microsoft to give two presentations in Slovenia. My presentations went well and I had good energy and the audience was interacting with me. When I had some time over from networking and partying, I attended a few other presentations. At least the ones who where held in English. One of these was "SQL Server Integration Services 2012 - All the News, and More", given by Davide Mauri, a fellow co-worker from SolidQ. We started to talk and soon came into the details of the new things in SSIS 2012. All of the official things Davide talked...

posted @ Saturday, May 26, 2012 10:52 AM | Feedback (2) | Filed Under [ Optimization Denali SSIS ]

##### 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 ]

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

##### 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 ]

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

##### 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 ]

##### SQL Server 2008 Compression

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX)   DECLARE curTables CURSOR FOR        ...

posted @ Thursday, June 17, 2010 2:16 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Administration ]

##### 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 ]

##### 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 ]

##### Performance consideration when using a Table Variable

This is nothing new to me (I come across this autumn 2007) and probably not to you either but I forgot about it and yesterday it came alive again. I often choose a table variable for performance reasons (if data is less than 1 page anyway) due to the benefits of no logging etc etc. But yesterday I wrote a query for Phil Factor's "Subscription List" competition where I had choosen a table variable for the same reasons as before. It took a while to realize why (in this case) the solution with a temporary table was 30% faster than the solution...

posted @ Thursday, October 15, 2009 3:24 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 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 ]

##### 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 ]

##### 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 ]

##### 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 ]

##### 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 ]

##### Microsoft Connect - Enhanced Syntax For Insert Into Statement

I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax. Especially for INSERT INTO ... EXEC ... Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets. And it's only possibly to fetch and store the first resultset. What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ) EXEC usp_MyStoredProcedure @Param1, @Param2 In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first. First resultset has two columns, and second resultset has three columns. Let Microsoft know...

posted @ Saturday, June 27, 2009 10:06 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Miscellaneous ]

##### 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 ]

##### 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 ]

##### Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there. With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML? Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query! In this example I am showing you how to audit certain...

posted @ Wednesday, April 08, 2009 12:24 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Administration ]

##### 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 ]

##### 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 ]

##### Competition

posted @ Saturday, February 28, 2009 4:29 PM | Feedback (5) | Filed Under [ Optimization ]

##### 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 ]

##### 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 ]

##### 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 ]

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

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

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

##### 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 ]

##### 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 ]

##### Horizontal partitioning, Enterprise style

CREATE PARTITION FUNCTION   pfOrderDate ( DATETIME ) AS RANGE RIGHT FOR VALUES  ( '20000101', '20010101', '20020101' ) GO   CREATE PARTITION SCHEME    psYak AS PARTITION               pfOrderDate ALL TO                     ([PRIMARY]) GO   CREATE TABLE Orders               (                      OrderID INT NOT NULL,                      CustomerID VARCHAR(15) NOT NULL,                      OrderDate DATETIME NOT NULL               ) ON            psYak(OrderDate) GO   CREATE CLUSTERED INDEX      IX_OrderID ON                          Orders ( OrderID ) CREATE NONCLUSTERED INDEX  IX_OrderDate ON                         Orders ( OrderDate ) INCLUDE                    ( OrderID, CustomerID ) GO   INSERT Orders        (               OrderID,               CustomerID,               OrderDate        ) SELECT 1, 'Peso', '20011225' UNION ALL SELECT 2, 'Jennie', '20020314'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderDate = '20011225'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderID = 1   DROP TABLE                 Orders DROP PARTITION SCHEME      psYak DROP PARTITION FUNCTION    pfOrderDate

posted @ Thursday, June 12, 2008 4:18 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Administration SQL Server 2005 ]

##### 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 ]

##### 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 ]

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

##### 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 ]

##### 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 ]

##### Finding records in one table not present in another table II

In previous topic here http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx Michael Valentine Jones suggested an alternative route to get all records from one table not found in another. Here are the results (including my suggestion with only MIN(t1) = 1 as MVJ2) SQL 2000 CPU DURATION READS WRITESOriginal 1781 1783 30667 0Peso 1 0 0 29      0Peso 2 0        0 31      0Peso 3 ...

posted @ Monday, September 24, 2007 9:04 AM | Feedback (0) | Filed Under [ Optimization ]

##### Finding group of records with a certain status II

I got some response from same topic posted on September 20 http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx Hugo Kornelis posted an alternative code to my improvement and I promised to test it. Here is the new results (made on other machines so the absolute numbers do not match). SQL 2000  Memory CPU DURATION READS WRITESOriginal    5304 670      920 13555      0Peso        8685 125      125   604      0Hugo        8685 110      110   604      0 SQL 2005 Memory CPU DURATION READS WRITESOriginal 8838 437 558 851 0Peso 8838 172 ...

posted @ Monday, September 24, 2007 8:38 AM | Feedback (0) | Filed Under [ Optimization ]

##### Finding records in one table not present in another table

Difference between NOT IN, LEFT JOIN and NOT EXISTS. The objective is to fetch all records in one table that are not present in another table. The most common code I’ve seen at client sites includes the use of NOT IN, because this keyword is included in most programming languages and programmers tend to use this technique when writing stored procedures in the database too. The code example I have found in most cases is this simple and understandable SELECT     a.iFROM       #a AS aWHERE      a.i NOT IN (SELECT b.j FROM #b AS b)           OR a.i IS NULL The basic idea is to get all records...

posted @ Thursday, September 20, 2007 10:17 AM | Feedback (13) | Filed Under [ Optimization ]

##### Finding group of records with a certain status

I recently was given the task to optimize some code prior to a client's upgrade to SQL Server 2005. The objective for the old code was to get all orders where status for all orderlines where set to 'DROP'. SELECT     t.OrderID,           t.OrderDateFROM       tblOrder AS tINNER JOIN (            SELECT     a.OrderID,                          CASE                           WHEN SUM(b.Type) = COUNT(*) THEN 'DROP'                              ELSE NULL                          END AS [Type]               FROM       tblOrder AS a               INNER JOIN (                           SELECT     OrderID,                                         CASE                                          WHEN [Status] = 'DROP' THEN 1                                             ELSE 0                                         END AS Type                              FROM       tblOrder                          ) AS b ON b.OrderID = a.OrderID               GROUP BY   a.OrderID            ) AS c ON c.OrderID = t.OrderID AND c.Type = 'DROP' After some testing I...

posted @ Thursday, September 20, 2007 9:06 AM | Feedback (5) | Filed Under [ Optimization ]