Data Type Conversions in Predicates

Bert Wagner takes us through a troublesome table design:

This table stores data for an application that has many different types of Pages. Each Page stores different types of data, but instead of creating a separate table for each type, we store all the different data in the varchar DataValue column and maintain the original data type in the DataType column.

This structure reduces the complexity required for maintaining our database (compared to creating possibly hundreds of tables, one for each PageName) and makes querying easier (only need to query one table). However, this design could also lead to some unexpected query results.

This is your daily reminder that an attribute should be a thing which describes an entity, not one of multiple things.

Related Posts

Reading SQL Server Error Logs

Thomas Rushton has a script for us: Why Script This? What’s Wrong With SSMS’s GUI?Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I […]

Read More

Custom SQL Server Error Messages

Kenneth Fisher shows how you can build your own custom SQL Server error messages: I’m sure lots of you have used the function RAISERROR to handle an error caused by your code. The problem is, what do you do if the error you want to display isn’t one that Microsoft choose to include in the list of […]

Read More

Categories

May 2019
MTWTFSS
« Apr Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031