26,060 reputation
656103
bio website
location United Kingdom
age
visits member for 2 years, 6 months
seen Apr 3 at 19:03

Email: stackoverflowmartinsmith@gmail.com


Jan
29
awarded  Enlightened
Jan
29
awarded  Nice Answer
Jan
28
comment Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?
Shared memory still uses a fixed size buffer (I presume) so it is still possible for the server to need to wait on the client to consume more rows before being able to continue adding to it.
Jan
28
comment Disabled index still incurs overhead when inserting
@JonSeigel - Yes I found a connect item stating that way round is expected. Haven't found one for the reverse yet though.
Jan
28
comment Disabled index still incurs overhead when inserting
This is contrary to the documentation here though which seems to suggest dropping the CI leaves NCIs disabled.
Jan
28
comment Disabled index still incurs overhead when inserting
I see. Looks like disabling the NCIs then dropping the CI causes the NCIs to become re-enabled! The is_disabled flag in sys.indexes gets reset to 0
Jan
28
comment Disabled index still incurs overhead when inserting
@ah - Yes. Exactly. It is expected. The "IndexesDisabled" plan doesn't show any maintenance for the NCIs though.
Jan
28
comment Disabled index still incurs overhead when inserting
What on the "IndexesDisabled" plan are you interpreting as the non clustered indexes being maintained?
Jan
28
revised Disabled index still incurs overhead when inserting
deleted 546 characters in body
Jan
28
revised Disabled index still incurs overhead when inserting
added 601 characters in body
Jan
28
awarded  Popular Question
Jan
27
revised How do I copy a table with SELECT INTO but ignore the IDENTITY property?
added 90 characters in body
Jan
25
reviewed Close Database Recovery Model - Simple v Full
Jan
25
awarded  Deputy
Jan
24
answered SQL Server transaction replication ignores DML which does not change data
Jan
24
answered Where clause for multiple rows and columns
Jan
24
revised Necessary to include clustered index columns in non-clustered indexes?
Added Delaney link and example that demonstrates how the CI key is appended in more detail.
Jan
24
comment Necessary to include clustered index columns in non-clustered indexes?
@JonSeigel - Yes. SQL Server always ends up making every index's key columns unique. So if the CI is not declared as unique then the uniqueifier (if present) just gets treated the same as any other CI key column when copied into the NCI.
Jan
24
answered Necessary to include clustered index columns in non-clustered indexes?
Jan
24
revised Why does SQL Server run a subquery for each row of the table it's qualifying?
added 158 characters in body