Index in SQL Server

Lets understand disk structure and how table data stores on disk.

In above diagram, orange color section is tracks and yellow color section is sector. In my case based on above diagram, I have disk with 6 sectors and 3 tracks. Track 1 (blue color) is inner circle from above diagram with number 1 shown above which is 2nd inner circle.
Gray color section is block. Block can be identified with the help of sector and track. Above gray highlighted block is from sector 4 and track 2.
Each block from disk has specific memory in my case lets assume 100 byte. Each block in disk is of size 100 bytes. Each block contains 100 offset shown below starts from 0-99.

Now lets understand above concept while storing table data. Suppose, you have database table having 100 rows, and for each 5 rows for storing into disk(Rom) requires 1 block.
Total blocks required to store entire table = 20 blocks
To search any row from this table, It might I have to search entire blocks which is called as table scan or heap. I have to go through and check all 20 blocks where table data stored which is time consuming task.
To avoid this, Index come into picture. Index hold primary key value/index column value as key and base address as value.
Index table also stored on disk and each 25 rows from index takes 1 block to store. Total records from index takes 4 blocks.
Now to find any specific records from table if index available, I need to go through 5 block, 4 blocks from index and 1 block is from actual block where row stored. I can directly find specific row address using index block.
Instead of scanning 20 blocks I have to scan 5 block which reduces time but also increase some space in disk to manage index.
Lets understand this with diagram shown below.

But as size of table increase, index size also increase and needs to search entire index, to avoid this multilevel index come into picture.

For storing database records, B-tree is used. Above diagram is just for understanding how actually multilevel index and DB table are in relation to reduce time reading data from database table.
B tree is extension to M-search tree, which is again created based on above concept of multilevel index. Support I want to retrieve
Lets turn diagram and understand with tree structure.

There are two major type of indexes in MS SQL Server
1. Cluster Index
2. Non Cluster Index

Cluster Index : Clustered index stored table data physically in sorted order. As clustered stored table data physically sorted order only one clustered index can be created per table. By default clustered index get created if primary is created for table. Internally it uses B-tree data structure and leaf node of B-tree contains table data.
Insert, update and delete operation become slow as data stored physically in sorted order. For insert or update operation it check free space available in page or not and if space not available in that case it make space available and insert/update data.
To avoid this, while creating index make sure you provide fill factor. Fill factor is percentage of occupied memory in page. Suppose you provide 80 as fill factor in that case 80% page filled and remaining 20% remains free.

Non Clustered Index : Non Clustered index not sort data physically in table. Non Clustered index can be created more than one and max 999 per table. Non clustered index also follow B-tree to stored data but leaf nodes of B-tree contains reference of page extend which contains table data.
Lets create table with index and without index to compare performance.

Right click on above query and select menu option display estimate execution plan.

SQL Join

In this article I will explain about joins in SQL server, join means combine/merge tow or more different table in SQL server based on certain join/filter condition. Join applied on two or more table when logical relation exists between SQL server tables. Logical relation means SQL server tables containing Primary and foreign key relation.

Join can also be applied on tables which may not be in primary and foreign key relation but data in both tables are more of a like parent-child (primary – foreign key relation). Lets understand with simple example.

Types of Join
1. Inner Join
2. Left Join
3. Right Join
4. Full Join
5. Cross Join


Before looking into types on join, lets create required tables and insert data.

CREATE TABLE [dbo].[tblDepartment]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NULL
)


CREATE TABLE [dbo].[tblEmployee]
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NULL,
[DepartmentId] INT NULL
)


I have successfully created tblDepartment and tblEmployee. As discussed, above two tables not following parent-child relationship (primary and foreign key). Let’s insert some dummy data shown below.

Insert into tblDepartment values (1, 'IT')
Insert into tblDepartment values (2, 'Sales')
Insert into tblDepartment values (3, 'HR')
Insert into tblDepartment values (4, 'Finance')
Insert into tblDepartment (Id, Name) values (5, 'Accounts')


Insert into tblEmployee values (1, 'Salim', 4)
Insert into tblEmployee values (2, 'Rohit', 1)
Insert into tblEmployee values (3, 'John', 3)
Insert into tblEmployee values (4, 'Sameer', 2)
Insert into tblEmployee values (5, 'Saddam', 2)
Insert into tblEmployee values (6, 'Sandeep', 3)
Insert into tblEmployee values (7, 'Roma', 1)
Insert into tblEmployee values (8, 'Puja', 4)
Insert into tblEmployee (Id, Name) values (9, 'Ramesh')


Inner Join : It return all the records from both table which satisfy Join condition or common data from both the table based on join condition.

The above query will return common data from both the tables. If you notice I have inserted 9 rows in employee table but only 8 rows showing which are common from both the table and matching join condition. In Department table I have inserted 5 rows out of only 4 rows which are common shown in query result.

Left Join : This join returns all data from left table and common/matching data from both tables.

As shown in above figure, left join returns all data from left table (tblEmployee) and common data from both the tables.

Right Join : This join returns data from right table and common table from both the tables.

As shown in above figure, left join returns all data from right table (tblDepartment) and common data from both the tables.

Full Join : This type returns common data from both tables and also returns remaining data from both left and right tables.

Cross Join : This join is also called as Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables. It means each row from table one combines with each row of the table two.
Number of rows return in cross join is multiplication of number of rows from table one and table two.

Number of rows from table one * Number of rows from table two = Cross Join [ 9 * 5 = 45 ]

Part 11 : Token Based Authentication In Web API

In this article I will show you how to authenticate web api using JWT token. JWT stands for Json Web Token. I have created separate blog to explain about what is JWT and how it work please visit the blog from here.
Before proceding this tutorial I will strogly recommend to read previos article to have good understanding on asp.net Core API.
Create asp.net core web api project and create Model class shown below.

Create EmployeeDbContext class and inherit from IdentityDbContext.
Identity is framework from Microsoft which provide default implementation for Authentication and Auhrization with minimal configuration.

Add connection string in appSettings.Json file and open startup file to configure SQL server.

Add-Migration and Update-Database command from package manager console and open SQL server Management studio to check created database with default tables from Identity framework.

Create Account Controller with Login method shown below. For more detail of Token based authentication visit my Token based Authentication blog.

In above screen I have written code in Login method to generate token and this token needs to validate in each incoming Http request. StartUp.cs file is best place to authenticate as code from this class execute for each incoming request. Add code for token validation under configuration service highlighted below.

Open postman and enter URL for Login, provide username and password from body.

Login method returns token generated, this token can be used while accessing authorized data from API. Now Create new controller with below code.

Open Postman and copy Token generated from Login request above. Copy value of Token property and not expiration property.
Select Authorization highlighted below and paste copied token from Login response and click on send. If Token is valid it will return message ‘For Admin Use’ highlighted below.

Part 10 : Consuming API from Console Application

#5 Minutes Of Reading

In this article, I will show you how to consume web API from console application and asp.net core web application in upcoming article. I’m using web API project created in Part 7 of Web API series and code available here.
I have two API method Get and Get with specific Id shown below and consuming below methods in this article.

Create core Console application ConsumingRest_API and create class Users under it with below piece of code.

Above method from class Users under console application is responsible to call Get method of API.
HTTPClient is class from System.Net.Http namespace responsible to call web api method in console application. To call API method HttpClient expect URL of web api hosted on web server, Media type (Accept is a request header value for informing server what format client support) in my case I choose application/json.
Client.GetAsync() method call Get method and I’m reading data in string, you can also get it in complex type which I will show you in later article.
I shown you how to call Get method of API which return list of users from API, let’s see how to call Get method with specific Id.

Above two methods from Users class is ready to consume API methods. Below piece of code call methods from Users and show API response on console.

In upcoming article I will show you how to consume Web API in web application. I will also walk you through how to consume API with Bearer Token in upcoming articles.

Part 9 : Accessing Values From Different Configuration

#5 minutes of reading

In this article I will show you, how you can configure properties like configuring properties in web.config in old asp.net framework. Here in Asp.Net core we don’t have web.config but core support configuring properties from multiple files like AppSetting.Json, Environment variable, User secrete, command line argument and custom file created to store configuration. Will check all different configuration in asp.net core one by one. Let’s start with appsettings.json.
I have created web API project shown below and created Configurationcontroller.

Open appsettings.json file and add config property shown below.

Open ConfigController.cs file and Inject IConfiguration interface which is responsible for reading config properties from different config sources.
For now I’m reading value from appSettings.json later will show you how to read value from User secrete, environment variable and custom file.
For user secret, right click on project-> Manage User Secrets and add config property shown below. User secrets works on development environment.

For environment variable, right click on project -> properties->Debug, shown highlighted in below screen.

Run application and check response.

I have added below highlighted keys in appSettings.json

Implement below Http get method which return list of available config properties under Configuration controller.

In above screen, API return all configuration key available and to get specific key value from API, call get method with key name shown below.

I shown you asp.net core provide variety of different files for configuration. Advantage of this is if you are in development environment you can configure connection string in User secret or environment variable, for staging and propduction connection string(environment variable) should be different.
Core also provide concept of environment based files. You can create appSettings.{Environment}.Json. based on Environment specific appSetting file used for reading configured values.

Part 8 : EF Core in Web API

#5 Minutes Of Reading

In this article I will show you how to implement entity framework and how useful dependancy injetcion, I’m moving to database data from in memory data without affecting controller code. This is the advantage of using DI in application. You will came to know as later in this course. Source code available here Change model name from User to Employee shown below.

Add class EmployeeDbContext which is resposible add/modify SQL database. Property with type DbSet represent database table as property in c#.

Open appSettings.Json file and add connection string here.

Open Startup.cs file and add below highlighted line of code to configure EF core in ConfigureService method.

Everything is set up to create database with employee table. From visual studio go to tools -> NugetPackageManager -> PackageManagerConsole
Enter Add-Migration command under package manager console window shown highlighted.

I have commented code for repository ‘EmployeeRepository’ is resposible to read data from in-memory data and EmpRepository is responible for reading data from EF core.
The benefit of using dependancy injection, high level object not change, here in this Employee controller code is same for EmployeeRepository (in-memory) and EmplRepository(Ef Core). Changes done in backend and controller referencing interface which is common for both repository.
Run application and add record for employee in database table using POST method.

Create EmpRepository shown below which inherit same interface which I used for in-memory data.

Update Startup.cs to refer updated repository.

Now this is the power of dependancy injection, commented code with EmployeeRepository is resposible to read data from in-memory data and EmpRepository is responible for reading data from EF core.
The benefit of using dependancy injection, high level object not change, here in this Employee controller code is same for EmployeeRepository (in-memory) and EmplRepository(Ef Core). Changes done in backend and controller referencing interface which is common for both repository.
Run application and add record for employee in database table using POST method.

Click on POST button, again click on TryOut button, modify data shown below highlighted and click on execute button.

Data added in database table.

Add few more records for employee and test all remaining Http post methods implemented in Employee controller.