Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 6563 | Views in the last 30 days: 6
 
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
data types    
database design    
miscellaneous    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones