Tag Info

Hot answers tagged

34

To check your request I created 2 tables following this scheme: 7.9 million records representing balance information. an identity field counting from 1 to 7.9 million a number field grouping the records in about 500k groups. The first table called heap got a non clustered index on the field group. The second table called clust got a clustered index on ...


28

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney. It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row Edit, Feb 2012 Found authoritative article by Kimberly L. Tripp "The Clustered ...


26

Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of ...


25

Ask yourself another question: If the entire database is in memory and I never have to touch the disk, do I want to store my data in an ordered B-tree or do I want to store my data in an unordered heap? The answer to this question will depend on your access pattern. On most cases your access requires single row look-up (ie. seeks) and range scans. These ...


18

If you use a well-chosen clustered index, you are more likely to get all the related data you need in fewer pages of data. That is, you can hold the data you need in less memory. This gives a benefit regardless of whether you use spinning disks or SSD. But you're correct that the other benefit of a clustered index -- to read/write related data ...


18

To sum it up, in modern SQL Server versions a clustered key on an identity column is the preferred option these days.


17

I modified @Phil Sandler's code to remove the effect of calling GETDATE() (there may be hardware effects/interrupts involved??), and made rows the same length. [There have been several articles since SQL Server 2000 relating to timing issues and high-resolution timers, so I wanted to minimise that effect.] In simple recovery model with data and log file ...


16

On a fresh database in simple recovery model with the data file sized at 1GB and the log file at 3GB (laptop machine, both files on the same drive) and recovery interval set to 100 minutes (to avoid a checkpoint skewing the results) I see similar results to you with the single row inserts. I tested three cases: For each case I did 20 batches of inserting ...


14

In simple terms... A telephone directory: the data is the index/the index is the data. To look you up, I'd start with Rezaei, Amir for example. No external lookup is needed. In database terms: The table data and clustered index are one and the same (in SQL Server, also InnoDB, Oracle IOT) Best practice is narrow, numeric, strictly increasing (think ...


14

If an index is very small (I believe less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes. Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be ...


13

The primary problems with GUIDs, especially non-sequential ones, are: Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index. Index fragmentation: It is virtually impossible to keep a non-sequential GUID column ...


13

When a table has a clustered index, the index is the table data (otherwise you have a heap type table). A rebuild of the clustered index (any index in fact, but the space wouldn't be counted as "data" for a non-clustered index) will result in partially used pages being merged into a more full form. As you insert data into an index (clustered or otherwise) ...


12

As Kimberly Tripp - the Queen of Indexing - explains quite nicely in her blog post The Clustered Index Debate continues..., having a clustering key on a database table pretty much speeds up all operations - not just SELECT. SELECT are generally slower on a heap as compared to a clustered table, as long as you pick a good clustering key - something like an ...


12

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key. Oh, and the fact that a table can have one PK and many UK :-). These are both differences in INTENT not in PERFORMANCE. Otherwise, ...


12

In short, no. A primary key by definition requires uniqueness, an index on the primary key field is the database engines route to enforcing this constraint. From BOL: When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast ...


12

This can also happen with very LARGE indexes. I had some indexes on a table with around 700m rows that I couldn't defragment below around 30%. The issue was not enough contiguous free space inside the database to arrange the index consecutively. To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and ...


12

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must ...


11

Yes, it absolutely still does make sense. You're thinking too low-level in your approach. SQL Server (in a very very simplified explanation) stores clustered data in a B-tree architecture. This allows for fast data retrieval based on the clustered index key values. A heap (no clustered index) has no sequential order of data. The most important thing to ...


10

As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert. This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted). If you find ...


10

Kimberly Tripp has a fantastic blog post about just this topic. I could paraphrase, but trust me, I wouldn't do it justice. Have a read. http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx While there, check out some of her other posts on the topic of clustering keys. There is a good wealth ...


10

The only valid use is for staging tables used in import/export/ETL processes. These table are typically quite flat and truncated before/after use. Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure. Heap tables also have problems. At least these: can not be defragmented to ...


10

There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns. There is a widespread belief that GUID are a great problem in SQL Server - largely, this is quite simply wrong. As ...


9

This is bollocks: ... ClaimId alone is a better clustered key because it is narrower because of this ClaimId alone is NOT uniquej A non-unique clustered index will add a 4 byte uniquifier to remove ambiguity of ClaimId because it is the clustered index. Why? One reason is all NC indexes refer to it: so how to know ClaimId is which? It was ...


9

Data pages are stored contiguously when the index is created and when the index is rebuilt. Otherwise, SQL Server will attempt to keep the pages in physical order. That not being possible, logical order is attempted. You can get gaps in a table because of other writes that are happening in the database. SQL Server uses B+ trees for indexes - the leaf (data) ...


9

"My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index." The 2008 R2 documentation for sys.dm_db_index_physical_stats includes a link to Table and Index Organization, which shows the following diagram: It describes the data that may be stored in each of the three possible allocation unit types: ...


9

An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example). *The query processor internally renames the work table when ...


8

It the optimiser decides it would be more efficient to sort the data prior to insert, it will do so somewhere upstream of the insert operator. If you introduce a sort as part of your query, the optimiser should realise that the data is already sorted and omit doing so again. Note the execution plan chosen may vary from run to run depending on the number of ...


8

No, a primary key constraint is always enforced in SQL-Server by a unique index. The index may be clustered or unclustered. If you don't specify which type, default is CLUSTERED for the primary key. From MSDN documentation, CREATE TABLE: CLUSTERED | NONCLUSTERED Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or ...


8

Matching indexed views is a relatively expensive operation, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan ...


8

I'm going to go on the assumption that you've identified a problem that needs to be fixed... though I'm not entirely convinced of that yet. Similar to my answer here, there are two cases when it comes to vendor applications. The application is currently under license and/or the vendor/owner doesn't want you meddling. In this case, definitely do not ...



Only top voted, non community-wiki answers of a minimum length are eligible