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.

Filter by
Sorted by
Tagged with
23
votes
1answer
2k 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 ...
16
votes
2answers
2k views

Why is my index not being used in a SELECT TOP?

Here's the run-down: I'm doing a select query. Every column in the WHERE and ORDER BY clauses are in a single non-clustered index IX_MachineryId_DateRecorded, either as part of the key, or as INCLUDE ...
15
votes
1answer
5k views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
13
votes
1answer
479 views

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

This is a strange situation, but I'm hoping someone has an answer. During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex. We checked its ...
12
votes
2answers
18k views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
11
votes
3answers
1k views

Field order in a composite index order with high selectivity and low selectivity fields

I have a SQL Server table with over 3 billion rows. One of my query takes an extremely long time so I am considering optimizing it. The query looks like this: SELECT [Enroll_Date] ,Count(*) AS [...
11
votes
3answers
662 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] ...
11
votes
1answer
6k views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
9
votes
3answers
1k views

Does a non-clustered index make any guarantees about row order?

I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-...
9
votes
1answer
4k 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. ...
8
votes
1answer
414 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 ...
8
votes
6answers
3k views

Is there any best number for rows count in a table to start using indexing?

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between ...
8
votes
1answer
5k views

Why can a non-clustered index not be dropped using online=on option

When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message. Msg 3745, Level ...
8
votes
3answers
954 views

Reasonable way to predict query performance over time

I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database. One of the first things ...
7
votes
3answers
2k views

SQL Server clustered index, index balancing and insert performance using NewID

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average ...
7
votes
2answers
2k views

Is heap a table without indexes at all or without a clustered index?

BOL seems to define a heap as a table without a clustered index. But many online posts seem to equate a heap to a table without any kind of indexes. Is there any subtlety I am not aware of? Thanks
7
votes
1answer
418 views

Query optimizer recommends adding index instead of using existing index

I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query. First the table. ...
7
votes
1answer
1k views

Any reason to have both an index with included columns and one without?

I am looking through a few slow running queries on a database that has under gone significant development (under a number of different developers) over the last couple years. One of the most commonly ...
7
votes
3answers
8k views

Does it make sense to create index with all columns included

We have a table with columns ID OtherTableId DataA DataB DataC DataD Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or ...
7
votes
1answer
2k views

Nonclustered index column order

I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order. If I run the following query: select col1,col2 from ...
7
votes
2answers
807 views

Clustered Table Scan Because of "SELECT *"

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
6
votes
3answers
7k views

Stop SQL Server from ordering results? [duplicate]

I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server ...
6
votes
2answers
651 views

Need for reaching data through clustered index with a non-clustered index

I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the ...
6
votes
2answers
10k views

Should I create a nonclustered index on all searchable columns or just each column separately?

I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already. CREATE NONCLUSTERED INDEX [...
6
votes
1answer
1k views

Temporary Tables With Nonclustered Indexes Including All Columns

Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY ...
6
votes
2answers
847 views

Wide clustered index vs multiple narrow nonclustered indexes?

Say I have a contrived Student table like so: CREATE TABLE Student ( Id IDENTITY INT, SchoolId INT NOT NULL, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL ) ...
5
votes
3answers
1k views

SQL Server 2016 not using index used in 2012

2016 plan here 2012 plan here I have almost identical tables with very small difference in row count. One on 2012, and the other on 2016. Indexing is identical. These VMs are in exact same ...
5
votes
1answer
290 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 ...
5
votes
2answers
6k views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
5
votes
2answers
883 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
5
votes
1answer
3k views

What is a non-clustered index scan

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered ...
5
votes
1answer
249 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 ...
5
votes
2answers
2k views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
5
votes
1answer
81 views

Drawbacks to add clustered keys

I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all. At a very ...
4
votes
2answers
2k views

NonClustered Index not being Used

I have a table (created below) that contains 321 rows. I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a ...
4
votes
2answers
790 views

Best indexing strategy for query that searches between two bigint columns

I have the following table in MS-SQL: CREATE TABLE [dbo].[dbip_locations]( [ip_from] [bigint] NOT NULL, [ip_to] [bigint] NOT NULL, [country_code] [nvarchar](64) NOT NULL, [region_name]...
4
votes
3answers
1k 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,...
4
votes
3answers
3k views

Why SQL Server uses Non-Clustered but not Clustered Index?

I have a table with 145 million rows CREATE TABLE [dbo].[RFTest]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [datetime] NOT NULL, [LoanNum] [int] NOT NULL, [GLSourceSystem] [varchar](...
4
votes
1answer
388 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have to ...
4
votes
1answer
853 views

Will a nonclustered index with a unique column always address all queries filtering that column first?

I have found large heap tables that are over-indexed and for example have multiple different nonclustered indexes with different columns, but some of those indexes have the (only one present) primary ...
4
votes
2answers
2k views

Clustered vs Non Clustered Indexes for a Primary Key

I have a large table with around 19 million records in it. The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column. An application generates these ...
4
votes
1answer
1k views

Non-clustered index with all columns for "select * from"?

I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013' query on a table that contains 340 columns and 3.4M rows. Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a ...
4
votes
1answer
87 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 ...
4
votes
1answer
125 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, ...
4
votes
1answer
937 views

CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database. Here is some information about the index: Rows: 112 Millions Disk ...
4
votes
1answer
176 views

Is Non-Clustered index with all others columns included equivalent to a Clustered Index

Is a nonclustered index on a column with all other columns included almost like a clustered index on a different column? Will it actually duplicate the full Table? Normally you might not need to do ...
4
votes
1answer
1k views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution plan for ...
4
votes
2answers
595 views

Interesting DBCC CHECKDB scenario

I woke up this morning to corruption in one of my databases. I have a job that runs daily DBCC CHECKDB's for all databases on a server. The output indicated corruption in only ONE table (fortunately)...
4
votes
1answer
256 views

SQL Server suggestion to create nonclustered index - on 2 columns, reversed

I have a query, where SQL Server 2012 suggested a create a nonclustered index as follows: CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[tblUserCourses] ([courseID]) INCLUDE ([userID]) I created ...
4
votes
1answer
4k views

How to copy non clustered indexes in transactional replication

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to ...