After you create a partition function and a partition scheme, you need to create a table that will store the partition records. You can use the following statements for the same:
Create Table EmpPayHistPart
(
EmployeeID int,
RateChangeDate datetime,
Rate money,
PayFrequency tinyint,
ModifiedDate datetime
) ON RateChangDate (RateChangeDate)
In the preceding statement, the RateChangDate refers to the partition scheme that is applied to the RateChangeDate column. The records entered in the EmpPayHistPart table will be stored based on the condition specified in the partition function.
The syntax of the ALTER TABLE statement is:
ALTER TABLE [database_name . [ schema_name ] .] table_name
(
ALTER COLUMN column_name
{
[NULL | NOT NULL ]
}
|WITH {CHECK | NOCHECK }] ADD COLUMN <column_difinition>
{
ADD CONSTRAINT constraint_name constraint_type
Where,
The following SQL query adds a column called ApprovedBy to the EmployeeLeave table:
ALTER TABLE HumanResources.EmployeeLeave
ADD ApprovedBy VARCHAR(30) NOT NULL
In the preceding example, the ApprovedBy column is added that can store string values.
When modifying a table, you can drop a constraint when it is not required. You can perform the task by altering the table by using the ALTER TABLE statement. The syntax to drop a constraint is:
ALTER TABLE [database_name . [ schema_name ] . [ schema_name . ] table_name DROP CONSTRAINT constraint_name
Where,
The following statement drops the default constraint, chkDefLeave of the EmployeeLeave table:
ALTER TABLE HumanResources.EmployeeLeave DROP CONSTRAINT chkDefLeave
In the preceding statement, the chkDefLeave constraint is dropped from the EmployeeLeave table.
Partition function in sql
Create Table EmpPayHistPart
(
EmployeeID int,
RateChangeDate datetime,
Rate money,
PayFrequency tinyint,
ModifiedDate datetime
) ON RateChangDate (RateChangeDate)
In the preceding statement, the RateChangDate refers to the partition scheme that is applied to the RateChangeDate column. The records entered in the EmpPayHistPart table will be stored based on the condition specified in the partition function.
Modifying a Table
You need to modify tables when there is a requirement to add a new column, alter the data type of a column, or add or remove constraints on the existing columns. For example, Adventure Works stores the leave details of all the employees in the EmployeeLeave table. According to the requirements, you need to add another column named ApprovedBy in the table to store the name of the supervisor who approved the leave of the employee. To implement this change, you can use the ALTER TABLE statement.The syntax of the ALTER TABLE statement is:
ALTER TABLE [database_name . [ schema_name ] .] table_name
(
ALTER COLUMN column_name
{
[NULL | NOT NULL ]
}
|WITH {CHECK | NOCHECK }] ADD COLUMN <column_difinition>
{
ADD CONSTRAINT constraint_name constraint_type
Where,
- Database_name specifies the name of the database in which the table is created. Schema_name specifies the name of the schema to which the table belongs.
- Table_name is the name of the table that is to be altered. If the table is not in the current database, then the user needs to specify the database name and the schema name explicitly.
- ALTER COLUMN specifies the name of the altered column.
- ADD COLUMN spevifies the name of the column to be added,
- Column_definition specifies the new column definition.
- WITH CHECK|WITH NOCHECK specifies whether the existing data is to be checked for a newly added constraint or a re-enabled constraint.
- Constraint_name specifies the name of the constraint to be created and must follow the rules for the identifier.
- Constraint_type specifies the type of constraint.
The following SQL query adds a column called ApprovedBy to the EmployeeLeave table:
ALTER TABLE HumanResources.EmployeeLeave
ADD ApprovedBy VARCHAR(30) NOT NULL
In the preceding example, the ApprovedBy column is added that can store string values.
When modifying a table, you can drop a constraint when it is not required. You can perform the task by altering the table by using the ALTER TABLE statement. The syntax to drop a constraint is:
ALTER TABLE [database_name . [ schema_name ] . [ schema_name . ] table_name DROP CONSTRAINT constraint_name
Where,
- Database_name specifies the name of the database in which the table is created.
- Schema_name specifies the name of the schema to which the table belongs.
- Table_name specifies the name of the table that contains the constraint to be dropped.
- Constraint_name specifies the name of the constraint to be dropped.
The following statement drops the default constraint, chkDefLeave of the EmployeeLeave table:
ALTER TABLE HumanResources.EmployeeLeave DROP CONSTRAINT chkDefLeave
In the preceding statement, the chkDefLeave constraint is dropped from the EmployeeLeave table.
Partition function in sql