Skip to main content

Posts

Showing posts from December, 2018

Column-Store index in SQL Server

Column-Store Indexes
Column-Store index is designed for improving query performance which is used for heavy amount of data like data warehouse fact tables. This type of index stores the index data in column based rather than row based. This index is introduced in version SQL server 2012; basically this is column based non-clustered index.
Why should we use Column-Store Index Column-Store index provides a very high level of compression, typically by 10 times, to significantly reduce your data warehouse storage cost. For analytics, a column-store index offers an order of magnitude better performance than a B-Tree index. Columns store values from the same domain and commonly have similar values, which result in high compression rates. I/O bottlenecks in your system are minimized or eliminated, and memory footprint is reduced significantly. High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can p…

Non-clustered index in SQL Server

Non-clustered index A non-clustered index has a structure separate from data row, it means non-clustered order of the row does not match the physical order of the actual data. If we talk about the B-TREE structure, in the non-clustered index, the leaf pages of the index do not contain any actual data, but instead, contain pointers to the actual data. These pointers would point to the clustered index data page where the actual data exists (or the heap page if no clustered index exists on the table).
Non-clustered indexes (also called “indexes”) are the normal indexes that are created on your tables. SQL Server supports 999 non-clustered indexes per table.
Each non-clustered index can contain up to 1023 included columns. Columns of the TEXT, NTEXT and IMAGE data types cannot be included either.
There are more things that could be put in here, but this will get you a good start. If you think of anything that I’ve missed please feel free to post them below.
Benefits of non-clustered index A non…

Clustered Index in SQL Server

What is a clustered index? The clustered index is a special index which sorts and stores the data in the table or view based on their key values. There can be only one clustered index per table because the data rows themselves can be stored in only one order. If we talk in term of B-Tree design, the leaf nodes of the index store the data for the rest of the columns in the table so when a lookup is performed on this type of index there are no other structures that need to be referenced.
Why create clustered indexes? Having clustered index in the table is beneficial; first, when querying the table, there is only required IO operations occurred except extra lookup to get any/all of the non-key column data. Second, it gives us a way to recognize the table data. If there is no clustered index on the table then data stored disorganized in a heap structure. When our data becomes defragmented due to DML operations then the only one way to fix this fragmentation is to reload all the data into a n…

Index tutorial SQL Server

When we need to improve the performance the first thing comes in your mind is Index, while there are many different things can be done to improve the performance of the system. But indexing of the table should be done carefully which may reduce the query time as unexpected. Although every SQL Server release comes with new updates so we have to know about the index, which one updates comes on Index and which type of index should be used to increase your performance. In this tutorial, we will learn about each type of index available in SQL Server and will explain why/when one could be used to improve the query performance.
In starting I will explain the basic about Index so that new database professional learn about the index in SQL Server. According to Microsoft Doc, an index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a st…

Disable/Enable All the Foreign Key Constraint in SQL Server Database

In some scenario we need to disable all foreign key relationships from all table, suppose for bad testing purpose or you want to see what will be effect of garbage data on application or performance, due to this purpose you want to insert garbage data into table and this table contains foreign key constraints then you will get the error of foreign key constraint.
Many people face these types of issue, the following query will resolve this problem but be careful on the production server, it would be better if you do this under guidance to any DBA.
The following query will disable all constraints in the database.

execsp_msforeachtable'alter table ? nocheck constraint all'

Once you disabled all constraints you should also need to enable all constraints once you completed your work, so following query will help you to enable all constraints in the database.

execsp_msforeachtable'alter table ? with check check constraint all'

Using the above query you can resolve above described …

Query to find foreign key relationship and name of the constraints for each table in the database

Sometimes we need to find all foreign key on a table so that we can analyze the relation of a table and can find the solution accordingly. It is very difficult to see it manually, by using the following query we can find all foreign key relationships on the particular table.

SELECT K_Table=FK.TABLE_NAME, FK_Column=CU.COLUMN_NAME, PK_Table=PK.TABLE_NAME, PK_Column=PT.COLUMN_NAME, Constraint_Name=C.CONSTRAINT_NAME FROMINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSC INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTSFKONC.CONSTRAINT_NAME=FK.CONSTRAINT_NAME INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTSPKONC.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_NAME INNERJOININFORMATION_SCHEMA.KEY_COLUMN_USAGECUONC.CONSTRAINT_NAME=CU.CONSTRAINT_NAME INNERJOIN( SELECTi1.TABLE_NAME,i2.COLUMN_NAME FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTSi1 INNERJOININFORMATION_SCHEMA.KEY_COLUMN_USAGEi2ONi1.CONSTRAINT_NAME=i2.CONSTRAINT_NAME

Check if an index exists in SQL Server

Many times we come across the scenario where we need to some code based on whether an index exists or not.
The following query may help you to check an index is exists in a particular table or not. Suppose we have a table like dbo.Table1 and it has index idx_Index and in some scenario, we need to check idx_Index exists or not.

System.index catalog view records for each Clustered and Non-Clustered indexes.  We can execute the following query to find out a particular index exists or not in a particular table

IFEXISTS (   SELECT 1 FROMsys.indexes   WHEREname='idx_Index'ANDobject_id=OBJECT_ID('dbo.Table1') ) BEGIN PRINT'Index is Exists' END

Check if a primary key constraint exists in SQL server

A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column for a table has a unique identity. Primary key constraints. A primary key (PK) is a single column or combination of columns (called a compound key) that uniquely identifies each row in a table.
Following example to check if a primary key exists

CREATETABLEPRODUCT ( IDINTNOTNULLIDENTITY(1,1), SKUVARCHAR(20)NOTNULL, TITLEVARCHAR(200)NOTNULL, PRICEMONEYNOTNULL, DESCRIPTIONVARCHAR