THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Browse by Tags

All Tags » Internals » Query Plans » Indexes » Undocumented

  • A Tale of Two Index Hints

    If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is that both hints can result in a scan. If that is the case, you might wonder if there is any effective difference between the two. This blog entry explores that question, and highlights an optimizer quirk that can result in a much less efficient query plan when using INDEX(0). I’ll also cover some stuff about ordering guarantees. Read More...
  • The Impact of Non-Updating Updates

    From time to time, I encounter a system design that always issues an UPDATE against the database after a user has finished working with a record – without checking to see if any of the data was in fact altered. The prevailing wisdom seems to be that “the database will sort it out”. This raises an interesting question: how smart is SQL Server in these circumstances? In this post, I’ll look at a generalisation of this problem: what is the impact of updating a column to the value it already contains? The specific questions I want to answer are: Does this kind of UPDATE generate any log activity? Do any data pages get marked as dirty (and so eventually get written out to disk)? Does SQL Server bother doing the update at all? Read More...
Privacy Statement