The size of each record of table is a performance parameter. that means if size of record was small SQL Server fetch more records in each read from physical hard.
表的每个记录的大小是一个性能参数。这意味着如果记录的大小是小的SQL Server，那么每次从物理硬读中获取更多的记录。
In most of our queries we not use all column of table, and may be some column use only in specific query. Is it possible for we partitioning columns of each table to have better performance.
在大多数查询中，我们不使用表的所有列，可能只在特定查询中使用某些列。是否可以对每个表的列进行分区，以获得更好的性能。
I use SQL Server 2008 R2.
我使用SQL Server 2008 R2。
Thank you.
谢谢你！
3 个解决方案
#1
3
True column level partitioning comes with column oriented storage, see Inside the SQL Server 2012 Columnstore Indexes, but that is available only in SQL Server 2012 and addresses specific BI workloads, not general SQL Server apps.
真正的列级分区具有面向列的存储，请参阅SQL Server 2012 Columnstore索引，但这只能在SQL Server 2012中使用，并处理特定的BI工作负载，而不是一般的SQL Server应用程序。
In row oriented storage the vertical partitioning is actually another name for designing proper covering indexes. If the engine has an alternative narrow index it will use it instead of the base table, when possible.
在面向行存储中，垂直分区实际上是设计适当的覆盖索引的另一个名称。如果引擎有一个可选的窄索引，它将在可能的情况下使用它而不是基表。
The last alternative, manually splinting the table and joining the vertical 'shards' in queries (or defining joining views, same thing) is usually ill advised and seldom pays off.
最后一种选择是，手动将表夹板并连接到查询中垂直的“碎片”(或定义连接视图，同样的事情)通常是不明智的，很少有回报。
#2
1
At the moment with SQL Server 2008, you cannot partition tables horizontally. If you have a large number of columns, you would need to chop it into horizontal chunk tables that share a common key and then skin them with an update-able view to give the illusion of one very wide table.
在使用SQL Server 2008时，不能水平地分区表。如果有大量列，则需要将其分割为水平块表，这些表共享一个公共键，然后使用可更新的视图对它们进行皮肤处理，以产生一个非常宽的表的错觉。
If there are just a few large columns (e.g. VARCHAR(1000)), you can normalize your data into unique value tables.
如果只有几个大的列(例如VARCHAR(1000))，您可以将数据规范化为唯一的值表。
#3
0
The one exception to the no column partitioning rule are character columns declared as max (varchar(max), for example).
无列分区规则的一个例外是将字符列声明为max(例如，varchar(max))。
These are stored on a separate data page. I believe this page is not read in unless the column is referenced in the query. If I am wrong, I'am sure more knowledge people will correct me.
它们存储在一个单独的数据页面上。我相信这个页面不会被读取，除非这个列在查询中被引用。如果我错了，我相信更多的人会纠正我。
#1
3
True column level partitioning comes with column oriented storage, see Inside the SQL Server 2012 Columnstore Indexes, but that is available only in SQL Server 2012 and addresses specific BI workloads, not general SQL Server apps.
真正的列级分区具有面向列的存储，请参阅SQL Server 2012 Columnstore索引，但这只能在SQL Server 2012中使用，并处理特定的BI工作负载，而不是一般的SQL Server应用程序。
In row oriented storage the vertical partitioning is actually another name for designing proper covering indexes. If the engine has an alternative narrow index it will use it instead of the base table, when possible.
在面向行存储中，垂直分区实际上是设计适当的覆盖索引的另一个名称。如果引擎有一个可选的窄索引，它将在可能的情况下使用它而不是基表。
The last alternative, manually splinting the table and joining the vertical 'shards' in queries (or defining joining views, same thing) is usually ill advised and seldom pays off.
最后一种选择是，手动将表夹板并连接到查询中垂直的“碎片”(或定义连接视图，同样的事情)通常是不明智的，很少有回报。
#2
1
At the moment with SQL Server 2008, you cannot partition tables horizontally. If you have a large number of columns, you would need to chop it into horizontal chunk tables that share a common key and then skin them with an update-able view to give the illusion of one very wide table.
在使用SQL Server 2008时，不能水平地分区表。如果有大量列，则需要将其分割为水平块表，这些表共享一个公共键，然后使用可更新的视图对它们进行皮肤处理，以产生一个非常宽的表的错觉。
If there are just a few large columns (e.g. VARCHAR(1000)), you can normalize your data into unique value tables.
如果只有几个大的列(例如VARCHAR(1000))，您可以将数据规范化为唯一的值表。
#3
0
The one exception to the no column partitioning rule are character columns declared as max (varchar(max), for example).
无列分区规则的一个例外是将字符列声明为max(例如，varchar(max))。
These are stored on a separate data page. I believe this page is not read in unless the column is referenced in the query. If I am wrong, I'am sure more knowledge people will correct me.
它们存储在一个单独的数据页面上。我相信这个页面不会被读取，除非这个列在查询中被引用。如果我错了，我相信更多的人会纠正我。