Questions tagged [clustered-index]

A type of index mostly used in SQL-Server, which aligns the data of a table with the index.

Filter by
Sorted by
Tagged with
0
votes
2answers
15 views

Index of which columns are NOT empty

I am trying to enhance the search performance of a table based on whether certain columns contain values. I have the following columns : id | link | text | date | lat | long | type If a record ...
0
votes
0answers
11 views

Is 'data corruption occurs in the clustered index' still open?

Based on sp_Blitz Result: DBCC CHECKDB Not Run Recently, there is a reported bug in SQL Server 2012 & 2014, Data corruption occurs in the clustered index when you run online index rebuild in SQL ...
3
votes
2answers
114 views

SQL Server - creating a clustered index in a large heap - should I add a column?

I'm going to add a clustered index to a fairly large table: over 7 million records 106 columns no PK no other indexes. There is an Id column which could potentially be used for the index ...
1
vote
0answers
14 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
31 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
41 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
0answers
18 views

Would a set of non-clustered index work better than a clustered index for my large table?

I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns: name="date", type ="DATE" name="account_id", type ="INT64" name="group_id", type =...
0
votes
1answer
29 views

Does the order for clustered index have the same constraints as non-clustered index on search?

I have a table filled with a bunch of data, and I want to use the following clustered index: [account_id, group_id, run_id, page_id, date] For a non-clustered index, if I were to to use WHERE ...
0
votes
1answer
41 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
67 views

How does an index speed up insertion in a table

How does index speed up insertion in a table? I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion? CREATE TABLE persons( id NUMBER , ...
-1
votes
1answer
22 views

Order of a 2-level b+tree with n-distinct (assume e.g. n=10) entries

Given a two-level clustered b+tree on a non-key attribute foodCategory (of which 10 distinct values exist), is it possible to calculate the order of the b+tree? Assuming an average 69% occupancy of ...
2
votes
3answers
85 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
16 views

Clustered Index in Big Partitioned Table on SQL Server 2019

We are in process of developing a project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose "Always-On" solution which will contain: One primary ...
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 ...
0
votes
1answer
26 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...
0
votes
1answer
66 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
1
vote
2answers
101 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
36 views

How to create a clustered index on a schema bound view with the least amount of contention caused?

I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 ...
1
vote
1answer
48 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...
12
votes
1answer
2k views

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view. Here's an example: CREATE VIEW ...
0
votes
1answer
595 views

Columnstore Index maintenance and finding fragmentation

We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a ...
1
vote
3answers
70 views

When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?
2
votes
1answer
183 views

Indexed view referencing objects on two different schemas

When I try to create/alter a view to create an index like this CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation ON sfdc.vSalPopulation (ID); I get the following error message Msg 1938, Level ...
0
votes
0answers
35 views

Rebuild clustered index online on specific partition

Can I rebuild clustered index ONLINE (MS SQL 2017 Developer Edition) for specific partition which is on partitioned table with different filegroups? Selected partition is on read_write filegroup, but ...
0
votes
1answer
63 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
0answers
42 views

Changing clustered key on a big table

I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way CREATE TABLE BigFactTable ( BigFactTableId bigint IDENTITY ...
0
votes
1answer
42 views

Is the default clustered index file physically the same file as the file containing the data records?

I have read that the records in a sorted data file (for a DB table) are sorted in the same order as the keys in its corresponding clustered index -- and a leaf node of clustered index contains the ...
1
vote
1answer
153 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 ...
9
votes
4answers
1k views

How do I intentionally fragment a SQL Server Index?

I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance ...
0
votes
1answer
253 views

Clustered Index taking huge amount of space

I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me. The database is SQL Server 2018. The table itself ...
21
votes
1answer
805 views

In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

I've been reading about SQL Server internals and every book or blog mentions this about backward scans. A backward scan of a clustered index cannot use parallelism The only post that said ...
2
votes
0answers
85 views

Clustered Index: Narrow vs Unique

I have run into an interesting dilemma working on optimizing a table in a Microsoft SQL database. Table details: The primary key is 3 parts (Date (date), Account (bigint), Field (nvarchar(4)). The ...
1
vote
1answer
70 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].[...
0
votes
0answers
8 views

InnoDB clustered index performance when using random values as primary key

By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
1
vote
1answer
23 views

Multi-dimensional range queries with single column indexes

I am reading a book that covers many database topics on a very high level, but doesn't dive deeper. It introduces multi-column indexes as a better way to query for 2 dimensional ranges. For example, ...
4
votes
3answers
487 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,...
2
votes
3answers
646 views

Postgres heap vs SQL Server clustered index

I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres. Can someone share their ...
0
votes
0answers
461 views

Create a clustered primary key on partitioned table

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
2
votes
2answers
439 views

Second index on junction table with composite primary key: include both or one column

I'm working on a database that will be very read-intensive, and uses a junction table that's read often and in both directions. The junction table contains two columns, IDA and IDB The primary key ...
1
vote
2answers
1k views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
0
votes
1answer
31 views

Primary index is being used, not the one which is on the field present in the where condition

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column. cat_id (primary key), acc_id, islive Another table is cat_brand table which has below fields ...
1
vote
0answers
55 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, ...
1
vote
2answers
181 views

Will Re-Seeding an Identity column back to 0 cause page splits?

Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine ...
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 ...
2
votes
2answers
3k 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 ...
2
votes
1answer
1k views

Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns ...
4
votes
2answers
1k 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 ...
2
votes
2answers
1k views

Can one make MySQL cluster on a unique index rather than a primary key?

The MySQL docs states that: When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. But that's not the only possibility, you can cluster off of a unique index instead: ...
3
votes
3answers
178 views

Is there any way to test which clustered index is better for specific queries?

We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we ...
2
votes
1answer
3k views

Non-Clustered Index takes much longer to create than clustered Index

I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create: CREATE ...

1
2 3 4 5 6