# Thinking outside the box

Patron Saint of Lost Yaks

## December 2008 Blog Posts

##### One way to resolve and calculate fractional strings

CREATE FUNCTION dbo.fnResolveFractionals (             @data VARCHAR(20) ) RETURNS FLOAT AS BEGIN             RETURN      CASE                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)                                      ELSE NULL                          END END GO   DECLARE     @Sample TABLE             (                          data VARCHAR(20)             )   INSERT      @Sample SELECT      '5 3/16' UNION ALL SELECT      '7' UNION ALL SELECT      '2 /' UNION ALL SELECT     ...

posted @ Monday, December 15, 2008 3:01 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Find popular combos

DECLARE     @Sample TABLE             (                  StudentID INT,                  Class VARCHAR(20)             )   INSERT      @Sample SELECT      1, 'Maths' UNION ALL SELECT      1, 'English' UNION ALL SELECT      1, 'Science' UNION ALL SELECT      2, 'Maths' UNION ALL SELECT      2, 'English' UNION ALL SELECT      2, 'Science' UNION ALL SELECT      2, 'History' UNION ALL SELECT      3, 'English' UNION ALL SELECT      3, 'Maths' UNION ALL SELECT      3, 'Science' UNION ALL SELECT      3, 'RE'  UNION ALL SELECT      4, 'Science' UNION ALL SELECT      4, 'Maths' UNION ALL SELECT      4, 'English' UNION ALL SELECT      4, 'History' UNION ALL SELECT      4, 'French'   ;WITH Yak(ClassName, ClassPath, Combinations) AS (             SELECT      Class,                         CAST(Class AS VARCHAR(MAX)),                         CAST(1 AS INT)             FROM        @Sample             GROUP BY    Class               UNION ALL               SELECT     s.Class,                        y.ClassPath + '-' + s.Class,                        y.Combinations + 1             FROM       Yak AS y             INNER JOIN @Sample AS s ON s.Class > y.ClassName            ...

posted @ Tuesday, December 09, 2008 4:16 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Strange happening

I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE     @SQL NVARCHAR(200) SET         @SQL = 'SELECT  DB_NAME()' EXEC        sp_executesql @SQL EXEC        (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks". Anyone knows why?

posted @ Thursday, December 04, 2008 4:58 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

##### How many logical and physical processors do you have?

SELECT cpu_count AS [Logical CPUs],         cpu_count / hyperthread_ratio AS [Physical CPUs] FROM   sys.dm_os_sys_info

posted @ Tuesday, December 02, 2008 5:08 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]