Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Character Datatype Decisions

By Brian Knight,

The type of datatypes that you use in your schema could impact the performance and the accuracy of your database. For obvious reasons the decision to use a float versus a int field is a big one. This article will compare the different types of datatypes that SQL 7.0 offers and the pros and cons of all of them.

The primary decision is between using varchar and char datatype. A varchar(10) or char(10) field named FName that stores a users first name will store the value of "Steve" in two completely different ways. Using a char(10), SQL Server uses 10 bytes to store the value "Steve". If you used a varchar(10) field, trailing spaces are truncated. So a varchar(10) field will store "Steve" using only 5 bytes. There is a performance hit by using varchar fields. The benefit from using a varchar field in storage space usually outweighs the performance hit at about 8 bytes. In other words, any character field less than 8, use a char field and anything greater, use a varchar.

The datatype nvarchar is used in storing multiple languages for one field. If you do not plan on doing this, then it is recommended that you do not use unicode datatypes. This is because a unicode datatype is uses twice the amount of space than a character datatype and performs considerably slower. A nvarchar(10) storing the value "Gold" will be stored using 8 bytes. Another trick with nvarchar fields is that a nvarchar(8000) can only store 4000 characters due to each character is stored into 2 bytes.

In SQL Server 6.5, it is not recommended that you use text datatypes becuase of data corruption. This problem is minimized in SQL Server 7.0 and even more so in 2000. You can store up to 2 gigabytes of data into a text or image field. In SQL 7.0 with the increase of page size, you can now store up to 8000 bytes into a char or varchar field. This makes if viable to store large chunks of data into a varchar or char field. Times when you would store a description in a text field should now be stored into a varchar field. Although text field storage is much improved in 7.0, varchar is still more optimized.

 
Total article views: 6572 | Views in the last 30 days: 3
 
Related Articles
FORUM

"/" allowed in VARCHAR Datatype?????

"/" allowed in VARCHAR Datatype?????

FORUM

index on Varchar datatype field

index on Varchar datatype field or integer data type field which is better

FORUM

varchar datatype not writing all data to table

I have a table that contains a field using varchar 4000 as its datatype. My .NET web application cr...

ARTICLE

Numeric Datatype Decisions

Last week we discussed character datatypes and their performance effects on your database. This week...

Tags
database design    
datatypes    
miscellaneous    
strategies    
 
Contribute