Column-Store index in SQL Server

Column-Store Indexes
Column-Store index is designed for improving query performance which is used for a 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 a 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 perform more query and data operations in memory.
Batch execution improves query performance, typically by two to four times, by processing multiple rows together.
Queries often select only a few columns from a table, which reduces total I/O from the physical media.

Note:
1-    Use a clustered column-store index to store fact tables and large dimension tables for data warehousing workloads. This method improves query performance and data compression by up to 10 times.
2-    Use a non-clustered column-store index to perform analysis in real-time on an OLTP workload.

How to create a column-store index
Using the following script you can create the column-store index in SQL Server.


--to create a column-store index
CREATE COLUMNSTORE INDEX IX_OrderDetail_ProductIDOrderQty_ColumnStore
ON Sales.OrderDetail (ProductId,OrderQty);


Source of content from Microsoft: column-store index

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 on a table provides fast access to data. If a table has a non-clustered index then the index allows the database engine to locate data quickly without having to scan through the entire table.

How to create a non-clustered index
A non-clustered index can create as like clustered index created in the previous article. Please see the following script which will help you to create a no-clustered index.


-- Adding non-clustered index on a table Employee
CREATE NONCLUSTERED INDEX IX_Employee_EmpLastNameEmpFirstName ON 
dbo.Employee(EmpLastName ASC,EmpFirstName ASC);

CREATE INDEX IX_Employee_EmpFirstName ON dbo.Employee (EmpFirstName ASC);

Clustered Index in SQL Server


What is a clustered index?

The clustered index is a unique index that 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 terms 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 way to fix this fragmentation is to reload all the data into a new table. With a clustered index in the table, we can run index reorganized or rebuild to address the fragmentation.

When clustered indexes should be created?

Generally, we use an index on those columns which heavily used in the WHERE clause portion in the query. As we know, query performs huge range scans on those columns in the WHERE clause can be beneficial from having a clustered index on these columns. In both cases, we will use fewer disk IO, and in some cases (depending on the ORDER BY clause), we can save ourselves memory and CPU by not having to perform a sort since data is already ordered.

How to create a clustered index

A clustered index can be created on the table either through a primary key constraint or merely using the create index statement. In both cases, we have to add the CLUSTERED keyword to either the ALTER TABLE or CREATE INDEX statement, as shown in the below TSQL examples.


-- When we create a primary key
ALTER TABLE Sales.BookInventory ADD CONSTRAINT PK_BookInventory_BookID
PRIMARY KEY CLUSTERED (BookID ASC);

-- Without constraints
CREATE CLUSTERED INDEX IX_BookInventory_BookID ON Sales.BookInventory (BookID ASC);


Here I will highlight some vital things on the clustered index and will analysis of myth about the clustered index.

1-    It is essential to know; only one clustered index can be defined on each table in your database.
2-    Changing the clustered index from one column to another column may be a bit complicated and does require some downtime. Since all non-clustered indexes on table reference the row data in the clustered index if we drop the current clustered index and create a new one, then all of the non-clustered indexes will end up being updated twice.
3-    When the clustered index will be dropped, all data of the columns will move into the heap. It will be updating second time when new the new clustered index is created, and all the column data move into the structure.
4-    Dropped and create new index may probably increase the fragmentation, which is poison for index. So we have to rebuild all the indexes.
5-    Many people or new DBA think, we can create clustered index only on primary key column(s), this is not true, you can create clustered index on another column too which probably used more in WHERE clause. Suppose you have created a table and create an index on the primary key, which rarely used in WHERE clause while DateTime columns use mostly in the query, so it would be better if you create clustered index on DATETIME column.
6-    Remember, it would be better if the clustered index column has unique data and sort in length.

Query to change the clustered index

I above points I mentioned, changing the clustered index in tables is may be dangerous, but the following query may help you.

Suggestion: If you are not DBA, then don’t take the risk; contact your DBA; he will help you.



DROP INDEX AK_BookOrder_rowguid ON Sales.BookOrder;
DROP INDEX IX_BookOrder_ProductID ON Sales.BookOrder;
ALTER TABLE Sales.BookOrder
   DROP CONSTRAINT PK_BookOrder_BookOrderID_BookOrderID;
CREATE CLUSTERED INDEX IX_BookOrder_ProductID
   ON Sales.BookOrder (ProductID ASC);
ALTER TABLE Sales.BookOrder
   ADD CONSTRAINT PK_BookOrder_BookOrderID_BookOrderID
   PRIMARY KEY NONCLUSTERED (BookOrderID ASC, BookOrderID ASC);
CREATE UNIQUE NONCLUSTERED INDEX AK_BookOrder_rowguid
   ON Sales.BookOrder (rowguid ASC);

Index tutorial SQL Server

When we need to improve the performance, the first thing that comes in your mind is Index, while 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 basics about Index so that new database professionals 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 structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
Type of Index: There are basically two types of index
1-        Clustered Indexes
2-        Non-Clustered Indexes

But some specialized index types are available in SQL Server, these indexes can create on regular tables and datatypes and some work only on specific data type, please see the following.

1-        ColumnStore Indexes
2-        Full-Text Indexes
3-        XML Indexes
4-        Spatial Indexes
5-        Filtered Indexes

Don’t miss one thing which is more critical, sometimes index can create trouble if we do not maintain indexes from time to time.
1-    Index Fragmentation

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 immoral 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.


exec sp_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 the following query will help you to allow all constraints in the database.


exec sp_msforeachtable 'alter table ? with check constraint all'


Using the above query, you can resolve the above-described scenario, but if you want to truncate the table, this will not be work, you have to remove all foreign key constraints. 

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
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='<YourTableName>'


This article is referred from SQLAuthority to know more please click here MORE

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 scenarios, 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.


IF EXISTS
(
  SELECT 1 FROM sys.indexes
  WHERE name='idx_Index' AND object_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.

The following example to check if a primary key exists.


CREATE TABLE PRODUCT
(
ID INT NOT NULL IDENTITY(1,1),
SKU VARCHAR(20) NOT NULL,
TITLE VARCHAR(200) NOT NULL,
PRICE MONEY NOT NULL,
DESCRIPTION VARCHAR(2000)  NULL,
DTCREATE DATETIME NULL
CONSTRAINT pk_Product_ID PRIMARY KEY (ID)
)

IF OBJECT_ID('dbo.pk_Product_ID') IS NULL
  ALTER TABLE PRODUCT ADD CONSTRAINT pk_Product_ID PRIMARY KEY(ID) 
ELSE
  PRINT 'Product table already has a primary key.'

DROP TABLE PRODUCT




How to escape a single quote in SQL Server

Single quote issue always irritates me, and still, I ignored this issue many times, but in some scenarios, we can't. I searched this issue on google and found some solution, and I want to share it with you.

Suppose we have a string "I like codefari's article", we will escape the single quote here is the example.

SELECT 'I like codefari''s article' 

Please add a single quote at apostrophe s

Second example as given below

SELECT 'I like codefari'+CHAR(39)+'s article'

Here you can use CHAR(39) as given above example.


Find the user who dropped database table


Once a table was missing from our database and my project manager ask to find to user who dropped the table I search on google and find the solution so I want to share my experience with you.


CREATE DATABASE myTest
GO

USE myTest
GO

CREATE TABLE Table1 (ID INT)
GO

ALTER TABLE Table1
ADD FirstCol VARCHAR(100)
GO

DROP TABLE Table1
GO


Run above query, it will create a database and table also alters the table after that it will delete the created table. We want see the changes history of this table, please follow the as given in below pic.




Once you click on schema changes history then you will found the following results




You can see the DDL operation history for table Table1.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...