Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

November 2008 Blog Posts

An alternative to IDENTITY column

Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources. And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%. If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...

posted @ Friday, November 28, 2008 8:48 AM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Expand network using CTE without circular reference

Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference. See topic here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290 You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.   Here is the result.   DECLARE @Stations TABLE       (             stationID INT,             name VARCHAR(255)       )   INSERT      @Stations SELECT      1, 'Glasgow' UNION ALL SELECT      2, 'Edinburgh' UNION ALL SELECT      3, 'York' UNION ALL SELECT      4, 'London' UNION ALL SELECT      5, 'Aberdeen' UNION ALL SELECT      6, 'Bjuv'   DECLARE @Links TABLE      ...

posted @ Thursday, November 27, 2008 4:00 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Run jobs synchronously

If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished? Try this CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job (        @jobName SYSNAME ) AS   SET NOCOUNT ON   DECLARE       @jobID UNIQUEIDENTIFIER,        @maxID INT,        @status INT,        @rc INT   IF @jobName IS NULL       BEGIN             RAISERROR('Parameter @jobName have no value.', 16, 1)             RETURN -100       END   SELECT @jobID = job_id FROM   msdb..sysjobs WHERE name = @jobName   IF @@ERROR <> 0       BEGIN             RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)             RETURN -110       END   IF @jobID IS NULL       BEGIN             RAISERROR('Job %s does not exist.', 16, 1, @jobName)             RETURN -120       END   SELECT @maxID...

posted @ Thursday, November 27, 2008 2:48 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Half Hour Impacts

Yesterday I came across this question on another forum.   I am trying to come up with a way to identify the half hour impact from several exceptions across multiple days. I have access to SQL 2000 and SQL 2005. The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM), stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact. When I have one exception with a start time of 7:15 and an end time of 8:20, I would like to see...

posted @ Thursday, November 27, 2008 10:50 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Finding streaks in data

A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx And this is an alternative way to find your streaks -- Prepare sample data SET NOCOUNT ON   DECLARE       @GameResults TABLE        (               gameID INT,               homeScore INT,               awayScore INT        )   INSERT @GameResults SELECT 1, 2, 1 UNION ALL SELECT 2, 4, 1 UNION ALL SELECT 3, 4, 3   DECLARE       @Program TABLE        (               gameID INT,               gameDate DATETIME,               homeID INT,               awayID INT        )   INSERT @Program SELECT 1, '2008-05-12', 101, 102 UNION ALL SELECT 2, '2008-05-20', 106, 101 UNION ALL SELECT 3, '2008-05-14', 107, 101   -- Prepare staging data DECLARE       @Stage TABLE        (               teamID INT,               gameDate DATETIME,               outcome CHAR(3),               streak INT,              ...

posted @ Wednesday, November 26, 2008 1:25 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Easy sorted numeric pivoting with maximum three columns

-- Prepare sample data DECLARE       @Sample TABLE        (               ID INT,               col INT        )   INSERT @Sample SELECT 0, 1 UNION ALL SELECT 0, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 6 UNION ALL SELECT 3, 6 UNION ALL SELECT 5, 8 UNION ALL SELECT 5, 9 UNION ALL SELECT 4, 7   -- Pivot the source data SELECT        ID,               MIN(col) AS col1,               CASE COUNT(*)                      WHEN 1 THEN NULL                      WHEN 2 THEN MAX(col)                      ELSE SUM(col) - MIN(col) - MAX(col)               END AS col2,               CASE COUNT(*)                      WHEN 3 THEN MAX(col)                     ...

posted @ Tuesday, November 25, 2008 3:58 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Datetime manipulation - Time only by Itzik Ben-Gan

See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html  And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html    

posted @ Monday, November 24, 2008 5:17 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

SQL Server 2008 with MERGE and triggers

I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements. According to Books Online, there is no sure way to guarantee the order of "streams" to execute, but it seems SQL Server favors INSERT / UPDATE / DELETE order.   CREATE TABLE tTemp               (                      i INT,                      j INT               )   INSERT tTemp        (               i,               j        ) SELECT 1, 0 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT...

posted @ Monday, November 24, 2008 2:38 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration ]

Bin packaging

With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm.   This new algorithm is about finding all possible sums and how many combinations you have of each sum. Have fun!   DECLARE       @Data TABLE        (               faceValue MONEY,               maxItems INT,               permCount INT        ) INSERT        @Data               (                      faceValue,                      maxItems               ) SELECT        faceValue,               1 + COUNT(*) FROM          (                      SELECT 899 AS faceValue UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION...

posted @ Sunday, November 23, 2008 3:32 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to get the productsum from a table

IF EXISTS(SELECT * FROM YourTable WHERE Number = 0)     SELECT 0.0E ELSE     SELECT CASE IsNegativeProduct                WHEN 1 THEN -EXP(theSum)                ELSE EXP(theSum)            END     FROM   (                SELECT SUM(LOG(ABS(Number))) AS theSum,                       SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct                FROM   YourTable            ) AS d

posted @ Wednesday, November 19, 2008 2:13 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Reading the transaction log

SELECT        * FROM          ::fn_dblog(DEFAULT, DEFAULT) AS l INNER JOIN    sysobjects AS so ON so.name = l.[transaction name] SELECT        so.name AS ObjectName,               so.type AS ObjectType,               MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM          ::fn_dblog(DEFAULT, DEFAULT) l inner join    sysobjects so on so.name = l.[transaction name] --where              so.type = 'u' GROUP BY      so.name,               so.type ORDER BY      so.name,               so.type

posted @ Thursday, November 13, 2008 9:49 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Stripping out all non-numeric characters from a string

DECLARE @Value NVARCHAR(200) SET     @Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE @Value LIKE '%[^0-9]%'         SET     @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT  @Value

posted @ Wednesday, November 12, 2008 8:48 AM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Getting date or time only from a Datetime value

SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly

posted @ Wednesday, November 12, 2008 8:38 AM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Create nested hierachy XML

-- Prepare sample data DECLARE @Master TABLE         (                 ID INT,                 Name VARCHAR(20)         ) INSERT  @Master SELECT  1, 'Peso' UNION ALL SELECT  2, 'SQLTeam' UNION ALL SELECT  3, 'SQL' DECLARE @Child TABLE         (                 MasterID INT,                 ID INT,                 Value VARCHAR(20)         ) INSERT  @Child SELECT  1, 1, 'Row 1 for Peso' UNION ALL SELECT  1, 2, 'Row 2 for Peso' UNION ALL SELECT  2, 3, 'Row 1 for SQLTeam' -- Display the XML SELECT          ePurchaseOrder.ID AS MasterID,                 ePurchaseOrder.Name AS MasterName,                 ePurchaseOrderLine.ID AS ChildID,                 ePurchaseOrderLine.Value AS ChildValue FROM            @Master AS ePurchaseOrder LEFT JOIN       @Child AS ePurchaseOrderLine ON ePurchaseOrderLine.MasterID = ePurchaseOrder.ID FOR XML         AUTO,                 ROOT('POSystem'),                 ELEMENTS SELECT      m.ID AS MasterID,             m.Name...

posted @ Thursday, November 06, 2008 4:57 PM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Using namespaces in XML queries

If your xml data contains a namespace, you also need to query the data using the same xml namespace. DECLARE @Sample TABLE         (                 rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                 [xml] XML         ) INSERT  @Sample SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL SELECT  NULL UNION ALL SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>' SELECT  * FROM    @Sample ;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS theYak) SELECT DISTINCT t.c.value('.', 'VARCHAR(20)') FROM            @Sample AS s CROSS APPLY     s.xml.nodes('/theYak:stringList/theYak:value') AS t(c)

posted @ Thursday, November 06, 2008 10:36 AM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET