pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Monday, April 12, 2010

LINQ doesn't like TINYINT PK IDENTITY fields

So, to save space (and it is a bit archaic, but why not) I sometimes use tinyint and smallint for the PK IDENTITY columns in small lookup tables.

For example, a list of counties or congressional districts for a state government project.  Will never be more than 255 or negative.  Tinyint.  1 byte instead of 4.

Yeah yeah, I'm aware that in the long run, that adds up to very little saved disk space.  Very little.  Still, I can't help but be tight with data type constraints.  Its not like I will be allowing every column to be declared varchar(8000), much less varchar(max), even when "disk space is cheap".  Its not like I'll be using bigint instead of int when I know I'll never get more than 2 billion records. 

Except that when your developers want to use LINQ, you can't use a TINYINT IDENTITY column. Stupid error.

Changed it, begrudgingly, to smallint.  2 bytes instead of 4.  Grrr...

Read more:
http://linqinaction.net/blogs/roller/archive/2007/11/28/linq-to-sql-doesn-t-support-identity-tinyint.aspx

No comments: