Have Geek Brain

Clustered Index

What type of index (clustered / unlinked) should be used for Insert / Update / Delete statement in SQL Server. I know this creates additional overhead, but is it better compared to a nonclustered index? Also, what index should I use for Select statements in SQL Server?

+2


a source to share


2 answers


Not 100% sure what you expect to hear, you can only have one clustering index per table and by default each table (with very few exceptions) should have one. All indexes usually help your SELECTs the most, and some tend to hurt INSERT, DELETE, and possibly UPDATEs a lot (or a lot if poorly chosen).

The clustered index makes the table faster for each operation. YES! It does. See Kim Tripp excellent The Clustered Index discussion continues for more information. She also mentions her main criteria for a clustered index:

  • narrow
  • static (never changes)
  • unique
  • if possible: ever increasing

INT IDENTITY does this perfectly - GUID does not. See GUID as Primary Key for detailed reference.

Why narrow? ... Because the clustered key is added to every index page of every non-clustered index on the same table (to be able to actually look up the data row if needed). You don't want to have VARCHAR (200) in the clustering key ....



Why unique? See above - clustering key is a member and mechanism that SQL Server uses to uniquely find a row of data. It must be unique. If you choose a custom clustering key, SQL Server itself will add a 4 byte identifier to your keys. Be careful!

Next up: non-clustered indexes. Basically there is one rule: any foreign key in a child table referencing another table must be indexed, this will speed up JOIN and other operations.

Also, any queries that contain WHERE clauses are a good candidate - pick ones that run a lot. Place the indexes on the columns that appear in WHERE clauses in ORDER BY clauses.

Next: measure your system, check DMVs (Dynamic Management Views) for clues about unused or missing indexes, and tune your system over and over. It is an ongoing process, you will never be achieved!

Another warning: with a load of indexes, you can make any SELECT query really fast. But at the same time INSERT, UPDATE and DELETE may suffer, which must update all involved indexes. If you only CHOOSE - go! Otherwise, it's a fine and delicate balance. You can always tweak one request outside of faith, but the rest of your system can suffer in the process. Don't re-index your database! Put some good indexes in place, check and observe how the system is performing, and then maybe add one or two more, and again: see how this affects the overall system performance.

+7


a source


I'm not really sure what you mean by "should be used for the Insert / Update / Delete statement", but in my opinion each table should have a clustered index. The clustered index determines the order in which data is actually stored. If no clustered index is defined, the data will simply be stored on the heap. If you don't have a natural column for a clustered index, you can always just create an identity column as int or bigint like this.



CREATE TABLE [dbo].[demo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nchar](10) NULL,
[LastName] [nchar](10) NULL,
[Job] [nchar](10) NULL,
 CONSTRAINT [PK_demo] PRIMARY KEY CLUSTERED 
(
[ID] ASC
))
+3


a source






More articles:


All Articles