|
|
November 2008 Blog Posts
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...
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
...
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...
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...
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,
...
-- 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)
...
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
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...
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...
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
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
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
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
-- 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...
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)
|