what are CTE? When and Why To Use CTE in SQL ?

What are CTE in SQL ?

CTE are Common Table Expressions first introduced in Sql Server 2005. It is basically a result set or result holder or separate plugin / extension that can hold some complex query separately from main query and supply the named result set or result holder name in place of that complex query in main query.

It really improves complexity of complex queries. It is specially used in queries that contains complex sub-queries in them. Using them makes our query more concise, improves readability of query and makes it easy for maintainable for long time for future use.

CTE always start with Semicolon preceeded by 'with' keyword and CTE are temporary result holders and are removed form memory after query executed its life is only during query is executing.

When To Use CTE 

CTE is helpful to be used in Below two Conditions -

1.  In order to reduce complexity of query with complex subqueries that will make code easy to read and maintain.
   
2. Very helpful to create Recursive query if required in some cases.

you can read more about recursive queries with CTE on link below -
Read More About How To Use Recursive Query With CTE

Why We use ; ( Semicolon ) in start of CTE

It is actually to ensure previous statement before with / CTE are terminated. Most of times
we use semicolon before with keyword in CTE as we don't know what is before CTE.

Example - Without semicolon may create problem in below code in some StoredProcedure

DECLARE @foo int
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Above query will create error in Stored Procedure as DECLARE @foo int is not ended with
semicolon and CTE's with keyword is not ended with ; ( Semicolon ). Better way will  to write this query in stored procedure or anywhere is below -


DECLARE @foo int
;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Now in this case semicolon before WITH Keyword will terminate previous statement first and then execute CTE

Table Valued Functions in Sql

What are Table Valued Functions in Sql ?

There are two types of “User Defined” fucntions in sql server

1. scalar function ( that returns single value in response to function call )

     2. inline table valued ( that returns function result in tabulated form but query can single lined e.g.

     3. return (select * from table_name).

1. Inline Table Valued Functions
If function is made to execute a select query with or without parameters and return result as table type it comes in category of Inline table valued functions.

Sql Code Example -


CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS table AS
RETURN (SELECT au_fname, au_lname FROM Authors WHERE state=@state)
GO


2. Multi-statement table valued Functions
Multi-statement table valued that returns function result in tabulated form but query may have multiple statements that can make changes to query and return the tabulated data.

If functions is made to execute multiple sql statements including insert, update statements etc and return result as table type then it comes in category of Multistatement table valued functions

Example

CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT au_id,au_fname FROM Authors WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState VALUES ('','No Authors Found')
END
RETURN
END
GO

What are Instead of Triggers

Instead of triggers are used to reverse the default action of the query same as the word ‘insted of’ denotes. For example if we are going to execute query to delete record in Empoyee table and we want to first check whether id of employee passed Exist in database or not then we can place a “Insted of Trigger” in database on Delete Operation for that particular table and we will place code in trigger to first check whether employee with specified id exist in db . IF yes then delete that employe , otherwise abort the query execution.

In general language we use instead of triggers when we need to code - instead of doing xyz do abc . means we need to trigger the selection of operations in db depending on Values in database tables or other certain criteria.

INSTEAD OF TRIGGERS can be classified further into three types as:

1. INSTEAD OF INSERT Trigger.
2. INSTEAD OF UPDATE Trigger.
3. INSTEAD OF DELETE Trigger.

EXAMPLE
Below trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
 declare @emp_id int;
 declare @emp_name varchar(100);
 declare @emp_sal int;
 
 select @emp_id=d.Emp_ID from deleted d;
 select @emp_name=d.Emp_Name from deleted d;
 select @emp_sal=d.Emp_Sal from deleted d;

 BEGIN
  if(@emp_sal>1200)
  begin
   RAISERROR('Cannot delete where salary > 1200',16,1);
   ROLLBACK;
  end
  else
  begin
   delete from Employee_Test where Emp_ID=@emp_id;
   COMMIT;
   insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
  end
 END
GO

Convert Date Format To dd/mm/yy in Sql Server

If you Mssql Server is Using Some Other Date Format For Insert Date in Date Column Then Possibly Default Date format is set to US Date Format ( mm/dd/yy ) and if you want to insert date in dd/mm/yy . In order to insert date in this format you need to set Date Format to French / British ( dd/mm/yy )

So first I recommend you to Use any of Jquery Date Picker and pass the selected date / datetime as below to Insert Query as Below -

insert Table_Name (Date_Column)  values (convert(datetime,'18-06-12 10:34:09 PM',103));

Here second parameter 103 is For French Date Format dd/mm/yyyy .If you want to have dd/mm/yy then use 3 in that parameter

Different Types of Date Format Code as As Below - 

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	British/French	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	German	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	Italian	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106 (1)	-	6 = dd mon yy 106 = dd mon yyyy
7	107 (1)	-	7 = Mon dd, yy 107 = Mon dd, yyyy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	JAPAN	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = yyyymmdd
-	13 or 113(1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces) Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) Note: When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM In this style, mon represents a multi-token Hijri unicode representation of the full month's name. This value will not render correctly on a default US installation of SSMS.
-	131 (2)	Hijri (5)	dd/mm/yyyy hh:mi:ss:mmmAM

Difference Between Stored Procedure and Functions Sql

Stored Procedure and Functions

 

1. Function Must Return Value

   For Stored Procedure it's Not Must to return value it's optional 

2. Stored Procedures are pre-compile objects which are compiled for 

first time and its compiled format is saved which executes (compiled code) whenever it is called 

But Function is compiled and executed every time when it is called

3. Function can Accept only Input Parameters

   But Stored Procedure Can Accept Both Input and Output Parameters

4. Functions can be called from Procedure whereas Procedures cannot be called from Function

5. Functions Like Views only Accept Data Select Statements It does not Allow the Permanent Storage of data so it does not allows DML statements like Insert , Update , Delete

Whereas Store Procedure Allows All type of DML statements like Insert , Update and also Select Statements.

6. Funtions can be used in Select Statements like views to View data 

But Stored Procedure Cannot be Embedded in Select Statements

7.

 

Difference Between Views And StoredProcedure Sql

Stored Procedure Vs Views

1. Stored Procedure are collection of pre-executed sql Statements that accepts parameters as input and depending on input parameters passed gives the Output Result

Views Act like Virtual Tables That Contains set of Rows and Columns from multiple original table of Database according to defined Query Commands

2. Stored Procedure Cannot be Used as Large Building Block or we can simply say It cannot be treated as tables in large queries 

But Views Can be treated like tables we can Use Views similar to table and execute select statements on Views Similar to Tables

3. Stored Procedures Allow the Use of Data Manipulation Command like Insert Data , Update data on Table or Schemas To Manipulate Data

But Views Cannot have Data Manipulation Command It can only give list of data or View of data based on set of Queries in it . Views cannot be used to Permanently store data in database it allows just to view the data.

4. Views  can have  only one Select Statement Allowed To Use

Store Procedure Can Use Various Set of Statements That also Include If-Else Statements

5. View is a virtual table that only exists when you use the view in a query. Its considered virtual table because it acts like a table, and the same operations that can be performed on a table can be performed on a view. Virtual table doesn't stay in the database, it gets created when we use the view and then delete it. 

But Stored Procedure Physically Exist 

6. Views are Used for Providing Security to data So that by using the Views we can View only Particular view of the schema to the user Not the Full View with required columns. Their is no physical presence of view data anywhere in database.

Stored Procedure allows to perform any type of data Manipulation operation But We can also Put Security Permission on Store Procedure In order to Restrict its Access From Unauthorized User

7. Code Sample For Stored Procedure and Views

/*
This Stored procedure is used to Insert value into the table tbl_students. 
*/

Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
) 
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End

/*

View Example

*/

CREATE VIEW [Category Sales For 1997] AS

SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales

FROM [Product Sales for 1997]

GROUP BY CategoryName