Skip to main content
Share Your Experience: Take the 2024 Developer Survey
62 votes
Accepted

Indexes: integer vs string performance if the number of nodes is the same

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. ...
Erwin Brandstetter's user avatar
46 votes

How does SQL Server determine key column order in missing index requests?

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups. The first set contains all of the recommended columns that are ...
Bryan Rebok's user avatar
  • 1,169
37 votes
Accepted

If a database only ever has one insert, is it bad to index every possible column combination?

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider. Since you're on SQL Server 2017, loading once, and running reports, why ...
Erik Darling's user avatar
29 votes
Accepted

Difference between clustered index seek and non clustered index seek

A nonclustered index that has the same key(s)* as the clustered index, may still be useful, because the nonclustered index will usually be smaller and denser. Remember, a clustered index includes all ...
Paul White's user avatar
  • 85.7k
29 votes
Accepted

Why filtered index on IS NULL value is not used?

Why this index is not "covering" for this query: No good reason. That is a covering index for that query. Please vote for the feeback item here: https://feedback.azure.com/d365community/...
David Browne - Microsoft's user avatar
27 votes

If a database only ever has one insert, is it bad to index every possible column combination?

If you have N columns in a table, every possible column combination is 2^N-1 (removing the empty set). For 10 columns that would mean 1023 indexes, for 20 columns we end up with a whopping 1048575 ...
Lennart - Slava Ukraini's user avatar
20 votes
Accepted

Order By causes a scan on a large table

sortie I would change the index to look like this: CREATE INDEX [TypeId_Id_DateTimeUTC] ON [dbo].[TableName] ( TypeId, Id, DateTimeUTC ) WITH ( FILLFACTOR = 100, ...
Erik Darling's user avatar
19 votes
Accepted

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong? At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to ...
Paul White's user avatar
  • 85.7k
18 votes
Accepted

Which index will be used in this scenario?

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last ...
ypercubeᵀᴹ's user avatar
17 votes
Accepted

Impact of CLUSTER on performance

Yes, there can be downsides. If other queries look at a different data segments not determined by the date, those may take a performance hit if rows are spread out over more data pages now. Just like ...
Erwin Brandstetter's user avatar
16 votes
Accepted

Why is my EXISTS query doing an index scan instead of an index seek?

If you want good results from the query optimizer, it pays to be careful about data types. Your variables are typed as datetime2: DECLARE @OrderStartDate datetime2 = '27 feb 2016'; DECLARE @...
Paul White's user avatar
  • 85.7k
16 votes
Accepted

Does it ever make sense to have a clustered and non clustered index for the same column

Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes. The clustered index is organized by the key columns. It also includes every ...
AMtwo's user avatar
  • 16.2k
16 votes
Accepted

Index Uniqueness Overhead

I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. The environment I'm currently involved in has 250 servers with 2500 ...
Hannah Vernon's user avatar
  • 70.1k
15 votes
Accepted

Index help for bad Query on fairly big table (2m)

If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot. Something like this (which Erik Darling kindly provided as a comment): CREATE INDEX ...
Josh Darnell's user avatar
  • 29.4k
14 votes
Accepted

Force SQL Server to use fragmented indexes?

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If ...
Erik Darling's user avatar
13 votes
Accepted

Can a un-used NONCLUSTERED INDEX still enhance query speed?

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan. I have come across situations when ...
DMDM's user avatar
  • 1,738
13 votes
Accepted

Applying indexes on columns used in join condition

B-tree indexes provide three main benefits: Seek to a specific value or range of values Return rows in index order Smaller row size than the base table (clustered index or heap) Without useful ...
Paul White's user avatar
  • 85.7k
13 votes
Accepted

SELECT Statement with , * in column list is faster than same statement without *

While there are many differences in the two query plans, the problem you run into in the slower plan occurs mainly in a spill operation because of an undersized memory grant: There is at least one ...
Erik Darling's user avatar
12 votes

Rebuild Very Large Primary Key Index

Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question. First, and I ...
John Eisbrener's user avatar
12 votes
Accepted

Search for nested values in jsonb array with greater operator

This is generally hard to optimize: no direct operator or index support for jsonb for this kind of test. EXISTS should at least be faster than what you have, while also avoiding duplicate rows (where ...
Erwin Brandstetter's user avatar
12 votes

how to find out the `Key Lookups` in the current query plan?

This is not a good idea. Take it from me. A long time back I coded a check into sp_BlitzCache that found plans with Key Lookups in them, and then compared that operator's cost to the total cost of the ...
Erik Darling's user avatar
12 votes

Order By causes a scan on a large table

You should use a consistent unambiguous format for datetime literals. It is weird having two entirely different formats for the > and < predicates. DateTimeUTC, TypeId is not the optimal order ...
Martin Smith's user avatar
  • 84.8k
11 votes

Find unused indexes

FWIW here's a query I've been using SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM ...
John Bachir's user avatar
11 votes

Why is my EXISTS query doing an index scan instead of an index seek?

SQL Server is doing an index scan since it thinks that is cheaper than seeking to each required row. Most likely, SQL Server is correct, given the choices it has in your setup. Be aware SQL Server ...
Hannah Vernon's user avatar
  • 70.1k
11 votes
Accepted

Improve UPDATE performance on big table

Assuming the column uuid is supposed to be UNIQUE, this table definition should save some space and help performance: CREATE TABLE public.calls ( id serial PRIMARY KEY, result int4 DEFAULT 0 ...
Erwin Brandstetter's user avatar
11 votes

Ola Hallengren Index maintenance on servers with Availability Groups

Yes, the script is intelligent enough. This is explicitly stated in the FAQ: How should I configure IndexOptimize for an Availability Group? Indexes can only be rebuilt or reorganized and ...
Tom V's user avatar
  • 15.7k
10 votes

Is there any reason for an index to contain the primary key?

You are right to be suspicious of the two indexes that you listed in your question, but there are a few narrow use cases for including the primary key in an index as a key column. By default SQL ...
Joe Obbish's user avatar
  • 32.2k
10 votes
Accepted

Updating statistics on 18 TB database

Based on your question I can think of four possible issues with statistics that could be causing the issues that you're experiencing. 1. Statistics are not automatically updated often enough. In SQL ...
Joe Obbish's user avatar
  • 32.2k
9 votes

Does a re-index update statistics?

The Microsoft Docs page for SQL Server statistics states: Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need ...
Andrii Viazovskyi's user avatar
9 votes
Accepted

Composite index

If all of your query patterns include filters on both a and b, and the table gets written to a lot, I would argue for testing a single index that looks like this: (a,b) include (c,d,e,other include ...
Aaron Bertrand's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible