StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Hash partitioning is not available in SQL Server 2005/2008. You must use range partitioning.</p> <p>That being said, you should be aware that partitioning is primarily a storage option, see <a href="http://msdn.microsoft.com/en-us/library/ms190787.aspx" rel="nofollow noreferrer">Partitioned Table and Index Concepts</a>:</p> <blockquote> <p>Partitioning makes large tables or indexes more <strong>manageable</strong>, because partitioning enables you to <strong>manage</strong> and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as <strong>loading data</strong> from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. <strong>Maintenance</strong> operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.</p> </blockquote> <p>As you can see, the introduction of partitioning in MSDN focuses on maintenance, manageability and data load. In my experience partitioning gives, at best, 0 performance gain. Specially in SQL 2005. Usualy it gives performance degradation. To improve performance you should use a correct clustered index and properly designed non-clustered indexes.</p> <p>In SQL 2008 there are improvements in the parallel operators in regard to partitions if they are properly distributed from an IO point of view, see <a href="http://msdn.microsoft.com/en-us/library/ms177411.aspx" rel="nofollow noreferrer">Designing Partitions to Improve Query Performance</a>. Their benefit are marginal though and overshadowed by the benefits of a properly designed set of clustered and non-clustered indexes. Case in point a clustered index in (id, topic_id) where id is an identity is usefull solely for single item lookup by id. On the other hand a clustered index by (topic_id, id) would benefit any queries that look for specific topic(s). I don't know your system requirements and the queries you run, but 10M rows performance problems on such a narrow table smell like indexing ands querying issue, no partitioning issue.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload