What are the Phases of the Query Processing Logic in the SQL Server

SQL Query Processing Phases In the SQL Server
The Query processing logic in SQL is different from the other Programming languages, where the code is processed in the order in which it is written (called its typed order).

In SQL Server, the Logical processing order is different from typed order, the first clause that is processed is the FROM clause, where as the SELECT clause is almost processed later though it appears in the first in a query.

As we know that the SQL, was earlier known as the "SEQUEL" , in this original name the letter E stands for English. The designers of this language had in mind a declarative language where you declare your instructions in a language similar to English.

Consider the manner in which you provide an instruction in English "Bring me the T-SQL Querying Book from the Microsoft Store". Here, you indicate the Object(the Book) before indicating the location (the Store) from which you need to get the book. 

But it clearly indicating that the person carrying out the instruction would have to first go to the Store, then from there he obtain the Book.

In a similar manner, in SQL, the typed order indicates the SELECT clause with the desired columns before the FROM clause.

Each phase in Logical query processing generates a Virtual Table(VT) that is used as the input to the next step. These VTs are not available to the caller(client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in the query, that step is simply skipped.

Logical Query processing Phases :
The following diagram showing the Logical Query processing in SQL Server.

                                                               Image courtesy : Microsoft

The following showing the Logical order numbers of the Query processing in SQL Server.

(5)    SELECT

(7)    TOP(<top_specification>)

         (5-2) DISTINCT

         (5-1) <select_list>

(1)    FROM 

         (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>

         (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>

         (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>

         (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alisas>

(2)    WHERE <where_predicate>

(3)    GROUP BY <group_by_specification>

(4)    HAVING <having_predicate>

(6)    ORDER BY <order_by_list>

(7)    OFFSET <offset_specification> ROWS FETCH NEXT <fetch_spec> ROWS ONLY;

Now we will discuss in detail about each Phase of the Query processing.

(1) FROM:

This Phase identifies the queries source tables and process the tables operators(eg. Pivot).

Each table operator applies a series of sub phases. For example, the phase involved in a Join are (1-J1) Cartesian Production, (1-J2) ON Predicate, (1-J3) Add Outer Rows. These phases generates a first virtual table VT1.

(1-J1) Cartesian Product : 

This phase performs a Cartesian Product(Cross Join) between the two tables involved in the table operator, generating VT1-J1.

(1-J2) ON Predicate :

This phase filters the rows from VT1-J1 based on the predicate that appears in the ON Clause(<on_predicate>). Only the Rows for which the predicate evaluates to True are inserted in to VT1-J2.

(1-J3) Add Outer Rows :

If OUTER Join is specified(as opposed to CROSS Join or INNER Join), rows from the preserved table or tables for which a match was found are added to the rows from VT1-J2 as outer rows and then generates VT1-J3.

(2) WHERE :

This phase filters the rows from VT1 based on the predicate that appears in the Where clause (<where_predicate>). Only the rows for which the predicate evaluates to True are inserted into VT2.

(3) GROUP BY :

This phase arranges the rows from VT2 into groups based on the set of expressions (aka, grouping set) specified in the GROUP BY Clause, and generating VT3. Ultimately there will be one result row per qualifying group.

(4) HAVING :

This phase filters the groups from VT3 based on the predicate that appears in the Having clause (<having_predicate>). Only groups for which the predicate evaluates to True are inserted into VT4.

(5) SELECT :

This phase processes the elements in the SELECT clause and then generates VT5.

(5-1) Evaluate Expressions :

This phase evaluates the expressions in the SELECT list, generates VT5-1.

(5-2) DISTINCT :

This phase removes the duplicates from VT5-1 and generates VT5-2.

(6) ORDER BY :

This phase orders the rows from VT5-2 according to the list specified in the Order By clause and generates the Cursor VC6.(A Cursor is a temporary work area created in the system memory when a SQL statement is executed. A Cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data.)

In case of Absent of an Order BY clause then VT5-2 will becomes VT6.

(7) TOP | OFFSET-FETCH :

This phase filters the rows from VC6 or VT6 based on the Top or Offset-Fetch specification and the then generates VC7 or VT7 respectively. With Top, this phase filters the specified no.of rows based on the ordering in the Order By clause, or based on the arbitrary order when Order By clause is absent.

With Offset-Fetch, this phase skips the specified no.of rows and then filters the specified no.of rows based o n the ordering in the Order By clause. The Offset-Fetch filter was introduced in the SQL Server-2012.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

What are the Differences between the Temporary Table and Table Variable

Key Differences between the Temporary Table and Table Variable
The following are the Main differences between the Temporary Table and Table Variable.

Temporary Table	Table Variable
The Temporary Tables can be used in Stored Procedures, Triggers and Batches but not in User defined Functions.	The Table Variables can be used in User defined Functions, Stored Procedures, and Batches.
The Local Temporary Tables are Temporary Tables that are available only to the one Session in which we created them. Global Temporary Tables are Temporary Tables that are available to all sessions and all the users.	The scope of the Table Variable in the stored procedure, user defined function or batch where it is declared like any local variable we create with a DECLARE statement.
The Local Temporary Tables are automatically destroyed at the end of the Procedure or Session in which we created them.  Global Temporary Tables are dropped automatically when the last session using the Temporary Table has been completed. We can also drop Temporary Tables explicitly using Drop command similar to normal table.	The Table variables are automatically cleaned up at the end of the User defined Function, Stored Procedure, or Batch in which they are defined.
The Temporary Table name can be of maximum 116 characters.	The Table Variable name can be of maximum 128 characters.
The constraints like PRIMARY KEY, UNIQUE, CHECK, NULL etc can be implemented at the time of creating Temporary Tables using CREATE TABLE statement or can be added after the table has been created. FOREIGN KEY not allowed.	The constraints like PRIMARY KEY, UNIQUE, CHECK, DEFAULT, NULL can be added, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
The Temporary Tables supports adding Indexes explicitly even after creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.	The Table Variables doesn’t allow the explicit addition of Indexes after it is declared, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined at the time of declaring Table Variable.
The Temporary Tables can also be directly created and data can be inserted using Select Into statement without creating a Temporary Table explicitly.	The Table Variables can’t be created using Select Into statement because being a variable it must be declared before use.
The SET IDENTITY_INSERT statement is supported in Temporary Table.	The SET IDENTITY_INSERT statement is not supported in Table Variables.
We can’t return a Temporary Table from a user-defined function.	We can return a Table Variable from a user-defined function.
The Temporary Table can be truncated like normal table.	The Table Variables can’t be truncated like normal table or Temporary Tables.
The data in the Temporary Table will be rolled back when a transaction is rolled back similar to normal table.	The data in the Table Variable will not be rolled back when a transaction is rolled back.
The Temporary Tables used in Stored Procedures cause more recompilations of the stored procedures than when Table Variables are used.	Table Variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
The Temporary Table will generally use more resources than Table Variable.	The Table Variable will generally use less resources than a temporary table.
The Temporary Tables can be access in Nested Stored Procedures.	The Tables Variables can’t be access in Nested Stored Procedures.
The ALTER command can be used with Temporary Tables.	The ALTER command does not support with Table Variables.
The Temporary Tables should be used for large result sets.	The Table Variables should be used for small result sets and the everyday type of data manipulation since they are faster and more flexible than Temporary Tables.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

What is Materialized View or Indexed View and how to create it in SQL Server

What is difference between View and a Materialized View in SQL Server

View :
The View is a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data.
So thaat we always get the updated or latest data from original tables.
Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query.

If we need multiple joins between tables, then use the Materialized view defined below, where we define the Index based columns which we used for joining, as we know that the index based columns are faster than a non-index based column.

Materialized View :
Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.
When we see the performance of Materialized view it is better than normal View, because the data of materialized view will be stored in table and table may be indexed so faster for applying joins.
Also joining is done at the time of materialized views refresh time so that no need to fire join statement every time as in case of view.

Creating a Materialized View :

If you want to create an Index on your View, you must create it with using the " WITH SCHEMEBINDING" Option.
If you want to create a Schema-Bound user defined Function that references to your View then your View must also be " SCHEMEBINDING".

The SHEMABINDING is essentially takes the things that your VIEW is depend upon(Tables/ other Views), and "Binds" them to that View. The significance of this is that, no one can make alteration (ALTER/DROP) to those underlying objects, unless you drop the Schema-Bound View first.

--------------------------------------------------------------------------------------
Example :
Creating a View with SchemaBinding Option
USE [TAMATAM]
GO
CREATE VIEW vw_EmpDeptDtls
WITH SCHEMABINDING
AS
SELECT E.[Emp_Id],
E.[Emp_Name],
E.[Gender],
E.[Joining_Date],
E.[Basic_Sal],
D.[Dept_Id],
D.[Dept_Name],
D.[Bonus_Rate],
J.[Job_Id],
J.[Job_Title]
FROM [dbo].[Emp_Test] E Inner Join [dbo].[Dept] D
ON E.Dept_Id=D.Dept_Id
Inner Join [dbo].[JobTitles] J
ON E.Job_Id=J.Job_Id
GO
--------------------------------------------------------------------------------------
Now you can create an Index on the above View to make the Query execution faster :
Create Unique Clustered Index Ind_Vw_EmpDept
On vw_EmpDeptDtls (Emp_Id,Dept_Id,Job_Id)
--------------------------------------------------------------------------------------

Notes :
Now if you try to make any alterations to the underlying objects...
--ALTER TABLE EMP_TEST Drop Column [Job_Id]
--ALTER TABLE EMP_TEST ALTER Column [Emp_Name] Varchar(250)
--Drop Table [Emp_Test]

You will get the following errors...
--The object 'vw_EmpDeptDtls' is dependent on column 'Job_Id'
--ALTER TABLE DROP COLUMN Job_Id failed because one or more objects access this column.
--Cannot DROP TABLE 'Emp_Test' because it is being referenced by object 'vw_EmpDeptDtls' 

Please note that :
--You can Alter the underlying objects of the View, by removing the SchemaBinding by altering the View .
--You can drop/alter a Column from the base table, which is not used in the View.

Difference between View vs Materialized View :

a) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.
b) When we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different (in Oracle)
c) In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
d) Performance of View is less than Materialized view.
e) In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
f) In case of Materialized view we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Difference between SQL Server Delete,Truncate and Drop Commands

What are the Key differences between Delete,Truncate and Drop Commands
TRUNCATE :

TRUNCATE is a DDL command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.
We cannot use Where clause with TRUNCATE.
TRUNCATE removes all rows from a table.
Minimal logging in transaction log, so it is performance wise faster.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
Identify column is reset to its seed value if table contains any identity column.
To use Truncate on a table you need at least ALTER permission on the table.
Truncate uses the less transaction space than Delete statement.

Truncate cannot be used with indexed views. 

Notes :

The Truncate will Fail when there is Foreign key violation, and will get the below error.

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

To avoid this error, we can set the below properties.

SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE Table TableName; 

--Set back the Check again to default.

SET FOREIGN_KEY_CHECKS = 1;

DELETE :
DELETE is a DML command.
DELETE is executed using a row lock, each row in the table is locked for deletion.
We can use where clause with DELETE to filter & delete specific records.
The DELETE command is used to remove rows from a table based on WHERE condition.
It maintain the log, so it slower than TRUNCATE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Identity of column keep DELETE retain the identity.
To use Delete you need DELETE permission on the table.
Delete uses the more transaction space than Truncate statement.
Delete can be used with indexed views.

 /* Deletes all records from a Table */

DELETE From TableName ;

/* Delete records based on Condition*/

DELETE From TableName Where FieldName='Value' 

DROP : 
The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back (undone) easily, while DROP and TRUNCATE 
operations also can be rolled back if we define them in a TRANSACTION.

DROP Table TableName; 

Note :
We can rollback DELETE, TRUNCATE and DROP Operations when we defined them in a TRANSACTION with Save Points.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Modify Date and Time Values with DateDiff and DateAdd Functions in SQL Server

SQL Server DateDiff and DateAdd Functions to Modify Date and Time Values

USE TAMATAM_db GO

Declare @StDate as DATE = '06/14/2015' --'MM/DD/YYYY HH:MM:SS'

Declare @EnDate as DATE='12/25/2017'

Declare @NewDate as DATE

---------------------------------------------------------

Select DateDiff(YY,@StDate,@EnDate ) AS DateDiff_in_Years

--Results the Ouput as '2'; The Difference of Dates in Years

Select DateDiff(QQ,@StDate,@EnDate ) AS DateDiff_in_Quarter

--Results the Ouput as '10'; The Difference of Dates in Quarters

Select DateDiff(MM,@StDate,@EnDate ) AS DateDiff_in_Months

--Results the Ouput as '30'; The Difference of Dates in Months

Select DateDiff(WK,@StDate,@EnDate ) AS DateDiff_in_Weeks

--Results the Ouput as '132'; The Difference of Dates in Weeks

Select DateDiff(DD,@StDate,@EnDate) AS DateDiff_in_Days

--Results the Ouput as '925'; The Difference of Dates in Days
---------------------------------------------------------

SELECT DATEADD(YY,2,@StDate) AS Add_Years_to_Date

--Results as '2017-06-14'; The Addition of 2 Years to '06/14/2015'

SELECT DATEADD(YY,-2,@StDate) AS Substract_Years_from_Date

--Results as '2013-06-14'; The Substraction of 2 Years from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(QQ,2,@StDate) AS Add_Quaters_to_Date

--Results as '2015-12-14'; The Sub Addition of 2 Quarters from '06/14/2015'

SELECT DATEADD(QQ,-2,@StDate) AS Substract_Quaters_from_Date

--Results as '2014-12-14'; The Sub Straction of 2 Quarters to '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(MM,4,@StDate) AS Add_Months_to_Date

--Results as '2015-10-14'; The Addition of 4 Months to '06/14/2015'

SELECT DATEADD(MM,-4,@StDate) AS Substract_Months_from_Date

--Results as '2015-02-14'; The Substraction of 4 Months from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(WW,4,@StDate) AS Add_Weeks_to_Date

--Results as '2015-07-12'; The Addition of 4 Weeks to '06/14/2015'

SELECT DATEADD(WW,-4,@StDate) AS Substract_Weeks_from_Date

--Results as '2015-05-17'; The Substraction of 4 Weeks from '06/14/2015'

---------------------------------------------------------

SELECT DATEADD(DD,17,@StDate) AS Add_Days_to_Date

--Results as '2015-07-01'; The Addition of 17 Days to '06/14/2015'

SELECT DATEADD(DD,-14,@StDate) AS Substract_Days_from_Date

--Results as '2015-05-31'; The Substraction of 14 Days from '06/14/2015'

---------------------------------------------------------

SELECT Day(@StDate) AS Day_of_Month

--Results as '14'; The Day of the Month in the Date '06/14/2015'

SELECT Day(EOMONTH(@StDate)) AS Days_in_Month

--Results as '30'; The last Day of the Month in the Date '06/14/2015'
GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to get Date and Time parts using SQL Server DateName and DatePart Functions

SQL Server DateName and DatePart Functions to get Date and Time Parts from DateTime Values
Use Tamatam_DB GO
Declare @StDate as DATETIME = '08/20/2015 15:25:45' --'MM/DD/YYYY HH:MM:SS'
Declare @ToDate as DATETIME --'MM/DD/YYYY HH:MM:SS AM/PM'
SET @ToDate = GETDATE()
---------------------------------------------------------
SELECT DateName(DAY,@StDate) AS Day_No_in_Month
SELECT DatePart(DAY,@StDate) AS Day_No_in_Month
SELECT DatePart(D,@StDate) AS Day_No_in_Month
SELECT DatePart(DD,@StDate) AS Day_No_in_Month

--Output Result as '20' ; The Day number in Month of Date
---------------------------------------------------------

SELECT DatePart(DAYOFYEAR,@StDate) AS Days_No_in_Year
SELECT DateName(Y,@StDate) AS Days_No_in_Year
SELECT DatePart(Y,@StDate) AS Days_No_in_Year

--Output Result as '232' ; The Day Number in Year of Date
---------------------------------------------------------

SELECT DateName(WEEK,@StDate) AS WK_No_in_Year
SELECT DatePart(WEEK,@StDate) AS WK_No_in_Year
SELECT DatePart(WK,@StDate) AS WK_No_in_Year

--Output Result as '34' ; The Week Number in Year of Date
---------------------------------------------------------

SELECT DatePart(WEEKDAY,@StDate) AS WK_No_in_Week
SELECT DatePart(W,@StDate) AS WK_No_in_Week

--Output Result as '5' ; The Weekday Number in Week of Date
---------------------------------------------------------

SELECT DateName(W,@StDate) AS WK_Day_Name
SELECT DateName(DW,@StDate) AS WK_Day_Name
SELECT Left(DateName(W,@StDate),3) AS WK_Day_Name

--Output Result as 'Thursday' ; The Weekday Name in Week of Date
---------------------------------------------------------

SELECT DatePart(MONTH,@StDate) AS Month_Number
SELECT DatePart(MM,@StDate) AS Month_Number
SELECT DatePart(M,@StDate) AS Month_Number

--Output Result as '8' The Month Number of Date
---------------------------------------------------------

SELECT DateName(MONTH,@StDate) AS Month_Name
SELECT DateName(MM,@StDate) AS Month_Name
SELECT DateName(M,@StDate) AS Month_Name

--Output Result as 'August' The Month Name of Date
---------------------------------------------------------

SELECT DateName(Q,@StDate) AS Quarter_Nummber
SELECT DatePart(Q,@StDate) AS Quarter_Nummber
SELECT DateName(QQ,@StDate) AS Quarter_Nummber
SELECT DatePart(QQ,@StDate) AS Quarter_Nummber

--Output Result as '3' The Quarter Number of Date
---------------------------------------------------------

SELECT DateName(YY,@StDate) AS Year_Nummber
SELECT DatePart(YY,@StDate) AS Year_Nummber

--Output Result as '2015' The Year Number of Date
GO
---------------------------------------------------------
Declare @StDate as DATETIME = '08/20/2015 15:25:45' --'MM/DD/YYYY HH:MM:SS'

SELECT DAY(@StDate) AS Day_in_Date
--Output Result as '20' ; 'DD'

SELECT MONTH(@StDate) AS Month_in_Date
--Output Result as '8' ; 'M'

SELECT YEAR(@StDate) AS Year_in_Date
--Output Result as '2015' ; 'YYYY'

SELECT CONVERT(DATE,@StDate) AS Date_from_DateTime_ANSI_Format
--Output Result as '2015-08-20' ; 'YYYY-MM-DD'

SELECT FORMAT(CONVERT(DATE,@StDate),'dd/MM/yyyy') AS Date_from_DateTime_Custom_Format

SELECT FORMAT(@StDate,'dd/MM/yyyy') AS Date_from_DateTime_Custom_Format
--Output Result as '20/08/2015' ; 'DD/MM/YYYY'

SELECT CONVERT(VARCHAR(10),@StDate,108) AS Time_from_DateTime
--Output Result as '15:25:45' ; 'HH:MM:SS'

SELECT FORMAT(@StDate,'hh:mm tt') AS Time_AmPm_Format
--Output Result as '03:25 PM' ; 'HH:MM AM/PM'

SELECT FORMAT(@StDate,'dd/MM/yyyy hh:mm tt') AS DateTime_AmPm_Format
--Output Result as '20/08/2015 03:25 PM' ; 'DD/MM/YYYY HH:MM:SS AM/PM'

SELECT DATEPART(HOUR,@StDate) AS Hours_from_DateTime
--Output Result as '15' ; 'HH'

SELECT DATEPART(MINUTE,@StDate) AS Minutes_from_DateTime
--Output Result as '25' ; 'MM'

SELECT DATEPART(SECOND,@StDate) AS Seconds_from_DateTime
--Output Result as '45' ; 'SS'
GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Find when the Table was last refreshed in SQL Server

How to Find when the Table was last updated by a user in SQL Server

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, Last_User_Update,*
FROM sys.dm_db_Index_Usage_Stats
WHERE Database_Id = DB_ID( 'TAMATAM') --Database name need to pass here
And Object_Id=Object_Id('TBL_Weekly_Data') --Table name need to pass here

--------------------------------------------------------------------------------------------------------

SELECT Distinct Last_User_Update
FROM sys.dm_db_Index_Usage_Stats
WHERE Object_id=Object_id('[dbo].[TBL_Weekly_Data]')

--------------------------------------------------------------------------------------------------------

How to Update a Target Table by Mapping with another Table in SQL Server

SQL Query to Update a Target Table by Mapping with another Table in SQL Server

Suppose , we have a Target Table called 'Tbl_Customers', in which the columns [Cust_Segment] and [Bookings_Flag] are need to update by Mapping it with a another Table called 'Map_Table' . we can do it by using the Update query as follows..

With Inner Join :

UPDATE [Tbl_Customers]
SET [Cust_Segment] = M.[CustSegment],
[Bookings_Flag]= CASE When M.[Bookings_Type]='Product' Then 'Prod' ELSE 'Svc' END
From [Tbl_Customers] C Inner Join [Map_Table] M
On C.Cust_Id=M.CustId

With Where Clause :

UPDATE [Tbl_Customers]
SET [Cust_Segment] = M.[CustSegment],
[Bookings_Flag]= CASE When M.[Bookings_Type]='Product' Then 'Prod' ELSE 'Svc' END
From [Tbl_Customers] C, [Map_Table] M
Where C.Cust_Id=M.CustId

--------------------------------------------------------------------------------------------------------