The problem came up on newsgroups. The set based solution is quite simple:
SELECT REPLICATE('0', COUNT(*) - MAX(CASE WHEN c<>'0' THEN n ELSE 0
END))
+
CAST((SUM(CAST((c + LEFT('00000000000000000',n)) AS INT)) /10) AS
VARCHAR(100))
FROM(
SELECT c, ROW_NUMBER()OVER(ORDER BY Number DESC) AS n FROM(SELECT
SUBSTRING('asdf004506õÎÉÏÃÄÅ8sd',Number, 1) AS c, Number FROM
Data.Numbers) t
WHERE c IN('0','1','2','3','4','5','6','7','8','9')
) t
-----------------
0045068
It assumes that there is an auxiliary table Data.Numbers.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390
To address a very common requirement, Itzik Ben-Gan is suggesting the following syntax:
TOP(@n) OVER(PARTITION BY ... ORDER BY ...)
For instance, the requirement "select three latest orders for every customer" would be implemented as
SELECT TOP(3) OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) ...
Advantages:
all intentions are expressed in one place, in an intuitively clear
way, similar to existing OLAP functions syntax. The alternative is to
use ROW_NUMBER(), and the implementation of the requirement is
scattered all over the query, nd requires an inline view.
In my C# projects I am using NCover (http://www.ncover.com/). It shows me which lines of code are executed by my unit tests (I am using NUnit). I added more unit tests in which my stored procedures blow up and raise exceptions. Now all my catch() branches are executed, and the coverage is 100%. Every line in my source code is executed at least once.
CREATE TABLE dbo.TaskStatuses(TaskID INT NOT NULL,
Status VARCHAR(20),
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
PreviousFinishedAt DATETIME NULL,
CONSTRAINT PK_TaskStatuses_TaskID_FinishedAt PRIMARY KEY(TaskID, FinishedAt),
CONSTRAINT UNQ_TaskStatuses_TaskID_PreviousFinishedAt UNIQUE(TaskID, PreviousFinishedAt),
CONSTRAINT FK_TaskStatuses_TaskID_PreviousFinishedAt
FOREIGN KEY(TaskID, PreviousFinishedAt)
REFERENCES dbo.TaskStatuses(TaskID, FinishedAt),
CONSTRAINT CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt CHECK(PreviousFinishedAt <= StartedAt)
)
go
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070101', '20070103', NULL)
-- you cannot have more than one beginning of history chain per task:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070104', '20070105', NULL)
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070104', '20070105', '20070103')
-- history windows cannot overlap:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Opened', '20070104', '20070109', '20070105')
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt'. The conflict occurred in database 'RiskCenter', table 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Opened', '20070114', '20070119', '20070105')
-- you cannot fill a gap in one insert
-- Gap between Jan 5 and Jan 14
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Reviewed', '20070105', '20070114', '20070105')
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/
-- to fill a gap, add to the end of the chain:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Reviewed', '20070125', '20070129', '20070119')
/*
TaskID Status StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1 Pending 20070101 20070103 NULL
1 Pending 20070104 20070105 20070103
1 Opened 20070114 20070119 20070105
1 Reviewed 20070125 20070129 20070119
(4 row(s) affected)
*/
-- then move the last period to fill the gap
UPDATE dbo.TaskStatuses SET StartedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE StartedAt END,
FinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070114' ELSE FinishedAt END,
PreviousFinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE '20070114' END
WHERE TaskID = 1 AND FinishedAt IN('20070129', '20070119')
SELECT TaskID, Status, CONVERT(CHAR(8), StartedAt, 112) StartedAt,
CONVERT(CHAR(8), FinishedAt, 112) FinishedAt,
CONVERT(CHAR(8), PreviousFinishedAt, 112) PreviousFinishedAt
FROM dbo.TaskStatuses ORDER BY FinishedAt
/*
TaskID Status StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1 Pending 20070101 20070103 NULL
1 Pending 20070104 20070105 20070103
1 Reviewed 20070105 20070114 20070105
1 Opened 20070114 20070119 20070114
(4 row(s) affected)
*/
SELECT
t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND t.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND tc.CONSTRAINT_NAME IS NULL
ORDER BY t.TABLE_NAME