Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is
SELECT COUNT(*) OfflineData
Do you see the bug?
Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case?
It returns 1.
So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes?
An...

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

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

This bug has haunted me for a while, until today when I decided to not accept it anymore.
So I filed a bug over at connect.microsoft.com,
https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed.
Here is a very simple repro of the problem
DECLARE @Sample TABLE
(
a DECIMAL(38, 19),
b FLOAT
)
INSERT @Sample
(
a,
b
)
VALUES (1E / 7E, 1E / 7E)
SELECT *
FROM @Sample
Here is the actual output.
a b
--------------------------------------- ----------------------
0.1428571428571428400 0,142857142857143
I think that both columns should have the same decimal separator, don't you?
//Peter

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

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

Today, let's examine encoding with SQL Server and XML datatype.
DECLARE @Inf XML
SET @Inf = '<?xml version="1.0" encoding="utf-16"?>
<root>
<names>
<name>test</name>
</names>
<names>
<name>test1</name>
</names>
</root>
'
SELECT x.value('name[1]', 'VARCHAR(10)') AS Name
FROM @Inf.nodes('/root/names') AS t(x)
If you try to run the code above, you will get an error message like this
Msg 9402, Level 16, State 1, Line 3
XML parsing: line 1, character 39, unable to switch the encoding
Why is that? If you change the encoding to UTF-8, the code works.
The solution is to know that UTF-16 works like UNICODE, and how do we denote UNICODE strings in SQL Server? Yes, by prefixing the string with N.
So this code works with UTF-16 encoding...

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

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

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

SELECT name AS Setting,
CASE
WHEN @@OPTIONS & number = number THEN 'ON'
ELSE 'OFF'
END AS Value
FROM master..spt_values
WHERE type = 'SOP'
AND number > 0
Or this
SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
Or this
SELECT *
FROM sys.dm_exec_request
WHERE session_id = @@SPID

In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic.
In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that.
The nay-sayers argument are valid
Ordered updates are not supported by Microsoft
It is an undocumented and unsupported feature which may break in next service pack or version of SQL Server
Most of the nay-sayers reference to the traditional "Quirky Update",...

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

With SQL Server 2008, we can easily use
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
SELECT CONVERT(VARCHAR(MAX), @bin, 2)
But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.
So, here is how you can do the conversion with SQL Server 2005 by using XML.
-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
-- Display the results
SELECT @bin AS OriginalValue,
CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

Joe Celko has posted a new Stumper - The Class Scheduling Problem
here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/
Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes.
-- Initialize and find the valid combinations
DECLARE @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils.
DECLARE @Source TABLE
(
room_nbr CHAR(2),
class_nbr CHAR(2),
recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
)
INSERT @Source
(
room_nbr,
class_nbr
)
SELECT r.room_nbr,
c.class_nbr
FROM dbo.Rooms AS r
INNER JOIN dbo.Classes AS...

This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.
DECLARE @Interval INT = 7
;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
SELECT ProductID,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
FROM Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
SELECT s.ProductID,
s.TransactionDate AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM ...

This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple.
DECLARE @Word VARCHAR(10) = 'Peter'
;WITH cteYak(Word, Letters)
AS (
SELECT CAST(SUBSTRING(@Word, Number, 1) AS VARCHAR(10)) AS Word,
STUFF(@Word, Number, 1, '') AS Letters
FROM dbo.TallyNumbers
WHERE Number BETWEEN 1 AND LEN(@Word)
UNION ALL
SELECT CAST(Word + SUBSTRING(y.Letters, n.Number, 1) AS VARCHAR(10)) AS Word,
STUFF(y.Letters, n.Number, 1, '') AS Letters
FROM cteYak AS y
INNER JOIN dbo.TallyNumbers AS n ON n.Number BETWEEN 1 AND LEN(y.Letters)
)
SELECT DISTINCT
Word
FROM cteYak
WHERE LEN(Word) = LEN(@Word)

The few last days, our hosting company have updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers.
The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status.
I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF...

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

This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers.
DECLARE @Result TABLE
(
LogDate DATETIME,
ProcessInfo NVARCHAR(MAX),
Text NVARCHAR(MAX)
)
INSERT @Result
EXEC sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'
IF EXISTS (SELECT * FROM @Result)
SELECT 'It seems you are running on VMware.' AS Msg
ELSE
SELECT 'It seems you are not running on VMware.' AS Msg
If you test this in your environment and found other virtual manufacturers, please let me know so I can add them in the code above.

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

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

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

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

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

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

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was
declare @s varchar(100)
set @s = 'xxxx yyyyy zzzzzz'
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')
I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.
Run this on your own risk, as you will see soon.
Select q,
len(q)
from (
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...

Yesterday I hade the unfortenate task to change the database collation for a particular database.
Not only the default database collation should be changed, but also all columns!
After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed.
I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future
Why a script? Because most...

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

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

This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys.
If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
;WITH Yak
AS (
SELECT ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,
QUOTENAME(o.name) AS CONSTRAINT_NAME,
QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,
QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,
QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,
QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,
QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,
QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,
CASE fk.is_disabled
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [ENABLED]
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id
INNER JOIN sys.objects AS ro ON ro.[object_id]...

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

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

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

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)

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column.
Here Some XML search approaches and here Updated XML search (test case with variables).
Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference.
And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about...

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

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

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

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

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

Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.
The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.
First I am going to show you the ordinary @@ERROR check which most of you are used to.
IF OBJECT_ID('uspTest_2000') IS NOT NULL
...

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

The scenario is very simple. You want to get all ID’s from one table that does not exists in another table.
This is how the two simple tables are set up.
DECLARE @Table1 TABLE
(
ID INT
)
INSERT @Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
DECLARE @Table2 TABLE
(
ID INT
)
INSERT @Table2
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION...

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

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

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

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

SELECT db.name AS DatabaseName,
bf.logical_name AS LogicalName,
CASE bs.[type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
ELSE 'Unknown'
END AS BackupType,
CASE bf.file_type
WHEN 'D' THEN 'SQL Server data file'
WHEN 'L' THEN 'SQL Server log file'
WHEN 'F' THEN 'Full text catalog'
ELSE 'Unknown'
END AS FileType,
bs.user_name AS UserName,
bs.backup_start_date AS StartDate,
bs.backup_finish_date AS FinishDate,
CAST(bs.software_major_version AS VARCHAR(11)) + '.'
...

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

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

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

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

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

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

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

When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS.
Here are some links to remedy the problems
http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx
http://technet.microsoft.com/en-us/network/bb545423.aspx

For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below.
Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected.
CREATE TABLE #Temp
(
RowID INT IDENTITY(1, 1),
theValue INT
)
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 99
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 123456
SELECT *
FROM #Temp
SELECT @@IDENTITY,
...

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

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

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

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

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

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

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