How to Rename and Dropping a Table in SQL

You can rename a table whenever required. The sp_rename stored procedure is used to remaname table. Sp_rename can be used to rename any database object, such as a table,view, stored procedure, or function. The syntax of the sp_rename stored procedure is:
Sp_rename old_name, new_name
Where,

• Oldname is the current name of the object.
• Newname is the new name of the object.

The following SQL query renames the EmployeeLeave table:
Sp_rename [HumanResources.EmployeeLeave] ,
[HumanResources.EmployeeVacation]

You can also rename a table by right-clicking the Table folder under the Database folder in the Object Employer window and selecting the rename option from the shortcut menu. After a table is created, you may need to see the details of the table. Details of the table include the column names and the constraints. For this purpose, you can use the sp_help command.

Dropping a Table

At times, when a table is not required, you need to delete a table. A table can be deleted along with all the associated database objects, such as its index, triggers, constraints, and permissions. You can delete a table by using the DROP TABLE statement. The syntax of
DROP TABLE [ database_name . [ schema_name ]. ] table_name
Where,

• Database_name specifies the name of the database where th 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 needs to be dropped.

When a table is deleted, any other database object referenced by the table needs to be deleted explicitly. This should be done before deleting the table. This is because while deleting a table, if violations occur in the rule of referential integrity then an error occurs that restricts you from deleting the table. Therefore, if your table is referenced then you must delete the referenced table or the referenced constraint and then delete the table.

For example in the HumanResources schema, the Employee table contains EmployeeID as its primary key. The EmployeeLeave table under the same schema contains EmployeeID as its foreign key and is referenced with the EmployeeID column of the Employee table. Therefore, when you want to delete the Employee table, you first need to delete the EmployeeLeave table.

You can also delete a table by right-clicking the Tables folder under the Database folder in the Object Explorer window and selecting the Delete option from the shortcut menu.

Create table using partition scheme

How to Create Partitioned Table in SQL Server

When the volume of data in a table increases and it takes time to query the data, you can partition the tables and store different parts of the tables in multiple physical locations based on a range of values for a specific column. This helps in managing the data and improving the query performance.

Consider the example of a manufacturing organization. The details of inventory movements are stored in the InventoryIssue table. The table contains a large volume of data and the queries take a lot of time to execute thereby slowing the report generation process.

To improve the query performance, you can partition the table to divide the data based on a condition and store different parts of the data in different locations. The condition can be based on the date of transaction and you can save the data pertaining to five years at a location. After partitioning the table, data can be retrieved directly from a particular partition by mentioning the partition number in the query.

In the preceding example, the partitioned table were created after the database had already been designed. You can also create partitioned tables while designing the database and creating tables. You can plan to create a partitioned table when you know that the data to be stored in the table will be large. For example, if you are creating a database for a banking application and you know that the transaction details will be voluminous, you can create the transaction table s with partitions.

Partitioning a table is only allowed in Enterprise Edition of SQL Server. To create a partitioned table, perform the following tasks:

• Create a partition function.
• Create a partition scheme.
• Create a table by using the partition scheme.

For example, the AdventureWorks database stores the data of all the employees for the last 11 years. This data includes the personal details of the employees and their payment rates. Whenever there is a change in the payment rate of an employee, it is recorded in a separate record. However, this result in generation of large volume of data. This adversely affects the query performance.

To improve the query performance, the database developer needs to partition the table storing the changes in wage rate.

Create Rule and User-defined DataType

How to Create Rule and User Defined Data Type in SQL

A rule enforces domain integrity for columns or user-defined data types. The rules is applied to the column of the user-defined data type before an INSERT or UPDATE statement is issued. In other words, a rule specifies a restriction on the values of a column or a user-defined data type. Rules are used to implement business-related restrictions or limitations. A rule can be created by using the CREATE RULE statement.

The syntax of the CREATE RULE statement is:
CREATE RULE rule_name AS conditional_expression
Where,

• Rule_name specifies the name of the new rule that must conform to rules for identifiers.
• Conditional_expression specifies the condition(s) that defines the rule. It can be any expression that is valid in a WHERE clause and can include elements, such as arithmetic operators, relational operators, IN, LIKE, and BETWEEN.

The variable specified in the conditional expression must be prefixed with the @ symbol. The expression refers to the value that is being specified with the INSERT or UPDATE statement.

In the preceding example of the EmployeeLeave table, you applied a rule to accept only three values: ‘CL’, ‘SL’, and ‘PL’. You can perform the same task by creating a rule. You can use the following statement to create the rule:
CREATE RULE rulType
AS @LeaveType IN (‘CL’, ‘SL’, ‘PL’)

After you create the rule, you need to activate the rule by using a stored procedure, sp_bindrule.

The syntax of sp_bindrule is:
Sp_bindrule <’rule’>, <’object_name’>, [<’futureonly_flag’>]
Where,

• Rule specifies the name of the rule that you want to bind.
• Object_name specifies the object on which you want to bind the rule.
• Futureonly_flag applies only when you want to bind the rule to a user-defined data type.

Using a user defined data type

User-defined data types are custom data types defined by the users with a custom name. User-defined data types allow modifying the composite data type used in the database. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values.

You can create user-defined data types by using the CREATE TYPE statement. The syntax of the CREATE TYPE statement is:
CREATE TYPE [schema_name. ] type_name {FROM base_type [ ( precision [, scale ] ) ] [ NULL | NOT NULL ] } [;]
Where,

• Schema_name specifies the name of the schema to which the alias data type or the user-defined data type belongs.
• Type_name specifies the name of the alias data type or the user-defined data type.
• Base_type specifies the SQL Server supplied data type on which the alias data type is based.
• Precision specifies the decimal or numeric point. Decimal and numeric are non-negative integers that indicate the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.
• Scale specifies the decimal or numeric scale.
• NULL | NOT NULL specifies whether the data type can hold a null value. If not specified, NULL is the default value.

The following SQL query creates a user-defined data type for descriptive columns:
CREATE TYPE DSCRP
FROM varchar (100) NOT NULL;

In the preceding example, a user-defined data type DSCRP is created to store the varchar data type and the size limit is specified as 100. Further, it also specifies NOT NULL. Therefore, you can use this data for the columns that hold description, address, and reason.

For example, you can use the DSCRP data type to store the data of the LeaveReason column of the EmployeeLeave table, as shown in the following statement:

CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES
HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate),
LeaveEndDate datetime NOT NULL,
LeaveReason DSCRP,
LeaveType char(2) CONSTRAINT chkLeave CHECK (LeaveType IN (‘CL’, ‘SL’, ‘PL’)) CONSTRAINT chkDefLeave DEFAULT ‘PL’)

Customizing and Concatenating Output in SQL Database: SQL Server

Sql Server Management Studio have some options to customizing the display like adding the literals in the sql query to change the column name in output. It also have an option to concatenate strings in records, selected by sql query in sql server. In this article we will do these tasks with some examples in sql.

Customizing the Display

Sometimes, you might be required to change the way the data is displayed on the output screen. For example, if the names of columns are not descriptive, you might need to change the column headings by creating user-defined headings.

Consider the following example that displays the Department ID and Department Names from the Department table of the Adventure Works database. The report should contain column headings different from those given in the table, as specified in the following format.

Department Number  Department Name

You can write the query in the following ways:

• SELECT ‘Department Number’ – DepartmentID, ‘Department Name’ FROM HumanResources.Department
• SELECT DepartmentID ‘Department Number’, Name ‘Department Name’ FROM HumanResources.Department
• SELECT DepartmentID AS ‘Department Number’, Name AS ‘Department Name’ FROM HumanResources.Department

Similarly, you might be required to make results more explanatory. In such case, you can add more text to the values displayed by the columns by using literals. Literals are string values enclosed in single quotes and added to the SELECT statement. The literal value is printed in a separate column as they are written in the SELECT list. Therefore, literals are used for display purpose.

The following SQL query retrieves the department-Id and their name from the Department table and change the column header as specified in the query.

SELECT DepartmentID 'Department Number', Name 'Department Name'
FROM Department

The SQL Server will display the output of the query, as shown in the following figure

Add caption

Concatenating the Text Values in the Output

As a database developer, you will be required to address requirements from various users, who might want to view results in different ways. You might be required to display the values of multiple columns in a single column and also to improve readability by adding a description with the column value. In this case, you can use the Concatenation operator. The concatenation operator is used to concatenate string expressions. They are represented by the + sign.

The following SQL query concatenates the data of the Name and GroupName columns of the Department table into a single column. Text values, such as “department comes under” and “group”, are concatenated to increase the readability of the output:

SELECT Name + ' department comes under ' + groupName + ' group' AS Department
FROM Department

When you execute the query, it will format the output according to above query. The SQL Server will display the output of the query, as shown in the following figure.

Retrieving specific Records
Retrieve Selected Rows and Calculate Column values