EXPLAIN EXTENDED

How to create fast database queries

Archive for June, 2013

Score charts, friendship lists and unique multiple columns: easy with SQL Server’s indexed views

Comments enabled. I *really* need your comment

SQL Server supports a really useful feature called indexed views. This means it lets you materialize results of some queries and build indexes over them. It’s as if you haven’t actually run the query yet - but it’s already complete, stored conveniently on the disk for you, ordered and waiting for you to read its results. Not all queries are eligible but some quite often used by the database developers are.

In this series, I'll describe three scenarios which could really benefit from using indexed views.

Scenario One: Unique multiple columns

Assume you are building a database for multiplayer online tic-tac-toe tournaments. The first thing you’d want would be recording game results: who played with whom and who won. This can be easily achieved with a simple layout:

Table game
id round x o winner start duration

Now we have additional requirement: no person should be able to play in a tournament more than once. How do we do that?

Read the rest of this entry on tech.pro »

Written by Quassnoi

June 27th, 2013 at 11:00 pm

Posted in SQL Server