Tag Info

Hot answers tagged

32

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 ...


27

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 ...


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 ...


25

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 ...


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 ...


16

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 ...


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 ...


13

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 ...


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 ...


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

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 ...


11

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 ...


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 ...


11

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 ...


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

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 ...


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 ...


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

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 ...


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: ...


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 ...


7

The ORDER BY clause in the SELECT statement is redundant. It is redundant because the rows that will be inserted, if they need to be sorted, are sorted anyway. Let us create a test case. CREATE TABLE #Test ( id INTEGER NOT NULL ); CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id); CREATE TABLE #Sequence ( number INTEGER NOT NULL ); INSERT ...


7

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead. My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the ...


7

In simple terms, it involves less processing and movement of NC index entries when data in the clustered index physically moves (row forwarding, page splits, INSERTs etc). Mostly the clustered index entries only need changed: not the NC index pointers. By using RIDs, you'd need to do a lot more work on the NC indexes. To minimize this lookup in a query, ...


7

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full. Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, ...



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