# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## April 2009 Blog Posts

##### Double duty

I was browsing some of my older solutions, and I stumbled across this interesting piece. The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'. DECLARE     @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1)) INSERT      @Roles SELECT      'Smith', 'O' UNION ALL SELECT      'Smith', 'D' UNION ALL SELECT      'Jones', 'O' UNION ALL SELECT      'White', 'D' UNION ALL SELECT      'Brown', 'X' SELECT      Person,             SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty FROM        @Roles WHERE       [Role] IN ('D', 'O') GROUP BY    Person

posted @ Tuesday, April 21, 2009 3:24 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Greatest Common Divisor function

This approach is not dependent on the 32-level recursion as most other algorithms are for this problem. CREATE FUNCTION dbo.fnGCD (             @a INT,             @b INT ) RETURNS INT AS BEGIN             DECLARE     @c INT               IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)                          RETURN NULL               IF @a = 0 OR @b = 0                          RETURN ABS(@a) + ABS(@b)               IF ABS(@a) < ABS(@b)                          SELECT      @c = ABS(@a),                                      @a = ABS(@b),                                      @b = @c             ELSE                          SELECT      @a = ABS(@a),                                      @b = ABS(@b)               SET         @c = @a % @b               WHILE @c > 0                          SELECT      @a = @b,                                      @b = @c,                                      @c = @a %...

posted @ Wednesday, April 15, 2009 10:17 AM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there. With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML? Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query! In this example I am showing you how to audit certain...

posted @ Wednesday, April 08, 2009 12:24 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Administration ]

##### @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.  The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process. First I am going to show you the ordinary @@ERROR check which most of you are used to.   IF OBJECT_ID('uspTest_2000') IS NOT NULL ...

posted @ Tuesday, April 07, 2009 9:53 AM | Feedback (5) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

##### One way to calculate decimal year

Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent. The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that. First, set up a test environment by declaring and setting two datetime variables like this declare @f datetime...

posted @ Wednesday, April 01, 2009 10:09 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Powered by: