SQL Server Tsql CTE Common Table Expressions

CTE: CTE’s (SQL Common Table Expression ) are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

CTE: CTE’s are more like temporary views than anything else. When you look at the execution plan, you’ll see that they are substituted/inlined into the query but not materialized and stored anywhere. With the exception of recursion, they’re more to make queries simpler to write than faster to run.

Example below

Say Query is below which is in need to be used multiple times in a Stored Procedure. SQL Server CTE Performance is same as query used in it.

SELECT A.Address, E.Name, E.Age From EmployeeAddress A
Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50

Rewriting the above query using CTE expressions will be as below

With T(Address, Name, Age) --Column names for Temporary table
SELECT A.Address, E.Name, E.Age from EmployeeAddress A
SELECT * FROM T --SELECT or USE CTE temporary Table
WHERE T.Age > 50

Common Table Expressions have the samilar functionality as a view. They are ideal within Stored Procedure where you don’t necessarily need a view defined for the system. CTE can improve readability of the Query and is very helpful.

CTEs can also be used to recursively enumerate hierarchical data.

SQL Server TSQL Table Variables

Table Variables In SQL Server: These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped. Like with temp tables, table variables also reside in TempDB. They are also generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table variables can have a primary key, but no indexes can be created on them. They don’t have any statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimizer has no way of knowing the number of rows in the table variable.

Syntax to create Table variable is

It is similar to any variable where declare variable  and set/insert values into it for Temporary storage like a temp table.

declare @Temp table (ID int, Name varchar(128))

Table Variables are like Temporary tables but have some difference which are highlighted in my article http://sqlserver-dba.co.uk/cte/cte-temp-table-temp-variables.html

SQL Server TSQL Temporary Table

Temp tables in SQL Server: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases. Temp tables can be created in all versions of SQL Server including SQL Server Express Edition.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

It is most common using temporary tables is from within a stored procedure.

It is common to use Temp table is to store the results of a SELECT INTO statement and then Insert into… Select statement.

Different Types of Temporary Tables SQL Server provides two types of temp tables based scope of the table. They are:

1.Local Temp Table
2.Global Temp Table

1.Local Temp Table

Local temp tables are only available to the current connection of the user. They are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash “#” sign.
Global Temp Table

Global Temp Table can be created by any connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.Global Temporary tables name starts with a double hash “##”

Creating Temporary Table in SQL Server

Local Temporary Table


CREATE TABLE #LocalTempTable
(ID int,
Name varchar(30),
Address varchar(300))

It Can be used like below

insert into #LocalTempTable values ( 1, 'Vamshi','India');

CREATE TABLE ##NewGlobalTempTable
(ID int,
Name varchar(30),
Address varchar(300))

It Can be used like below

insert into #NewGlobalTempTablevalues ( 1, 'Vamshi','India');

All temporary tables can be seen under Tempdb in SQL server under tables while they are in use and are stored in TempDB.

SQL Server Database Mail Configuration Step by Step

This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.

Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server

One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example

1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports

Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by following below steps

Step 1. Get the needed information handy for setting up database mail like below

SMTP Server Information. We will fully qualified name, port information, and authentication information for your smtp server. Get this from your IT Team.

It will be like mailrelay.yourcompanydomain.com and port will be like 25 and authentication.

We will need to enable Database Mail in SQL server which is not enabled default by below code

Below will tell us if its already enabled

SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'

Below TSql commands will enable the Database mail option on SQL Server

EXEC sp_configure 'show advanced options', '1';
EXEC sp_configure 'Database Mail XPs', 1;

Normally by default Service Broker is enabled but we can check this as its needed

Below will tell us if its already enabled

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

Step 2. Configuring Database Mail Using the Wizard

a. In Object Explorer, expand Management and right click Database Mail:

Click ‘Next’, then click the first option to set up Database Mail.


b. Create a Profile

Create a profile for Database mail. We can have multiple.


c. Create An Account

We will need to create a Account for Database Mail which is used by above Profile for Database Mail to work.

Email address: Say like DBA@yourCompanydomain.com
Display name: We can use SQL Server’s name from where emails are sent.
Reply email: We can use DONOTREPLY@yourCompanydomain.com or sometimes DBA Email address
Server name: Ask your IT Team for the details of your Exchange server. This smtp server we will use. Select the Port and your authentication options as given by IT Team.

d. Send a Test Email

Right click on “Database Mail” in Object Explorer and click on “Send a Test Email.” and its pretty simple to send a test email to yourself

If it doesn’t work, there’s a problem in your setup. Right click “Database Mail” again and select “View Database Mail Log” for errors needed for troubleshooting

Step 3. We will need to enable Database Mail on the SQL Server Agent for notifications.

Configuring SQL Agent to use Database Email.

Right click on the SQL Server Agent and select properties, like this:

SQL Server Agent Properties

Now Click on the Alert System Tab


Now here is where we configure SQL Server Agent to tell which database mail profile to use. Enable the mail profile, then select your mail profile. we can also set failsafe operator here.

Restart the SQL Server Agent Service to Make That Take Effect

Prince2 Project Management Simplified Revision

PRINCE2: (Project Management IN Controled Environment)  Prince2 has Foundation & Practitioner Exams. Prince2 certification value is recognized everywhere in industry.

Project: A PROJECT is a temporary organization that is created for the purpose of delivering one or more business products according to an agreed Business Case.

Many Project Management Software are available in market to use with Prince2 methodologies like MS Project Server or MS Project Management Software etc… There are some free tools also available for project management methodologies like Prince2 

Key features of PRINCE2:

•Focus on business justification
•Defined organisation structure for the project management team
•Product-based planning approach
•Emphasis on dividing the project into manageable and controllable stages
•Flexibility that can be applied at a level appropriate to the project.

6 Aspects of PRINCE2


4 Elements of Prince2

•7 Principles
•7 Themes
•7 Processes
•Tailoring PRINCE2 to a project environment.

7 Principles of PRINCE2

1.Continued Business Justification: (why to start, why to continue)
2.LEARN from experience.(learn from previous, lessons are recorded)
3.Define Role and Responsibilities (stakeholders)
4.Managed by STAGE
5.Managed by EXCEPTION
6.Focus on PRODUCT
7.Tailor to suit project environment.

7 Themes of PRINCE2

1.Business case (WHY)
2.Organization (WHO)
3.Quality (WHAT/End product quality)
4.Plans (How? How much? When?)
5.Risk (What if?)
6.Change (What is the impact)
7.Progress (Where are we now in Project? where are we going next?)

7 Processes of PRINCE2

1.Starting up a project process (SU)
2.Directing a Project (DP)
3.Initiating a Project (IP)
4.Controlling a Stage (CS)
5.Managing Product Delivery (PD)
6.Managing Stage Boundaries (SB)
7.Closing Project (CP)

Key benefits of PRINCE2

•Proven best practice
•Applied to any type of projects
•Promoting effective communication (same language) and control
•Explicit recognition of RESPONSIBILITIES
•Managed by exception – economical/wise use of management time
PRINCE2 is like a diagnostic tool.

Stakeholders in Project

1.Business (People who endorse the project)
2.Users (People who will use the output of the project)
3.Suppliers (provide resources both INTERNAL or EXTERNAL or both)

Project Logs and Registers used:

1.Risk Register
2.Quality Register
3.Issue Register
4.Configuration Item Records
5.Lesson Log
6.Daily Log


•Checkpoint Report: team managers to the project manager
•Highlight Report: project manager to the project board
•End Stage Report: Project Manager to the Board
•End Project Report – Project Manager to Board
•Lessons Report – Project Manager

For more info refer to website Prince2 .com

SQL DBA blog is dedicated in providing the highest quality and most in-depth SQL Server material to boost your career or to make one more productive.