some of our tables have a 'Deleted' flag, and a row cannot be considered to really be in a table unless the Deleted flag = 0. this means your joins will always have something like "t1.Id = t2.Id AND t1.Deleted = 0".
the design goal here was to keep some historical record of changes without the hassle of a history table, but it was really a bad idea since it’s way too easy to forget about when writing queries, and all indexes are irrelevant unless they start with the Deleted bit...
Showing posts with label schema design. Show all posts
Showing posts with label schema design. Show all posts
Thursday, February 14, 2008
Monday, June 11, 2007
beware BIT columns in SQL2000
found a strange one today… if you have a BIT column and you write the following TSQL:
WHERE BitColumnName = 1
SQL Server doesn't quite do what you want it to do. rather than converting the 1 to a bit and then applying the where clause filter, it instead converts BitColumnName to an int and then applies the filter. this is problematic since you won’t be properly using indexes you have on the bit column. to avoid this, write:
WHERE BitColumnName = convert(bit, 1)
this is fixed in SQL2005--
WHERE BitColumnName = 1
SQL Server doesn't quite do what you want it to do. rather than converting the 1 to a bit and then applying the where clause filter, it instead converts BitColumnName to an int and then applies the filter. this is problematic since you won’t be properly using indexes you have on the bit column. to avoid this, write:
WHERE BitColumnName = convert(bit, 1)
this is fixed in SQL2005--
Subscribe to:
Posts (Atom)
