Archives: SQL Server Tutorials

Data Integrity

After having a good discussion on “Table Relationships” in previous chapter, we are going to discuss Data Integrity in this chapter. First, we would discuss the term Data Integrity, followed by a discussion on data integrity types, and finally, we would go through an image which summarizes the data integrity concepts in one sight view. Lets start the discussion with the definition first.

Data Integrity

In relational database management systems, data integrity ensures the accuracy, reliability and consistency of the data during any operation like as data manipulation, data retrieval, data storage, backup or restore operation etc. It also guarantees that the recording of data is accurate and as intended. Having any bad or unintended data in the database, is a failure of data integrity. For example, having sales detail in Sales table of a product not available in Product master is a failure of referential integrity.

Below … More

Table Relationships

We have already been familiar with “DBMS, RDBMS and SQL Server” in previous chapter. In this chapter named “Table Relationship”, we are going to understand the relationship between tables. In relational database management systems, tables are used to store every information in the form of columns and rows. Before moving to the topic “Table Relationship”, we would have a look on the definition of Database, Table, Column and Row, as these entities participate in relationships.


Database is an organized collection of data for fast processing on these data. These data are arranged in such form that enables fast retrieval of information and manipulation on these data. In RDBMSs, database organizes its data in relational form only using tables, column and rows. We can define relationships between these tables.


A table is a collection of related data in a structured form using columns and rows within a database. … More

DBMS, RDBMS and SQL Server

Lets start this tutorial with the chapter “DBMS, RDBMS and SQL Server”. Before starting T-SQL part of the SQL Server tutorial, we will learn basic concepts of database systems. In this chapter, we will explore “Database management systems” and its relational extension “Relational database management systems”. We will also have an introduction to Microsoft SQL Server at the end of the chapter. This chapter has necessary images to explain the concepts in pictured way which will help you to understand the topics with ease.

Database management system (DBMS)

Database management system is a software used to define, record, query, update and manage data in a database. DBMS stores data in a structured way (relational, hierarchical, flat files, objects etc), and executes operations requested by various users on these data. It interacts with user, receives commands, and runs it against the database. DBMS can interact with some other applications, if required. … More

SQL Server Tutorial – Prerequisite

To start with this tutorial, you need to download and install SQL Server (Latest version recommended) from Microsoft and to do some hands-on throughout this tutorial and for assignments purposes, you need to download and attach AdventureWorksDatabase also. Below you have some links, which might help you out in these prerequisites.


Just two things for this “SQL Server Tutorial” and you all set to go with this tutorial.

  1. Install SQL Server – [Recommended – SQL Server 2014 or 2012]
  2. Download and attach “AdventureWorksDatabase”

If you don’t have installed SQL Server yet, no worries, here are the links which may help you.

To download SQL Server 2014;

For Step by Step installation of SQL Server 2014

To download AdventureWorks2014 Database

How to restore “AdventureWorks2014” Database

Download the MS Word file from below link and follow the instructions mentioned in “Install Adventure Works 2014 OLTP database from More

SQL Server Tutorial – About

SQL Server Tutorial – Step by Step

About this tutorial

You are welcome at my first tutorial, titled as “SQL Server Tutorial“. If I am not wrong, it seems that you are keen to learn SQL Server database programming. This “SQL Server Tutorial” is specially written for novice programmers. It starts with simple Database concepts and takes a deep dive into T-SQL. You just have to follow this tutorial to learn the skill. Well organised chapters in easy to understand language, complemented with good assignments to work on, should help you acquire the knowledge swiftly.

This SQL Server Tutorial written for Microsoft SQL Server database programming and has its primary focus in T-SQL (Transact SQL, Microsoft proprietary extension to SQL). I have worked a lot to make the content easy to understand, yet impressive. You will get a lot of real life examples, images to understand the concepts … More

Mutable logical condition in CASE expression 6

In our previous blog post Different data types in case expression, we have explored the behavior of CASE expression in case of different data types in “THEN” and “ELSE” part. Now in this post “Mutable logical condition in CASE expression”, we are going to unfold the behavior of CASE expression when we have a non deterministic function in logical expression (WHEN part).

Mutable logical condition in CASE expression

Have a look on the below query:

--Query 1
WHEN 0 THEN 'Case 0'
WHEN 1 THEN 'Case 1'
WHEN 2 THEN 'Case 2'


In above query, logical expression “ABS(CHECKSUM(NEWID())) % 3” of CASE expression, has a non deterministic function NEWID() wrapped inside a CHECKSUM function to generate some random number on fly. Going further the CHECKSUM value has also wrapped inside a ABS function which insures a positive number. Finally there is a … More

Different data types in case expression 5

In this blog post titled as “Different data types in case expression”, we will explore the behavior of CASE expression when dealing with different data types in THEN part (true) and ELSE part (false) of CASE expression. We will limit the scope of discussion of this post to data conversion issue only. If you are interested to explore more about the CASE expression, visit this MSDN link.

What is the Return type of CASE expression

As per MSDN, CASE expression returns the highest precedence type from the given set of types in THEN and ELSE part (true and false part). To explore data type precedence, you can visit Data Type Precedence link here.

Lets play with a simple demo to see this return type in action. This demo is for test and may look a bit silly. In below SQL query, we have two variables, @RetValThen as DATETIME … More

Unique Key is Clustered or Nonclustered 2

An easy question for you “Unique Key is Clustered or Nonclustered”? Lets explain in more detail, When I create a Unique key on a table without explicitly defining the type of index(clustered or nonclustered), which type of index will be used to accommodate the key?

I have already written a blog on behavior of primary key for clustered and nonclustered index type selection in such kind of scenarios and here is the link. In this post titled as “Unique Key is Clustered or Nonclustered”, we will explore the behavior of Unique Key in regards of Clustered and Nonclustered index type selection. Before going forward, I would like to focus on the differences between Primary Key and Uniquer Key below.

Difference between Primary Key and Unique Key

 Primary key

  1. It is a column or set of columns, used to uniquely identify each row of the given table.
  2. NULL values

Rename Table and Change Schema with SP_RENAME

In this post “SP_RENAME table with schema change” I will share a trick to rename a table using sp_rename and then transfer it schema. As we know that we can use SP_RENAME system stored procedure to rename user created objects like tables, procedures, functions, views, indexes, columns, user defined types, CLR user defined types etc in current database.

For example, if you have a table with name “TestTableA” in schema “dbo” and want to change its name to “TestTableB”, you can use below command;

SP_RENAME 'dbo.TestTableA', 'TestTableB'

But in case you want to change the table name with schema change using procedure “sp_rename”, putting “Schemaname.Tablename” as new name in second parameter, will not work. What will happen if we put “Schemaname.Tablename” as new name and why it will happen, I have explained it in next section of this blog post.

Schema.TableName as New Name in sp_rename will not work

In … More

NOLOCK table hint 9

A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.

What is NOLOCK

NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.

It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true … More