Reputation
506
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
4 15
Impact
~27k people reached

  • 0 posts edited
  • 0 helpful flags
  • 30 votes cast
Aug
18
awarded  Nice Question
Jul
31
accepted SET STATISTICS IO- worktable/workfile
Jul
30
awarded  Curious
Jul
29
revised SET STATISTICS IO- worktable/workfile
added 7 characters in body
Jul
29
comment SET STATISTICS IO- worktable/workfile
@TomV, yes- spill level 1 & spill level 2.
Jul
29
asked SET STATISTICS IO- worktable/workfile
Jul
28
awarded  Caucus
Jul
13
comment SARG cardinality estimate, why not full-scan?
Its possible to get different sort order also by comparing windows collation and "new" windows collation (for example Latvian_CI_AS and Latvian_100_CI_AS will return different results when sorting Latvian characters)..
Jul
6
accepted SARG cardinality estimate, why not full-scan?
Jul
6
comment SARG cardinality estimate, why not full-scan?
"When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index." msdn.microsoft.com/en-us/library/ms186342.aspx
Jul
3
comment SARG cardinality estimate, why not full-scan?
@MikaelEriksson i ran test on my machine as colleague pointed out this might be the reason- server collation Latin1_General_CI_AS and DB collation- SQL_Latin1_General_CP1_CI_AS. Thannks for link!
Jul
3
comment SARG cardinality estimate, why not full-scan?
And it not always cheapest to seek/scan non-clustered index- when values are not distinct enough or non covering index, it may be cheaper to do clustered index scan instead.
Jul
3
comment SARG cardinality estimate, why not full-scan?
Yes, i am aware of what you writing. As you can see, there is clustered index already on TestTableID. But the thing is- if SQL server cant see statistics of column data distribution (as in this case, due to data type mismatch that should require all row value data type conversion), it should choose Clustered index scan in this case, not index seek.
Jul
3
awarded  Custodian
Jul
3
reviewed Approve SARG cardinality estimate, why not full-scan?
Jul
3
asked SARG cardinality estimate, why not full-scan?
Mar
5
awarded  Yearling
Mar
5
accepted Change Tracking clenup
Mar
5
comment Change Tracking clenup
Thanks for answer and links! Kendra Little on Change Tracking article is great. And seems can use sys.dm_tran_commit_table to see oldest transaction in change tracking table.
Mar
2
asked Change Tracking clenup