22,016 reputation
660119
bio website sqlperformance.com/author/…
location New Zealand
age 45
visits member for 3 years, 9 months
seen 1 hour ago

3h
comment How do I create a partition using a bit field in SQL Server 2014
@Founder Updated the answer to address that point (better than answering in comments :)
19h
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.
20h
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?
1d
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.
Oct
16
comment Unexpected results with random numbers and join types
connect.microsoft.com/SQLServer/feedbackdetail/view/350485/…
Sep
15
comment Is database record access sequential after retrieving the indexed page from disk?
@waheebyaqub A physical change to the page will invalidate the cookie. The question of order is complex. For an index page, the offset array is always ordered, even though the entries it points to may be in any order. Big topic.
Sep
8
comment Replication Error when adding small table as new article
For future visitors: support.microsoft.com/kb/2655789
Sep
6
comment Casting fails on subquery result
@ckerth No it is not.
Aug
14
comment Why does transaction log continue to grow in Simple recovery mode with nightly backups
Another thing to consider: an automatic checkpoint for a simple recovery database is only queued once the log is already 70% full. So, you might not need all that much log activity to result in growth, depending on timing.