# Thinking outside the box

Patron Saint of Lost Yaks

## August 2009 Blog Posts

##### Updated site

Yesterday I updated my site. It was not that appealing. This is how it looked before http://209.85.129.132/search?q=cache:CMr9J28OCwkJ:www.developerworkshop.net/+developerworkshop.net&cd=1&hl=en&ct=clnk&gl=uk and this is how it loooks now http://www.developerworkshop.net I think it's a great improvement, even if there still are some things to fix. //Peso

posted @ Saturday, August 29, 2009 2:13 PM | Feedback (0) | Filed Under [ Miscellaneous ]

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

##### SQLCLR aggregate function

Phew! Now it's finally done. I haven't coded outside SQL Server since 2007 and that was with VB version 6.0. Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location Regression.dll http://regression.developerworkshop.net/dws.Regression.dll Install.sql http://regression.developerworkshop.net/Install.sql Sample.sql http://regression.developerworkshop.net/Sample.sql (good linear regression) Sample2.sql http://regression.developerworkshop.net/Sample2.sql (better linear regression) Sample3.sql http://regression.developerworkshop.net/Sample3.sql (polynomial regression) Update: * I have created a homepage for this SQLCLR function http://regression.developerworkshop.net For the Sample2 above, the result for c: drive on Server1 look like this <dws bestfit="linear" r2="0.99" type="least square...

posted @ Wednesday, August 26, 2009 12:22 PM | Feedback (5) | Filed Under [ Algorithms Miscellaneous ]

##### CHAR(0) is not that innocent you may think

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

##### Third running streak

declare @t table (Id int, dt datetime, value int) set dateformat 'dmy' insert into @t   select 1, '10/12/2008', 10 union all select 1, '11/12/2008', 10 union all select 1, '12/12/2008', 10 union all select 1, '13/12/2008', 9 union all select 1, '14/12/2008', 10 union all select 1, '15/12/2008', 10 union all select 1, '16/12/2008', 10 union all select 1, '17/12/2008', 10 union all select 2, '05/03/2008', 8 union all select 2, '06/03/2008', 6 union all select 2, '07/03/2008', 8 union all select 2, '08/03/2008', 8 union all select 2, '09/03/2008', 8 union all select 2, '20/03/2008', 8   SELECT      Id,             MIN(dt) AS Startdt,             MAX(dt) AS Enddt,             MIN(value) AS Value FROM        (                 SELECT Id,                         dt,                         value,                         ROW_NUMBER() OVER (PARTITION BY Id,...

posted @ Friday, August 21, 2009 11:19 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Change collation

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

posted @ Wednesday, August 19, 2009 2:07 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

##### Complement of ISNUMERIC function

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

posted @ Friday, August 14, 2009 12:18 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Calculating Running Streak over many records

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. But this will work, and work fast! DECLARE  @Sample TABLE         (             Col1 INT,             Col2 INT,             Col3 INT,             Col4 INT,             Col5 INT,             Col6 INT,             Col7 INT,             Col8 DATETIME         ) INSERT  @Sample SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0,...

posted @ Wednesday, August 12, 2009 7:24 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Curiosity about SQL Server 2008 R2 installation

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. After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance. So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there...

posted @ Wednesday, August 12, 2009 9:04 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration ]

##### Another running streaks algorithm

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

posted @ Wednesday, August 12, 2009 12:49 AM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Script out your foreign keys

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

posted @ Friday, August 07, 2009 12:43 PM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

##### Manipulate XML data, continued

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. I am using the same sample data for easier understanding. CREATE TABLE #Sample              (                  RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                  MemberID INT,                  MemberData XML              )   INSERT  #Sample SELECT  1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL SELECT  2, '<meta><age>24</age></meta>'   CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)   CREATE XML INDEX IX_Element ON #Sample(MemberData)  USING XML INDEX IX_PrimaryXML FOR PATH   SELECT  MemberID,         MemberData,         MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,         MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,         MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,         MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,         MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status] FROM    #Sample   --...

posted @ Tuesday, August 04, 2009 9:30 AM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

##### Fast and Simple Prime Number Factorization

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

posted @ Sunday, August 02, 2009 2:12 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]