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.
