SELECT name AS Setting, CASE WHEN @@OPTIONS & number = number THEN 'ON' ELSE 'OFF' END AS Value FROMmaster..spt_values WHEREtype = 'SOP' AND number > 0 Or this SELECT* FROMsys.
Read more →
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.
Read more →
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.
Read more →
With SQL Server 2008, we can easily use DECLARE@bin VARBINARY(MAX) SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 SELECTCONVERT(VARCHAR(MAX), @bin, 2) But how can we do this in SQL Server 2005?
Read more →
This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx. The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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 →
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 →
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.
Read more →
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
Legacy Comments
Liam Caffrey
2009-08-27
re: Recursive Fibonacci number calculation Hi,
Read more →
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' SELECTREPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts.
Read more →
declare@t table (Id int, dt datetime, value int) setdateformat 'dmy' insertinto @t select1, '10/12/2008', 10 union all select1, '11/12/2008', 10 union all select1, '12/12/2008', 10 union all select1, '13/12/2008', 9 union all select1, '14/12/2008', 10 union all select1, '15/12/2008', 10 union all select1, '16/12/2008', 10 union all select1, '17/12/2008', 10 union all select2, '05/03/2008', 8 union all select2, '06/03/2008', 6 union all select2, '07/03/2008', 8 union all select2, '08/03/2008', 8 union all select2, '09/03/2008', 8 union all select2, '20/03/2008', 8 SELECTId, MIN(dt) AS Startdt, MAX(dt) AS Enddt, MIN(value) AS Value FROM( SELECT Id, dt, value, ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID, ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID, ROW_NUMBER() OVER (ORDER BY dt) AS colID FROM @t ) AS d GROUPBY Id, grpID - recID, grpID - colID ORDERBY id, MIN(dt)
Read more →