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
0 votes
0 answers
5 views

How to improve select and update query performance on Table containing DateTime in MS SQL server

I have 1000 over tables. Each table corresponds to one particular tag, and has the same structure as following: Column [Time_stamp] in the type of datetime Column [tagdata] in the type of float. All ...
Zhang yy's user avatar
0 votes
1 answer
69 views

Fragmentation when creating non-clustered index

When I was dealing with fragmentation, I had a question about a non-clustered index. In non-clustered index actual table data (typically stored in a clustered index, otherwise a heap) is stored ...
volodya_neftyannik's user avatar
0 votes
1 answer
39 views

Is there ever a reason to create a non-clustered index on a column that is already the clustered primary key?

Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to also make a non-clustered index on that same column with no other columns in any ...
J. Mini's user avatar
  • 135
0 votes
0 answers
99 views

why the index can take a long time to create on small table?

SQL Server 2019 Index was created with online = on. Table has about 35000 rows with 7 columns with int, uniqueidentifier and datetime data types. Table has only clustered index. sp_whoisactive doesn't ...
Il26's user avatar
  • 1
0 votes
1 answer
45 views

Index consolidation of two similar indexes

We have a primary table that drives most of the work within our product with many columns. Index 1 has a key consisting of: Column A, Column B, Column C, Column D Index 2 has a key consisting of: ...
Brian's user avatar
  • 3
0 votes
1 answer
102 views

Non clustered index not used

I have the following tables: User that has a non clustered Index on UserName and Active columns. Notification that has a non clustered index on the UserId I don't understand why in the first ...
Norbert Forgacs's user avatar
0 votes
1 answer
473 views

Modify an existing non clustered index or create a new index in SQL Server 2012

I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the ...
lifeisajourney's user avatar
1 vote
1 answer
124 views

Query for MAX/MIN of non-clustered index field for partitioned table

We have a table partitioned on a two-year based hash function from the system date at the insertion time (let's call it PartitionKey). It also has a non-clustered index on a single field (Key). We ...
PM 77-1's user avatar
  • 123
2 votes
2 answers
110 views

To INCLUDE or Not INCLUDE in an Index

Current Situation I am observing a simple statement that is querying one table and accessing multiple indexes to retrieve the data: SELECT DISTINCT feld16, zahl4, feld12, feld19 FROM object1 WHERE (...
John K. N.'s user avatar
  • 17.6k
2 votes
2 answers
828 views

Why is MySQL Delete on Foreign Key Index locking a record not part of the index (leads to DEADLOCK)

I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index. I'm not sure why this could happen. Here's my scenario: ...
Chris Landry's user avatar
0 votes
1 answer
77 views

Can a Clustered Index based on a key field be more performant than a non-clustered covering index for a big table

I am researching a DW reporting table that will grow very large. For simplicity, I will show the table as follows: BigTable -------- TableID INT IDENTITY NOT NULL, CompanyName NVARCHAR(100) NOT NULL ...
Ross Bush's user avatar
  • 505
2 votes
0 answers
72 views

Clustered Index being used over non-clustered with same key

I have a table that has a clustered index (CI) on the field filename. It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
Tom's user avatar
  • 29
1 vote
1 answer
63 views

Index creation on partitioned table is not completely offline in Standard Edition

We have SQL Server 2019 Standard Edition Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit)...
sepupic's user avatar
  • 10.7k
0 votes
0 answers
73 views

Setting up an HTAP Environment

I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment. I'm curious, going forward ...
Miguel Sanchez's user avatar
2 votes
2 answers
2k views

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, ...
Nuno's user avatar
  • 809
0 votes
1 answer
461 views

Index Optimization for tables that are truncated and charged everyday

I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server. When I ...
Hamza Hamdani's user avatar
0 votes
0 answers
261 views

Creating non clustered index in sql server in large table

Table is having 180 million records. Has a primary key(WExtractGrouping, Id, PEventId, CAId, TEventId, EventDate). No other indexes except clustered index on pk. CREATE NONCLUSTERED INDEX [...
Narendra's user avatar
14 votes
1 answer
763 views

Why am I seeing key lookups for all rows read, instead of all rows matching the where clause?

I have a table such as the following: create table [Thing] ( [Id] int constraint [PK_Thing_Id] primary key, [Status] nvarchar(20), [Timestamp] datetime2, [Foo] nvarchar(100) ) with a ...
Twicetimes's user avatar
0 votes
1 answer
85 views

Why new nonclustered index cause more IO reads on table?

We are in process of creating new non-clustered index for specific views. One of the view looks like this: SELECT * FROM [dbo].[table_backup] UNION ALL ( SELECT * FROM [dbo].[tableA] a LEFT JOIN [dbo]....
adam.g's user avatar
  • 335
0 votes
2 answers
626 views

What is the best way to measure INDEX impact on production?

We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
grochu's user avatar
  • 45
0 votes
2 answers
74 views

What is the best way in terms of non blocking table for creating NONCLUSTERED INDEX for partitioned tables?

We are using SQL Server 2016 and have 3 partitioned tables (every table around 300-500 milion of records). For every table would like to create NONCLUSTERED PARTITIONED INDEX. What is the best way to ...
grochu's user avatar
  • 45
5 votes
2 answers
787 views

With a multi column non clustered index, and the SELECT on middle column, why does SQL server query this index rather than scanning the table?

A single non clustered is set up on LastName, FirstName, MiddleName - in that order. SELECT * FROM PERSON WHERE FirstName='xyz' Why does the execution plan use the index and not scan the table ...
variable's user avatar
  • 3,541
1 vote
1 answer
594 views

Why engine choose Index Scan instead of Index Seek for Group By query?

I have query: SELECT M_nvarchar_3 , MA_nvarchar_40 , W_nvarchar_4 , LB_nvarchar_2 , SUM(ST_decimal_13_3) , SUM(CW_decimal_13_3) ...
grochu's user avatar
  • 45
0 votes
0 answers
60 views

Working with index-key sizes which are close to the DBMS's index-key size limit

I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
user5721565's user avatar
0 votes
2 answers
205 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
grochu's user avatar
  • 45
0 votes
2 answers
70 views

Is it worth to use NONCLUSTERD FILTERED INDEX with clause `<>''`?

I am just thinking to create NONCLUSTERED FILTERED INDEX with condition: WHERE varcharColumn <> '' This columns contains around 3127 different values including value ''. All the queries has ...
grochu's user avatar
  • 45
2 votes
0 answers
664 views

How to estimate the nonclustered index size before its creation?

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong. Scenario 1 Create table mytable1 with clustered index and ...
Rauf Asadov's user avatar
  • 1,253
0 votes
2 answers
54 views

Index Drop for Partial Overlap?

I'm working with a 50mil row table with the following: Clustered Index keyed on [Col1] Nonclustered Index keyed on [Col1], [Col2] (no includes) It seems like the right call here would be to drop #2 ...
AccidentalDBA_CO's user avatar
0 votes
1 answer
217 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
gopikrishna's user avatar
-2 votes
1 answer
115 views

Will index on a partition that doesn't include partition column work fast?

Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc. [CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
nightcoder's user avatar
0 votes
0 answers
38 views

Searching text using LIKE, and Included Column

SELECT * FROM Table WHERE (Column1 = 'ED69K') SELECT * FROM Table WHERE (Column1 LIKE '% ED69K%') I have a wide table with 25 columns on the table. The above queries do need to do key lookup ...
SQL_NoExpert's user avatar
  • 1,049
1 vote
1 answer
651 views

Should I rebuild index in non clustered index?

I'll be rebuilding our index in SQL Server since our fragmentation rate on all indexes for all tables are so high. Should I include to rebuild my non-clustered index also? Or rebuilding of clustered ...
Dwightqwerty's user avatar
0 votes
0 answers
230 views

Rebuild indexes makes switch partitions useless (SQL Server)

I have a big table containing multiple indicators per ticker. The table is partitioned by indicator. Each indicator has nearly 50 million rows and currently there are about 30 indicators and they will ...
Yana's user avatar
  • 101
0 votes
0 answers
183 views

SQL2005 Index exists in sys.indexes but not on table

I have a routine that runs through all the non-clustered indexes in a given database on SQL2005 (SP3), and reorganizes and rebuilds them (if fragmentation > 20%). This process is erroring out on ...
Kulstad's user avatar
  • 55
1 vote
1 answer
396 views

Is there a performance difference between a Dropped Index and a Disabled Index, for a NON-Clustered Index

I'm aware that there are substantial differences between Dropping and Disabling a CLUSTERED Index, but I want to know about NON-CLUSTERED indexes. And I'm aware that Disabling an Index retains the ...
Brondahl's user avatar
  • 286
0 votes
0 answers
60 views

Non cluster indexing with combination or on single entity for multiple columns

I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column table ...
Kiwi Rupela's user avatar
0 votes
0 answers
211 views

Update query performance improvement (MS-SQL)

UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 ON M.MARKID = S.MARKID AND S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
Luv Gupta's user avatar
0 votes
3 answers
606 views

Space consumed by a column in non clustered index

I m planning to add a key column index in one of the existing non clustered index in a big table which have millions of rows rather than create a new non clustered index. Is that any way for me to ...
Mario's user avatar
  • 39
0 votes
1 answer
149 views

What should be the size of nonclustered index?

I was experimenting with nonclustered indexes, especially their sizes. My aim was to understand how the amount of index keys in Clustered index affects the final size of Nonclustered index. I ...
Rauf Asadov's user avatar
  • 1,253
0 votes
2 answers
87 views

SQL Server - Overlapping NC Indexes, Drop Question

I'm doing some index cleanup/tuning on a SQL '14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following ...
MinnRick's user avatar
  • 119
1 vote
3 answers
271 views

SQL Server - Unique Constraint Syntax

Is there a functional difference between the tables built in the following two ways? CREATE TABLE [dbo].[test_uq]( [ID] [int] NOT NULL, [f1] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[...
MinnRick's user avatar
  • 119
0 votes
1 answer
47 views

SQL 2016 - NU/NC Index Missing From sys.partitions

Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view? There is a CI on the same table and there is a record for it in the view. My ...
MinnRick's user avatar
  • 119
0 votes
4 answers
343 views

Is a Non-Clustered index needed when referencing a small table with a Foreign Key

I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time. Assume there are 2 tables, one table with a lot of ...
Ozkan's user avatar
  • 175
0 votes
0 answers
230 views

How to avoid duplicate in mysql INNODB transaction for non index key?

I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
dpkrai96's user avatar
1 vote
2 answers
283 views

Index with few seeks and many updates [closed]

Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the ...
xhr489's user avatar
  • 805
1 vote
2 answers
581 views

How to reduce fragmentation of nonclustered index on date partitioned log table

Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a ...
dba_in_training's user avatar
0 votes
2 answers
200 views

Key Lookup isn't applied by default?

I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories: As you can see the table has a non-clustered index on the column CategoryName. This SQL ...
A.HADDAD's user avatar
  • 119
1 vote
1 answer
1k views

Select Query slow for Non-clustered index

I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table compared to table without index. Query Used: select * from table order by customer_name It is too ...
IT researcher's user avatar
1 vote
1 answer
902 views

Facing deadlock after introducing a new Non-clustered Index on Heap

I am facing a strange issue wherein I had analyzed a stored procedure which runs every 3 minutes and was putting load on CPU. I found out that there are number of select statements which is part of ...
Learning_DBAdmin's user avatar
0 votes
4 answers
996 views

Using an index when leading column is not in the predicate

Are there any circumstances when an index could be used when not all the index columns are included in the predicate? In my case the leading column in the index is unique and is part of the unique ...
Mike Fleming's user avatar