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:
id round x o winner start duration
o store respective players.
Now we have additional requirement: no person should be able to play in a tournament more than once. How do we do that?
Creating unique indexes on
(round, x) and
(round, o) won’t help: a person could engage in one game to play Xs and another one to play Os. Neither will help a single index on
(round, x, o): this would only disallow same pairs, not same players (and even pairs could swap the marks and keep playing).
We could redesign our table a little:
id round sign player
(id, sign) a composite primary key, so that two records would always be stored for a game. This process is called unflattening or unpivoting a table. On such an unflattened table we could just create a composite unique index on
(round, player) which would help us achieve our goal.
However, it would also allow data inconsistency: what if we did not insert a record for one of the games? And we would still need a separate game table to store winner, start time, duration etc.
Note that if we had the first table (with X and O), we could make a query to build the second one:
SELECT id, round, sign, CASE sign WHEN 'X' THEN x WHEN 'O' THEN o END AS player FROM ( VALUES ('X'), ('O') ) mark (sign) CROSS JOIN game
Here we use a run-time two-record table to join with games and thus reduplicate each of its records, so if we had a record like this:
id round x o winner start duration 1 1 John Jim John 2013-05-27 00:37
it would turn into these two records:
id round sign player 1 1 X John 1 1 O Jim
Each record turns into two and the respective player is substituted for each mark.
If only that were a real table: we could build an index over that! Fortunately, in SQL Server we can. To do this, we would need to make a so called indexed view.
An indexed view is almost the same as a simple view (which is in fact nothing more than a named query, which you can save and use as a table in
FROM clauses), however its results are stored on the disk (as opposed to being recalculated every time). Those results are updated each time you update the tables participating in this query.
This process is called query materialization. It’s not cheap though, so SQL Server does not let you index all queries, but only those it can materialize and update in a timely fashion. The query above is an example of materializable query which means it can be used in an indexed view.
Note that indexed views have certain limitations: some are easy to overcome, some are a little bit harder. For instance, you cannot use wildcards for field lists; you should qualify all your objects with their schema; and, the most nasty for us, you cannot use derived tables.
The latter means we cannot just generate a two-record inline table on the fly (like we did with
VALUES in the query). For the view, we should explicitly create it as a permanent table and put those two records there with an
So here goes our view:
CREATE TABLE mark (sign CHAR(1) NOT NULL PRIMARY KEY) GO INSERT INTO mark VALUES ('X'), ('O') /* Note that we create an actual table rather than online view. It is not elegant, however, this is what SQL Server requires us to do. */ GO CREATE VIEW gameplayer WITH SCHEMABINDING /* Note the WITH SCHEMABINDING clause above. Declaring this "binds" the view to the underlying tables so that you cannot modify or delete them anymore without affecting the view. This is a prerequisite for an indexable view. */ AS SELECT id, round, sign, CASE sign WHEN 'X' THEN x WHEN 'O' THEN o END AS player FROM dbo.mark CROSS JOIN dbo.game /* Note the "dbo." above. This is a schema qualifier: you can have multiple schemata in your database, each allowing the tables with the same name, but this very view specifically binds to the tables belonging to the schema "dbo" (the default schema in SQL Server). This is also a prerequisite */ GO CREATE UNIQUE CLUSTERED INDEX ux_gameplayer_id_sign ON gameplayer (id, sign) /* This is where our view actually gets materialized. By creating a clustered index, we bring a table with the same definition as the view into existence: the view's query is executed and its results are written onto the disk into a special table. The table is clustered by (id, mark) and contains all the view's columns. It will also be automatically updated whenever dbo.game or dbo.marks update. */ GO CREATE UNIQUE INDEX ux_gameplayer_round_player ON gameplayer (round, player) /* This is a secondary unique index which makes sure no cheater sneaks into a single round twice. */ GO
Now we can just go on with our app: insert the tournament records into game, select them etc. Let's create a couple of game records:
INSERT INTO game (id, round, x, o) VALUES (1, 1, 1, 2) -- Those are Alice (1) and Bob (2), playing in round 1 GO INSERT INTO game (id, round, x, o) VALUES (2, 1, 3, 4) -- Those are Chloe (3) and Dave (4), playing in round 2 GO
If we try to do anything fishy, like attempting to put Alice against Dave in the same round, even swapping their marks, the system won't let us do that:
INSERT INTO game (id, round, x, o) VALUES (3, 1, 4, 1) -- We try to book Alice and Dave for round 1 again. Cannot insert duplicate key row in object 'dbo.gameplayer' with unique index 'ux_gameplayer_round_player'.: INSERT INTO game (id, round, x, o) VALUES (3, 1, 4, 1)
Despite the fact we inserted it into the game, it was a constraint on gameplayer which got violated. Gameplayer is automatically updated whenever the underlying table game is changed.
Note that we actually have two full-scale, first class physical tables in our database. Updating game updates gameplayer, with disk I/O and everything, it's a real table.
If we wanted to, we could make it the other way around: declare gameplayer the table and game the view. However, this would break schema normality: partial matches (with only one player) would be allowed; winner, start time and duration would have to be stored twice (or a third table created for them) etc. Not that it's always a bad thing (say partial matches could really come handy), but it should be mentioned.
So with indexed views we can both keep our schema normal and allow constraints beyond what plain DDL allows us to declare.
Those interested can view and reproduce the whole story in SQL on an SQL Fiddle I created.
To be continued.