Creating a Database in SQL Mgt Studio 2012

Creating a Database is to occupy some space in physical drive for the records saving through the application. These saved records can easily be listed or modified as per user’s requirement. To perform operations like add/edit/delete on these records a person used to create some application having capability of doing so. In earlier article, we have installed SQL Server Management Studio 2012 following some easy steps. This time we will establish a connection with server and create a database EmpDb having only a single table Employee. Start Management studio through all programs or whatever shortcut you have on your system and follow these steps.

1. First screen will prompt you to establish a connection with server requiring some values as      
2.        
3.  below: Just write (LocalDb)\v11.0 because i have only local db installed in my system, you can change as per your server installation.
4. On the left side there are some options "Database, Security, Replication, Management" are pre-added. Right click on Database and select New Database, a window will show as below prompting database name:                                                                                                                
   
5. Enter database name "EmpDb" and click on Ok button, it will create a new database with none of tables, views. Now look out the server connection there is a new entry of your database as shown:                                                                                                                                              
   
6. Right click on tables and select New Table, it will show you editor having three columns name, data-types and nullable. Write some columns with their data-types, don’t forgot to make Id a primary key and Identity set to true. Identity property will auto-increment this columns according to data entry. Save this table and enter name “Employee”. You can see the columns and their data-types in the below image:                                                                                          
   

Your database have easily been created with a single table and some columns. In the next article we create an edmx file for this database to be used for MVC application and will perform operation on this database.

Installing SQL Server Mgt Studio 2012

Installing SQL Server is as simple as installing a software following a list of steps. SQL Server 2012 was released in April 2012 and because of its list of features and easiness to use made favorite among professionals and beginners. We will learn about to creating database in SQL Server Management studio after installing it in the system.

Microsoft SQL Server is a powerful and free data management tool, designed for easy development and utilities including:

• LocalDB (MSI installer) LocalDB is a lightweight version of Express that has all its programmability features. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases.
• Express (Containing only the database engine) Use this if you need to accept remote connections or administer remotely.
• Express with Tools (with LocalDB) Choose either LocalDB or Express depending on your needs above.
• SQL Server Management Studio Express (Tools only) This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, etc. Use this if you already have the database and only need the management tools.
• Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search) This is a larger download than "with Tools" as it also includes both Full Text Search and Reporting Services.

Here are the steps to installing SQL Server Management studio 2012:

1. Download the executable file from the Microsoft official site. Double click on the .exe file and "SQL Server installation center" window will open.                                                    
   
2. Select first option "New SQL server stand-alone installation" and it will check all the support rules with a progress bar.                                                                                                  
   
3. After completing rule check it will opt for license terms that must accept by you for installation. User may also select the option to send feature data to Microsoft or just ignore this checkbox.                                                                                                                         
   
4. It will then install setup files used for preparing the installation into your system showing progress to user. On this step if your system is connected to internet connection then any update available will also be installed with this installation.             
   
5. Next step will opt for feature selection to be installed as shown in the image. Some features must be installed and those features are only readable as LocalDB and Connectivity tools                                                                                                                              
   
6. User can help Microsoft to SQL server features and services with selecting a checkbox of Error reporting. It is optional.                                                                                                    
   
7. Important step in which the installation process continues with copying all the required files on the system. This step takes more time because of copying required files.                                                                                                                                                       
   
8. The last step of installation to confirm about installation have successfully completed or having some errors. Successfully completed window will show with list of all the features installed with this installation.                                                                                        
   

Completing these steps you can easily access SQL Server management studio to create database. We will create database with some tables in next article.

Data Definition Language (DDL) Triggers in SQL

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modifications of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger.

Depending on the way in which triggers are fired, they are categorized as:

After Triggers

The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The main difference in the functionality of an after trigger is that it is fired after the execution of the DML operation for which it has been defined. The after trigger is executed when all the constraints and triggers defined on the table are successfully executed.

By default, if more than one after trigger is created on a table is for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created.
For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated.

Instead of Triggers

The instead of triggers can be primarily used to perform an action, such as a DML operation on another table or view. This type of trigger can be created on both a table as well as a view.

An instead of trigger can be used for the following actions:

• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action when an error condition is encountered.

For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable.

You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master record cannot be deleted” if a delete statement is executed on the Employee table of the AdventureWorks database.

Unlike after triggers, you cannot, create more than one Instead of trigger for a DML operation on the same table or view.

Nested Triggers

Nested triggers are fired due to actions of other triggers. For example, you delete a row from TableA. A trigger on TableA deletes rows from TableB. Because you are deleting rows from TableB, a trigger is executed on TableB to record the deleted rows.

Recursive Triggers

Recursive triggers are a special case of nested triggers. Unlike nested triggers, support for recursive triggers is at the database level. As the name implies, a recursive trigger eventually calls itself. There are two types of recursive triggers, Direct and Indirect.

Direct Recursive Trigger

A direct trigger is a trigger that performs the same operation (insert, update, or delete) on the same table causing the trigger to fire itself again.

Indirect Recursive Trigger

An indirect trigger is a trigger that fires a trigger on another table and eventually the nested trigger ends up firing the first trigger again. For instance, an UPDATE on TableA fires a trigger that in turn fires an update on TableB. The update on TableB fires another trigger that performs an update on TableC. TableC has a trigger that causes an update on TableA again. The update trigger of TableA is fired again.

Execute Batches multiple times using Stored Procedures in SQL

Batches are temporary in nature. To execute a batch more than once, you need to recreate SQL statements and submit them to the server. This leads to an increase in the overhead, as the server needs to compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. A stored procedure is a precompiled object stored in the database.

Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. If you need to assign values to the variables declared in the procedures at the run time, you can pass parameters while executing them. You can also execute a procedure from another procedure. This helps in using the functionality of the called procedure within the calling procedure.

Creating Stored Procedures

You can create a stored procedure by using the CREATE PROCEDURE statement. The syntax of the CREATE PROCEDURE statement is:

CREATE PROCEDURE proc_name
AS
BEGIN
Sql_statement1
Sql_statement2
END

Where Proc_name specifies the name of the stored procedure.

The following example create a stored procedure to view the department names from the Department table:

CREATE PROCEDURE prcDept
AS
BEGIN
SELECT Name FROM HumanResources.Department
END

When the CREATE PROCEDURE statement is executed, the server compiles the procedure and saves it as a database object. The procedure is then available for various applications to execute. The process of compiling a stored procedure involves the following steps:

• The procedure is compiled and its components are broken into various pieces. This process is known as parsing.
• The existence of the referred objects, such as tables and views, are checked. This process is known as resolving.
• The name of the procedure is stored in the sysobjects table and the code that creates the stored procedure is stored in the syscomments table.
• The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as execution plan. The execution plan is saved in the procedure cache.
• When the procedure is executed for the first time. The execution plan will be read and fully optimized and then run. The net time the procedure is executed in the same session, it will be read directly from the cache. This increases performance, as there is no repeated compilation.

After creating the stored procedure, you can view the code of the procedure by using the sp_helptext command.

Implementing Triggers and its Types in SQL

In a relational database, data in a table is related to other tables. Therefore, while manipulating data in one table, you need to verify and validate its effect on data in the related tables. In addition, you might need to manipulate data in a table after inserting or updating data in another table.

You also need to ensure that if an error occurs while updating the data in a table, the changes are reverted. This helps in maintaining data integrity. The SQL Server allows you to implement triggers and transactions to maintain data integrity.

This article explains different types of triggers that can be created in SQL Server. Next, we will discusses how to implement triggers to enforce data integrity. Further, we will discuss about how to implement transactions.

Implement Triggers

At times, while performing data manipulation on a database object, you might also need to perform another manipulation on another object. For example, in an organization, the employees use the Online Leave Approval system to apply for leaves. When an employee applies for a leave, the leave details are stored in the Leave-Details table. In addition, a new record is added to the Leaves-For-Approval table. When the supervisors log on to the system, all the leaves pending for their approval are retrieved from the Leaves-For-Approval table and displayed to them.

To perform such operations, the SQL Server allows you to implement triggers. A trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete. Triggers are used to ensure data integrity before or after performing data manipulations.

Before you implement a trigger, it is important to know the different types of triggers that can be created by using SQL Server.

Identifying Types of Triggers

In the SQL Server, various kinds of triggers can be used for different types of data manipulation operations. The SQL Server supports the following types of triggers:

Data Modification Language (DML) triggers

A DML trigger is fired when data in the underlying table is affected by DML statements, such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex action and cascade these actions to other dependent tables. Cascading is the process of reflecting the changes made in a table in the other related tables.

Data Definition Language (DDL) triggers

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Creating Table-Valued Functions in SQL

A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types:

Inline Table-Valued Function

An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.

Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:

CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO

You can use the following statement to execute the fx_Department_Gname function with a specified argument:

SELECT * FROM fx_Department_GName (‘Manufacturing’)

The preceding statement will return a result set having the group name ‘Manufacturing’.

Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:

CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.

Multistatement Table-Valued Function

A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.

Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:

CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END

The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:

SELECT * FROM PayRate (45)

Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.

An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.