This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is a Table and Index Partitioning in SQL Server?
Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours.
Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.
The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.
Partitioned tables and indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to one server, you might want to implement a partitioned table instead.
Can you perform a table or index partitioning in SQL Server standard or web edition?
No: Partitioned tables and indexes are available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
In which situation, we require to implement table or index partitioning?
Deciding whether to implement partitioning depends primarily on how large your table is or how large it will become, how it is being used, and how well it is performing against user queries and maintenance operations.
Generally, a large table might be appropriate for partitioning if both of the following are true:
The table contains, or is expected to contain, lots of data that are used in different ways.
Queries or updates against the table are not performing as intended, or maintenance costs exceed predefined maintenance periods.
For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month.
Would you please describe the basic steps to implement partitioning on Table or Index?
Creating a partitioned table or index typically happens in four parts:
- Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
- Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
- Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
- Create or modify a table or index and specify the partition scheme as the storage location.
What is a Partition Function?
A partition function defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.
Do you know the syntax of PARTITION FUNCTION?
|
1 2 3 4 |
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] |
What is a Partition Scheme?
A partition scheme maps each partition specified by the partition function to a filegroup.
Do you know the syntax of PARTITION SCHEME?
|
1 2 3 4 |
CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) [ ; ] |
What are the limitations of Partitioning?
The scope of a partition function and scheme is limited to the database in which they have been created. Within the database, partition functions reside in a separate namespace from other functions.
If any rows within a partition function have partitioning columns with null values, these rows are allocated to the left-most partition.
However, if NULL is specified as a boundary value and RIGHT is indicated, the left-most partition remains empty and NULL values are placed in the second partition.
Which system table is used to find the information about the database partitions?
Using sys.partitions, It contains a row for each partition of all the tables and most types of indexes in the database.
Does it make sense to design a partitioned index separately?
- Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table.
- As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table.
- SQL Server does not align the index with the table if you specify a different partition scheme or a separate filegroup on which to put the index at creation time.
When you should implement Index partitioning?
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
- The base table has not been partitioned.
- The index key is unique and it does not contain the partitioning column of the table.
- You want the base table to participate in collocated joins with more tables using different join columns.
What do you know about the Functional Dependency?
Functional dependency (FD) is a set of constraints between two attributes in a relation. Functional dependency says that if two tuples have same values for attributes A1, A2,…, An, then those two tuples must have to have same values for attributes B1, B2, …, Bn.
Functional dependency is represented by an arrow sign (→) that is, X→Y, where X functionally determines Y. The left-hand side attributes determine the values of attributes on the right-hand side.
What is the difference between =,== and ===?
- ‘=’: is for assigning one value to the other variable.
- ‘==’: is for the comparison between string with number, number with number etc.
- ‘===’: is for the comparison between only number with number and string with string.
What do you mean by the derived tables?
They are basically select statements in the from clause referred to by an alias name. The alias name creates a table in the result set that can then be referred to by some other Select statement.
It also called as Inline views.
What is a deterministic and nondeterministic function in SQL Server?
- A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function.
- A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values.
What do you know about LEAD() of SQL Server 2012?
SQL Server 2012: Introduced LEAD function, the best alternative of Self Join to compare Next Row
What do you know about LAG() of SQL Server 2012?
SQL Server 2012: Introduced LAG function, the best alternative of Self Join to compare Previous Row
