Tagged Questions
-1
votes
0answers
43 views
Unexpected index scan against partitioned table
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
1
vote
1answer
71 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 ...
5
votes
2answers
146 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
votes
0answers
62 views
What is meant by Clustering index and Multilevel indices? [closed]
What is meant by Clustering Index and Multilevel Indices ?
I could not find much when I googled it.
1
vote
2answers
121 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 ...
4
votes
1answer
67 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
46 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
95 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
1k 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
157 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 ...
2
votes
1answer
211 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 ...
1
vote
1answer
78 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
139 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
184 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
264 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);
...
4
votes
2answers
591 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
2answers
791 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
votes
2answers
122 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 ...
3
votes
1answer
99 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 ...
2
votes
2answers
170 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
160 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 ...
11
votes
2answers
813 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?
