Patron Saint of Lost Yaks

## 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. Read more →

## 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. Read more →

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

## Recursive Fibonacci number calculation

The answer to your question is "Yes, I am having a slow day today." ;WITHFibonacci(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 ) SELECTn, f AS Number FROMFibonacci Read more →

## Simple base converter

Here is a simple base converter that manages [from and to] base 2 to 16. CREATEFUNCTION dbo.fnBaseConvert ( @Value VARCHAR(8), @FromBase TINYINT, @ToBase TINYINT ) RETURNSVARCHAR(32) AS BEGIN RETURN ( SELECT SUBSTRING('0123456789abcdef', 1 +(x. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. Read more →

## 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. CREATEFUNCTION dbo.fnErf ( @z1 FLOAT, @z2 FLOAT, @MaxIterations TINYINT = 10 ) RETURNSFLOAT 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. Read more →

## 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. Read more →

## Competition

Adam Machanic has a nice competition going on here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx until the 16th of March 2009. Please join in! The prize, for the best submission, is a full MSDN subscription, valued at around \$10,000. Read more →

## Fastest LeapYear checker?

CREATEFUNCTION dbo.fnIsLeapYear ( @Year SMALLINT ) RETURNSBIT AS BEGIN RETURN CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229')) WHEN29 THEN 1 ELSE 0 END END Legacy Comments Brian Tkatch 2009-02-25 re: Fastest LeapYear checker? Read more →