sql server - Table partitioning vs multiple tables -

say have huge table run complex queries on.

say it's crm , have table called people [id (guid), listid (guid), name, email, city, ......]

would better represent multiple tables per user so:

people_<user_id> , if have 100 users have 100 such tables

or

one table above user_id column partition on?

thanks

there advantages , disadvantages each approach.

for instance, if use separate tables, can perform maintenance such rebuilding indexes or statistics on each individually, , if add new users, simple create new table, rather amending partition function.

with partitioned table, queries vastly simplified, won't relying on query union each individual table.

the half-way ground use partitioned view; i.e. build view has definition of individual tables unioned together. offers of advantages of partitioned tables, view definition require maintenance if add new tables, , 100 tables, quite unwieldy.

partitioned tables of best use in data warehouse kind of environment, can have loading table, , add in new partition each load of data. in environment, have data being written 1 partition @ defined time, , read partitions rest of time.

if data size large, partitioning offer benefits, in terms of time taken perform maintenance tasks, if required on 1 or few partitions. however, best find partition function accommodates this. example, if rows written once , not updated, adding created datetime , partitioning on that, or using incrementing primary key , partitioning on that, mean need reindex active partition.