sql server 使用特定索引（sql server 使用特定索引）.docVIP

sql server 使用特定索引（sql server 使用特定索引） A: index usage - the efficiency of the database depends largely on the establishment of the index, specific indexing principles, a lot of articles are introduced -- introduces some methods of indexing with SQL statements, and methods of maintaining indexes: * Build index * / Build common index Create index index name on table name (field) Build clustered index Create, clustered, index, index name, on table name (field) Build non clustered index Create, NONCLUSTERED, index, index name, on table name (field) * Delete index * / Drop index table name. Index name * Use index * / Situation 1: If you use arguments in the where clause, you will also cause full table scans. Because SQL only parses local variables at run time, the optimizer cannot defer the access plan selection to run time; it must select at compile time. However, if the access plan is established at compile time and the value of the variable is unknown, it cannot be used as an input to the index selection. Full table scans are performed as follows: Select, ID, from, t, where, num=@num Can be changed to mandatory queries using indexes: Select, ID, from, t, with (index (index name)), where, num=@num Situation 2: Using index queries: Select * from table name (index = index name) where index field =50100 B: the connection query has no subquery and is efficient A large number of left queries, connection efficiency, no subquery, high efficiency! - case 1: The stored procedure is as follows: CREATE, PROCEDURE, P_TEST, @Date, INT AS SELECT F2_A001, F15_A001, SplitStyle = F11_A074, [Schema] = Isnull (` Rule 1 + Convert (varchar (100), SongGu) + ` unit 1; + Isnull (` Rule two + Convert (varchar (100), XianJin) + ` unit two ; + Isnull (` Rule three + Convert (varchar (100), SuoGu / 10) + unit three; + Isnull (` Rule four + Convert (varchar (100), RenGou) + ` unit four ; + Isnull (` Rule six + Convert (varchar (100), RenGu) + ` unit five ; + Isnull (` Rule seven + Convert