23 What is Columnstore and Filtered Index in SQL|Filtered Indexes Vs Columnstore index in English

This Video contains In-depth coverage of Filtered Indexes and Columnstore index. Covers all of the important queries and SQL commands. In this videos series, you'll learn how to read and write complex queries. Handle tricky SQL interview questions. Implement complex SQL queries and answer popular interview questions on SQL. These videos series will help to crack interviews and get jobs in SQL Developer, Database Developer, Data Engineer, Reporting Analyst, Data Analyst, Data Scientist, System Analyst, Power BI Developer, Tableau Developer, BI Developer, etc.
-----------------------------------------------------------------------------------------------------------------
For any query Contact me on social media:
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Filtered Indexes
A filtered index is a special index type where only a certain portion of the rows of the table are indexed. Based on the filter criteria that is applied when the index is created only the remaining rows are indexed which can save on space, improve on query performance and reduce maintenance overhead as the index is much smaller.



Why use it?
Filtered indexes are useful when you are creating indexes on tables where there are a lot of NULL values in certain columns or certain columns have a very low cardinality and you are frequently querying a low-frequency value.
Example:
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderDate_INC_ShipDate ON Sales.SalesOrderHeader(OrderDate ASC) WHERE ShipDate IS NULL;
SELECT OrderDate FROM Sales.SalesOrderHeader WHERE ShipDate IS NULL ORDER BY OrderDate ASC;
Columnstore index
A column store index is an index that was designed mainly for improving the query performance for workloads with very large amounts of data (eg. data warehouse fact tables). This type of index stores the index data in a column-based format rather than row-based as is done with traditional indexes.

There are two main benefits of column store indexes. First, they reduced storage costs. Column store indexes provide a very high level of compression, up to 10x, due to the fact that the data across columns is usually very similar and will compress quite well. Second is better performance. This benefit is multi-faceted. With a smaller index footprint, due to the compression, we reduce the amount of IO we have to perform. Also because of this small footprint we can fit more of the index into memory which helps to speed up processing. Finally queries often only query a few columns from the table. Since the data is stored in a column based format this also reduces the amount of IO that needs to be performed.

Example:

CREATE COLUMNSTORE INDEX IX_SalesOrderDetail_ProductIDOrderQty_ColumnStore
ON Sales.SalesOrderDetail (ProductId,OrderQty);

SELECT ProductID,SUM(OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductId;

#Call_Now_8309569513_Realtime_One_to_One_Training,#Columnstoreindex,#FilteredIndex