Take care when choosing your data-types
I’ve come across many SQL Server database designs where the vast majority of the data-types used were INTEGER, NVARCHAR, UNIQUEIDENTIFIER and DATETIME.
If you have a domain table, with less than say 100 rows, it has an INTEGER Primary Key and it’s rarely updated; perhaps TINYINT would meet your needs?
If you’re storing ASCII character data now and in the future, e.g. an “email address” column, there’s no need to use NVARCHAR, use VARCHAR instead. URLs and codes (e.g. ISO country codes) are further examples that may not require Unicode support.
When storing date/time information, do you really need granularity down to the nearest three milliseconds? If not, maybe SMALLDATETIME (nearest minute) will meet your requirements instead of DATETIME?
How about fixed length strings? Use (N)CHAR instead of (N)VARCHAR – save those two bytes of storage that the CHARACTER VARYING types use to record the length of the string.
If you’re careful when choosing your data-types you’ll reap the benefits in terms of reduced storage requirements and faster performing queries – reduced Memory, I/O and CPU storage/overhead (from greater index/data page density) almost always translates to improved performance. You’ll be making more efficient use of buffer-cache (more data in cache and/or more room for the plan-cache (which can be at least as large as the buffer cache for some workloads)) and transfer less data over your (relatively) slow LAN/WAN connection to the client resulting in lower latency and hence better client response times. In fact, the knock-on benefits are so compelling; it’s well worth that little extra effort up-front with your schema design.
A note on implicit data-conversion
Ensure that your literals and variable data-types match up with the column data-type. Otherwise, you may (in certain circumstances) inadvertently prevent an index from being eligible for consideration.
