September 2007 Blog Posts
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...
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...
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...
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)
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...
In previous topic here
http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx
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 ...
I got some response from same topic posted on September 20
http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx
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 ...
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)
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...
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...