Space taken by an empty nvarchar data field
-
I have a table where one field of nvarchar (1000) exists. As an average only 5% of the records have some data in this field. I want to know how much space a nvarchar fields takes when it has no data.
If it takes no space then it is the best situation. But what I want to know is whether it does take a minimum space such as 10 bytes etc for some sort of identification purpose. If a considerable amount of space is being wasted as 95% of fields are emply then I will think of restructing my database.
-
I'd have to check, but I believe that there is a 1 or 2 byte offset that is stored in each row. There might be a 1 byte placeholder at the offset location as well. I'm not sure it's a significant amount, even at 1M rows.
-
I believe it to be 2 bytes in the NULL bitmap. So considering the storage situation today no big deal for the SQL Server storage engine anyway.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I could be wrong but, I think it is 2 bytes for any varchar or nvarchar field per row and one bit per page for any column that allows nulls. So, if you are allowing nulls in this column with 10 rows per page, you are using 20bytes plus 1bit per page even if there are no values in this column.
If you are looking what is actually stored in the column even if there are no values in this filed, then I do not have an answer. I guess you could try the "DBCC Page" and you might be able to figure out.
Post the answer here if you find anything, it is an interesting question you have.
-
Here's an interesting quote from "Inside SQL Server 2000"
Unlike with fixed-length fields, if a variable-length field has a NULL value, it takes no room in the data row. SQL Server distinguishes between a varchar containing NULL and an empty string by determining whether the bit for the field is 0 or 1 in the NULL bitmap.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The answer is it depends on the column order with relation to other variable length columns.
When the data is stored there are several bytes resevered for various purposes including the null bitmap itself then the data for all fixed width columns are stored next in column order of each. After that come the variable length columns offsets (2 bytes each) and end of offsets marker and then the column data. A nulled column is still part of the null bitmap field because a ' is not null when stored but takes the same space depending on if the columns is the last variable length column or not and if the others after it are empty or not.
So say you have a table with 3 variable length columns of which the first is 5% not null and the others are never null. The 2 bytes for the first column are still recorded to ensure the offsets for the next two are right and accounted for. However if you reverse that and the first 2 are 100% full and the last is not then the offset for the 95% of the time null are not recorded at all. My rulle is make sure you place the variable length columns in order of nullability, if a column is markeed not null then place it in the design first and work from there. For fixed length it doesn't matter and as far as design goes there is no naming convention logic that says columns have to be in a specific order, if someone has issues figuring it out then that is a perception issue they have and not a logic issue.
-
I thank every one who tried to answer the question, especially Antares686 detailed answer is very knowledgable and clears the concept of space taken by null varchar.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply