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

5h
comment How to recursively find gaps where 90 days passed, between rows
I can't up-vote this as it stands because the code isn't runnable, or easily mind-parsable. Provide complete sample data and a script that demonstrates that it works, and I'll look again.
1d
comment How to write a procedure /job to kill all session running for more than 5mins in Oracle?
Link-only answers are discouraged because the link can stop working. Consider adding the key information from the post to your answer here. Oh, and only adding answers that link to your own writing is a quick way to upset the site moderators.
Nov
25
comment Verify if user has read access to a table
@CraigEfrein The built-ins take everything into account. (SQL Server has to know how to correctly determine effective permissions; this function exposes that ability).
Nov
21
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.