How to use Constructs in Batches for Conditional Execution: SQL

SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displayed.

The SQL Server allows you to use the following constructs to control the flow of statements:

• IF…..ELSE statement
• CASE statement
• WHILE statement

Using the IF….ELSE Statement

You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:

IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,

• Boolean_expression specifies the condition that evaluates to either TRUE or FALSE. Sql_statement specifies a T-SQL statement.
• Statement_block is a collection of T-SQL statements.

The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.

DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO

In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.

Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.

IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO

Implementing Batches in SQL Server and Guidelines

As a database developer, you might need to execute more than one SQL statement to perform a task. For example, when a new employee joins AdventureWorks, Inc., you need to insert the employee details in the database. The details of the employees are stored in more than one table. Therefore, you need to execute an insert statement into store the details in each table. In such a case, you can send all the SQL statements together to the SQL Server to be executed as a unit. This helps in reducing the network traffic.

Creating Batches

A batch is a group of SQL statements submitted together to the SQL Server for execution. While executing batches, the SQL Server compiles the statements of a batch into a single executable unit called an execution plan. This helps in saving execution time.

Consider an example. You have to execute 10 statements and you are executing them one by one by sending 10 requests. This process takes time if your queries are in queue. All statements together in a batch, then the execution process becomes faster as all the statements are sent to the server together.

To create a batch, you can write multiple SQL statements followed by the keyword GO at the end, as shown in the following listing:
<T-SQL Statement1>
<T-SQL Statement2>
<T-SQL Statement3>
…. . .
GO

GO is a command that specifies the end of the batch and sends the SQL statements to an instance of the SQL Server.

Consider an example. If you want to store the details of new employees in the Adventure Works database, you can create the following batch:

INSERT INTO [AdventureWorks]. [Person]. [Contact]
VALUES (0, null, ‘Robert’, ‘J’ ‘Langdon’, NULL
,’rbl@adventure-works.com’, 0, ‘1 (11) 500 555-0172’
,’9E685955-ACD0-4218-AD7F-60DDF224C452’, ‘2a31OEw=’ , NULL
, newid( ), GETDATE ( ) )
INSERT INTO [AdventureWorks]. [HumanResources] . [Employee]
VALUES (‘AS01AS25R2E365W’, 19978, ‘robert1’, 16, ‘Tool Designer’,
‘1972-05-15’, ‘S’, ‘M’, ‘1996-07-31’, 0, 16, 20, 1, newid ( ) ,

When a batch is submitted to the SQL Server, it is compiled to create an execution plan. If any compilation error occurs, such as a syntax error, the execution plan is not created. Therefore, none of the statements in the batch is executed. However, after the execution plan is created, if a run-time error occurs, the execution of the batch stops. In such a case, the statements executed before the statement that encountered the run-time error are not affected.

Using Variables

While creating batches, you might need to save some values temporarily during the execution time. For example, you might need to store some intermediate values of calculations. To store the intermediate values, you can declare variables and assign values to them. You can declare a variable by using the DECLARE statement. The syntax of the DECLARE statement is:

DECLARE @variable_name data_type

Variables that are declare in a batch and can be used in any statement inside the batch are called local variables.
The following code declares a variable, @Rate, and assigns the maximum value of the Rate column from the EmployeePayHistory table to the variable:

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
GO
In the preceding example, the @Rate variable is declardd and used to store the maximum value of the Rate column. The max aggregate function is used to retrieve the maximum pay rate from the EmployeePayHistory table. The GO keyword is used to send all the statements together to the SQL Server.

Displaying User-Defined Messages

At times, you need to display user-defined messages or values of variables when the batch is executed. For this, you can use the PRINT statement, as shown in the following batch.
The following code displays the value of the rate variable by using the PRINT statement.

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
PRINT @Rate
GO
You can also use comment entries in batches to write a description of the code. This will help understand the purpose of the code. A comment entry can be written in two ways:

• Multiple line comment entries enclosed within /* and */
• Single line comment entry starting with – (double hyphens).

Guidelines to Create Batches

While crating batches, you need to consider the following guidelines:

• You cannot combine statements, such as CREATE DEFAULT, CRATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW with other statements while creating a batch. Any statement that follows the create statement is interpreted as part of the definition.
• You can use the EXECUTE statement in a batch when it is not the first statement of the batch, otherwise the EXECUTE statement works implicitly.

In addition, you need to consider the following restrictions:

• You cannot bind rules and defaults to columns and use them in the same batch.
• You cannot define and use the CHECK constraint in the same batch.
• You cannot drop objects and recreate them in the same batch.
• You cannot alter a table by adding a column and then refer to the new columns in the batch created earlier.

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

• UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
• Index_name specifies the name of the index.
• Table_name specifies the name of the table that contains the attributes on which the index is to be created.
• Column specifies the column or columns on which the index will be created.
• Column_name specifies the name of the column or columns on which the index would be created.
• ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
• ON filegroup_name specifies the filegroup on which index is created.
• ON DEFAULT specifies that the specified index will be created on the default filegroup.
• PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
• FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
• SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
• IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
• STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
• DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
• ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Non-clustered index in sql
Guidelines to create indexes

How to use Correlated Subqueries in SQL Programming

In SQL programming, a correlated subquery can be defined as a query that depends on the outer query for its evaluation. In a correlated subquery, the WHERE clause references a table in the FROM clause. This means that the inner query is evaluated for each row of the table specified in the outer query.

For example, the following query displays the employee ID, designation, and number of hours spent on vacation for all the employees whose vacation hours are greater than the average vacation hours identified for their title:

SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanREsources.Employee el WHERE el.VacationHours>
(SELECT AVG(e2.VacationHours)
FROM HumanResources.Employee e2 WHERE el.JobTitle = e2.JobTitle)

In the preceding example, the inner query returns the Titles of the employees, from the Employee table, whose vacation hours are equal to the average vacation hours of all employees. The outer query retrieves the Employee ID, Title, and VacationHours of all the employees whose vacation hours is greater than the average vacation hours retrieved by the inner query. The output of the correlated subquery is shown in the following figure.

Nested Query in SQL
Aggregate Functions in SQL

How to use Nested Subqueries in SQL Programming

A subquery can contain one or more subqueries. Subqueries are used when the condition of a query is dependent on the result of another query, which in turn is dependent on the result of another subquery.

Consider an example. You need to view the DepartmentID of an employee whose e-mail address is Sales Agent. To perform this task, you can use the following query:

SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory
WHERE BusinessEntityID = /* Level 1 inner query */
(SELECT BusinessEntityID FROM Person.BusinessEntityContact
WHERE ContactTypeID = /* Level 2 inner query */
(SELECT ContactTypeID FROM Person.ContactType WHERE Name= 'Sales Agent')
)

In the preceding example, two queries are nested within another query. The level 2 inner query returns the Contact ID of an employee based on the e-mail address of the employee from the Person table.

The level 1 inner query uses this ContactID to search for the BusinessEntityID of the employee with the given e-mail address. The main query uses the BusinessEntityID returned by level 1 inner query to search for the DepartmentID from the EmployeeDepartmentHistory table.

You can implement subqueries upto 32 levels. However, the number of levels that can be used depends on the memory available on the database server.

How to use Aggregate Functions in SubQuery with Database

While using subqueries, you can also use aggregate functions in the subqueries to generate aggregated values from the inner query. For example, in a manufacturing organization, the management wants to view the sale records of all the items whose sale records are higher than the average sale record of a particular product.

Therefore, the user first needs to obtain the average of a particular product and then find all the records whose sale record exceeds the average value. For this, you can use aggregate functions inside the subquery.

The following example displays the BusinessEntityID of those employees whose vacation hours are greater ta e average vacation hours of employees with title as ‘Marketing Assistant’:

SELECT BusinessEntityID FROM HumanResources.Employee
WHERE VacationHours > (SELECT AVG(VacationHours) FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Assistant')

The output of the subquery that uses the aggregate function is shown in the following figure.

In the preceding example, the inner query returns the average vacation hours of all the employees who are titled as Marketing Assistant. The outer query uses the comparison operator ‘>’ to retrieve the employee ID of all those employees who have vacation hours more than the average vacation hours assigned for a Marketing Assistant.