Make it Short and Sweet

Readers often ask me which data type is the best to use for a primary key. I always recommend the simplest of data types—either an integer or a short-character data type. Managing these data types is easier than managing more complex data types such as variable-length character data types, which require additional processing with each operation.

Every time SQL Server has to access a record that contains variable-length fields, for each data record and for each index built on those data records, SQL Server has to check the column offset array to determine at which point the variable-length field begins in the data record. SQL Server's additional activity creates increased overhead that might become a liability when your databases grow or when the queries accessing the data become more complicated.

Main article: Surrogate Key vs. Natural Key

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.