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

learn more… | top users | synonyms

21
votes
2answers
2k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
5
votes
2answers
2k 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 ...
2
votes
2answers
756 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
3
votes
2answers
3k views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
6
votes
2answers
924 views

Clustered vs non-clustered tables

It happened I had to work with both SQL Server and Oracle for quite a while (thankfully not at the same time). What still puzzles me is the approach to storing tables as balanced trees. In ...
2
votes
3answers
1k views

Meaning of “nonclustered located on primary”

When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example: nonclustered located on PRIMARY (on foreign keys) nonclustered, ...
0
votes
2answers
1k views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
1
vote
1answer
463 views

Would Mongo's ObjectID work well in InnoDB's clustered index?

Mongo's ObjectID is defined like this: ObjectId is a 12-byte BSON type, constructed using: a 4-byte value representing the seconds since the Unix epoch, a 3-byte machine identifier, ...
3
votes
2answers
4k views

SQL Server clustered index high fragmentation

I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index. Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
4
votes
3answers
3k 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 ...
7
votes
1answer
4k 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 ...
1
vote
4answers
6k views

Speed up large clustered index creation?

I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB. And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
1
vote
2answers
407 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
2
votes
1answer
278 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
5
votes
1answer
524 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
2
votes
1answer
367 views

How to solve index missing problem in one table, while other table of same property does not produce any error?

Two databases with same structure at least for my eye. One of them was used as training/testing, where I can add list of products in Art table via a program's extension FDT but not in Art table of ...
3
votes
1answer
76 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
3
votes
1answer
74 views

How to efficiently hit an index in a query that uses IN (mysql)

I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription. Here is a sample table: ...
0
votes
1answer
133 views

clustered index - data warehouse

My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system ...
0
votes
1answer
5k views

fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

Are Primary Index and Clustered Index similar in concept Secondary Index and Non-Clustered Index similar in concept
3
votes
2answers
271 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
5
votes
2answers
1k views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
18
votes
3answers
2k 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 ...
6
votes
2answers
841 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
2
votes
1answer
761 views

Index with multiple leaf levels

I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
7
votes
1answer
1k views

What factors go into an Indexed View's Clustered Index being selected?

Breifly 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 ...
3
votes
1answer
525 views

How do I automate converting heaps into clustered indexes?

I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index. What is the best automated method for converting these heaps? Since ...
4
votes
3answers
2k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
3
votes
0answers
571 views

SQL Server 2008 R2 clustered index issue

Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization. A stored procedure is written for our application which contains a cursor in it. ...
1
vote
1answer
165 views

Finding exact row in Clustered index leaf page

When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page). Every data page has row ...
2
votes
1answer
185 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
4
votes
1answer
789 views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
5
votes
2answers
257 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 ...
4
votes
2answers
632 views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); ...
2
votes
0answers
423 views

Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population [closed]

When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key: create table #temp ( field1 int not null field2 int ...
4
votes
2answers
1k views

Should a table have a clustered index even if it doesn't have appropriate fields for it?

If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices ...
5
votes
1answer
2k 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 ...
13
votes
2answers
10k 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 ...
2
votes
1answer
148 views

After dropping the clustered index, it is still attempting to do a clustered insert [closed]

I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows. I am running the following script to do it: USE ...
5
votes
2answers
181 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
1
vote
1answer
316 views

Update and select concurrently running on same set of rows but different indexes are referred?

In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of ...
7
votes
3answers
757 views

What happens to non clustered index when included column is updated using update statement?

Question on non clustered index with included columns (DB - MS SQL Server). I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
3
votes
1answer
1k views

Clustered Index Update on Nonclustered Columns

Using: SQL Server 2008 R2 I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
0
votes
2answers
1k views

Clustered index always better than Non-Clustered index?

When I have a clustered index on 'A', is it better to use this clustered index even if I have selection of 'A=constant'? If not, why? Thank you!
3
votes
1answer
139 views

should we replace some non-clustered indexes with clustered indexes

We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk. I am thinking that ...
3
votes
3answers
2k views

Does ORDER BY on a clustered key effect performance?

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries. Does this ORDER BY affect ...
2
votes
2answers
242 views

Execution plan doesn't use clustered index!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID. I have created a clustered B+tree index on the field foodID of my table foo. And I do the following: ...
5
votes
1answer
194 views

Composite or Single-Field Clustering Key

My DBA and I are having a disagreement on index structure. Consider data for a medical claim... We have the Header table with fields like: ClaimId (varchar(50)), PaidAmount, MemberID... and for ...
33
votes
3answers
2k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
16
votes
3answers
17k 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, ...