Thursday, March 30, 2017

Disable Indexes and Constraints in SQL Server

We know that Indexes can help queries to find data quickly in a database in SQL Server where they provide opportunities for much more efficient use of I/O because of having great potential. Downside to using an index is the performance implication on data modification statements means any time a query modifies the data in a table (INSERT, UPDATE, or DELETE) then the database needs to update all of the indexes where data has changed.
After an index is disabled in SQL Server, it remains in a disabled state until it is rebuilt or dropped.

To improve the other operations (INSERT, UPDATE, or DELETE) performance on the data table, it becomes an urgent requirement to disable or drop indexes on that table. If we are going to disable any index then we should be awarded of the following limitations and restrictions of this process –
  • SQL Server does not maintain a disabled index as well as the SQL Server Query Optimizer does not consider any disabled index in the query execution plans.
  • If any existing queries that reference these disabled indexes with a table hint must be failed.
  • SQL Server does not allow us to create an index that uses the same name as an existing disabled index and will not be allowed to drop any disabled index.
  • After disabling a unique index, all constraints such as PRIMARY KEY or UNIQUE KEY constraint and all FOREIGN KEY constraints that reference that indexed columns from other tables are also disabled.
  • After disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. 

Note: The constraint names are listed in a warning message when the index is disabled.
After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.
The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.
  • All depended nonclustered indexes are automatically disabled when the associated clustered index is disabled and cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped.
  • Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.
  • After disabling a clustered index on a table, SQL Server automatically disables all clustered and nonclustered indexes on views that reference that table.
  • SQL Server helps us to rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. Though, we must always rebuild a disabled clustered index offline if we use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement.
  • SQL Server does not allow to CREATE STATISTICS statement on a table that has a disabled clustered index. In this case, AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled.

How to disable index on a table?
Before disabling any index, you must have at least ALTER INDEX permission. There are two ways to disable index in SQL Server such as through SQL Server Management Studio and T-SQL statements-
By using SQL Server Management Studio, we need to expend our index folders of the data table in the database as given below-

After expending the index folder, right click on the index and choose Disable. It will be launched Disable Indexes window where we need to click on OK button as given below-

By using Transact-SQL - In this scenario, we have to disable a particular index or all indexes on a table as given below-  
USE DEMO; 
GO 
-- Disables IndCustItems index 
-- on the dbo.TB_SalesOrders table 
ALTER INDEX IndCustItems
ON dbo.TB_SalesOrders
DISABLE; 

-- Disables all indexes on dbo.TB_SalesOrders table
ALTER INDEX ALL
ON dbo.TB_SalesOrders
DISABLE;

Conclusion
After disabling the index, it definition remains in metadata, and index statistics are kept on nonclustered indexes and after rebuilding the index, any constraints that were disabled because of disabling the index must be manually enabled.  Disabling a nonclustered or clustered index on a view physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.
Reference:https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints

Wednesday, March 22, 2017

ColumnStore Index Architecture in SQL Server 2016

Microsoft development team has been stated that Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.
ColumnStore index is one of the best features in SQL Server 2016. Before introducing this, all indexes have B-Tree structures which use rowstore for the data storage but ColumnStore indexes use both types (rowstore and columnstore) of data storage.
How does Data Storage work?
There are two compression techniques for data storages such as columnstore and rowstore which are used to reduce the IO required to execute analytics queries and therefore improve query performance.
Columnstore Data Storage – In this storage, data is logically organized as a table with rows and columns but physically stored in a column-wise data format.
A columnstore index physically stores most of the data in columnstore format. In columnstore format, the data is compressed and uncompressed as columns.
There is no need to uncompress other values in each row that are not requested by the query. This makes it fast to scan an entire column of a large table.
Rowstore Data Storage - rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data such as a heap or clustered B-Tree index.
A columnstore index also physically stores some rows in a rowstore format called a deltastore. The deltastore, also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Each delta rowgroup is implemented as a clustered B-Tree index.
deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. The deltastore is a rowstore.

References: https://msdn.microsoft.com/en-us/library/mt793289.aspx

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