# Thinking outside the box

Patron Saint of Lost Yaks

## October 2008 Blog Posts

##### Bayesian Estimate and Root Mean Square

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

posted @ Monday, October 27, 2008 11:14 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Sequencies in string

DECLARE @s VARCHAR(100)   SET    @s = 'aardddvaaaarrkkkk'   -- Highest frequency of same character SELECT TOP 1 WITH TIES               [char],               COUNT(*) AS cnt FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               ) AS q GROUP BY      [char] ORDER BY      COUNT(*) DESC   -- Longest sequence of same character SELECT TOP 1 WITH TIES               [char],               CASE [seq]                      WHEN 0 THEN DATALENGTH(@s) - Number                      ELSE [seq]               END AS [seq] FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char],                            Number,                            PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               )...

posted @ Monday, October 27, 2008 8:32 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

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

posted @ Monday, October 27, 2008 8:29 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to script out all your objects one per file

/*******************************************************************************  Initialize communication variables *******************************************************************************/ SET NOCOUNT ON DECLARE @pathProc VARCHAR(255),         @pathFunc VARCHAR(255),         @pathTrig VARCHAR(255),         @pathView VARCHAR(255),         @cmd NVARCHAR(4000),         @pathBase VARCHAR(256) SELECT  @pathBase = '\\Archive\Documents\Projects\Peso\Code\',         @pathProc = @pathBase + 'Stored Procedures\',         @pathFunc = @pathBase + 'Functions\',         @pathTrig = @pathBase + 'Triggers\',         @pathView = @pathBase + 'Views\' SET     @cmd = 'md "' + @pathProc + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathFunc + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathTrig + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathView + '"' EXEC    master..xp_cmdshell @cmd, no_output /*******************************************************************************  Stage all existing relevant code *******************************************************************************/ CREATE TABLE  TempDB..CodeOut               (                      spID INT,                      [uID] INT,                      colID INT,                      codeText NTEXT,                      isProc TINYINT,                      isFunc TINYINT,                      isTrig...

posted @ Friday, October 24, 2008 2:04 PM | Feedback (10) | Filed Under [ Administration ]

##### Delete all subset records

Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record. See this sample data DECLARE       @Sample TABLE        (               recID INT IDENTITY(1, 1),               col1 VARCHAR(1),               col2 VARCHAR(2),               col3 VARCHAR(3),               userID INT        ) INSERT @Sample SELECT 'A', 'B', 'C', 1 UNION ALL SELECT 'A', 'B', ' ', 1 UNION ALL SELECT 'A', ' ', 'C', 1 UNION ALL SELECT 'F', ' ', 'C', 1 UNION ALL SELECT ' ', 'M', ' ', 2 UNION ALL SELECT 'T', 'M', 'O', 2 UNION ALL SELECT ' ', 'M', 'O', 2 UNION ALL SELECT 'X', 'M', 'O', 2...

##### Get the job name for current context

Today I had to write some code to dynamically get the job name currently running. DECLARE @SQL NVARCHAR(72),         @jobID UNIQUEIDENTIFIER,         @jobName SYSNAME SET     @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)' EXEC    sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT SELECT  @jobName = name FROM    msdb..sysjobs WHERE   job_id = @jobID

posted @ Monday, October 13, 2008 10:32 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

##### Keep track of all your jobs schedules

This is a piece of code I use to create a resultset from and display in Outlook calendar. CREATE PROCEDURE dbo.uspGetScheduleTimes (        @startDate DATETIME,        @endDate DATETIME ) AS /*     This code is blogged here     http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx */ SET NOCOUNT ON   -- Create a tally table. If you already have one of your own please use that instead. CREATE TABLE #tallyNumbers               (                      num SMALLINT PRIMARY KEY CLUSTERED               )   DECLARE       @index SMALLINT   SET    @index = 1   WHILE @index <= 8640        BEGIN               INSERT #tallyNumbers                      (                            num                      )               VALUES (                            @index                      )                 SET    @index = @index + 1        END   -- Create a staging table for jobschedules CREATE TABLE #jobSchedules               (                      rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                      serverName SYSNAME NOT NULL,                     ...

##### Excerpt from The Compression Session

This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to. With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...

posted @ Thursday, October 09, 2008 8:39 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration ]

##### SUM and JOIN together

DECLARE    @a TABLE (pk INT) DECLARE    @b TABLE (fk INT, i INT) DECLARE    @c TABLE (fk INT, j INT) INSERT    @a SELECT    1 UNION ALL SELECT    2 UNION ALL SELECT    3 INSERT    @b SELECT    1, 1 UNION ALL SELECT    1, 3 UNION ALL SELECT    2, 4 UNION ALL SELECT    2, 8 UNION ALL SELECT    2, 10 UNION ALL SELECT    3, 1 INSERT    @c SELECT    1, 11 UNION ALL SELECT    1, 13 UNION ALL SELECT    2, 14 UNION ALL SELECT    2, 18 UNION ALL SELECT    2, 60 UNION ALL SELECT    3, 11 -- Wrong way SELECT        a.pk,         SUM(b.i) AS SumAct,...

posted @ Wednesday, October 01, 2008 3:48 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]