A clustered index and a nonclustered index in the ape Eye of the program:

OS: This is not a description of the concept of aggregation and non-aggregation. As a program ape, in peacetime development, data operation is often to do, most companies do not have DBA, so the program developers in the operation of the data is not to see the efficiency of SQL statement execution,    So often find big data in the case of querying the database will always encounter a variety of slow loading situation. From the user's point of view, my pants are off, you show me this?
From the technical point of view, I he is so abuse, how can make inquiries so card.    Therefore, as a procedural ape we, in the absence of a DBA, to grasp the most basic speed up the database query awareness and skills; Directly on the example, dynamic description, there is a picture of the truth, simple rough. Here we first create a table:

CREATE TABLE [dbo]. [Student] ([ID] [int] IDENTITY () not NULL, [Name] [NVARCHAR] (a) not NULL, [age] [int.] NOT NULL, [Height] [int] is not NULL, [Addre SS] [NVARCHAR] (+) NULL, [Class] [NVARCHAR] (NO) null, [Entrancedatetime] [DATETIME] NOT NULL, CONSTRAINT [pk_student] PRIMARY KEY CLUSTERED ([ID] ASC) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_lock S = on, allow_page_locks = on) on [PRIMARY]) on [PRIMARY] GO

ALTER TABLE [dbo]. [Student] ADD CONSTRAINT [Df_student_entrancedatetime] DEFAULT (GETDATE ()) for [Entrancedatetime]go

Insert 5 million data into the table:

DECLARE @i INT; SET @i=1; while (@i<5000001) BEGIN inserts into dbo. Student (name,age,height,[address],class,entrancedatetime) VALUES (' Yang_ ' +convert (NVARCHAR), @i), RAND () *10+7, RAND () *100+50, ' Xiamen Local tyrants Community 1-seater ' +convert (NVARCHAR (+), convert (Int,rand () *100+1)) + ' sign ', convert (NVARCHAR), convert (INT, RAND () *6+1) + ' Grade ', GETDATE ()) SET @[email protected]+1; END

1. Use indexes reasonably to improve query speedIn the query table, all ages 10 names, you can see, using the clustered index Scan, logical read 55,057 times to add the index:

CREATE nonclustered INDEX [ix_student_age_name] on [dbo]. [Student] ( [age] ASC) INCLUDE ([Name]) With (Pad_index = off, Statistics_norecompute = off, sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = off, Onli NE = OFF, allow_row_locks = on, allow_page_locks = on) on [Primary]go,

Obviously, the query optimizer uses index lookups, and the number of logical reads is reduced to: 2411, which is considerable. (an index lookup is seen in the execution plan, which means that the index is used, if an index scan indicates that the index is not being used)Note here:Myth: I added the index query speed is certainly more thanThe table scan comes fast and the index is bound to be usedMy Summary understanding: First, the index is not necessarily faster than the scan, in the case of less data, the use of table scanning will be faster than the index, two, the addition of indexes may not be used, the first thing is to know that SQL Server in the execution of the statement will choose the best energy-consuming scheme to execute, It will not be used if the index is not as efficient as possible. For example: The following query operation, it is not used to index, but the use of a clustered index scan  What is the situation above? Because I created an index that only covers the Name field, and now I'm querying the address field, not the index overlay, then the query optimizer did not use the index when executing the statement, and chose a less expensive clustered index scan but I was so willful, to force the use of the index To query, the results such as: This result is very obvious, logical reading times, and scanning times a lot more. The plan also gives a hint, let's index overwrite address field2. Proper use of the clustered indexWe add the table's primary key when the default is to add the primary key as a clustered index, but not the clustered index must be the primary key field, a table can only add a clustered index, so reasonable use of the characteristics of the clustered index, can greatly improve the query speed.    Generally we are in the self-increment of the ID is set as the primary key, but rarely the ID of the query operation, more will be in the table of other fields query, such as: Time field. You can add a clustered index to the time field at this point, and you'll find that the entire query is much more efficient. 3,4,5,6not to be continued ... -----------------------------------[I'm just a beautiful dividing line]-----------------------------------------advantages and disadvantages of indexesAdvantages: Faster access, stronger row uniqueness disadvantage: Indexed tables require more storage space in the database, commands that manipulate data require longer processing time because they need to be updated on the indexguidelines for creating indexesSelect an indexed column according to the following criteria: This column is used to search the column frequently for sorting data do not use the following column to create an index: The column contains only a few different values. The table contains only a few rows. Creating an index on a small table may not be a good deal because SQL Server spends more time searching for data in the index than it does in a row-by-line search in the tableSuppose we create a single-column index on the Col1 column, and we can index lookups on the following predicates:

Ø[col1] = 3.14ø[col1] > 100ø[col1] between 0 and 99ø[col1] like ' abc% ' ø[col1] in (2, 3, 5, 7)

However, index lookups cannot be used on the following predicates:

Ø ABS ([Col1]) = 1ø[col1] + 1 = 9ø[col1] like '%abc '

-----------------------------------[I'm just a beautiful dividing line]-----------------------------------------

A clustered index and a nonclustered index in the ape Eye of the program: