It allows you to enter duplicate values and then drops them semi-silently.

Very cool and useful. Thank-you for your efforts in writing this up.

I also ran into some issues with the Primary key, and changed it to

constraint pk_invalid primary...

@jeff.Moden

In previous organisations I took to having a Primary, Data and Index filegroup and put all my clustered indices in the Data filegroup and non-clustered in the Index...

• This reply was modified 3 years, 1 month ago by  Toby Harman.

One thing I am disappointed not to be hearing more about is the use of resumable index rebuilds.

I've stopped using reorganize on huge tables where we can't rebuild because we...

No mention of using schemabinding on scalar functions that don't actually touch the database. This can provide a significant performance benefit by removing  the unnecessary Halloween protection

The error message for that isn't quite what I got, and with a little tweaking I could add the index

drop table if exists dbo.MonthlySales;
create table dbo.MonthlySales (
...

We currently get the Lenovo X1 Carbon with 16GB RAM and a 512GB NVMe drive for travel / home use. Plenty for email, browsing and RDP to the desktop over...

Thanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and...

I had to cheat to get this and actually run the code. I got suspicious when I couldn't see the answer I was expecting "Invalid column name 'c1'."
Personally I...

Our CTO wrote an article on Forbes about engineering soft skills and culture which you might enjoy.

So there are a couple of things that block the re-use of a transaction log.

Firstly, backups prevent the re-use of the transaction log. Backups need the database to...

There's always a trace flag to help with this.

Rather than adding option (recompile) to the end of every select that references the table variable (and therefore...

Thanks for reminding me of this!

I built a fairly extensive custom ETL system a few years ago which used MERGE and OUTPUT clause to audit the actions and...

So 3 variables x, y and z all defined as DECIMAL (9, 5) (i.e 99,999.99999)

What is the resultant datatype from this calulation

(x * y) / z

Starting with the multiplication inside...

Thumbs up for this link posted by xanthos.

https://msdn.microsoft.com/en-us/library/ms190476.aspx

Some time ago I was working with financial services organisation and yield calculations were an important part. Getting these decimal values and sizes...