April 12, 2015 at 12:26 am
hi,
I am making one big table which will have good no of cols,
There is one critaria that is, to seepd up keep the lengh of row less than 8 kb pages(now it has been raised).
Q1)please tel me row length which is stored in 8kB page,
is made up of full col length, or the data stored in it?
yours sincerley.
April 12, 2015 at 12:55 am
rajemessage 14195 (4/12/2015)
hi,I am making one big table which will have good no of cols,
There is one critaria that is, to seepd up keep the lengh of row less than 8 kb pages(now it has been raised).
Q1)please tel me row length which is stored in 8kB page,
is made up of full col length, or the data stored in it?
yours sincerley.
Quick questions:
1. Are all the columns used all the time
2. Are the columns NULL-able?
3. Is there a subset of columns which is used most of the time?
4. What are the data types of the columns
5. Are the data types variable length or fixed length?
😎
April 12, 2015 at 1:38 am
Quick questions:
1. Are all the columns used all the time ( no, few are not used at all and they are datetime, varchar,int)
2. Are the columns NULL-able? ( yes)
3. Is there a subset of columns which is used most of the time? (yes they are generally not null)
4. What are the data types of the columns (datetime, varcahr , int , bigint)
5. Are the data types variable length or fixed length? ( both)
yours sincelrey
April 12, 2015 at 2:43 am
rajemessage 14195 (4/12/2015)
Q1)please tel me row length which is stored in 8kB page,is made up of full col length, or the data stored in it?
The row size is roughly the (size of the fixed width columns) + (size of the variable width data), note that BLOBs may or may not reside on the row data pages depending on the size.
😎
Quick questions:
1. Are all the columns used all the time ( no, few are not used at all and they are datetime, varchar,int)
2. Are the columns NULL-able? ( yes)
3. Is there a subset of columns which is used most of the time? (yes they are generally not null)
4. What are the data types of the columns (datetime, varcahr , int , bigint)
5. Are the data types variable length or fixed length? ( both)
Thanks for the answers, reason for asking was that there are two patterns that might be beneficial, vertical partitioning and sparse columns, suggest you look into those.
April 12, 2015 at 11:45 am
Although not always (For example, Call Detail Records from telephone companies), I've found that most very wide tables are wide simply because they're not designed/normalized properly. If it were me, I'd carefully study that nuance to see if the data can be normalized.
The other thing to consider is groups of columns that are normally null for most rows especially if the datatypes for the columns are fixed length such as INT, DATETIME, etc. In those cases, consider building a "sister" table that would only contain a row related to the main table if there were actually something other than all NULLs in the row. Depending on the groups of columns, you might have multiple types of groups, which would justify multiple sister tables (or "real" tables due to real normalization).
Also consider a sister table for any blob datatypes even if the blobs are populated in every row. This will make reindexing of the much more important main table in an online fashion until you get to SQL Server 2014. Yes, REORGANIZE is always done in an online fashion but, sometimes, you've just gotta do a full REBUILD to make things right. Done correctly, you could also partition the "blob oriented" table and that would make your life a whole lot quicker when it comes to backups even if you have to use partitioned views to do it.
As for predicting row size, what's been said so far is almost spot on. You do sum up the datatype width of the fixed length columns and the max width of the variable length columns (not including the blobs) and then add an additional 2 bytes per variable length column (stores the length of the contained data). Bit columns take 1 byte for every 8 bit columns. For example, if you have 9 bit columns, 1 byte would store the first 8 bit columns and another byte would store the 9th (up to the 16th). Blobs are always a pain even if supposedly "in row" and I recommend avoiding them whenever possible and storing them in a sister table whenever needed.
Note that if you don't have a clustered index on the table, you'll need to allow for an imbedded row identifier. If it's not a UNIQUE clustered index, then any dupes according to the index key will also contain a uniquifier, all of which adds to the width of the rows.
As a bit of a side bar, if you're planning on getting as close to 8K row widths as possible, then make sure that your FILL FACTOR is set to 100 because it won't actually do you any good otherwise because there will be no room for such reserved empty space on the clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 9:59 am
I agree with what Jeff has said yet I feel that he has not given enough reason so that you have a complete understanding. This is because you are new to table design.
Fixed width columns (like DATE, INT, DATETIME) take up the same amount of space in a row without regard to content of the data. For example a DATETIME column takes up 8 bytes even if the data in that row is NULL.
Internally in a row there is a NULL map. This map contains 1 bit for each column in the row that can be NULL. So allowing a column to be NULLable means that it actually takes up more space than a fixed width column that is NOT NULL.
The belief that NULL data does not take up any space is not true for fixed width data in SQL Server. Now variable length data like Nvarchar(50) is different.
You will have to look deeper into the nature of your data to discover the best structure. Tell us about the nature of the data that you want to store. We may be able to give to some pointers.
ATBCharles Kincaid
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply