Tag Archives: Sql Server Tutorial

Indexes in Sql Server – Part I

If you need your query to run faster, then you must know about the indexes. In this series of articles on Indexes, I will walk you through Sql Server Indexes with examples and explain how Sql Server Indexes come handy to resolve query performance issue. This series of articles will be helpful for both Sql beginners and advance users. I will try to keep this series of articles on Indexes as simple as possible by keeping in mind the beginner audience.

In this Part-I article of the series of articles on Sql Server Indexes, I will explain how to enable the execution plan, IO and Time statistics. It will also cover HEAP tables and problems while querying data from it.

The main purpose of Sql Server indexes is to facilitate the faster retrieval of the rows from a table. Sql Server indexes are similar to indexes at the end of the book whose purpose is to find a topic quickly.

To understand Sql Server Indexes with example, let us create a Customer table as shown in the below image. Execute the following script to create the Customer table with sample 100,000 records.

--Create Demo Database
CREATE DATABASE SqlHintsIndexTutorial
GO
USE SqlHintsIndexTutorial
GO
--Create Demo Table Customer
CREATE TABLE dbo.Customer (
    CustomerId INT IDENTITY(1,1) NOT NULL,
    FirstName VARCHAR(50), LastName  VARCHAR(50),
    PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
    CreationDate DATETIME
)
GO
--Populate 1 million dummy customer records
INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 1000000 REPLACE(NEWID(),'-','') FirstName, REPLACE(NEWID(),'-','') LastName, 
    CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10))
	PhoneNumber,
    REPLACE(NEWID(),'-','') + '@gmail.com' EmailAddress,     
    DATEADD(DAY,CAST(RAND(CHECKSUM(NEWID())) * 3650 as INT) + 1 ,'2006-01-01') CreationDate
FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
GO
--Update one customer record with some know values
UPDATE dbo.Customer
SET FirstName = 'Basavaraj', LastName = 'Biradar', 
    PhoneNumber = '4545454545', EmailAddress = 'basav@sqlhints.com'
WHERE CustomerId = 50000
GO

Enable Execution Plan

Enable the display of query execution plan in Sql Server Management Studio (SSMS) by clicking on the Actual Execution Plan option as shown in the below image. You can as well enable/disable the Actual Execution Plan by pressing the key stroke CTRL + M.

Enable Actual Execution Plan

What is execution plan?

Beginners may be wondering what is this execution plan? In Sql Server execution plan is nothing but the visual representation of steps or operations which Sql Server performs internally to execute a query and return the result.

Enable the display of IO and Time Statistics for a Query

Execute the following statements to enable both IO and Time statistics in one statement.

SET STATISTICS IO,TIME ON

The SET STATISTICS IO ON statement displays the disk activity performed to execute the Sql query. In Sql Server table data is stored in 8 KB data pages on the disk. Whenever we try to read the data from a table, the Sql Server query engine first checks whether data page is already in memory. If the page is already in memory then sql uses that, this operation is shown as logical read in SET STATISTICS IO output of the query. If sql doesn’t find the data page in the memory then it reads it from the disk, this operation is shown as physical read in SET STATISTICS IO output of the query. Both logical and physical reads of a data page is a costly operation. A query should have minimal page reads. The SET STATISTICS TIME ON output of the query shows the time taken by the query to complete the execution. In the following sections, I will explain both these settings results with an example. It will be more clear once you go through the examples in the following sections.

Execute the following query to get the details of a Customer whose CustomerId is 50000.

SELECT * 
FROM dbo.Customer WITH(NOLOCK) 
WHERE CustomerId = 50000

From the above result, we can see that we have one customer record in the Customer table with CustomerId as 50000.

Let us now go to the Execution Plan tab of the result and see the execution plan of the query.

From the above execution plan we can see that Sql Server is doing the table scan of the Customer table. Table scan means Sql server is reading all the data pages and rows of the table to find the records in the customer table. Even after finding the first record with CustomerId = 50000, Sql server will not stop searching till it reads the last row as it doesn’t know that there is only one record with CustomerId = 50000 unless it reads the last row.

Let us now go to the Messages tab and see the IO and Time Statistics.

From the above IO and Time statistics of the query, we can see that it is reading 18,182 data pages. Each data page is of 8KB size, so to get one Customer record Sql server is reading 142 MB of data. And it is taking 250 millisecond CPU time.

From the above result we can observe that Sql Server is doing lot of IO and consuming CPU resource to fetch just one Customer record. If you see such things in your environment on any transactional table then there is something terrible wrong. You should immediately solve such problems. next sections will guide you through how such problems can be solved using Indexes.

HEAP Table

A table without a Clustered Index is called as a Heap Table. Frankly speaking we should never have a HEAP table in an Online Transaction Processing System (OLTP). There are some .01% edge case scenario where we may go for heap table if we need faster DML (INSERT, UPDATE, DELETE) operations. If we see any transactional table without a Clustered index then you can assume the table is badly designed.

The Customer table at the current state is a HEAP table, as it doesn’t have any Clustered index. In the above execution plan we have seen that Sql Server is doing a Table Scan, we see this operation of ” Table Scan” only for the HEAP Table. In case of a Table without a Clustered index , table data is stored in a heap structure. It means data is stored in an unordered way.

What is the Solution for the above query problem where it is reading 142 MB of data and using a quarter of a second to return just one customer record? Clustered Index is the Solution for the above query problems. In the next article in this series of articles on Indexes I will explain how Clustered Index solves these problems. Will post this article on Clustered Index next weekend, till then bye and be safe.

Querying Data Using SELECT In Sql Server

Sql Server Tutorial Lesson 5: Querying Data Using SELECT

SELECT is one of the basic construct of Sql Server, which basically facilitates the retrieval of information from Tables. This lesson covers the following Topics with extensive list of real-time examples.

  1. Introduction to SELECT Statement
  2. Using WHERE Clause
  3. Using Boolean Operators AND, OR and NOT
  4. Using LIKE Predicate
  5. Using BETWEEN Clause
  6. Table and Column Name Alias
  7. Using ORDER BY Clause
  8. Concatenation

To demo these features let us first create the Employee table with seven employee records as depicted in the below image by using the following script:

QueryingDataUsingSELECT

CREATE DATABASE SqlHintsQueryingDataDemo
GO
USE SqlHintsQueryingDataDemo
GO
CREATE TABLE dbo.Employee
(EmployeeId INT, Name NVARCHAR(50),
DOJ DateTime,City NVarchar(50), Salary Money)
GO
INSERT INTO dbo.Employee VALUES(1,'ShreeGanesh Biradar','2011/12/18','Pune',45000)
INSERT INTO dbo.Employee VALUES(2,'Sandeep Patil','2010/02/24',NULL,55000)
INSERT INTO dbo.Employee VALUES(3,'Abhi Akkanna','2008/03/22','Bangalore',89000)
INSERT INTO dbo.Employee VALUES(4,'Sandy Thomas','2008/04/28','Delhi',39000)
INSERT INTO dbo.Employee VALUES(5,'Kalpana Biradar','2013/11/15','Bangalore',60000)
INSERT INTO dbo.Employee VALUES(6,'Basav Biradar','2012/11/15','Bangalore',54000)
INSERT INTO dbo.Employee VALUES(7,'Deepak Kumar','2006/04/08','Hyderabad',75000)

1. Introduction to SELECT Statement

Demo 1: Retrieve All the Records from the Employee Table

SELECT * FROM dbo.Employee

RESULT:

QueryingDataUsingSELECTDemo1

Demo 2: Retrieve only the Required Information

In most of the scenario’s we don’t need the complete table data, so it is always best practice to include the columns which are required in the Select query to reduce unnecessary data transfer over the Network and IO’s.

SELECT EmployeeId,Name,City FROM dbo.Employee

RESULT:

QueryingDataUsingSELECTDemo2

2. Using WHERE Clause

Demo 1: Get all the Employees whose City is Bangalore

SELECT * FROM dbo.Employee WHERE City = 'Bangalore'

RESULT:

UsingWHEREClause1

Demo 2: Get all the Employees whose city is not Bangalore

SELECT * FROM dbo.Employee WHERE City <> 'Bangalore'

RESULT:

UsingWHEREClause4

Note: This query ignored the employees whose City column value is NULL, because NULL value can’t be compared with some value (i.e. Null means unknown value, so it can’t be used to compare with any know values).  The only operation we can do with NULL is we can check whether it is NULL or NOT NULL as shown in the below Demos 3 an 4.

Demo 3: Get all the Employees whose city column has some value (i.e. employees whose City column value is NOT NULL)

SELECT * FROM dbo.Employee WHERE City is NOT NULL

RESULT:

UsingWHEREClause2

Demo 4: Get all the Employees whose city column value is null

SELECT * FROM dbo.Employee WHERE City is NULL

RESULT:

UsingWHEREClause3

3. Using Boolean Operators AND, OR and NOT

Demo 1 Using Boolean Operator AND: Get all the Employees whose City is Bangalore and Salary is above 55000

SELECT * FROM dbo.Employee 
WHERE City = 'Bangalore' AND Salary > 55000

RESULT:

UsingBooleanOperatorAND

Demo 2 Using Boolean Operator ORGet all the Employees whose city is either Bangalore or have salary above 62000

SELECT * FROM dbo.Employee 
WHERE City = 'Bangalore' OR Salary > 62000

RESULT:

UsingBooleanOperatorOR

Demo 3 Using Boolean Operator NOT: Get all the Employees whose city is other than Bangalore and Hyderabad

SELECT * FROM dbo.Employee 
WHERE City NOT IN ('Bangalore','Hyderabad')

RESULT:

UsingBooleanOperatorNOT

Note: The above query didn’t return the employees whose is CITY column value is NULL. As explained previously NULL means unknown value, it can’t be compared with a known value.

4. Using LIKE Predicate

Demo 1: Get all the Employees who have word deep anywhere in their Name.

SELECT * FROM dbo.Employee WHERE Name Like '%deep%'

RESULT:

LIKE1

Demo 2: Get all the Employees whose Name starts with the word dee

SELECT * FROM dbo.Employee WHERE Name Like 'deep%'

RESULT:

LIKE2

Demo 3: Get all the Employees whose Name starts with the character a or b or c.
Note the below three SELECT statements are equivalent

SELECT * FROM dbo.Employee WHERE Name like '[a-c]%'
SELECT * FROM dbo.Employee WHERE Name like '[abc]%'
SELECT * FROM dbo.Employee WHERE Name like '[a,b,c]%'

RESULT:

LIKE3

Demo 4: Get all the Employees whose Name starts with the character a or b or c and second character must be a

SELECT * FROM dbo.Employee WHERE Name like '[a-c][a]%'

RESULT:

LIKE4

Demo 5: Get all the Employees whose Name is not starting with letter a or b or c

SELECT * FROM dbo.Employee WHERE Name like '[^a-c]%'

RESULT:

LIKE5

5. Using BETWEEN Clause

BETWEEN clause can be used to compare range of values.

Demo 1: Get all the Employees whose Salary is between 45000 to 60000.

SELECT * FROM dbo.Employee
WHERE Salary between 45000 AND 60000

RESULT:

UsingBETWEENClause1

Demo 2: Above Demo 1 query can also be written without between Clause as below:

SELECT * FROM dbo.Employee 
WHERE Salary >= 45000 AND Salary <= 60000

RESULT:

UsingBETWEENClause2

6. Table and Column Name Alias

Sql Server provides an option to give temporary alias name for the Table and it’s Column Names in the query. In that way we can give an meaning full alias to the Tables. And if two tables are joined both have the same column name in it, then we have to write two part column names i.e. [Table Name].[Column Name] otherwise Sql server gives an ambiguous column name error. If table name is too long it looks to clumsy, so better give a short alias name for the table and use this alias table name in the Two part column name specification to avoid ambiguity.

Demo 1: Table Alias Name demo. In this demo example for Employee Table the alias name specified is E in the FROM clause. Because of this we can access the employee table column names by prefixing E. 

SELECT E.Name, E.City FROM dbo.Employee E

RESULT:

TableAndColumnNameAlias1

Demo 2: Table and Column Name Alias demo. In this demo example for Employee Table the alias name specified is E in the FROM clause. Because of this we can access the employee table column names by prefixing E. . And for the Name column we are specifying the alias name as ‘Employee Name’ and for City column the alias name is ‘Employee City’

SELECT E.Name AS 'Employee Name', E.City AS [Employee City]
FROM dbo.Employee AS E

RESULT:

TableAndColumnNameAlias2

7. Using ORDER BY Clause

ORDER BY Clause can be used to sort the result set based on the Column Value.

Demo 1: Sort the Employee records based on Name column value. The default sorting of the ORDER BY clause is in the Ascending Orders.

SELECT Name, City FROM dbo.Employee
ORDER BY Name

RESULT:

UsingOrderByClause4

Demo 2: Sort the Employee records based on Name column value in the descending order. Here using the keyword DESC in conjunction with ORDER By clause to sort the records by Name in Descending order.

SELECT Name, City FROM dbo.Employee
ORDER BY Name DESC

RESULT:

UsingOrderByClause2

8. Concatenation

‘+’ symbol  is used for concatenating string values

Demo 1: Concatenate Name and City Column Value.

 

SELECT Name + City as [Name & City]
FROM dbo.Employee

RESULT:

ConcateNation1

Note:  If ‘+’ symbol is used to concatenate the values, then if one of the values is NULL then resultant concatenated value will also be NULL.

Demo 2: One way of avoiding NULL as the RESULT of concatenation if one of the value of the to be concatenated is NULL is to use the ISNULL function like below. Here ISNULL function returns an empty string if the value is NULL otherwise the specified value.

SELECT ISNULL(Name,'') + ISNULL(City,'') AS [Name & City]
FROM dbo.Employee

RESULT:

ConcateNation6

Demo 3: Add an empty space between Name and City.

SELECT ISNULL(Name,'') + ' ' + ISNULL(City,'') AS [Name & City]
FROM dbo.Employee

RESULT:

ConcateNation5

You may like to go through the string function CONCAT() which is introduced in Sql Server 2012 for concatenation.

Joins In Sql Server

Sql Server Tutorial Lesson 6: JOINS in Sql Server with Examples

Join facilitates the retrieval of information from multiple tables. In Sql server we have following 6 types of Joins:

  1. INNER JOIN
  2. LEFT OUTER JOIN
  3. RIGHT OUTER JOIN
  4. FULL OUTER JOIN
  5. CROSS JOIN
  6. SELF JOIN

To demo these features let us first create the Customers and Orders table as depicted in the below image by using the following script:

Joins1

CREATE DATABASE SqlHintsJoinDemo
GO
USE SqlHintsJoinDemo
GO
--Create Customers Table and Insert records
CREATE TABLE Customers 
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) VALUES(1,'Shree')
INSERT INTO Customers(CustomerId, Name) VALUES(2,'Kalpana')
INSERT INTO Customers(CustomerId, Name) VALUES(3,'Basavaraj')
GO

--Create Orders Table and Insert records into it
CREATE TABLE Orders
(OrderId INT, CustomerId INT, OrderDate DateTime)
GO
INSERT INTO Orders(OrderId, CustomerId, OrderDate) 
VALUES(100,1,Getdate()-1)

INSERT INTO Orders VALUES(200,4,Getdate())
INSERT INTO Orders VALUES(300,3,Getdate()+1)
GO

[ALSO READ] Joining Two Tables without any Common Column between them

1. INNER JOIN in Sql Server

Inner Join returns only the matching rows in both the tables (i.e. returns only those rows for which the join condition satisfies).

Demo 1: As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Inner Join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 only.

SELECT * 
FROM  Customers C 
		INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:
INNER JOIN

Demo 2: Below Inner Join query demonstrates how to get name of all the  Customer who have at-least one order in the Orders table.

SELECT C.Name 
FROM  Customers C 
		INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:
Name
——————-
Basavaraj
Shree

2. Left OUTER JOIN in Sql Server

Left Outer Join/Left Join returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

Demo 1: As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Left join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result.

SELECT * 
FROM  Customers C 
		LEFT OUTER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:
LEFT OUTER JOIN1

Demo 2: Below query demonstrates how to get the name of the Customer who don’t have Orders using LEFT OUTER JOIN.

SELECT C.CustomerId, C.Name 
FROM  Customers C 
		LEFT OUTER JOIN Orders O
			ON O.CustomerId = C.CustomerId
WHERE O.OrderId IS NULL

RESULT:
CustomerId Name
———– ————————————————–
2 Kalpana

3. RIGHT OUTER JOIN in Sql Server

Right Outer Join/Right Join returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

Demo 1: As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result.

SELECT *
FROM  Customers C 
		RIGHT OUTER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:
RIGHT OUTER JOIN

Demo 2: Below query demonstrates how to get the Orders with a CustomerId, for which we don’t have a mapping any record in the Customers Table:

SELECT O.*
FROM  Customers C 
		RIGHT OUTER JOIN Orders O
			ON O.CustomerId = C.CustomerId
WHERE C.CustomerId IS NULL

RESULT:
OrderId CustomerId OrderDate
———– ———– ———————–
200 4 2014-01-31 23:48:32.853

4. FULL OUTER JOIN in Sql Server

It returns all the rows from both the tables, if there is no matching row in either of the sides then it displays NULL values in the result for that table columns in such rows.

Full Outer Join = Left Outer Join + Right Outer Join

Demo 1: As per the data in our Demo tables the Customer with CustomerId 2 doesn’t have order in the Orders table. So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Orders table columns for the Customer with CustomerId 2.

And for the Order with OrderId 200 having CustomerId 4 doesn’t have a matching record in the customer table with CustomerId 4. So in the result of FULL Outer join between Customers and Orders table on the CustomerId column will have NULL values for the Customers table columns for the Order with OrderId 200.

SELECT *
	FROM Customers C
			FULL OUTER JOIN Orders O
				ON O.CustomerId = C.CustomerId

RESULT:
FULL OUTER JOIN

Demo 2: Below query demonstrates how to get the list of all the Customers without Orders and also the Orders which doesn’t have corresponding customer in the Customers Table.

SELECT *
	FROM Customers C
			FULL OUTER JOIN Orders O
				ON O.CustomerId = C.CustomerId
WHERE C.CustomerId IS NULL OR O.OrderId IS NULL

RESULT:
FULL OUTER JOINDemo2

5. CROSS JOIN in Sql Server

Cross join is also referred to as Cartesian Product. For every row in the LEFT Table of the CROSS JOIN all the rows from the RIGHT table are returned and Vice-Versa (i.e.result will have the Cartesian product of the rows from join tables).

No.of Rows in the Result of CRoss Join = (No. of Rows in LEFT Table) * (No. of Rows in RIGHT Table)

SELECT *
FROM Customers C
		CROSS JOIN Orders O

RESULT:
CROSS JOIN

6. SELF JOIN in Sql Server

If a Table is joined to itself using one of the join types explained above, then such a type of join is called SELF JOIN.

To demo this join let us create an Employee table with data as depicted in the below image by the following script:

SELF JOIN

CREATE TABLE Employee
(EmployeeId INT, Name NVARCHAR(50), ManagerId INT)
GO
INSERT INTO Employee VALUES(1,'Shree',1)
INSERT INTO Employee VALUES(2,'Kalpana',1)
INSERT INTO Employee VALUES(3,'Basavaraj',2) 
INSERT INTO Employee VALUES(4,'Monty',2) 
GO

Demo 1: Now if we need to get the name of the Employee and his Manager name for each employee in the Employee Table. Then we have to Join Employee Table to itself as Employee and his Manager data is present in this table only as shown in the below query:

SELECT E.EmployeeId, 
       E.Name 'Employee Name', M.Name 'Manager Name' 
FROM dbo.Employee E 
		INNER JOIN Employee M
			ON M.EmployeeId = E.ManagerId

RESULT:
SELF JOIN RESULT

[ALSO READ] Joining Two Tables without any Common Column between them