SQL Server Performance Forum – Threads Archive

Char fields and nulls?

In the Datatype Performance Tuning Tips page, it states the following:

quote:If you are using fixed length columns (CHAR, NCHAR) in your table, do your best to avoid storing NULLs in them. If you do, the entire amount of space dedicated to the column will be used up. For example, if you have a fixed length column of 255 characters, and if you place a NULL in it, then 255 characters have to be stored in the database. This is a large waste of space to store a NULL. This added unnecessary space causes SQL Server to have to perform extra disk I/O to read data pages, and it also wastes space in the data cache buffer. Both of these contribute to reduced SQL Server performance. One way around this problem, if you must use NULLs, is to use a variable length column instead. Variable length columns only use a very small amount of space to store a NULL. [7.0, 2000] Added 4-18-2002

However, in this forum post:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3573
Twan mentioned:

quote:remember also that a char(x) NULL column is actually a varchar(x) NULL column. A char column is incapable of holding a null, so behind the scenes SQL will actually use a varchar field.

This seems to be conflicting–is a char(x) null column taking up x, or nothing (plus the varchar overhead)? Thanks–
Jarrett

When in doubt, Books Online: If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated. Derrick Leggett

Thanks Derrick. So, the answer was both, I guess. Is there any way to determine what the ANSI_PADDING option was set to upon creation of the table? Also, since concatenating NULL with a string results in NULL, (NULL + ‘ ‘ = NULL), BOL’s description is a bit vague. Just to clarify things: 1. If ANSI_PADDING is ON during the creation of a char(10) field, then all values, including NULL, will take up 10 bytes. 2. If ANSI_PADDING is OFF during the creation of a char(10) field, then NULL values will take up no space, whereas other fields will take up 10 bytes but not display the trailing blanks in a query? On a side note, I’ve tried doing experiments where I have a char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> field in a table. I’m using sp_MStablespace to determine how much space is being used, and it tells me that for 17499 rows, the dataSpaceUsed is 3048. This number remains the same regardless if I fill the field with chars or if I make it null. However, if I change that one field to varchar, then I have a big variation in size. With the fields set to null, it takes up 2920. However, if I fill the field with 8 characters, it jumps to 5944. I didn’t expect the overhead of a varchar field to be this big! I don’t know what type of measurement this is referring to, but it seems pretty significant. Thanks for the help– Jarrett

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_PADDING to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting. SET ANSI_PADDING defaults to OFF for connections from DB-Library applications.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>