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 →
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 →
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 →
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.
Read more →
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. CREATEFUNCTION dbo.fnIsINT ( @Data NVARCHAR(11) ) RETURNSINT 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
Read more →
It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
Read more →
I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance. For the default instance, I have a database named Test on a separate partition of my laptop harddrive.
Read more →
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.
Read more →
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.
Read more →
In my previous blog post about how to manipulate data in XML columns, http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx, I didn't have time to include how to delete elements. Now I have and here you can see how to delete elements from a XML column.
Read more →
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.
Read more →
Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement.
Read more →