Tagged Questions

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

learn more… | top users | synonyms

0
votes
1answer
125 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
4k 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
2
votes
2answers
216 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
898 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 ...
15
votes
2answers
913 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
658 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
546 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
362 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
1k 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
486 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
149 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
162 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
511 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 ...
4
votes
2answers
199 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
460 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
359 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
1k 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 ...
8
votes
2answers
6k 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
136 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
164 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
305 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
585 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
126 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
1k 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
219 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
187 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 ...
29
votes
3answers
1k 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 ...
15
votes
3answers
11k 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
votes
2answers
823 views

Why is query using Clustered Index when it shouldn't?

Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
2
votes
2answers
490 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
7
votes
1answer
2k 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 ...
31
votes
6answers
5k 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 ...
4
votes
3answers
628 views

Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Is it possible to create an InnoDB table without a Primary Key? Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
16
votes
4answers
2k 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 ...
29
votes
3answers
2k 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 ...
11
votes
2answers
7k 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?
12
votes
2answers
1k 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?
11
votes
1answer
668 views

SQL Server - How are data pages stored when using a clustered index

I recently heard that the data pages in a clustered index aren't stored contiguously. Is this true? Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I ...