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.
166
questions
0
votes
0answers
22 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 ...
0
votes
0answers
47 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
1answer
52 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 ...
0
votes
1answer
64 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 ...
0
votes
2answers
34 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 ...
1
vote
3answers
61 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].[...
0
votes
1answer
23 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 ...
0
votes
4answers
79 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 ...
0
votes
0answers
11 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 ...
1
vote
2answers
86 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 ...
1
vote
2answers
59 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
2answers
38 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 ...
1
vote
1answer
83 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 ...
1
vote
1answer
116 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 ...
0
votes
4answers
111 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 ...
1
vote
1answer
56 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 = '...
1
vote
1answer
82 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 ...
0
votes
2answers
56 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
108 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
64 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
45 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
168 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
22 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
20 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
43 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
43 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
48 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
44 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 ...
1
vote
1answer
76 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
106 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
271 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 ...
1
vote
1answer
60 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
71 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
285 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
82 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
117 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
103 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
203 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
181 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
59 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
85 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
51 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
88 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
142 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
113 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
392 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
68 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
116 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
96 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].[...
