SQL Index Archives - Page 19 of 47 - SQL Authority with Pinal Dave

SQL SERVER – Basics ColumnStore FAQ – Part II

November 2, 2015

I wrote the first part of FAQ in here. This is not a typical blog post but more of a process oriented topic when working with ColumnStore Indexes. With the advancements done in SQL Server 2016, I know that this is going to be even more critical to understand some…

SQL SERVER – ColumnStore Frequently Asked Queries

October 19, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index

This blog talks about troubleshooting scenarios in FAQ format when dealing with ColumnStore Indexes inside SQL Server. Use a combination of trace flags, query hints, performance counters, extended events discussed in various blogs to gather relevant data and troubleshoot the problem. Sometimes I have seen people use ColumnStore as a…

Interview Question of the Week #040 – Difference Between Unique Index vs Unique Constraint

October 11, 2015

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

SQL Index

Question: What is the difference between unique index and unique constraint? Answer: Add Unique Constraint ALTER TABLE dbo.<tablename> ADD CONSTRAINT <namingconventionconstraint> UNIQUE NONCLUSTERED ( <columnname> ) ON [PRIMARY] Add Unique Index CREATE UNIQUE NONCLUSTERED INDEX <namingconventionconstraint> ON dbo.<tablename> ( <columnname> ) ON [PRIMARY] There is no difference between Unique Index…

Interview Question of the Week #039 – What is Included Column Index in SQL Server 2005 and Onwards?

October 4, 2015

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index

You may find it interesting that not many people still know what is Included Index in SQL Server. I have seen it personally around me and also have seen at Question: What is Included Column Index? Answer: In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key…

Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

May 24, 2015

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index

Question: What is the difference between Index Seek and Index Scan? Answer: Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table. Index Scan: Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the…

SQL SERVER – Mistake to Avoid: Duplicate and Overlapping Indexes – Notes from the Field #073

March 26, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Notes from the Field, SQL Index

[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about indexes and its impact. We often believe that indexes will improve the performance of the query, but it is not true always. There are cases when indexes can reduce the performance as well. Read…

SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression

February 23, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

ColumnStore Index, SQL Index

In earlier blogs on “Fundamentals of Columnstore Index”, I took a simple approach on taking a tour of how to create a simple Columnstore index. Having said that, SQL Server 2014 supports columnstore and columnstore archival compression for columnstore tables and indexes. Columnstore tables and indexes are always stored with…