CODEWITHSUNDEEP
sql-server
user2624034
user2624034

Reputation: 77

Table partitioning vs multiple tables

Say we have a huge table that we run very complex queries on.

Say it's a crm and we have a table called People [id (GUID), listId (Guid), name, email, city, ......]

Would it be better to represent this as multiple tables per user so:

People_<USER_ID> and if we have 100 users we have 100 such tables

Or

One table like the above with a user_id column that we partition on?

Upvotes: 2

Views: 5578

Answers (1)

Ed B
Ed B

Reputation: 796

There are advantages and disadvantages to each approach.

For instance, if you use separate tables, you can perform maintenance such as rebuilding indexes or statistics on each individually, and if you add new users, it is simple to create a new table, rather than amending the partition function.

With a partitioned table, queries are vastly simplified, as you won't be relying on a query with a UNION for each individual table.

The half-way ground is to use a partitioned view; i.e. build a view that has a definition of the individual tables UNIONed together. This offers some of the advantages of partitioned tables, but the view definition will require maintenance if you add new tables, and with 100 tables, would be quite unwieldy.

Partitioned tables are generally of best use in a data warehouse kind of environment, where you can have a loading table, and add it in as a new partition for each load of data. In this environment, you generally only have data being written to one partition at a defined time, and read from all the partitions the rest of the time.

If your data size is very large, then partitioning could offer some benefits, in terms of the time taken to perform maintenance tasks, if they are only required on one or a few partitions. However, it is best to find a partition function that accommodates this. For example, if your rows are written once and not updated, adding a created datetime and partitioning on that, or using an incrementing primary key and partitioning on that, would mean that you would only need to reindex the active partition.

Upvotes: 3

Related Questions