Presents your  
SQL SERVER E-NEWSLETTER for January 23, 2004 
 
<-------------------------------------------> 
  
SHRINK YOUR DATABASE AND INCREASE PERFORMANCE   
  
In my experience, a lot of database designers don't think about column 
sizes enough; instead, they accept the defaults that Microsoft 
provides.

By my estimation, these defaults are usually incorrect, resulting in 
tables that are larger than they need to be and less-than-optimal
performance. 
I'll discuss several of the specific defaults that you may want to 
reconsider.   
  
* NVARCHAR TYPE: It's exactly twice the size of the varchar type. 
Unless

you really need to support multibyte text, you don't need nvarchars.   
  
* MONEY: The money data type requires eight bytes, while the smallmoney 
type requires only four bytes. The smallmoney type can store values 
from

-214,748.3648 to 214,748.3647. While theree are many applications that 
have values outside this range, this range is fine for most businesses. 
 
  
* DATES: The datetime type requires eight bytes, while the 
smalldatetime

type requires only four bytes. The smalldatetime type can store values 
from Jan. 1, 1900, to June 6, 2079, which is more than sufficient for
most 
business applications.   
  
* NUMERIC DATA TYPES: I've often seen long integers used when much 
smaller types could be used instead. The following table lists byte
sizes and 
ranges of the numeric types:   
  
Type          Size              Low              High   
Tinyint        1                 0                255   
SmallInt       2              -32768            32768   
Int            4             -2147483648   2147483647   
BigInt         8                2^63          2^63 -1   
  
You should also examine the values you store in your columns--you may 
be

using datatype overkill in more columns than you realize. If that's the 
case, you may be able to shrink your database significantly and gain a 
performance increase as a nice side benefit. If your app has any 
possibility of being ported to multibyte languages, you may as well 
plan
for it now.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and
.NET.   
  
----------------------------------------    

SQL FORUM: SHARE YOUR EXPERTISE WITH THE COMMUNITY   
  
The SQL Server Forum is the place where members can talk technique,
argue methods,
ask questions, and share experiences. Offer your views in the SQL 
Forum.
  
http://ct.com.com/click?q=0a-ZnNCIrryXye8AeWZgU2CSIJH6zOd 
  
Or send us an e-mail with "SQL: Shrink your database" in the subject
line.
mailto:Enews2@cnet.com  

----------------------------------------