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.
190
questions
1
vote
0
answers
26
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 ...
- 19
1
vote
1
answer
26
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)...
- 10.4k
0
votes
0
answers
30
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 ...
2
votes
2
answers
453
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, ...
- 634
0
votes
1
answer
83
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 ...
0
votes
0
answers
83
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 [...
- 1
14
votes
1
answer
695
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 ...
- 263
0
votes
1
answer
76
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]....
- 257
0
votes
2
answers
90
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 ...
- 45
0
votes
2
answers
50
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 ...
- 45
5
votes
2
answers
603
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 ...
- 2,874
1
vote
1
answer
398
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)
...
- 45
0
votes
0
answers
53
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 ...
- 101
0
votes
2
answers
73
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 ...
- 45
0
votes
2
answers
62
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 ...
- 45
1
vote
0
answers
241
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 ...
- 1,193
0
votes
2
answers
46
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 ...
- 115
0
votes
1
answer
124
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 ...
- 11
-2
votes
1
answer
55
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 ...
- 97
0
votes
0
answers
36
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 ...
- 1,007
1
vote
1
answer
219
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 ...
- 21
0
votes
0
answers
70
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 ...
- 101
0
votes
0
answers
87
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 ...
- 33
0
votes
1
answer
72
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 ...
- 101
0
votes
0
answers
29
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 ...
- 123
0
votes
0
answers
68
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 <> '...
0
votes
3
answers
202
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 ...
- 39
0
votes
1
answer
80
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 ...
- 1,193
0
votes
2
answers
58
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 ...
- 119
1
vote
3
answers
109
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].[...
- 119
0
votes
1
answer
31
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 ...
- 119
0
votes
4
answers
134
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 ...
- 175
0
votes
0
answers
65
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 ...
- 41
1
vote
2
answers
140
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 ...
- 655
1
vote
2
answers
248
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 ...
0
votes
2
answers
79
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 ...
- 119
1
vote
1
answer
521
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 ...
- 3,075
1
vote
1
answer
382
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 ...
- 3,764
0
votes
4
answers
369
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 ...
- 11
1
vote
1
answer
263
views
Non-clustered index with WHERE clause
I have a large table (around 100 million rows). I am trying to optimize searches. Most of my searches use two columns: bar nvarchar(64) and foo nvarchar(64).
I am only interested in foo when:
foo = '...
- 173
1
vote
1
answer
295
views
Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?
The Backstory:
I have a table with the following structure:
CREATE TABLE WideTable1
(
BoringColumn1,
BoringColumn2,
CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
- 26.7k
0
votes
2
answers
283
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
2
answers
352
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-...
- 275
1
vote
1
answer
207
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
1
answer
203
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 ...
- 103
5
votes
1
answer
598
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 ...
- 26.7k
0
votes
0
answers
56
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, ...
- 21
1
vote
0
answers
29
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 ...
- 21
0
votes
1
answer
183
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] ...
- 21
0
votes
1
answer
107
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 ...
- 113