cataloguePreliminary preparation:Create clustered indexMethod 1Method 2Create composite indexCreate overlay indexCreate Unique IndexFilter IndexSummary:Preliminary preparation:createtableEmployee(IDintnotnullprimarykey,Namenvarchar(4),Credit_ Card_ IDvarbinary(max));--- Be careful with this data type. goExplanation: All indexes on this table will be deleted before creating the next index

Preliminary preparation:

createtableEmployee(IDintnotnullprimarykey,Namenvarchar(4),Credit_ Card_ IDvarbinary(max));--- Be careful with this data type. go

Explanation: All indexes on this table will be deleted before creating the next index.

Create clustered index

Method 1

ALTERTABLEtable_ nameADDCONSTRAINTcons_ namepriamryKEY(columnnameASC|DESC,[.....])WITH(drop_existing=ON); ALTERTABLEemployeeADDCONSTRAINTpk_ for_ employeePRIMARYKEYCLUSTERED(id); go

This is a special method because when defining the primary key, the index is automatically added. Fortunately, whether to add a clustered index or a non clustered index is something that we can manually control.

By SP_ Helpndex can view the indexes in the table

executesp_helpindex@objname ='Employee'; go

Attention: This index cannot be deleted, do not believe it! You go and delete it

dropindexEmployee.PK__ Employee__ 3214EC277D95E615; go

Method 2

createclusteredindexix_ nameontable_ name(columnNameASC|DESC[,......])with(drop_existing=on); createclusteredindexix_ clu_ for_ employee_ IDonEmployee(ID); go

View Created Index

Create composite index

createindexix_ com_ Employee_ IDNameonEmployee(ID,Name)with(drop_existing=on);

This is considered creating a composite index, but the road ahead is long. Let's take a look at the example sentence of a composite index:

createindexix_ com_ Employee_ IDCreditCardIDonEmployee(ID,Credit_Card_ID);

When you see this sentence, first ask yourself if it's wrong!

It can be found that it is wrong,Varbinary cannot be indexed

Create overlay index

createindexindex_ nameontable_ Name(columnNameASC|DESC[,......])include(column_Name_List)with(drop_existing=on); createindexix_ cov_ Employee_ ID_ NameonEmployee(ID)include(Name); go

Firstly, coverage index is only a special form of non clustered index, and the non clustered index mentioned below does not include coverage index. Of course, this convention only applies to this paragraph, and the purpose of doing so is to illustrate the differences between each.

first:

• A non clustered index does not include data, and what is found through it is only a reference to a data row in a file (in the case of a table being a heap) or a reference to a clustered index. SQL Server needs to use this reference to find the corresponding data row.

• It is precisely because the non clustered index has no data that a second search is triggered.

• Covering an index is adding data to a non clustered index, so there is no need for a second search. This is a method of exchanging space for performance. Non clustered indexes are also included. It's just not as out of style as it is.

Create Unique Index

createuniqueindexindex_ nameontable_ name(columnASC|DESC[,.....])with(drop_existing=on);

As I mentioned earlier, before creating indexes on a table, I will delete all indexes on the table. Why should I say it again! Because I'm afraid you might forget. Secondly, this example applies to it.
The current table is an empty table, and I will add two rows of data to it.

insertintoEmployee(ID,Name)values(1,'AAA'),(1,'BBB');

Now we add a unique index to the table, which is defined in the ID column

createuniqueindexix_ uni_ Employee_ IDonEmployee(ID); Go - You can imagine that it cannot be created because the ID is duplicated.

Conclusion 1: If there are duplicate values on a column, it cannot be defined as a unique index on that column.

Let's clear the table below:truncatetableEmployee

Create Unique Index

createuniqueindexix_ uni_ Employee_ IDonEmployee(ID); go

insertintoEmployee(ID,Name)values(1,'AAA'),(1,'BBB');go

Conclusion 2
After defining a unique index, duplicate values cannot be inserted on the corresponding columns.

Filter Index

createindexindex_ nameontable_ name(columName)whereboolExpression; createindexix_ Employee_ IDonEmployee(ID)whereID> 100andID< 200; go

Only index hot data. If a large number of queries are only interested in data with IDs ranging from 100 to 200, this can be done.

• Can reduce the size of the index
• Improve query performance for site data.

Summary:

BTree indexes can be divided into clustered and non clustered indexes.
It can be seen that the performance of clustered indexes is better than that of non clustered indexes.

Non clustered index score
Covering index, unique index, composite index (of course, clustered index also has compound, compound two words, only indicating that the index refers to multiple columns). Generally, non clustered index can see that the performance of covering index in non clustered index is better than that of other non clustered indexes. Its performance is similar to that of clustered index, but it is not; Silver Bullet‘ It will use more disk space.

Finally, let's talk about this

With (drop_existing=on | off), adding this means that if the index is still on the table, it will be dropped and a new one will be created. Especially when using this on clustered indexes, it can avoid the reconstruction of non clustered indexes.

With (online=on | off) When creating an index, users can also access the data in the table,

with(pad_index=on|offfillfactor=80); Fillfactor is used to set the filling percentage, pad_ Index is only used to connect fillfactors, but it cannot be missing, which is speechless.

with(allow_row_locks=on|off|allow_page_locks=on|off);& nbsp;Allow page or row locks

with(data_compression=row|page); This can compress the index size

That's all for this article about the 5 methods of SQL Server index creation summary. For more related SQL Server index creation content, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!