Char, varchar and Not Nulls

Last Post 17 May 2006 06:27 PM by cmdr_jpskywalker. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
eramgarden_SQL
New Member
New Member

--
17 May 2006 11:39 AM

Second week at my new job and looking at the DB

1.They have made every field either char or numeric or created user-defined database type.
Read about the diff between char and varchar. This
SQl Mag Link talks about char being faster and it's used for "fixed length" if it's less than 30 bytes. Varchar used for "variable length".

2. They have made ALL field NOT NULL with default value of BLANK.

Questions

1. Still not sure what variable length and fixed length mean. How would I know if the data is less than 30 bytes?

2. Is there a downside to making everything NOT Null and having default of blank?
cmdr_jpskywalker
New Member
New Member

--
17 May 2006 06:27 PM
1. to check the actual data, use LEN function. To check the column max val, select the character_maximum_length in the INFORMATION_SCHEMA.COLUMNS

2. having no null means you don't have to deal with the tri-state of the column (NULL/UNKNOWN can also mean user has not initialize the table, consider the CHECKBOX control). NOT NULL also means additional table constraints. If you can maintain NOT NULL without violating business rules(ie. let the user know that the object has not been initialized yet, that is, gray in a checkbox control), its a good practice.
eramgarden_SQL
New Member
New Member

--
18 May 2006 03:24 AM

I think if all Char fields are initialized to Blank, that's not good for performance because it's fixed-length, and there's gonna be blank databytes flying around everywhere. Brings down performance.
yes, no?
cmdr_jpskywalker
New Member
New Member

--
18 May 2006 04:33 PM
i don't think it will be a problem in terms of performance but rather on performance. In any RDBMS, the principle of improving performance is to minimize disk read because disk read is costly. That is, if SQL read one whole chunk of 1 extent into memory instead of just 1 page, then SQL don't need to access the disk to read the other 7 pages because its already in the memory, the 1 extent (although the pages itself may contain a lot of blank data). But by design principle, you always try to minimize NULL columns because the attribute is not fully dependent to the primary key. In addition, programming wise, you don't have to worry about the other state of the column (UNKNOWN which is NULL) during comparison (TRUE AND NULL IS NULL, that is, depending on your ANSI NULL settings ). Anyway, having ALL fields might be overkill but then again, you have to see the business requirements, rules, and application assumptions made to the database (as the saying goes, don't break the wall not unless you know why it was put there in the first place).
You are not authorized to post a reply.

Acceptable Use Policy