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

1d
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
2d
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
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?
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
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
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.)
Jan
22
comment Alias of COUNT is not being “recognized” by SQL Server
MySQL does allow this and AFAIK it doesn't create any more ambiguity than does allowing it in the ORDER BY. However SQL Server supports window functions SELECT ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RN FROM SomeTable HAVING RN > 10 wouldn't make any sense at all as the ROW_NUMBER is calculated after the HAVING.
Jan
22
comment Why does SQL Server refuse to update these statistics with anything but fullscan?
(Regarding the last paragraph) SELECT WebAnalyticsId, MarketKey from [dbo].[factWebAnalytics] TABLESAMPLE (897667 ROWS) ORDER BY MarketKey, WebAnalyticsId runs in less than 30 seconds for me. It doesn't use the columnstore index though. It uses the clustered index.
Jan
18
comment Is introducing a where condition into the join clause bad practice
@Ascendant bugmenot.com/view/sqlservercentral.com
Jan
17
comment Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?
Does it use binary(16) consistently throughout? Including for variables and parameters? If not you need to consider effects of implicit casts.
Jan
17
comment SSIS 2012 metadata not refreshing on re-deploy
I've never encountered this issue on SSIS 2012. As long as the metadata is refreshed where the connection manager is looking at a database with the updated schema I can then deploy and set the connection manager to a different connection with the same updated schema without problems, are there other differences in the tables?
Jan
14
comment How can I find a table in the whole instance?
@AA.SC - Ah, in that case no then. There is a SCHEMA_ID function but that doesn't accept two part names or database id.
Jan
14
comment How can I find a table in the whole instance?
@AA.SC - yes, just use [HR].[YourTableName] instead of [dbo].[costomersHR]
Jan
9
comment Phantom comparison data in SQL Server Reporting Services - How do I remove it?
What is the definition of the view?
Jan
7
comment How to retrieve the latest guid value after you have inserted a new row in realtime
For an ascending integer it would be incorrect if another session inserts in between your insert and the SELECT query you would read the id of that insert instead. For GUIDs generated by newid() the order of them will be in no way correlated to insert order.
Jan
7
comment How to retrieve the latest guid value after you have inserted a new row in realtime
This would be incorrect even for an ascending integer because of concurrency and it won't work at all (except by chance) for a GUID.
Jan
7
comment How to retrieve the latest guid value after you have inserted a new row in realtime
Retrieve the value and do what with it? Pass it to the calling application? Assign it to a TSQL variable?
Jan
6
comment Is nvarchar 0-indexed?
The column will take up to 4 bytes for the 2 dbcs characters and, usually, two bytes in the column offset array.
Jan
6
comment Avoiding a divide by zero error in a computed column
Do you intend to use integer division here or not?