CODEWITHSUNDEEP
sql-serverpartitioning
Gayan Dinuzhka
Gayan Dinuzhka

Reputation: 331

Sql Server - Working with large table

I have a table with about one million records inside. Currently it has grown upto 2GB. I need to partition this table or any other method to make this table less size.

What I meant by partitioning is breaking the table into TableName_1, TableName_2 when size is growing & combining them at the data retrieval

Is there any way to perform this?

Upvotes: 0

Views: 83

Answers (3)

dattatraynale
dattatraynale

Reputation: 76

I have implemented one such solution and blogged about it long ago. That has a very detailed explanation with diagrams. Hope that would help you.

http://dattatraynale.blogspot.co.uk/2012/11/sql-server-partitioning-design-4.html

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 77045

Your assumptions that you have a large table and you need to partition it is incorrect.

You need to optimize your queries and to index your fields. Also, if you have frequently used data then it might be a good idea to create a table for cached values.

Upvotes: 0

SchmitzIT
SchmitzIT

Reputation: 9582

First off, 1 million rows is really not that much, nor is 2 GB worth of a table.

To answer your question, though, yes, table partitioning is fully supported in SQL Server.

You could for instance, partition between historic and "recent" or "active" data based on a date field. Alternatively, you could partition using geographical segmentation and thus spread the data based on a country field.(Both examples of horizontal partitioning).

Alternatively, you can do a vertical partition where you cut the table into different amounts of columns (which could be useful in case your table contains a number of columns that are rarely used).

More info: MSDN - Table Partitioning (explains horizontal and vertical partitioning)

Upvotes: 2

Related Questions