Questions tagged [clustered-index]
A type of index mostly used in SQL-Server, which aligns the data of a table with the index.
34
questions
33
votes
3
answers
42k
views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
- 5,333
22
votes
5
answers
6k
views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in the ...
- 12.2k
8
votes
1
answer
6k
views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
A ...
- 529
20
votes
1
answer
3k
views
What factors go into an Indexed View's Clustered Index being selected?
Briefly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
- 690
40
votes
3
answers
7k
views
Performance of Non Clustered Indexes on Heaps vs Clustered Indexes
This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
- 84.7k
17
votes
2
answers
2k
views
Is there any tangible difference between a unique clustered index and a clustered primary key?
I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
- 1,626
15
votes
2
answers
8k
views
What is a Clustered Index?
I need a short explaining of clustered index.
What is a clustered index?
What are best practices for using the clustered index?
- 633
13
votes
2
answers
14k
views
Why SQL Server has 900 byte index size limit
Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer.
I can think of this way to answer this question.
Page size ...
- 331
33
votes
3
answers
80k
views
Efficient INSERT INTO a Table With Clustered Index
I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER.
E.G.:
INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, ...
- 2,807
31
votes
3
answers
10k
views
What are valid usage scenarios for HEAP tables?
I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
- 413
11
votes
2
answers
15k
views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
- 255
11
votes
1
answer
4k
views
noexpand hint for non-enterpise edition and performance
I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:
Indexed views can be created in any
...
- 6,722
8
votes
3
answers
2k
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-...
- 305
5
votes
2
answers
1k
views
Howto Identify Clustered Index Candidates
I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
- 4,787
3
votes
1
answer
2k
views
Is a clustered index on column A the same as creating a table ordered by A?
Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables:
The CLUSTER command reorders the original table by ...
- 726
3
votes
1
answer
4k
views
What happens when you delete a Clustered index and Non Clustered Index once created
What happens when we dropped a Cluster index and Non Clustered Index once they are created?
What will happen to the data at the leaf level according to Clustered Index ?
- 1,590
0
votes
2
answers
248
views
Which should I use on my DB? - Clustered Index or Non Clustered Index or Both
I just need advice on how I am going to optimize my database.
Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN
I have that format of tables on my database. Each tables ...
40
votes
6
answers
12k
views
Why do sequential GUID keys perform faster than sequential INT keys in my test case?
After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid()...
- 591
11
votes
2
answers
5k
views
SUM of DATALENGTHs not matching table size from sys.allocation_units
I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken?
SELECT
SUM(DATALENGTH(Field1)) ...
- 1,771
9
votes
4
answers
3k
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
...
- 193
6
votes
3
answers
8k
views
Cannot rebuild index, but there's no reason why not?
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
- 161
6
votes
2
answers
722
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 ...
5
votes
2
answers
5k
views
Disk Space Considerations for Rebuilding a large Clustered Primary Key Index
We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived ...
- 437
5
votes
3
answers
2k
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,...
- 53
5
votes
1
answer
969
views
Column Store Index over an clustered index (created when primary key is declared) - SQL Server
When a clustered index is created, the table itself becomes the index structure sorted by the index key.
Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
- 155
4
votes
2
answers
3k
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-...
- 43
4
votes
1
answer
2k
views
Does rebuilding a clustered index on an IDENTITY column do anything useful?
We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and ...
- 41
3
votes
1
answer
1k
views
NEWSEQUENTIALID() resets sequence on server restart
MSDN learns me that NEWSEQUENTIALID():
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows,...
- 191
3
votes
1
answer
116
views
Moving Clustered Index to new filegroup results in excessive filegrowth
I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup.
Problem:
A 315GB compressed Clustered ...
- 31
2
votes
1
answer
2k
views
Save performance with large update on Index with Included Column
Points
------------------
PK QuestionId int (+30.000.000 distinct values)
PK EventId int (large batches where 80.000 rows have the same EventId)
Value smallint
The table is about 40 ...
- 365
2
votes
1
answer
647
views
Clustered Index with nightly ETL process
We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting. The clustered index on ...
- 757
1
vote
2
answers
396
views
Clustered Index Misunderstanding
I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record.
Some say, clustered index stores the whole records in ...
user71787
1
vote
1
answer
391
views
What is fragmenting my index on a table with stable traffic?
I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's ...
- 113
0
votes
1
answer
418
views
Does cost of an Operation matter.?
I have a query which is slow when i run from SSMS and from application.I am trying to tune it.I took the execution plan and on checking I can see cost of Clustured Index Seeks on a table is 85%.That ...
- 1,315