For partitioned tables, we can TRUNCATE a partition without use partition switching.

TRUNCATE TABLE YourTable WITH ( PARTITIONS 2, 4 to 7)

Table variables never stores data in memory but always in tempdb. It's also shown in the article referenced in the article.

It is possible for table variables to define the constraints...

I agree. But it is interesting when we talk about physical or logical order. We see again and again on the web that in a clustered index data is stored...

"However, the physical storage of the row on that page is not necessarily maintained in key value order" - the only important is, that the rows only can be accessed...

• This reply was modified 11 months, 2 weeks ago by  carsten.saastamoinen.

Some of the pages from my book published on Kindle.

• This reply was modified 11 months, 2 weeks ago by  carsten.saastamoinen.

When the book is profiled several times on your site, it must also be reasonable that it is the same place with the same readers who are warned. It is...

I strongly disagree. The book is Advanced/Expert, so I have to assume that it is not only read by beginners. So my code and example is just a simple example.

It...

There is a great problem in the book, when we are talking about performance. In a HEAP - on page 3 - data is NOT ordered as inserted. We reuse...

• This reply was modified 11 months, 3 weeks ago by  carsten.saastamoinen.

I don't agree with the rules about the order of columns in a multi-column index. It is correct that it depends on the consumption pattern with "equal to" operator before...

• This reply was modified 1 year, 3 months ago by  carsten.saastamoinen.

YES! And the elapsed time will change more than CPU time on a production server with many users.

Try to look at the CPU time

I use your script and just show the result.

Here is a test that shows using a separate MERGE is faster than a UPDATE/INSERT.

If you change your data to a more realistic scenario, you will come to the opposite...

I have seen - and being removed - because I told

• that DISTINCT do not order data - basic understanding.
• that it is not better to write COUNT (primary key)...

Else the whole solution is wrong!