FAQ : Explain a scenario which supports vertical partitioning of a table in SQL Server.
The scenario may be different according to the SQL Server Version you have (SQL 7.0, SQL 2000, SQL 2005). I will be covering SQL Server 2000 and 2005
SQL Server 2000.
In SQL Server (in 2000 and 2005) the IN_ROW_DATA or the row size of a table can have only max 8060 bytes. If you have a table which contains 4 columns of varchar (3000) then though you can create the table but if the data being inserted is more than 8060 bytes then the insert will fail. So what we generally do is, we vertically partition the table to two or more table as per the requirement and keep ONE to ONE relation between all the tables. So this is a valid reason to partition your table vertically. When you do vertical partitioning, try to keep most commonly used small size column in single table.
SQL Server 2005
In SQL Server 2005, the above mentioned problem of SQL Server 2000 is not there because of the storage architecture change called ROW_OVERFLOW_DATA. Ie. In SQL Server 2005 you can have a row which exceed 8060 bytes provided the columns are variable types(Varchar,nvarchar). What Database engine internally does is, it keep all the variable datatypes (varchar, nvarchar )columns data in ROW_OVERFLOW_DATA page. Precisely, the Row size limitation is only applicable to fixed size columns like CHAR, NCHAR. So SQL Server 2000 scenario of partitioning table because of the row size exceeds 8060 bytes is not valid in SQL Server 2005.
But there is a valid reason to do vertical partition of the table in SQL Server 2005. If you are using ONLINE INDEX feature of SQL Server 2005 then you cannot use LOB data as a part of index at the leaf level. And you want to use the LOB column in Index because of the performance benefit it provides. In that case best method is to partition the table vertically in such a way that, keep the small ,mostly used columns in single table (like product detailed description may not be asked frequently by the user) and the columns those are referred in less frequency in another table. Since you do not have LOB data in the table you can use ONLINE INDEX feature in the table.
SQL Server 2000.
In SQL Server (in 2000 and 2005) the IN_ROW_DATA or the row size of a table can have only max 8060 bytes. If you have a table which contains 4 columns of varchar (3000) then though you can create the table but if the data being inserted is more than 8060 bytes then the insert will fail. So what we generally do is, we vertically partition the table to two or more table as per the requirement and keep ONE to ONE relation between all the tables. So this is a valid reason to partition your table vertically. When you do vertical partitioning, try to keep most commonly used small size column in single table.
SQL Server 2005
In SQL Server 2005, the above mentioned problem of SQL Server 2000 is not there because of the storage architecture change called ROW_OVERFLOW_DATA. Ie. In SQL Server 2005 you can have a row which exceed 8060 bytes provided the columns are variable types(Varchar,nvarchar). What Database engine internally does is, it keep all the variable datatypes (varchar, nvarchar )columns data in ROW_OVERFLOW_DATA page. Precisely, the Row size limitation is only applicable to fixed size columns like CHAR, NCHAR. So SQL Server 2000 scenario of partitioning table because of the row size exceeds 8060 bytes is not valid in SQL Server 2005.
But there is a valid reason to do vertical partition of the table in SQL Server 2005. If you are using ONLINE INDEX feature of SQL Server 2005 then you cannot use LOB data as a part of index at the leaf level. And you want to use the LOB column in Index because of the performance benefit it provides. In that case best method is to partition the table vertically in such a way that, keep the small ,mostly used columns in single table (like product detailed description may not be asked frequently by the user) and the columns those are referred in less frequency in another table. Since you do not have LOB data in the table you can use ONLINE INDEX feature in the table.
posted by Madhu K Nair at
6:34 AM
8 Comments:
Hi friend.... Nice blog....
Do you need Anna University Model Question papers and related informations? - Visit Anna University World
For interesting international/national debates and controversies - Visit Selerines World
For stuffs like Movies, Sports, Anna University Papers, Guinness records, Programming, Computer Stuffs etc,. - Visit my PR 3 site http://www.laguiaincompleta.com
Please refer Anna University blog to your friends, so that it will help them a lot...
If you feel my blog is informative then comment there and keep in touch....
Take care....
Deciding to buy online can result in a huge cost savings while providing you the best options for your hair care needs today.
s Therapy Treatment line, which is a lightweight replenishing treatment that protects
and maintains hair condition. A well groomed woman will look to her hair
and makeup first and a poll of 3,000 females across
the UK revealed that about 44% feel attractive if they are having a good hair day, this compares to 16% who felt pleased with their appearance if good
teeth gave them that winning smile.
Also visit my web site hair products
It is also a good idea to avoid any accidents, slips or falls.
Nowadays with flight simulators you can have lots of fun out of
these games and besides all this you do not have to spend thousands.
However, such training programs are tough to wave
through since it require tons of learning patience and willingness to proven one’s innate skills.
As with most of the electronics, the main enemy of golf simulator is the dust.
Yes, it may be true, but the training flights are different.
The product, according to the brand, can be applied throughout the
day on dry hair if one's hair is medium to very coarse. Before you go for them, try products at Keranique for hair care. It has been proved that products which contain vitamins will strengthen your hair.
my weblog hair products
Here’s what your property ads in newspapers must have in order
to get the best offer. There were also interesting garden "flowers", made from tin cut into petal shapes, bolted together
on an aluminum rod, with bottle caps as the center. This is where
the online websites play quite a vital role.
As of now, I can only speculate that there are more good
than bad to go on-line for the newspaper publishers in
Malaysia as apparently there has been no case
where a newspaper publisher has gone bust. Studies about Egyptians lend itself to activities such as mummy-making,hieroglyphics tablets or Egyptian jewelry.
my weblog :: egyptian newspapers
This kind of mulch s also more natural looking than
other mulch choices out there for the delicate look of flower gardens.
Owning a regular lawn mower does not mean you have
to give up on mulching
your yard; you just have to be willing to do
some work. Sure, you had your occasional exception that stupidly borrowed from the local loan shark, but
most learned to live on less.
Way cool! Some extremely valid points! I appreciate you writing
this write-up plus the rest of the website is very good.
Here is my homepage: www.hhh.jp.net
It's a shame you don't have a donate button! I'd definitely donate to this excellent blog! I suppose for now i'll settle for book-marking
and adding your RSS feed to my Google account.
I look forward to brand new updates and will talk about
this blog with my Facebook group. Chat soon!
Stop by my site; mens victorinox swiss army
Post a Comment
Subscribe to Post Comments [Atom]
<< Home