What Makes an Index Clustered?
10 Jul 2017

In my previous post, I told you about what it meant for an index in SQL Server to be ‘clustered’. Usually, when we talk about SQL Server indexes, we are talking about indexes organized as B-trees (or technically, B+-trees, but we won’t go into the difference here.) For these indexes there are two basic properties that set clustered indexes apart from nonclustered. First, the index leaf level IS the data. It is not a copy of the data or pointers […]

Is Your Index Clustered Enough?
30 Jun 2017

When you tell SQL Server to create a regular clustered index on a table, it will completely rebuild your table. SQL Server will take your data rows and rearrange them on new pages. Your table’s pages become the leaf level of the clustered index, so sometimes we call the table a clustered table. People usually think of clustered as meaning ‘sorted’, and for the most part I’ll go along with this. But sorting can be logical or physical, and there […]

Is In-Memory OLTP a Useless Feature?
22 Jun 2017

What makes a feature useless? I might agree that if absolutely NO ONE uses it, or has ever used it, then it is useless, by obvious definition.  But if some people use it, but not everyone, is that useless? What if only 1% of product users employ a feature in production? Is that useless? What if those 1% base their entire production system around the feature? Is that more or less useless than another feature used by 80% of the […]

Would you like Amazon Web Services with your Pizza?
06 Jun 2017

Amazon SQL Server in the Cloud Over the last year or so, I’ve had several opportunities to get my hands dirty with Microsoft’s SQL Server cloud offerings, including taking a Train-the-Trainer class with Dandy Weyn. Now working with DB Best, I realized I needed to broaden my horizons a bit, so I was grateful that I had a chance this week to attend an online webinar called Introduction to Microsoft SQL Server in AWS. Amazon Web Services (AWS) provides two […]

How Fast is FAST?
25 May 2017

When listening to an online presentation a few days ago, the speaker suggested using one of my favorite, underused query hints: FAST N.  This replaces an older table hint called FASTFIRSTROW, which is one of the few deprecated features to actually be removed from SQL Server. The speaker suggested using the FAST 1 hint, because ‘sometimes it improves the query’, but didn’t say any more about how or why it would improve the query, and why you might NOT want […]

Rolling Back a Transaction on a Memory Optimized Table
18 May 2017

After three and a half weeks with DB Best, things are starting to settle in. After attending the SQL Server 2016 Workshop in Bellevue, and delivering one section on In-Memory OLTP and columnstore indexes, I flew to Houston and delivered the entire Workshop on Friday, May 12. I did have local support from Peter DeBetta, also from DB Best, and John Cook, from Microsoft. The Houston Workshop reached capacity by the Monday before the event, but with John Cook’s help, […]