Reputation
37,151
Next tag badge:
542/400 score
74/80 answers
Badges
8 97 156
Newest
 Enlightened
Impact
~1.4m people reached

2h
answered Progress of SELECT INTO statement
5h
comment Progress of SELECT INTO statement
@srutzky - yes. The transaction log has all the info needed to rollback. Dirty pages can be written to disc - e.g at a checkpoint or by the Eager writer especially in this case then removed from the buffer pool.
5h
comment Progress of SELECT INTO statement
This does assume that no pages have been evicted from the buffer pool yet.
2d
comment Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?
This screenshot from "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML" (on Google Books) seems relevant and indicates this has been the way it works since 7.0 i.stack.imgur.com/8pDGT.png
Feb
9
comment Speeding up insert in SQL Server
It looks like the select that is the problem not the insert. The index scan that inserts 4 million rows into a spool will be significantly more expensive than the final 100 row insert.
Feb
7
revised Select Into removes IDENTITY property from target table
edited title
Feb
1
comment How to trim first digit off an integer value?
What should be returned if the value in column1 is only a single digit? Does it need to handle negative values? What RDBMS is this for?
Feb
1
revised How to trim first digit off an integer value?
added 26 characters in body
Jan
30
comment Why does adding a TOP 1 dramatically worsen performance?
@KennethFisher You can apply this with a plan guide, spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides or just use a stored proc brentozar.com/archive/2015/06/…
Jan
30
revised Why does adding a TOP 1 dramatically worsen performance?
added 2 characters in body
Jan
30
answered Why does adding a TOP 1 dramatically worsen performance?
Jan
26
revised Why does SQL Server refuse to update these statistics with anything but fullscan?
deleted 6 characters in body
Jan
24
comment How to get SQL insert and/or update to not lock entire table on MS SQL Server
By the way WHERE LAYOUTS_key='" + newkey + "' is a complete no-no for various reasons including SQL injection, you should use parameterised queries.
Jan
24
comment How to get SQL insert and/or update to not lock entire table on MS SQL Server
@JohnRiehl - Yes if your aren't explicitly using SNAPSHOT isolation best to leave it disabled and then enable it if you subsequently decide this would be useful for you.
Jan
24
answered How to get SQL insert and/or update to not lock entire table on MS SQL Server
Jan
24
revised Change SELECT DISTINCT to UPDATE DISTINCT
added 2544 characters in body
Jan
24
answered Change SELECT DISTINCT to UPDATE DISTINCT
Jan
23
accepted Why does SQL Server refuse to update these statistics with anything but fullscan?
Jan
23
revised Why does SQL Server refuse to update these statistics with anything but fullscan?
added 944 characters in body
Jan
22
comment Why does SQL Server refuse to update these statistics with anything but fullscan?
Yes it definitely looks like something fixed in later versions. I produced a simple repro here pastebin.com/7f4TwmKW and on a test server running 11.0.5343.0 found my request for a sample size of 10,000 rows was ignored and all 8,000,000 rows sampled i.stack.imgur.com/DbbjZ.png (plan much the same as the one in the question) - But I don't encounter this on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (rows sampled there is 274,649 which is pretty close to the estimated number of rows in the earlier build and the plan uses the CI rather than the columnstore.)