Thursday, February 16, 2017

Nonclustered Index on Clustered Index in SQL Server

We know that Indexes have a single root node structurein SQL Server. SQL Server always uses this root node as the starting point for traversing an index. SQL Server always ensures that the key columns are unique for both types of index. Nonclustered indexes have the same B-Tree structure as clustered indexes only with one significant difference. The leaf node of the nonclustered index contains key values not the actual data. These key values map to pointer or clustering keys that locate rows in the data pages.
How does Nonclustered index implement?
The implementation of the nonclustered index depends on whether the data pages of a table are managed as a Heap or as a clustered index.
  1. Heap based Nonclustered Index structure in SQL Server - If nonclustered index is built in a heap then SQL Server uses pointers in the leaf node index pages that point a row in the data pages.
  2. Nonclustered Index based on Clustered Index Structure- If we have a table with clustered index, SQL Server builds a nonclustered index on the top of the clustered index structure and SQL Server uses clustering keys in the leaf node index page of the nonclustered index to point the cluster index.


How does Nonclustered Index navigate on Clustered Index Structure?
In this section, we are studying on Nonclustered Index Structure on the top of the Clustered Index structure. To understand its functionality, we could take an alphabet example where all the data on the Nonclustered index is built on the top of the clustered index structure as given below:

How does SQL Server navigate a nonclustered index?
To understand this, we are taking the above example and we need to find out the Alpha No ‘1052’ from our data table where nonclustered index is built on the Alpha No data field and the same table has a clustered index defined on the FirstName also. To pull the records from this table, we are using below script as given below:
SELECT AlphaNo, FirstName, LastName
FROM dbo.DataTable
WHERE AlphaNo =1052


Nonclustered Index Root node Navigation
SQL Server starts at the root node of the nonclustered index and evaluated that 1052 is greater than or equal to the key value actor 1024 and the compression result is to true. Now, SQL server moves to next key value 1052 on the root node and do the same compression evaluation which is to true again. Now, SQL server moves to next key value 1074 on the root node and do the same compression evaluation which is false. In this case, SQL Server uses the previous key value 1052 on the root node and moves to intermediate node.
Nonclustered Index Intermediate node Navigation
After moving to intermediate node, SQL Server evaluated that 1052 is greater than or equal to the key value actor 1045 where the compression result is to true.  Now, SQL server moves to next key value 1052 on the intermediate node and compression evaluation result is true. In this case, SQL Server moves to Leaf node.
Nonclustered Index Leaf node Navigation
SQL Server reads through the leaf node until it finds 1052 and then uses this point value that allocated with the data page clustering key on the clustered index.


Clustered Index Data Page Navigation
SQL Server reads through the data page until it finds the key value is equal to 1052 in clustered index. SQL Server returns this row to the query processor. The pages in the data chain and the rows in them are ordered on the value of the nonclustered index key.
Conclusion
SQL Server stores its indexes in B-Tree format except ColumnStore indexes where each node in such a tree is represented by a single page or node. With a nonclustered index, there is a second list that has pointers to the physical rows. Nonclustered indexes can be built on either a heap or a clustered index. They always contain a row locator back to the base table.

Wednesday, February 15, 2017

Nonclustered Index structure in SQL Server

We already know that all indexes are organized on B-trees except ColumnStore indexes and always have a single root node. SQL Server always uses this root node as the starting point for traversing an Index. In an Index tree, all the index nodes above the leaf node include the root node also are known as Non-leaf nodes. The leaf node is the bottom level of the index structure and contains the key value index entry either reference rows of the data pages or the complete rows of the data pages.
Nonclustered indexes have the same B-Tree structure as clustered indexes with one significant difference. The leaf node of the nonclustered index contains key values not the actual data. These key values map to pointer or clustering keys that locate rows in the data pages.


How does Nonclustered index implement?
The implementation of the nonclustered index depends on whether the data pages of a table are managed as a Heap or as a clustered index. If nonclustered index is built in a heap then SQL Server uses pointers in the leaf node index pages that point a row in the data pages.
If we have a table with clustered index, SQLServer builds a nonclustered index on the top of the clustered index structure and SQL Server uses clustering keys in the leaf node index page of the nonclustered index to point the cluster index.
How does Nonclustered index navigate on a Heap?
To understand this, we could take an alphabet example where all the data on the Nonclustered index is built in a heap as given below: 


In the above Heap based nonclustered index page, leaf node pages contain the pointers that point a row in the data pages also.
How does SQL Server navigate a nonclustered index?
To understand this, we are taking the above example and we need to find out the alphabet ‘M’ from our data table where nonclustered index is built on the first name data field and we are using below script to find out the result as given below:
SELECT FirstName, LastName
FROM dbo.DataTable
WHERE FirstName='M'
Root node Navigation
SQL Server starts with Root node of the nonclustered index and evaluated that ‘M’ is greater than or equal to the key value actor ‘A’ and the compression result is to true. Now, SQL server moves to next key value ‘M’ on the root node and do the same compression evaluation which is to true again. Now, SQL server moves to next key value ‘R’ on the root node and do the same compression evaluation which is false. In this case, SQL Server uses the previous key value ‘M’ on the root node and moves to intermediate node.


Intermediate node Navigation
After moving to intermediate node, SQL Server evaluated that ‘M’ is greater than or equal to the key value actor ‘M’ where the compression result is to true.  Now, SQL server moves to next key value ‘N’ on the intermediate node and compression evaluation result is false. In this case, SQL Server uses the previous key value ‘M’ on the intermediate node and moves to Leaf node.
Leaf node Navigation
SQL Server reads through the leaf node until it finds ‘M’ and then uses the point value that allocated with the data page. 

Data Page Navigation
SQL Server reads through the data page until it finds the key value is equal to ‘M’. SQL Server returns this row to the query processor. The pages in the data chain and the rows in them are ordered on the value of the nonclustered index key.

Conclusion
The implementation of the nonclustered index depends on whether the data pages of a table are managed as a Heap or as a Clustered index. The leaf node of the nonclustered index contains the key value not the actual data. These key values map to the pointer or clustering keys that locate in the data pages.


Index structure in SQL Server
References: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Tuesday, February 14, 2017

Clustered Index structure in SQL Server

We know that both types of indexes are following B-Tree (Balanced-Tree) structures except ColumnStore indexes. In the index structure, the top node is known as Root node and the bottom node is known as Leaf node. In the clustered indexes, leaf nodes are the actual data pages. A table having clustered index, the data is physically stored on the data page in ascending order and the order of values of the index pages is also in ascending.
How does SQL Server maintain uniqueness of key values for clustered index?
Internally, SQL Server does maintain uniqueness of key values for a clustered index even if the column data is not unique. SQL Server does this automatically by generating a value that stored with the duplicate key value.


To understand this, we could take an alphabet example where all the data on the clustered page is unique as given below:


What will happen to clustered index page after adding another alphabet ‘N’?
In this case, SQL Server will generate an internal number with this key value that uses to maintain uniqueness among these duplicate keys as given below in yellow colored background on the clustered index page:


This feature enables SQL Server to differentiate between all instances of alphabet ‘N’.
How does SQL Server navigate a clustered index?
To understand this, we are taking the above example and we need to find out the alphabet ‘M’ from our data table where clustered index is built on the first name data field and we are using below script to find out the result as given below:
SELECT FirstName, LastName
FROM dbo.DataTable
WHERE FirstName='M'
Root node Navigation
SQL Server starts with the Root node of the clustered index and evaluated that ‘M’ is greater than or equal to the key value actor ‘A’ and the compression result is to true. Now, SQL server moves to next key value ‘M’ on the root node and do the same compression evaluation which is to true again. Now, SQL server moves to next key value ‘R’ on the root node and do the same compression evaluation which is false. In this case, SQL Server uses the previous key value ‘M’ on the root node and moves to intermediate node.


Intermediate node Navigation
After moving to intermediate node, SQL Server evaluated that ‘M’ is greater than or equal to the key value actor ‘M’ where the compression result is to true.  Now, SQL server moves to next key value ‘N’ on the intermediate node and compression evaluation result is false. In this case, SQL Server uses the previous key value ‘M’ on the intermediate node and moves to Leaf node.
Leaf node Navigation
SQL Server reads through the data page until it finds ‘M’ and then it returns the row to the query process. The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. 
Conclusion
The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. 
Index structure in SQL Server
How does Nonclustered index navigate on a Heap?
References: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Friday, February 10, 2017

Index Structures in SQL Server

In SQL Server, all indexes are organized on B-trees except ColumnStore indexes. In a B-tree, every single page is known as Index node which is responsible to allow keyed access to data because it contains the pair of key value and pointer. In the Index, the top node of the B-tree is called the root node and the lowest nodes are called the leaf nodes. All index levels between the top node (Root Node) and bottom nodes (Leaf nodes) are known as intermediate levels or nodes.




If we are taking about a clustered index structure then we see that the leaf nodes are containing multiple data pages. There are multiple index pages between the root and intermediate level nodes to hold the index rows. We already stated that index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.
Clustered Index structure in SQL Server



SQL Server B-Tree Rules
There are some standard rules of B-Tree which are given below-
  • Root and intermediate nodes point only to other nodes where Root node is the top most layer of the index structure.
  • Only, the lowest node means leaf nodes point to data pages.
  • The number of nodes between the root and any leaf is the same for all leaves
  • A node always contains between K and K/2 branches, where K is the branching factor
  • Branching factor is the number of keys in the node
  • B-trees are always sorted
  • The tree will be maintained during insertion, deletion, and updating so that these rules are met
  • When records are inserted or updated, nodes may split
  • When records are deleted, nodes may be collapsed 
  • Index page fragmentation occurs when a new key-pointer pair must be added to an index page that is full 
  • Repair index fragmentation by rebuilding index
  • Rebuilding clustered index repairs table fragmentation


Conclusion
The clustered index is implemented as a B-treeindex structure that supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list. However, navigation from one level to another is performed by using key values.

References: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Monday, February 6, 2017

SSRS – How to create a matrix report in SSRS

Matrix is a wonderful feature in SSRS to display grouped data on the summary level to organise our business data in a better manner. Matrices provide functionality similar to cross-tabs and pivot tables as we did in Excel. On execution time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. After our initial design, we can continue to develop a matrix to improve the viewing experience for the user.

To understand the functionality of Matrix in SSRS, we can take an example of daily sales in SalesDetails table where users want to see the item performance in the various sales regions as given below:
Region
OrdNo
OrdDate
Item
SalesCash
10 - North India
20160106
12/15/2016
2052 - Inkjet Red Ink
8006.7
10 - North India
20160106
12/15/2016
2053 - Inkjet Blue Ink
6518.2
11 - South India
20160102
12/11/2016
2052 - Inkjet Red Ink
3284.8
11 - South India
20160107
12/16/2016
2051 - Inkjet Printer
2677.5
11 - South India
20160107
12/16/2016
2053 - Inkjet Blue Ink
6518.2
12 - East India
20170104
4/1/2017
2052 - Inkjet Red Ink
8006.7
12 - East India
20170104
4/1/2017
2053 - Inkjet Blue Ink
6518.2
13 - West India
20170108
6/1/2017
2051 - Inkjet Printer
2677.5
13 - West India
20170108
6/1/2017
2053 - Inkjet Blue Ink
2883.05
14 - Middle India
20160110
12/19/2016
2052 - Inkjet Red Ink
25149.25
14 - Middle India
20160110
12/19/2016
2053 - Inkjet Blue Ink
2883.05
In SQL Server Data Tools 2015 or Report Builder, we have the blank report with the SQL Server datasource as given below: 

In Report Data, right clicks on Datasets and click on Add Dataset as given below:

After clicking on Add Dataset, it will launch Dataset properties window and we need to set name of Dataset as dsSales, choose to use a dataset embedded and choose query type as Text here as given below:
 

After clicking OK button on the Dataset Properties window, a new dataset has been added in Report Data under Datasets section as given below:

Now, we have report dataset in our report and need to add the matrix over there.  To add a Matrix on the report, just right click on the report body and choose matrix from the Insert section as given below:

After clicking on the Matrix, a matrix has been added in our report as given below:

The matrix initially has a row group, a column group, a corner cell, and a data cell, as shown in the above figure where Values in matrix cells (data cell) display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. 
Now just select your matrix on the report and go to the properties of matrix and set the DataSetName as dsSales over there as given below:

After adding the dataset to the matrix, add groups by dragging dataset fields to the Row Groups and Column Groups areas of the Grouping pane. The first field that we drag to the row groups or column group’s pane replaces the initial empty default group as given below: 

After dragging the requested columns from the datasets to the report, we can apply formatting for each cell, depending on the data as given below:

In Preview, the matrix expands to show the row group and column group values. The cells display summary values, as shown below:

In Preview, we can see that values in matrix cells (data cell) display aggregate values scoped to the intersection of the row and column groups to which the cell belongs to give a clear understanding of the sales data.
Conclusion
In SSRS, a Matrix control provides a crosstab view and pivot tables of data, similar in behavior to a PivotTable in MS Excel. We can apply formatting for each cell and have the facility to write the conditional expression and custom code.  We can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed. For the dynamic columns, matrix is the best option in SSRS.

For the live demo, you can visit us at youtube also and subscribe our channels for more tutorial videos -

Popular Posts