Here's something neat about Microsoft SQL Server that I learned this week. I don't know if it applies to PostgreSQL, MySQL and other such systems, but I'd hope they're also as clever.
Let's assume we're building an improved of the 5-minutes-of-fame app Yo. As well as being able to send a 'Yo' to another user, you can also send the traditional Yiddish complaint, 'Oy'.
Note that I've created a constraint that specifies that Messages.MessageType can only be one of two possible values: 'YO', or 'OY'.
This is a great way to ensure that your data values fall within the ranges you expect, rather than having to debug ages later to figure out how some bad data got into your database. If I try insert a different value, such as 'HI', SQL Server will return an error instead of inserting the bad data.
As it turns out, this also helps SQL Server tune queries.
Let's look at the query execution plan for this. If you've never heard of an execution plan, this shows you how a SQL server will execute your query.
In MySQL and PostgreSQL, you can get this with the EXPLAIN keyword. In Microsoft SQL Server, you can get this by pressing Ctrl+L with your query selected in SQL Server Management Studio.
I've used OPTION (RECOMPILE) in the screenshots below, which forces Microsoft SQL Server to figure out a plan from scratch, rather than using one in it's execution plan cache.
This shows a pretty simple plan for a simple query on a simple table. It will simply search all rows in the table to find matching ones.
This one is exactly the same as above. It will simply search all rows in the table to find matching ones.
SQL Server doesn't scan the table. Or any indexes. In fact, it doesn't even look at the table.
Here it does a 'Constant Scan', which in this case is just a short-circuit of the entire query to return an empty set. SQL Server can do this, because it knows that 'HI' is not a valid value due to the constraint on that column.
By adding database-level validation to this table, SQL Server can better understand my data to sensibly tune queries, and spend no time at all answering bogus ones.