60
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.
...
- 167k
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 ...
- 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 ...
- 37.4k
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/...
- 44.2k
28
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 ...
- 80.3k
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 ...
- 22.8k
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,
...
- 37.4k
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 ...
- 80.3k
18
votes
Accepted
Why would SQL Server ignore an index?
Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table.
It would seem likely the cost-based optimizer thinks it would be ...
- 69.1k
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 ...
- 96.1k
16
votes
Should the index on an identity column be nonclustered?
By default the PK is clustered and in most cases, this is fine.
However, which question should be asked:
should my PK be clustered?
which column(s) will be the best key for my clustered index?
PK ...
- 10.1k
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 @...
- 80.3k
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 ...
- 69.1k
15
votes
Accepted
Good reasons to use SELECT... WITH XLOCK?
As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right?
...
- 80.3k
15
votes
Accepted
Unused Indexes Best Practices
This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch.
More importantly, the rows in this view for any specific ...
- 178k
15
votes
Accepted
Impact of CLUSTER on performance
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the ...
- 167k
15
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 ...
- 15.9k
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 ...
- 28.6k
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 ...
- 37.4k
13
votes
Accepted
How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables
It seems to ignore any index I put on it
Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order ...
- 80.3k
13
votes
Accepted
Reasons to use covering indexes
Clustered Index
This index is the table. The index information is stored in the b-tree pages while all of the other columns (those not in the index) are stored at the leaf level of the index. All ...
- 23.9k
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 ...
- 1,728
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 ...
- 37.4k
12
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 ...
- 80.3k
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 ...
- 9,242
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 ...
- 167k
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 ...
- 37.4k
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 ...
- 81.1k
11
votes
Should the index on an identity column be nonclustered?
For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered?
If you're asking if the default for a primary key on an identity column ...
- 80.3k
11
votes
Accepted
Computed Column Index Not Used
Try with COALESCE instead of ISNULL. With ISNULL, SQL Server doesn't seem capable of pushing a predicate against the narrower index, and therefore has to scan the clustered to find the information.
...
- 178k
Only top scored, non community-wiki answers of a minimum length are eligible