You can reference the same CTE multiple times in your later queries. This is a nice feature. But be aware that SQL seems to re-run the CTE rather...

Technically you don't have to manipulate the date in the table. For a million+ row table, you save a million+ calcs 🙂 :

ORDER BY

CASE

...

Nonclustered indexes generate additional I/O whether there is a clus index or not. As always, every index adds overhead to a table. That's all I meant.

I was hoping...

I would stick with the more used field (and properly ascending) field. On an index, the most important thing is to match the first column in the index, esp....

No, that's exactly right. You should be extremely careful about INCLUDEing columns in an index. It's a very useful feature, but it should be selectively, only to make...

His name is Joe Celko.

Period should definitely be the first column in the clus index. For now, just swap the two columns. That should end the issue with the INSERTs; it's likely...

Yes, an ident is unique, but that does not in and of itself ever make it the proper choice for a clus index.

The clus index column(s) should always...

My hope was that with a clus index...

Please clarify "often".

I see your points. But, don't overlook the fact that if you're going to create an index on the date anyway, you will churn that nonclus index instead of...

If you're actually doing validation and not just adjusting formatting, you may want to be more restrictive on valid formats. Below are some possibilities to get you started with...

I don't object to an IDENTITY column in tables per se, and they have very legitimate uses.

But I dislike the idea that the default for a clus index should be...

I wouldn't create a clus index on an ident column. That will not help your queries. In fact, you will reduce the chance of using a nonclus index.

You...

If there can only be two rows per name, and you just want the difference between ColumnA's, you can do it very simply:

SELECT UserName,

MAX(ColumnA) -...