Thinking outside the box

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

My Links




Post Categories

September 2007 Blog Posts

Save some time and key-typing

Sometimes you have a denormalized table with several BIT columns used as flags. Say you want to select every row that has at least one flag set. That's easy. SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1... But how to easy select all records where all flags are not set? SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0... That can be the way you normally write, and it can get very long! But if you write like this instead to get all records where at least one flag is set SELECT * FROM...

posted @ Friday, September 28, 2007 12:24 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

SQL Server 2005 too smart?

I was involved in a discussion today about the ISNUMERIC() function Someone proposed a nested solution like this SELECT Column_Name from    (               SELECT Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' as Column_Name union all                                select '12d1' as Column_Name union all                                select '45e0' as Column_Name union all                                select '$123.45' as Column_Name union all                                select '$12,345' as Column_Name                         ) as Table_Name                WHERE   ISNUMERIC(Column_Name) = 1         ) as d where    CAST(Column_Name AS INT) <= 1000000 But is does not work. I suggested an alternative method that seems to work. SELECT d.Column_Name from    (                SELECT x.Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' union all                                select '12d1' union...

posted @ Thursday, September 27, 2007 3:53 PM | Feedback (6) | Filed Under [ Algorithms Administration ]

Clever way to get the records you want with certain number of a given character

Consider this test data CREATE TABLE #Temp (ID INT, Directory TEXT) INSERT  #Temp SELECT  1, 'Sports' UNION ALL SELECT  2, 'Sports/Football' UNION ALL SELECT  3, 'Sports/Football/American' UNION ALL SELECT  4, 'Sports/Football/American/College_and_University' UNION ALL SELECT  5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL SELECT  6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL SELECT  7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL SELECT  8, 'Sports/Darts' UNION ALL SELECT  9, 'Sports/Darts/Organizations' UNION ALL SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama' Say you want to return all records that are at least three directories down, ie having at least 2 dividers. This is the most common way I have encountered SELECT ID,        Directory FROM   #Temp WHERE  LEN(Directory) - LEN(REPLACE(Directory, '/', '')) >= 2 This works but have one drawback. It does not work on TEXT columns! This is what I came up with today in this topic Is this possible? SELECT...

posted @ Thursday, September 27, 2007 10:41 AM | Feedback (5) | Filed Under [ Algorithms ]

Insert binary data like images into SQL Server without front-end application

I came across this solution for some months ago on another forum. It is very handy, so I thought i should share it. CREATE TABLE myTable(Document varbinary(max)) INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)

posted @ Wednesday, September 26, 2007 12:35 PM | Feedback (82) | Filed Under [ Administration ]

Horizontal partitioning

Today I investigated the potential benefits of horizontal partitioning for a client using SQL Server 2000.   I profiled the client's queries to the database and found out that current quarter only is used in 72% of the queries, 21% of queries is current and previous quarter only and remaining 7% of queries is for any quarter.   Their current Order table has about 4.5 million records, and the average duration for a query is about 1200 ms. Every quarter has an average of 100000 records (about 11 years of data). With this information at hand, I decided to partition the current order table in...

posted @ Monday, September 24, 2007 8:44 PM | Feedback (7) |

Finding records in one table not present in another table II

In previous topic here Michael Valentine Jones suggested an alternative route to get all records from one table not found in another. Here are the results (including my suggestion with only MIN(t1) = 1 as MVJ2) SQL 2000 CPU DURATION READS WRITESOriginal 1781 1783 30667 0Peso 1 0 0 29      0Peso 2 0        0 31      0Peso 3 ...

posted @ Monday, September 24, 2007 9:04 AM | Feedback (0) | Filed Under [ Optimization ]

Finding group of records with a certain status II

I got some response from same topic posted on September 20 Hugo Kornelis posted an alternative code to my improvement and I promised to test it. Here is the new results (made on other machines so the absolute numbers do not match). SQL 2000  Memory CPU DURATION READS WRITESOriginal    5304 670      920 13555      0Peso        8685 125      125   604      0Hugo        8685 110      110   604      0 SQL 2005 Memory CPU DURATION READS WRITESOriginal 8838 437 558 851 0Peso 8838 172 ...

posted @ Monday, September 24, 2007 8:38 AM | Feedback (0) | Filed Under [ Optimization ]

Example of MERGE in SQL Server 2008

MERGE Production.ProductInventory AS [pi]USING     (           SELECT ProductID,                           SUM(OrderQty) AS OrderQty               FROM       Sales.SalesOrderDetail AS sod                INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID                WHERE      soh.OrderDate = GETDATE()               GROUP BY   ProductID           ) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductIDWHEN      MATCHED AND src.OrderQty = 0            THEN DELETE; WHEN      MATCHED                THEN UPDATE SET [pi].Quantity = src.OrderQtyWHEN      NOT MATCHED                THEN INSERT VALUES (src.ProductID, src.OrderQty)  

posted @ Thursday, September 20, 2007 10:31 AM | Feedback (0) | Filed Under [ SQL Server 2008 ]

Finding records in one table not present in another table

Difference between NOT IN, LEFT JOIN and NOT EXISTS. The objective is to fetch all records in one table that are not present in another table. The most common code I’ve seen at client sites includes the use of NOT IN, because this keyword is included in most programming languages and programmers tend to use this technique when writing stored procedures in the database too. The code example I have found in most cases is this simple and understandable SELECT     a.iFROM       #a AS aWHERE      a.i NOT IN (SELECT b.j FROM #b AS b)           OR a.i IS NULL The basic idea is to get all records...

posted @ Thursday, September 20, 2007 10:17 AM | Feedback (13) | Filed Under [ Optimization ]

Finding group of records with a certain status

I recently was given the task to optimize some code prior to a client's upgrade to SQL Server 2005. The objective for the old code was to get all orders where status for all orderlines where set to 'DROP'. SELECT     t.OrderID,           t.OrderDateFROM       tblOrder AS tINNER JOIN (            SELECT     a.OrderID,                          CASE                           WHEN SUM(b.Type) = COUNT(*) THEN 'DROP'                              ELSE NULL                          END AS [Type]               FROM       tblOrder AS a               INNER JOIN (                           SELECT     OrderID,                                         CASE                                          WHEN [Status] = 'DROP' THEN 1                                             ELSE 0                                         END AS Type                              FROM       tblOrder                          ) AS b ON b.OrderID = a.OrderID               GROUP BY   a.OrderID            ) AS c ON c.OrderID = t.OrderID AND c.Type = 'DROP' After some testing I...

posted @ Thursday, September 20, 2007 9:06 AM | Feedback (5) | Filed Under [ Optimization ]

Powered by:
Powered By Subtext Powered By ASP.NET