When a database table grows in size to hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes simple operations to take much longer than they might otherwise. Data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. Database software, such as is provided with SQL Server, provides table partitioning to make such operations more efficient.
If your database has a very large table, you can "partition" this large table onto separate "filegroups." Filegroups are special types of files that allows a user to divide data onto different disks for the purpose of optimizing query performance and maintenance operations. For example, say you have a tall file cabinet where you keep lists of customer names. As the number of files grow, the longer it takes to locate any given file. Database tables operate on the same principle.
The first thing you will need to do when planning out a partitioned table is to decide how you will divide the table up. As with our file cabinet example, we might decide to create one file cabinet where last names begin with A – F, another cabinet for files G – N, and another for files O – Z. Likewise, our table might also be divided up using last names as a way to organize. You can see how faster it would be to locate the last name "Jones" in one of three cabinets instead of a giant cabinet.
As business changes and data grows, partitioning can become more complicated; however, the example above does provide a basic understanding as to how partitioning can save time and resources.
If your database has a very large table, you can "partition" this large table onto separate "filegroups." Filegroups are special types of files that allows a user to divide data onto different disks for the purpose of optimizing query performance and maintenance operations. For example, say you have a tall file cabinet where you keep lists of customer names. As the number of files grow, the longer it takes to locate any given file. Database tables operate on the same principle.
The first thing you will need to do when planning out a partitioned table is to decide how you will divide the table up. As with our file cabinet example, we might decide to create one file cabinet where last names begin with A – F, another cabinet for files G – N, and another for files O – Z. Likewise, our table might also be divided up using last names as a way to organize. You can see how faster it would be to locate the last name "Jones" in one of three cabinets instead of a giant cabinet.
As business changes and data grows, partitioning can become more complicated; however, the example above does provide a basic understanding as to how partitioning can save time and resources.
No comments:
Post a Comment