Green rope meshwork

Using indexed views? What is an imprecise or non-deterministic convert?

After last week’s post about using WITH (NOEXPAND) to query indexed views even on SQL Server Enterprise Edition, this week is a short but interesting side-road into deterministic values, and why it is important to get your data types correct. Longtime readers will know I care deeply about data types. Here is a T-SQL query
-> Continue reading Using indexed views? What is an imprecise or non-deterministic convert?

A filing cabinet drawer containing index cards

Using indexed views? Don’t forget this important tip.

Today after fighting with designing a view that let me create a clustered index on it (indexed views — also known as materialized views — are awesome in the right context!), I kept running into the same problem whenever I clicked Display Estimated Execution Plan in SQL Server Management Studio. The problem was that the plan was doing
-> Continue reading Using indexed views? Don’t forget this important tip.

construction

Does rebuilding my clustered index also rebuild my non-clustered indexes?

I’ve been working with SQL Server for many years now, and up until recently, I assumed that rebuilding any clustered index would cause non-clustered indexes to be rebuilt as well, because the non-clustered index includes the clustered index in it. This assumption is wrong. On SQL Server 2000, this only used to affect non-unique clustered
-> Continue reading Does rebuilding my clustered index also rebuild my non-clustered indexes?

key

On clustered indexes, clustering keys and primary keys

Many smart people have spoken about clustering keys and primary keys before, so here’s a less smart take on the topic. Let’s define some things first: Clustered Index – the column (or columns) by which the data in the table will be logically sorted. In other words, barring some exceptions, if you look at the
-> Continue reading On clustered indexes, clustering keys and primary keys