What is SQL (Structured Query Language)

SQL stands for Structured Query Language and it is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.

SQL statements are broadly categorized into 4 types.

1.   Data definition language (DDL)

2.   Data manipulation language (DML)

3.   Data Control Language (DCL)

4.   Transaction Control Statement (TCS)

Data definition language (DDL):

Data definition language type of SQL statement is used to define database objects like tables and index structures and example of DDL statements are Create, Alter, Delete, Truncate and Drop.

There are T-SQL example of T-SQL DDL statements to create database table and delete column name of table

CREATE TABLE OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50)    

)

GO

ALTER TABLE OrderStatus

DROP COLUMN StatusDescription;

GO

DROP TABLE OrderStatus

Data manipulation language (DML):

Data manipulation language type of SQL statement is used to managing data in database and example of DML statements are Select, Insert, Update, Delete and Merge

There are example of T-SQL DML statements to select records from table, insert data in table and update data in table based on condition

SELECT Name, Sex, Address FROM dbo.persons

GO

INSERT INTO Persons(Name, Sex, Address)

VALUES ('Smith', 'M','8693 Main ST')

GO

Update Persons SET Sex = 'F' Where Name = 'Julie'

Data Control Language (DCL):

Data Control Language type of SQL statement is used to grant the access permission on database object and control access to data stored in database and main DCL statement are Grant and Revoke.

There are example of T-SQL DCL statement to grant and revoke select permission on Person table from user

GRANT SELECT ON Person TO 'rtiwari'

GO

REVOKE SELECT ON Person FROM 'rtiwari'

Transaction Control Statement (TCS):

Transaction Control Statement (TCS) type of SQL statement is used to manage the current transaction and it includes Commit, Rollback and Begin Transaction

There are example of T-SQL TCS statement to commit the current transactions and rollback transaction

BEGIN TRANSACTION;  

Update Persons SET Sex = 'F' Where Name = 'Julie'

COMMIT TRANSACTION; 

GO

BEGIN TRANSACTION;  

DELETE FROM Persons 

    WHERE Address like 'Main%';  

ROLLBACK TRANSACTION; 

sp_executesql how to prevent SQL Injection

What is SQL Injection : 
SQL injection is a type of injection attack, in which inserts SQL query via the input data from client to application and it can read sensitive data from database or modified database data or execute administration operation.

sp_executesql is system stored procedure, it is used to execute the SQL script like EXEC but sp_executesql allows for SQL statement to be parameterized as instead of embedding or injecting it into SQL code so it stops any SQL Injection possibility.

sp_executesql how to prevent SQL Injection

Here is an example of sp_executesql how to prevent SQL Injection:

CREATE PROCEDURE dbo.usp_validate_user

     @UserName as varchar(10),

     @Password as varchar(10)

AS

BEGIN

    

     declare @sqlString as varchar(max)

     SET @sqlString = 'Select id from dbo.Users Where UserName = @UserName and password = @Password'

     EXECUTE sp_executesql @sqlString,

     N'@UserName as varchar(10),  @Password as varchar(10)'

       @UserName,  @Password

END

GO

Difference between Exec and sp_executesql :

There are some other difference between Exec and sp_executesql

sp_executesql	EXEC
Allow for statement to be parameterized	There is no option for parameterize column
No Risk of SQL Injection	High Risk of SQL Injection
Strongly typed parameters	No Strongly typed parameters
Performance Benefits - Cache an execution plane on first run	Create an execution plan for each run

What is sp_executesql system stored procedure in SQL Server

In this blog, we will discuss about sp_executesql stored procedure and what advantage sp_executesql stored procedure have it over EXEC.

sp_executesql is system stored procedure, it is used to execute the SQL script like EXEC but sp_executesql allows for SQL statement to be parameterized as instead of embedding or injecting it into SQL code so it stops any SQL Injection possibility.

There are some other difference between Exec and sp_executesql

sp_executesql	EXEC
Allow for statement to be parameterized	There is no option for parameterize column
No Risk of SQL Injection	High Risk of SQL Injection
Strongly typed parameters	No Strongly typed parameters
Performance Benefits - Cache an execution plane on first run	Create an execution plan for each run

Here is an example of sp_executesql:

CREATE PROCEDURE dbo.usp_validate_user

     @UserName as varchar(10),

     @Password as varchar(10)

AS

BEGIN

    

     declare @sqlString as varchar(max)

     SET @sqlString = 'Select id from dbo.Users Where UserName = @UserName and password = @Password'

     EXECUTE sp_executesql @sqlString,

     N'@UserName as varchar(10),  @Password as varchar(10)'

       @UserName,  @Password

END

GO

SQL Common Table Expressions (CTE)

SQL CTE stands for Common Table Expression, and it was introduced in SQL Server 2005 and basically it simplify the complex SQL queries and help to write the recursive query.

SQL CTE always returns temporary result set and the scope of result is only to next SQL query and it can be referenced within a Select, INSERT, UPDATE, or DELETE statement.

A SQL CTE can be used to:

·         Use the CTE's result-set more than once in SQL query

·         Substitute for a view when the general use of a view is not required;

·         Simplify the complex joins

·         Create a recursive query

Here is an example of CTE:

We want to calculate yearly sales reports by using SQL CTE

Order Table:

OrderID	OrderAmount	OrderDate
1	$           189.00	8/21/2017 10:58
2	$             76.00	12/21/2017 10:59
3	$             76.00	6/21/2017 10:59
4	$             79.00	8/21/2016 11:00
5	$             84.00	4/21/2019 11:00

With CTE_YearlySale AS

(

 Select OrderID, OrderAmount, Year(OrderDate) [Year] from dbo.Order

)

Select [Year], SUM(OrderAmount) [Sales] from CTE_YearlySale group by [YEAR]

Output:

Year	Sales
2016	$        79.00
2017	$      341.00
2019	$        84.00

Here is an example of Recursive Query by using CTE:

WITH [CTE_Recursive] AS (

SELECT 1 AS [Number]

UNION ALL

SELECT [Number]+1

FROM [CTE_Recursive]

WHERE [Number] <= 8

)

SELECT [Number], Case When ([Number] % 2 = 1) THEN 'ODD' ELSE 'EVEN' END [Label] FROM [CTE_Recursive]

Output:

Number	Label
1	ODD
2	EVEN
3	ODD
4	EVEN
5	ODD
6	EVEN
7	ODD
8	EVEN
9	ODD

 

For more information about the SQL Common Table Expressions (CTE) visit WITH common_table_expression (Transact-SQL)

PERSISTED Computed Column and Performance Improvement

In this post, we will learn how to create a PERSISTED computed column in SQL and how does it improve the performance of computed Column in large data calculation.

Persisted Computed Column means the SQL Server physically stores the data of computed columns and when the data is changed in table, the SQL server computes the value for computed column based on the defined calculation expression in schema.

So when you fetch data from table, it doesn’t perform any calculation and simply retrieves the value from table for computed column.

SQL : How to create a computed Column in SQL Server

Syntax: Create SQL PERSISTED Computed Column on Create Table:

The below SQL script creates a table with PERSISTED Computed Column ‘DiscountedAmount’.

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2),

    OrderDate date DEFAULT GETDATE(),

    DiscountedAmount AS (OrderAmount * .25) PERSISTED,

    PRIMARY KEY (OrderID),   

);

 

When you update the data in table, the value of Computed field DiscountedAmount will be will be updated by SQL server.

----Inserts value in the table

Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)

Select 1, 100, 1, 100

Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)

Select 2, 101, 1, 300

----Display the rows in the table

Select * from dbo.Orders

Syntax: Create SQL PERSISTED Computed Column on Alter Table:

The below SQL script creates a PERSISTED Computed Column ‘DiscountedAmount’ on an existing table

ALTER TABLE dbo.Orders ADD DiscountedAmount AS (OrderAmount * .25) PERSISTED

Syntax: Drop SQL PERSISTED Computed Column:

The below SQL script delete a PERSISTED Computed Column ‘DiscountedAmount’ from table

ALTER TABLE dbo.Orders DROP COLUMN DiscountedAmount

Performance Improvement by using PERSISTED Computed Column instead of Computed Column:

Here is an execution plan, which show that there two scalar operator for computed column without Persisted tag.

  

and an execution plan for PERSISTED Computed Column:

  

Conclusion that If the computed column is PERSISTED, there is no performance issue in selecting the data but there will be extra overhead while updating or inserting new row.