20,819 reputation
459113
bio website sqlperformance.com/author/…
location New Zealand
age 45
visits member for 3 years, 8 months
seen 9 hours ago

1d
comment Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?
Link to back up the history comment above <- by Paul Randal.
Nov
18
comment The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
@a_horse_with_no_name Thanks, I did not know that.
Nov
18
comment What Columns to create Indexes on?
A clustered index (defined as in my answer) will enable a seek. Try it!
Nov
18
comment What Columns to create Indexes on?
You are misunderstanding: 1) It is possible to use INCLUDE in SQL Server 2005 (documentation). 2) Your post still says "INCLUDING", which is not a valid keyword. 3) The whole point you're making is wrong, because a clustered index includes all the other columns already.
Nov
18
comment What Columns to create Indexes on?
A clustered index automatically 'includes' all the other columns, by definition. There is no INCLUDING syntax element, you meant INCLUDE. Finally, INCLUDE was added in SQL Server 2005. Aside from that... ;)
Nov
18
comment Filter table before join
Just to note: Adding a CHECK constraint to the table will result in the desired sort of sideways logic being applied as well.
Nov
18
comment Most cost efficient way to page through a poorly ordered table?
@beeks The expression sets the integer variable to the result of bitwise-ORing e.g. 16, 8192, and 16384. It's a pretty standard way to combine flags. As I say, you can use addition in this case instead, so DECLARE @scrollopt integer = 16 + 8192 + 16384 would come to the same thing. As well as Books Online, see this article by Itzik
Nov
17
comment Most cost efficient way to page through a poorly ordered table?
@beeks Sure, it's a bitwise OR. You could use '+' instead in this case. The sp_cursor* procedures are also all documented in Books Online.
Nov
17
comment Use of MAX data types when over >8000 characters. (error exceeds the maximum allowed for any data type (8000)
@Pixelated Thanks, yes it is improving each day :)
Nov
17
comment This instance of (MS) SQL Server has been using a process id of X since dd/mm/yyyy
The message is simply telling you what Windows process ID SQL Server itself is running under. Purely informational, and nothing to be concerned about.
Nov
17
comment SQL Server Standard Edition Memory Limit
@spaghettidba I agree with your sentiment (somewhat), but the down-vote reasons (see tooltip), close reason text, and links to the help center should provide sufficient context for the author. The original form of the question was even worse! Also, not every 'legitimate' question is on-topic here.
Nov
17
comment TSQL substitute for MAX() string ranking
The sample data would be more useful if it used the animal references instead of integers.
Oct
29
comment AFTER DELETE trigger will not fire during a cascade delete
This should work, if I am understanding your question correctly. Please add real code to the question to allow us to reproduce the issue. Also, specify SQL Server version and build number. An instead of delete trigger would not be allowed, by the way.
Oct
29
comment Why would I not specify ROWLOCK to get rid of page locks?
@sharptooth Capture the execution plan for the query and add it to your question. As I say in the answer above, with appropriate indexing, the question shouldn't arise at all.
Oct
27
comment Why would I not specify ROWLOCK to get rid of page locks?
@sharptooth Yes.
Oct
27
comment Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'
+1 This is nice. It should reject NULLs on id and code to preserve the semantics of the original query, but sadly even if you do that, the optimizer does not transform this logic to the efficient segment top plan generated by the original (somewhat ironically).
Oct
22
comment SQL Server Performance
Perhaps. 2GB might be enough to hold the data and index pages, but extra memory can be used for other purposes e.g. caching execution plans and sorting/hashing.
Oct
20
comment how to order a field based on two parts of the row value
A bit of explanation would be helpful here. Code-only answers are rarely great answers.
Oct
16
comment Unexpected results with random numbers and join types
connect.microsoft.com/SQLServer/feedbackdetail/view/350485/…
Oct
2
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@PavelNefyodov Yep.