You need to know SQL Server basics to keep database performance at the highest level. This knowledge will also help you to be prepared to any potential problems. When working with files, you may discover that there is not enough free space to store the required data in the file. By default, in such situations, SQL Server locks the file and extends it (it’s called – autogrow). Let us learn about SQL Server Index Fragmentation.
SQL SERVER – Basics ColumnStore FAQ – Part II
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…
Read MoreSQL SERVER – ColumnStore Frequently Asked Queries
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…
Read MoreInterview Question of the Week #040 – Difference Between Unique Index vs Unique Constraint
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…
Read MoreInterview Question of the Week #039 – What is Included Column Index in SQL Server 2005 and Onwards?
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…
Read MoreInterview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)
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…
Read MoreSQL SERVER – Mistake to Avoid: Duplicate and Overlapping Indexes – Notes from the Field #073
[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…
Read More