Questions tagged [nonclustered-index]
The nonclustered-index tag should be used for questions regarding how to implement and troubleshoot supplemental indexes used primarily to enhance performance. The term nonclustered is typically a SQL Server-specific term.
151
questions
0
votes
2answers
51 views
What does it mean that a “clustered index does not store pointers to the data”? [duplicate]
I am a beginner in SQL and Database Administration and I am trying to undestand the difference between clustered and non-clustered indexes. More or less I understood what the different tasks are but I ...
2
votes
2answers
95 views
SQL Server Index Scan when using 'OR' operator
We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-...
1
vote
1answer
52 views
Nonclustered Index Rebuild Online on Heap Table
Recently read that rebuilding a heap online in SQL Server Enterprise is single threaded and does not go parallel.
My question is: If you try to rebuild online just non-clustered indexes on a heap ...
0
votes
1answer
35 views
MySQL Clustered Index or Non Clustered index? (Database Design)
I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be ...
5
votes
1answer
128 views
Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?
I'm doing some testing of columnstore indexing on a single table that has about 500 million rows.
The performance gains on aggregate queries have been awesome (a query that previously took about 2 ...
0
votes
0answers
20 views
MySQL InnoDB Weird Query Performance
I designed two tables, both using InnoDB.
The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
1
vote
0answers
17 views
How to create Non-clustered Covering index on PK while creating a table
I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover.
CREATE TABLE [Ref].[User](
[UserRegisteredId] [varchar](100) NOT ...
0
votes
1answer
35 views
difference between create constraint non clustered and create index non clustered
I am new to sql server. i was trying to learn creating index. i tried to create a table with below query:
CREATE TABLE [Ref].[Country](
[CountryId] [uniqueidentifier] NOT NULL,
[CountryName] ...
0
votes
1answer
32 views
Value of the Included Column is stored in Leaf Node?
There is SQL- script, which generated the Nonclustered Index with Included Column:
CREATE TABLE users
(
id INT,
firstname VARCHAR(50),
surname VARCHAR(50)
);
CREATE ...
0
votes
1answer
45 views
Why scan clustered index instead seek nonclustered index?
There is SQL script:
CREATE TABLE users
(
id INT,
firstname VARCHAR(50),
surname VARCHAR(50)
);
CREATE CLUSTERED INDEX ix_users_id
ON users (id);
CREATE ...
0
votes
1answer
42 views
If PK is Non-Clustered index, how to identify clustered index candidate?
I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.
I did research ...
0
votes
1answer
39 views
Results in sys.dm_db_index_usage_stats
I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this ...
-1
votes
1answer
67 views
How many non-clustered indexes on one table? [closed]
I have a table with 12 indexes with just one column which are added by EF automatically:
CREATE NONCLUSTERED INDEX [IX_Table_Col1] ON [dbo].[Table]([ColId1] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, ...
2
votes
3answers
124 views
Is it possible to track the progress of when I drop a the primary key clustered index on a table?
I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine ...
0
votes
0answers
32 views
How can I tell which queries and nonclustered indexes are doing bookmark lookups on my primary key for a specific table?
Is it possible to get the queries and the names of the nonclustered indexes in my database which are doing bookmark lookups / key lookups in the execution plans?
Would I have to go through the plan ...
1
vote
1answer
54 views
Rebuild and Reorganize index while altering index
Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns.
due to some reason if i need to add one more column in the existing index .
Do i need to ...
0
votes
0answers
70 views
Explaining about SQL Server query Execution Plans and understanding Clustered and Nonclustered Indexes
I'm Christine and I'm a newbie. I would like to know if someone could help me clarify what I'm drilling down this execution plan as below :
Here's query generated from SQL server that is accompanied ...
5
votes
1answer
280 views
Is there any difference with specifying the primary key as an include column in a nonclustered index?
Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as ...
0
votes
2answers
70 views
Are key lookups from non-clustered indexes always slower than a second query that does the lookup?
I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
0
votes
1answer
50 views
Should a filtered index theoretically perform faster in this example?
I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "...
1
vote
2answers
105 views
How to compare write speed before and after adding index?
I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
0
votes
1answer
61 views
Offline index rebuild - does it also create a whole new copy of the index?
As I know, while online index is rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index.
...
-1
votes
1answer
91 views
Index Update with Eager Spool and Sort operators in Execution Plan
After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns.
Before each index update, there is an Eager Spool ...
3
votes
1answer
113 views
Do I need to rebuild heap table after rebuilding a non-clustered index?
Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
0
votes
2answers
58 views
non clustered index coverage with INCLUDED columns
If I have two indexes:
IDX_1 = (COL1), (COL2) DESC
IDX_2 = (COL1) INCLUDE (COL3)
will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE ...
4
votes
1answer
79 views
Maximizing throughput on bulk data processing
I'm using SQL Serverversion 14.0.3035.2
I have a pile of information that I need to process from table A and insert the modified data into table B. I have written an application to query the data, do ...
2
votes
1answer
49 views
Automatic seek on an index on a calculated column
I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using ...
0
votes
1answer
66 views
Column as an Included column or as a key column
I have a query which has an optional where condition on column classID(not unique).
This same classID is being displayed as a select column in various other queries.
Currently i have classID as an ...
1
vote
1answer
99 views
Do I need to update an index include columns every time I add a new column
I work on a team where we have millions of records of data, but not 100s of millions anything crazy.
We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
4
votes
1answer
100 views
How does SQL Server know what index to use?
I have this non clustered index
CREATE NONCLUSTERED INDEX [nci_wi_StoreVariantInventory_F09B4BE817BC953FAC6861B7E46F6F51] ON [dbo].[StoreVariantInventory]
(
[ProductId] ASC,
[StoreId] ASC,
...
1
vote
1answer
230 views
Adding a clustered index to a heap table
I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record ...
1
vote
1answer
60 views
Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?
I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.
Most of the tables in the database at present ...
2
votes
0answers
91 views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
1
vote
1answer
73 views
Understanding the recreation of index in Table partitioning
I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[...
2
votes
3answers
500 views
Moving all Heap Tables to another Filegroup in SQL Server
In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?
This question is only for Nonclustered Indexes.
https://stackoverflow.com/questions/...
4
votes
3answers
577 views
Why does sql server prefer the nonclustered index over the clustered index?
I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
1
vote
1answer
110 views
SQL Server: Index and Include Columns
Assume that I have a table Foo with columns A, B, C, D, and E. All are integers. Let's assume a million records, and that we're on SQL Server 2017.
The following index exists:
create Nonclustered ...
23
votes
1answer
1k views
Why Do My Nonclustered Indexes Use More Space When I Delete Rows?
I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.
I ...
8
votes
1answer
409 views
Index help for bad Query on fairly big table (2m)
I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records).
I have been trying to get the indexes working efficiently. There are a few other ...
0
votes
1answer
88 views
SQL Server indexes Work Sometimes [duplicate]
So I have the following indexes on my table:
CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link]
(
[Company] ASC,
[Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
2
votes
0answers
544 views
Default filegroup, specified in the SQL Server CREATE INDEX statement, is ignored (ON [DEFAULT] storage clause)
Am I missing something?
Documentation clearly states, that:
ON "default" Applies to: SQL Server 2008 through SQL Server 2017 and
Azure SQL Database.
Creates the specified index on the ...
-2
votes
2answers
64 views
Can a non-clustered index be transformed to a covering index in SQL Server?
I have a non-clustered index which includes one column. Now I want to alter the index and add additional two columns as covering columns. Is it possible? If yes, then can you, please, provide the T-...
11
votes
3answers
479 views
Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?
Given the next example:
IF OBJECT_ID('dbo.my_table') IS NOT NULL
DROP TABLE [dbo].[my_table];
GO
CREATE TABLE [dbo].[my_table]
(
[id] int IDENTITY (1,1) NOT NULL PRIMARY KEY,
[foo] ...
1
vote
1answer
312 views
Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
0
votes
1answer
122 views
use DBCC SHRINKDATABASE after rebuild / reindex [duplicate]
I have little free disk space.
Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?
9
votes
1answer
3k views
“Warnings: Operation caused residual I/O” versus key lookups
I've seen this warning in SQL Server 2017 execution plans:
Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.
...
1
vote
0answers
56 views
SQL Server Execution Plan
In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
2
votes
0answers
36 views
To cluster or not to cluster? That is the question!
I have a table with just one (nvarchar) column for tracking IDs that have been used, to ensure they aren’t duplicated. Should I use Clustered or Non-clustered Index for this? The only two operations ...
0
votes
1answer
273 views
Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?
I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
In it, she briefly ...
2
votes
2answers
4k views
Performance differences between RID Lookup vs Key Lookup?
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
