Table query is a named query which is like a relational table which can be used like other tables .

The Microsoft SQL has four types of table expressions and we will go one by one :

1 – Derived tables

2 – CTE (common table expressions)

3 – Views

4 – Inline table valued Function (TVF)

1 – Derived Tables :

Derived tables are the queries which we write in the FROM clause of the outer query and there existence depends on the outer query . Once the outer query is finished , the derived table is finished.

AS we are using the TSQL2012 database , We can execute the

select * from (select custid,companyname from Sales.Customers) as customer

select * from (select custid,companyname from Sales.Customers where city like ‘%Berlin%’ ) as customer
select * from (select orderyear,count(distinct custid) as numcusts
from (select YEAR(orderdate) as orderyear, custid from Sales.Orders) as outer1
group by orderyear) as outer2
where outer2.numcusts > 70

Note : A query must have to meet the requirements of the table expression.

1. The rows returned might not have guaranteed order as the table expression is supposed to represent a relational table .

2 . All columns names in the table expression must be unique for instance : If we join two tables in the table expression and both the tables have columns with same name then we can have a problem. The easiest way is to give column aliases to these columns which have same name.

3. Assign column aliases: The best benefit of table expression is that you can refer the column aliases used in the select of inner query in the outer query. From this we can undertand that the column aliases used in the select clause cannot be used in the where or group by clause because select clause is logically processed after the where and group by clause in SQL .

For example : The following statement will give an error :

select YEAR(orderdate) as orderyear, custid from Sales.Orders) as outer1
group by orderyear)

Msg 207, Level 16, State 1, Line 5
Invalid column name ‘orderyear’.

We can solve this problem by using the YEAR(orderdate) in group by or where clause. But there can be possibility that there can be many expressions or inner queries. So to maintain code readability , we can just give the name to the query for the ease and use that name wherever needed.

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS D
GROUP BY orderyear;

Here we defined a derived table with name D on select year(orderdate) as orderyear , custid  which returns order year and customer ID of all rows . The outer query can refer to orderyear alias of inner query , in the Group By clause and in its select clause .

2 – Common Table Expressions (CTE):

CTE are another type of table expressions . They are defined by WITH statements, have a inner query defining CTE and the outer query which uses against it .

For instance :

WITH USAcustomers as

( select custid, companyname  FROM sales.customers where country = ‘USA’

)

select * from USAcustomers ;

 

We can also use column aliases in the outer query .

WITH C AS
(
select year(orderdate) as orderyear, custid 
from sales.orders
)

select orderyear , count(distinct custid) as numcasts
from C 
Group by orderyear;

Use the semicolon to end the CTE at the end of outer query.

Multiple CTEs

CTE has several advantages over derived tables. If you need to refer to one CTE from another we don’t end up nesting like we do in derived tables. Instead , when we define multiple CTE we define them by commas under the same WITH statement.

with c1 as 
(
select year(orderdate) as orderyear, custid
from sales.orders
),
c2 as 
(
select orderyear, count(distinct custid) as numcasts
from c1 group by orderyear
)
select orderyear, numcasts from c2 where numcasts > 70

This approach improves the readability and maintainability of the code compared to the nested derived table does.

 

Recursive CTEs:        

Recursive CTE has recursion capablities. Recursive CTE is defined by two queries. One is called Anchor member and one is called recursive member .

WITH <CTE_Name>[(<target_column_list>)]
AS
(
<anchor_member>
UNION ALL
<recursive_member>
)
<outer_query_against_CTE>;

The anchor member returns a valid result table and is invoked only once .

The recursive member is a query which has reference to the CTE name.

The reference to the CTE name is the result set which is returned by the previous execution. When the first time the recursive member is invoked , the previous result set is whatever the anchor member returned.

In the next invocations , the result set referenced to the CTE name has the result of the previous invocations of the recursive members. The recursive member is invoked repeatedly until it returns an empty set .

Both the queries must be having the same columns and their types.

The reference to the CTE name in the outer query is the unified result sets of the invocation of the anchor member and all invocations of the recursive member .

with EmployeeCTE as 
( select empid, mgrid, firstname, lastname
from hr.employees
where empid = 2 

union all

select c.empid,c.mgrid,c.firstname,c.lastname
from EmployeeCTE p 
inner join HR.Employees as c 
on c.mgrid = p.empid
)
select empid, mgrid,firstname, lastname
from EmployeeCTE

The anchor member queries the HR.Employees table and simply returns the row for employee 2.
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 2

The recursive member joins the CTE—representing the previous result set—with the Employees table to return the direct subordinates of the employees returned in the previous result set.

SELECT C.empid, C.mgrid, C.firstname, C.lastname
FROM EmployeeCTE AS P
JOIN HR.Employees AS C
ON C.mgrid = P.empid

In other words, the recursive member is invoked repeatedly, and in each invocation it returns the next level of subordinates. The first time the recursive member is invoked, it returns the direct subordinates of employee 2—employees 3 and 5. The second time the recursive member is invoked, it returns the direct subordinates of employees 3 and 5—employees 4, 6, 7, 8, and 9. The third time the recursive member is invoked, there are no more subordinates; the recursive member returns an empty set, and therefore recursion stops.

The user defined functions returns result after using an input parameters with a logic that calculates something .

There are two types of UDF are : scalar and table valued functions.

Scalar UDF returns a single value while the table valued functions returns a table.

 

Trigger is a code just like a stored procedure that runs whenever an event takes place. The event is related to DML (Data manipulation Language ) events and DDL(Data definition Language ) events .

1 – DDL(Data definition Language ) Triggers :

DDL statements means statements like :Create , alter etc . These triggers can be used for Auditing purposes or change management . They can be used for tracking whenever a CREATE table  or ALTER table statement is being run and can help the database administrators to use the logged information through these triggers about the events of Creation , alter or other DDL events.

2 –  DML(Data Manipulation Language ) Triggers :

SQL supports two types of DML triggers : After and instead of.

2.1 – AFTER Trigger :  

After Trigger fires after the event it is associated with it completes.

This trigger works only on permanent tables.

In the trigger’s code you can access the table that are called inserted and deleted that contains which were affected by the modification that caused the trigger to fire. The inserted table contains the image of the new data inserted . Whereas the deleted table holds the old image of the affected rows.

Example : 

IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit;
IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Table1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);

--Now creating trigger

CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.Table1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO

--Now insert values into the table1

INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');

Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :

SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;

Now check the table1 :

select * from Table1

2.2 – INSTEAD OF Trigger :  

In the Instead of triggers, the inserted and deleted tables contain the rows that were supposed or about to be affected (Inserted or Deleted) by the modification that caused the trigger to fire.

Example : 

IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit;
IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Table1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);

--Now creating trigger INSTEAD OF

CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 instead of insert
AS
SET NOCOUNT ON;
INSERT INTO dbo.Table1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO

–Now insert values into the table1

INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');

Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :

SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;

To make clear the difference , check the Table1 for understanding !

select * from Table1

We declare table variables as we declare other variables in SQL .

Table variables are just like the local temp tables and they reside in tempdb which is the temporary database.

Table variable are only available to the creating session .

One drawback with table variable is that they are only available and visible to the current batch and they are neither visible to other batches not even to the inner batches.

So , use the table variables when there is less amount of rows to work with .

 

The cursors are in contrast with the relational model which is based on the set Theory .

The cursors are slow as compared to the relational based set implementation  as they manipulate and work on the data row by row iteratively which is typically slow.

One of the main reasons of cursors are slow is because of overhead being caused due to : declaration of the cursor , then opening the cursor , looping through the cursor , closing the cursor and then de allocating the cursor .

Use cursors when you have a situation where a not a better solution exists from logical set based implementations  .

A good example where you can cursors is to run it to know the total number  of rows in a table.

Global Temp Table:

Global temp table starts with ## operator such as ##table1.

They can be used by all other sessions .

Anyone can modify or read data , so you have to be careful !

Global temp table gets destroyed when the creating session is closed or disconnects.

Local Temp Table: 

Local temp table starts with # operator such as #table1.

Local temp tables can be used by only the session which created it and no other session can see or access it.

It is destroyed when the creating session is closed or disconnects. Also , in the creating session , it is only accessible to the level of batch it is created into and automatically destroyed when the temp table goes out of scope of the level.

All temp tables are created in TempDb database of the SQL.

Stored Procedure

• The Stored procedure is actually a piece of code that can be saved in order to reuse it and saves a lot of time instead of writing the queries over and over again.

• Depending on the need , SQL stored procedure can also use parameters as Input and Output.

• It is a secure place to write your code , for instance : A stored procedure can be encrypted which secures it from unauthorized access.

• Permissions can be given to only particular users to access the stored procedure to have a better control of security .

• When a stored procedure is executed  , it gets stored in the cache memory which increases performance and efficiency.

• They are executed after writing execute statement.

• Better error handling can be done in the stored procedure.

The difference between UNION and UNION ALL Operator :

There is a difference between union and a union All operator which users are not aware of:

The union all operator will result in a set which will not eliminate the duplicates from the resulting set , means it will return the duplicate rows if residing in the set .

On the other hand union operator will eliminate the duplicates from the resulting set .

Whenever , you see a ALL keyword with union or intersect or except operators , it means it will give all the results including the duplicate records in the set .