Stack Overflow
Stack Overflow

Questions tagged [clustered-index]

A clustered index determines the physical order of data in a table.

A clustered index determines the physical order of data in a table. As the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values.

Source: MSDN (Using Clustered Indexes)

508 questions
0
votes
1 answer

Removing index for bulk insertion of data

I was reding this post by Pinal Dave and found this line: Unindexed tables are good for fast storing of data. Many times, it is better to drop all the indexes from table and then do bulk of INSERTs and restore those indexes after that. Is that…
Aparan
  • 1,263
  • 3
  • 12
  • 17
0
votes
1 answer

is it good to use combined primary key for MySQL InnoDB clustered index?

I'm trying to build an aggregated news/blog/forum website from multiple sources. Because most queries are likely to be within same time periods for written_time column, I'm thinking about taking advantage of clustered index ordered by…
curinno
  • 38
  • 1
  • 7
0
votes
0 answers

Tables with clustered indexes or heap tables?

What if I have table X (heap table) that is being frequently updated (INSERT and DELETE only), and table Y which can never exceed 2000 rows. Table X has a foreign key column that references a unique index of table Y, and the relationship type is…
Stylock
  • 1,459
  • 2
  • 12
  • 14
0
votes
2 answers

Code not working with clustered index

I have an application where the instruction says that it doesn't work with clustered index with more than one field. Couldn't resist testing and it is true. The app gets stuck in some endless lopp if i add a clustered index with 2 fields. I thought…
adrianm
  • 14,468
  • 5
  • 55
  • 102
0
votes
5 answers

SQL 2005: Keys, Indexes and Constraints Questions

I have a series of questions about Keys, Indexes and Constraints in SQL, SQL 2005 in particular. I have been working with SQL for about 4 years but I have never been able to get definitive answers on this topic and there is always contradictory…
MrLane
  • 1,728
  • 22
  • 41
0
votes
2 answers

the role of index, clustered index and difference betw primary key with those

Im student studying DB.. I was studying big physical three join operations(nested loop, sort merge, and hash join). and I dont know how index is used with the above joins.. here I have question.. what is index exactly...? is it just key? what is…
Swen
  • 79
  • 2
  • 3
  • 8
0
votes
1 answer

Rearrangement of records in a table with clustered indexing

Assumption : In case of clustered indexing, the order of the records on the disk is the same as that of the clustered indices. Problem : Is the data rearranged each time on the disk when a new record is inserted? Isn't that a big hit on the…
Abhishek Jain
  • 4,478
  • 8
  • 34
  • 51
0
votes
1 answer

What's for the RavenDB clustered mode

On windows environment, i wonder if RavenDB cluster mode exist only to have failover or does increase read performance too?
Christophe Debove
  • 6,088
  • 20
  • 73
  • 124
0
votes
1 answer

Sql Server table structure

I have the following data i want to upload: (Pair) (Date) (Time) (Bid) (Ask) EurUsd 01/01/2012 18:04:01 1.55037 1.55137 EurUsd 01/01/2012 18:04:20 1.55018 1.55118 EurUsd 01/01/2012 18:04:22 1.55031 1.55131 EurUsd …
Hans Rudel
  • 3,433
  • 5
  • 39
  • 62
0
votes
2 answers

No real performance gain after index rebuild on SQL Server 2008

I have a table whose compound clustered index (int, DateTime) was 99% fragmented. After defragmenting and making sure that statistics were updated, I still get the same response time when I run this query: SELECT * FROM myTable WHERE myIntField =…
Adi
  • 5,113
  • 6
  • 46
  • 59
0
votes
0 answers

Joining tables via PK Columns when one is CLUSTERED whereas the other is NON-CLUSTERED... Bad Idea?

Can it have a negative performance impact to INNER JOIN two tables via PrimaryKey columns while one table has a CLUSTERED Index on its PrimaryKey column whereas the other table has a NON-CLUSTERED index on its PrimaryKey column?
pencilCake
  • 51,323
  • 85
  • 226
  • 363
0
votes
1 answer

Mysql How to check and change the value of MaxNoOfOrderedIndexes variable of NDB Cluster

When I am trying to create a table using NDB storage engine I get the following error: "Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB" How to change the size of this variable? I can't figure out where this is…
MySQL DBA
  • 5,692
  • 21
  • 54
  • 71
0
votes
1 answer

Indexing for uniqueidentifier column in table

i have created a table where i used unique identifier(GUID) as a primary key of table. Now i need to create a indexing on my table which one will be best for me..i am going to use this table for error logging. Following is my table structure CREATE…
Chetan S
  • 935
  • 1
  • 11
  • 21
0
votes
1 answer

Full-text indexing vs %% and clustered index

I have two questions. Does it make sense when I have to choice of a clustered index on an Integer type column instead of choosing it over string values type column. Why? The clustered index will cause a reordering of the page data whenever an…
Pankaj
  • 9,749
  • 32
  • 139
  • 283
0
votes
1 answer

Does performance improve when placing a nonclustered index on a field that already has a clustered index?

I executed an update statement along the following lines yesterday: UPDATE MainTable Set SomeField = SubsetTable.SomeField where MainTable.MainTableKey = SubsetTable.MainTableKey where SubsetTable is a subset of MainTable and has the same Primary…
Karl
  • 5,573
  • 8
  • 50
  • 73
1 2 3
…
33
34