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

Description
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

Following the discussion started by Hugo Kornelis and Old Grumpy DBA, an example of inexact calculations with float datatype:

declare @big float, @small float, @sum1 float, @sum2 float, @sum3 float, @i INT
SELECT @big = 12345678901234.50, @small = 0.01, @i = 0
SELECT @sum1 = @big, @sum2 = 0, @sum3 = 0
WHILE @i < 5 BEGIN
  SELECT @sum1 = @sum1 + @small, @sum2 = @sum2 + @small, @sum3 = @sum3 + @small
  SET @i = @i + 1
END
SELECT @sum2 = @sum2 + @big
SELECT @sum1, @sum2, @sum3

---------------------- ---------------------- ----------------------
12345678901234.5       12345678901234.6       0.05

(1 row(s) affected)

This is why you cannot have sums of floats in indexed views - they are not deterministic, they may depend on order of adding.

If you google up 'never use SELECT * in production code' you will get many many hits.
Clearly in many cases using SELECT * makes your code vulnerable to changes in underlying table(s) and as such should be avoided.
Yet I don't think the common rule of thumb 'never use SELECT * in production code' should be used
without thinking, I don't think it should be blindly applied in all the situations.
For instanse, consider a request to display top five sales for every region and a simple query that satisfies it

SELECT SalesAmount, RegionName, LongListOfOtherColumns
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
  ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

Does repeating SalesAmount, RegionName, LongListOfOtherColumns twice make your code nay better/safer/readable?
I don't think so. Consider the following alternative:

SELECT *
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
  ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

It is shorter, easier to maintain, and just as robust as the original query.
You have explicitly listed the columns
in your subquery, so you are already insulated from any changes in Sales.Sales table.
You don't need an additional layer of protection.
Repeating the list of columns twice only makes your code more prone to errors.

Following a discussion with SQL Server MVP Joe Webb at PASS conference in Denver, I decided to re-post this approach including all the bells and whistles. Of course, the general idea of the approach was borrowed from Erland Sommarskog's article on Arrays and Lists in SQL.

1. Packing an array of numbers in an image. I have to change the order of bytes when I store an array of long integers in an array of bytes, as follows:

static byte[] UlongsToBytes(ulong[] ulongs) {
int ifrom = ulongs.GetLowerBound(0);
int ito = ulongs.GetUpperBound(0);
int l = (ito - ifrom + 1)*8;
byte[] ret = new byte[l];
int retind = 0;
for(int i=ifrom; i<=ito; i++)
{
ulong v = ulongs[ i ];
ret[retind++] = (byte) (v >> 0x38);
ret[retind++] = (byte) (v >> 0x30);
ret[retind++] = (byte) (v >> 40);
ret[retind++] = (byte) (v >> 0x20);
ret[retind++] = (byte) (v >> 0x18);
ret[retind++] = (byte) (v >> 0x10);
ret[retind++] = (byte) (v >> 8);
ret[retind++] = (byte) v;
}

return ret;
This code snippet is also included in Erland's article at
http://www.sommarskog.se/arrays-in-sql-2005.html
 
2. Binding the array of bytes as an image parameter:
static void Main(string[] args)
{
DateTime d1, d2, d3;
d1 = DateTime.Now;
d2 = DateTime.Now;
string sddd = d1.ToString();
ulong[] ul = new ulong[10000];
for(uint i=0; i<10000; i++)
{
ul[ i ] = i;
}
string sss = UlongsToString(ul);
byte[] ba = UlongsToBytes(ul);
string directInsert = UlongsToDirectInsert(ul);
try
{
string source = @"packet size=4096;integrated security=SSPI;data source=MyPC\MyNamedInstance;persist security info=False;initial catalog=Sandbox";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand a = new SqlCommand("INSERT BigintsTarget(bi) SELECT * FROM dbo.ParseImageIntoBIGINTs(@BIGINTs)", conn);
a.CommandType = System.Data.CommandType.Text;
a.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.Image,2147483647));
for(int q=0; q<10; q++)
{
a.Parameters[0].Value = ba;
int res = a.ExecuteNonQuery();
}
d2 = DateTime.Now;
SqlCommand b = new SqlCommand("INSERT BigintsTarget1(bi) SELECT * FROM dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs)", conn);
b.CommandType = System.Data.CommandType.Text;
b.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.VarChar,2147483647));
for(int q=0; q<10; q++)
{
b.Parameters[0].Value = sss;
int res = b.ExecuteNonQuery();
}
//b.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
string s = ex.Message;
int t=0;
t++;
}
d3 = DateTime.Now;
string sdiff1 = d1.ToString() + " - " + d2.ToString();
string sdiff2 = d2.ToString() + " - " + d3.ToString();
string tttttt = "sdfa";
}
}
 
3. Objects on SQL Server side:
-- Assuming that there already is an auxiliary Numbers table:
SELECT Number*8 + 1 AS StartFrom, Number*8 + 8 AS MaxLen INTO dbo.ParsingNumbers FROM dbo.Numbers 

CREATE FUNCTION dbo.ParseImageIntoBIGINTs(@BIGINTs IMAGE)
RETURNS TABLE AS RETURN(
 SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 8) AS BIGINT) Num FROM dbo.ParsingNumbers WHERE MaxLen <= DATALENGTH(@BIGINTs))

 

Suppose you want you NCI index cover a query. Suppose you want to make sure that a column, let's say CustomerID, is stored in the index.
Even if your table is currently clustered on CustomerID, it is still a good practice to explicitly include CustomerID in your NCI. The reason is simple: sometimes we can drop the CI and build another CI on another column(s). Your index should still store CustomerID, so that it still covers your query.
CREATE TABLE #t1(i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)

CREATE TABLE #t2(i INT)
INSERT #t2 VALUES(1)
INSERT #t2 VALUES(3)

-- incorrect result
SELECT i FROM #t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = i)
/*
i
-----------

(0 row(s) affected)
*/

-- correct result
SELECT i FROM #t1 t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = t1.i)
/*
i
-----------
2

(1 row(s) affected)

*/