T-SQL and SSMS

By Ajeet Kumar

WHAT IS T-SQL(Transact-SQL)

T-SQL is a Microsoft proprietary database language used for SQL Server. It is a procedural language unlike 4^th generation language SQL and is used to extend the capability of the SQL. The procedural language like C provides different features control of flow of the program like loop, conditional statements etc. and T-SQL provides all such features apart from the features of SQL.We can declare variables in T-SQL, and they can be used in different blocks of T-SQL script. T-SQL is organized into blocks of codes. Each block begins and ends with BEGIN and END keywords respectively.The statements are written inside the blocks and each statement terminates with semi-colon. The variables can be declared outside or inside the block.

BEGIN

• Declare variables
• T-SQL statements

END

SSMS(SQL Server Management Studio)

SSMS, a client tool is used to connect with an instance of the SQL server and write T-SQL scripts. You can download SSMS separately for the SQL Server from the Microsoft download site. We can connect with more than one instance of SQL Server.

To run the SSMS, we can use the Run dialog as shown below. Type SSMS in the text box.

If the path of the execution file is properly set-up, SSMS will run and will ask for the login credentials as shown below:

You can choose the server name from the combobox or can use localhost to run the local server. The local server is denoted by period sign also.

To authenticate, we can use windows password or can use the SQL Server password.

You can use other options. Click Options button to use them.

When the login is successful, we find the SSMS interface. In the left hand side, Object Explorer window appears with all the database objects.
Explore different objects of the SQL Server to get acquainted with them.

The new connections can be established and the current connection can be disconnected as well. This is shown below.

We can create database manually without using script. Right click by selecting the Databases object as shown.

To write a query, we open code window for query by clicking New Query.

Procedural languages’ Features in T-SQL

There are some of the following procedural features of T-SQL which enables programmers to control the flow of program execution. They are as below:

1. Break
2. Continue
3. While
4. If else
5. Return
6. Throw
7. Try catch
8. Begin end

Variables in T-SQL

The concept of variable in T-SQL is about an object which can hold a single data value of a specific data type. To declare a variable, we use declare statement and to initialize value to the variable we use set statement. We can declare more than one variable in a single statement using comma separator.

The scope of variable is within the batch. We use the USE statement to activate a database. We can use SELECT @varlocal or SET @varlocal to assign a value to variable but using SET statement is recommended. Some examples are as follows:

EXAMPLE: SELECT STATEMENTS TO INITIALIZE VARIABLES

use testdb

DECLARE @var1 varchar (30);

SELECT @var1 = 'Ajeet Kumar';

SELECT @var1 AS 'Developer';

EXAMPLE: SET STATEMENTS TO INITIALIZE VARIABLES

Begin

       declare @number1 int;

       declare @number2 int;

       declare @result int;

       set @number1 = 200;

       set @number2 = 300;

       set @result = @number1 + @number2;

       print cast (@result asvarchar (20));

End;

EXAMPLE: IF ELSE IN T-SQL TO BRANCH THE FLOW OF PROGRAM

IF DB_ID()= 1 

PRINT N'The current database is master.';

ELSEIF DB_ID()= 2

       PRINT N 'The current database is tempdb.';

ELSEIF DB_ID()= 3

       PRINTN 'The current database is model.';

ELSEIF DB_ID()= 4

       PRINTN 'The current database is msdb.';

else

PRINT N 'The current database is testdb.';

GO

print DB_ID()

GO STATEMENT

The GO statement is used to separate one batch from another. The important point is to remember that GO statement is actually not part of T-SQL. Therefore, we don't use semicolon after GO statement. If semicolon is used after it, error will be generated.

UNDERSTANDING BEGIN END BLOCK IN SQL SERVER

The concept of BEGIN END block in SQL Server is very much similar to usage of curly braces { and } in programming languages like JAVA. The begin end block is used to declare variables and t-SQL statements. We can use nested BEGIN END block very much like nested curly braces in java.

All the statements written in a begin end block are executed successfully. In case of failure entire block is discarded. Without begin end block the following t-SQL script will be infinite loop.

EXAMPLE: WHILE IN T-SQL TO BRANCH THE FLOW OF PROGRAM

DECLARE @Iteration Integer = 0 

WHILE @Iteration <10 

BEGIN 

    SELECT FirstName, MiddleName

    FROM dbo.DimCustomer WHERE LastName = 'Adams'; 

SET @Iteration += 1 

END;

In the above example, begin end block is executed again and again. In each iteration, the value of iteration variable is increased, and the current value is passed to the conditional expression of WHILE statement. Without begin end block, while block will have SELECT statement to execute for infinite times. The SET statement never will be executed.

SCHEMA

Schema is adatabase object which is owned by a database user. Schema is an independent entity in SQL server. It means that a user can be authorized for a schema and the rights can be revoked as well. The ownership of schema is transferable as well as distributable. A schema can be owned by more than one user at the same time. Schemas can be thought as namespaces or containers which contain database objects. It means that database objects can be moved into a schema and can be removed from a schema. Schemas provide security features to the database objects and this is the primary importance of schema. We cannot access database objects of a schema unless we are authorized for the schema of the objects. In SQL server database, there are two types of schemas.

• In-built schemas and
• User-defined schemas

The in-built schemas in SQL Server are SYS . We can create user defined schema as well. A database can have more than one schema. Basically, schema is used to partition the database objects into different namespaces. Schemas provide security to the database and in the object explorer, they are under the Security.

Each schema object has a unique name and schema id. Creating a schema is very easy. We use the CREATE SCHEMA <schema name> to create a schema. We also can provide the owner name to a schema.

CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

We cannot delete a schema if it contains any database object.

Remark: before 2005, schema was tied with the owner and deleting owner led to deletion of schema as well. The database objects owner and database user were same. Now schema is independent of user, group or role.

We can query the SYS schema as follows:

• select*from sys.all_objects
• select*from sys.schemas

CREATESCHEMAMySchema AUTHORIZATION Ajeet

CREATETABLEMobiles (ModelIDint, Priceint, Modelvarchar(20)) 

GRANTSELECTONSCHEMA::MySchemaTOTom

    DENY SELECTONSCHEMA::MySchemaTOJohn; 

GO 

STORED PROCEDURE

Static or Embedded SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard coded into the application. Dynamic SQL is SQL statements that are constructed at run time; for example, the application may allow users to enter their own queries.

Stored procedure is a database object like table, views, queries etc.

Stored procedure is basically a SQL script which is stored in a database server. Before executing the stored procedure, the database engine creates an execution plan and the plan is stored in plan cache. The cached plan is reused in consequent executions.

We know that querying a database tables is one of the most important operations. No matter whether you have a desktop application or web-based application, you may require displaying data, storing and updating data in the tables of the database. The stored procedures facilitate the dynamic approach to carry out these operations.

Stored procedures the database objects like tables, queries, views etc. They are SQL scripts and can be executed with or without parameters. They can use input parameters which are consumed/used the stored procedures and may return output parameters which can be used later.

The client application gives the input parameter to the stored procedure and the stored procedure in turn returns data in following three ways:

1. Output parameter
2. Return statement
3. Select statement

When a stored procedure is created in the SQL Server management studio (SSMS), it appears in the Object Explorer pane.

It is a good practice to qualify the schema in the stored procedure during its creation.

The syntax of stored procedure

The stored procedure can be used to create a database object e.g. table but creating a view or trigger or another stored procedure is not allowed in the stored procedure.

BODY of SP

You can declare variable inside the body of stored procedure. The declaration of variable begins with @ symbol followed by datatype. Multiple variables can be declared in a single line using comma delimiter.

Initialization of the variable is done using set keyword in the assignment statement.

Output parameter is used in the stored procedure parameter. Output keyword is used after the datatype of the parameterized variable. The output parameter is assigned value inside the body of the stored procedure. To get the values of the output parameter during EXEC statement, we should declare the variables for the output parameters with compatible datatype before the EXEC statement. The order of the output parameters matters in the exec statement.

DYNAMIC SQL

Dynamic SQL is a technique of writing SQL query which includes one or more variables in the SQL query and the values of the variables are decided at the run-time on the fly during execution.

To write a dynamic SQL, we use parameterized stored procedure. Each variable in a stored procedure is defined which begins with @ symbol. In SQL server, we write a stored procedure as follows:

CREATEPROCEDUREdbo.uspGetModel @modelvarchar(75)

AS

BEGIN

SELECT * FROMPerson.MobilesWHEREModel= @model

END

GO

The parameters are written just after the name of the stored procedure and datatype of each parameter is written after it.

EXECUTION OF SP

Executing SQL Stored Procedure from Another Stored Procedure

TYPES OF SP

1. User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database.
2. System Stored Procedure: The system stored procedures have names prefixed with sp_. They are used to manage SQL Server administrative tasks. The system stored procedures are stored in the master and MSDB databases.
3. Temporary Stored procedures: The temporary stored procedures’ names are prefixed with the # symbol. Temporary stored procedures are stored in the TEMPDB databases. These temporary procedures are automatically dropped when the connection terminates between client and server.
4. Remote Stored Procedures: The remote stored procedures are created and stored in remote server databases. These remote procedures can be accessed by the users who have the appropriate permission
5. Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

ADVANTAGES OF SP

• 1.       The most important advantage of the stored procedure is this that it promotes code re-usability.
• 2.       The code maintenance is better by using stored procedures. We can encapsulate the business logic inside the stored procedures and can change when the business requirement changes.
• 3.       The stored procedure can be encrypted and hence it provides security of data. Only those who are authorized to execute the stored procedures can execute them.
• 4.       The stored procedure is stored at appropriate server database in secure manner. They reduce unnecessary network traffic and boost the performance of application for which the stored procedure and database is being used.
• 5.       Execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.

DATABASE PRINCIPALS

The SQL Server provides a software layer which deals with security of the SQL Server. The database and the server are two major units in the SQL server and the security is provided at these layers in using principals. PRINCIPALS are the individuals, groups, and processes which are granted access to SQL Server. SECURABLES are the server, database, and objects the database contains.

The SQL server SA login is a server level principal. The default database of SA is master.

Continued...