New answers tagged

3

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production. Setting that aside: There are a number of tools out there that claim to compare schema, and ...


0

The following is written for SQL Server, but should be quite the same with other RDBMS... The clustered index is physically sorted on the storage media and will cover all columns of your table - as if they were include columns. A good clustered index is bound to a column with an implicit sort quality, such as insertDateTime or a running number (e.g. with ...


0

If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries. There can be only one clustered index per table because it defines the physical data ...


10

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to fully scan the narrow index. It seems that you were expecting a nested loops with seeks on the clustered index? The execution plan shows that the table #ToPurge ...


-1

A primary key is a logical concept. When you create a primary key you are telling the DB that every row will be unique based on the columns specified in the primary key. How the DB enforces this uniqueness is up to the DB vendor. Microsoft has chosen to enforce the uniqueness of a primary key by creating a unique index.


-2

When you create Index or PK, SQL never creates a "File". When you create PK, it will be clustered by default, unless you already have clustered index or specified PK as NON CLUSTERED.


1

The leaf level of the Clustered index is the table. You still have the other levels of the B-Tree (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.



Top 50 recent answers are included