Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardps
Starting Member
33 Posts |
 Posted - 2007-08-13 : 04:30:11
|
| Hi,Does anyone know how much storage space is required in SQL Server for a char(1) compared to a varchar(1)?If I have a table with 14 varchar(1) fields and 60652902 rows, how much is this overhead costing me in wasted space?Answers on a postcard!Thanks,Richard. |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2007-08-13 : 09:41:44
|
| I use varchar when I want to store variable length info char is only used for things that have a fixed length.Try changing the datatypes to char(1) and run the queries and see if there is any performance diff. You can use select count(*) from table. But I doubt there will be much since its only varchar(1)The char is a fixed-length character data type, the varchar is a variable-length character data type.Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.You can use char when the data entries in a column are expected to be the same size.You can use varchar when the data entries in a column are expected to vary considerably in size.Ashley Rhodes |
 |
|
|
richardps
Starting Member
33 Posts |
Posted - 2007-08-13 : 09:56:00
|
| Thanks. You are absolutely right, hence my question.What I'm trying to understand is the overhead. In storage to set a field to varchar there are additional bytes required to set the length of that field it's just I don't know how many additional bytes. I'm sure it's about 3 or 4. Not sure if 'NULLABLE' also adds a byte.The 'variable' element adds overhead to SQL Server's storage.Cheers,Richard. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-13 : 11:35:43
|
| Fixed length take up the same amount of space whether null or "full". There is a single byte bitmap per 8 columns in the table (rounding up) irrespective whether they are fixed or variable in length. If there are 1+ variable length columns then there is an additional two bytes used to indicate the number of var len cols. Each var len col then also has two additional bytes overhead indicating where in the row the column ends. |
|
|
|
richardps
Starting Member
33 Posts |
Posted - 2007-08-13 : 12:14:30
|
| Thank you! |
|
|
|
|
|
|
How much does a VARCHAR cost?