21,524 reputation
761127
bio website sqlperformance.com/author/…
location New Zealand
age 45
visits member for 3 years, 9 months
seen 56 mins ago

2d
comment Handling concurrent access to a key table without deadlocks in SQL Server
SERIALIZABLE doesn't exist to prevent phantoms. It exists to provide serializable isolation semantics, i.e. the same persistent effect on the database as if the transactions involved had executed serially in some unspecified order.
Dec
23
comment use an Index correctly to optimize queries
Table partitioning is in preview link1 link2 but I doubt it's the right solution here. As you say at the start of your answer, the ntext columns need to go. I doubt they store > 8000 bytes anyway. The OP appears to be quite new to databases.
Dec
21
comment Why doesn't a delete increase unused space in a table?
Documented here and in this KB article. It is an internal limitation due to the way space is managed for heap structures.
Dec
21
comment How to fix error Msg 1801, Level 16, State 3, Line 3
SELECT M.[text] FROM sys.messages AS M WHERE M.message_id = 1801 AND M.language_id = 1033; -> "Database '%.*ls' already exists. Choose a different database name."
Dec
21
comment New Index on table cause table to be locked really often
@GuillaumePhilipp Ok well the computed column might not be such a good idea in that case. The original index suggestion should work well though.
Dec
21
comment Why doesn't a delete increase unused space in a table?
Try delete dbo.CleanTableTest WITH (TABLOCKX). This may allow empty pages to be released from the heap (assuming the database does not have snapshot isolation or read committed snapshot enabled.)
Dec
18
comment Phantom vs. Non-repeatable read
Only the authors of the book in question could answer this exactly, i.e. to explain exactly what they meant to say.
Dec
18
comment Results for every hour of the day even data if not present
I've cleaned up the question as best I can, but I still can't see the logic that produces the output from the source data given. Can you edit the question to clarify please?
Dec
17
comment Excessive sort memory grant
@Reaces Hm. Good question; not sure - I'll need to think about that. Probably a plan cache query of some sort. I came across this issue when answering a question I received by email.
Dec
15
comment How does objectproperty() figure out if a function is deterministic in sql server?
Explained here
Dec
10
comment SQL Server 2012 Table Partitioning - Have I done it correctly?
More specifically, any implicit conversion required must not result in possible truncation.
Dec
9
comment reporting space consumption to files in filegroup - how to reverse-engineer the algorythm spreading data across files
Some relevant links: Round Robin, Proportional Fill and Performance Considerations and Understanding the -E Startup Parameter
Dec
7
comment Reuse a complex non-deterministic expression in SELECT and WHERE clauses
This is quite likely to work, but it is not guaranteed. SQL Server does not guarantee the timing or number of evaluations of expressions. Even where it seems 'obvious' from the query text that evaluation only occurs once, the optimizer may produce a plan with different runtime characteristics.
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 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
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.
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.