sqlserver interview questions | partitioning

What is partitioning in SQL Server?
Partitioning is one of the new functionality in SQL Server 2005. Partitioning enables you to split the table across the multiple storage units called file groups based on user specification.
What are the advantages of partitioning?

• Partition allows you to place subset table or index on designated file group.
• Partition helps to us segregate the data within table based on age, gender etc
• This gives advantage to take a back up of subset of table based on specific category.

What are the different steps to partition table or index?

• Create a partition function.
• Create partition scheme mapped to a partition function.
• Create the table or index on the partition scheme.

What is Partition function?
Partition function is a database object that defines the boundary points to the partitioning the data.
Partition function will be first step to create partitioned table, index and view.
What is the syntax to create partition function?
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]
What is partition scheme?
Partition scheme defines file groups, which will be used with specific partition function.
Partition scheme will be second step to create partitioned table, index and view.
What are the requirements to create partition scheme?

• Partition function would required to create partition scheme
• Files groups mentioned in partition scheme must already be part of the database.
• File groups must not be marked as read only.

What are the steps to partition a non partitioned or existing table?

• Create a partition function.
• Create a partition scheme.
• Drop the existing clustered index.
• Recreate the clustered index on the partition scheme.