Go to Dba[mssql Article] interviewer's favorite question----index +c# face question Guest

In the original: Toward Dba[mssql Article] interviewer favorite question----index +c# face question Guest

When querying large amounts of data, it can be applied to indexing technology. An index is a special type of database object that holds the sort results of one or more columns in a data table, and the efficient use of indexes can improve the query efficiency of the data. When you are interviewing beginner, intermediate, or senior programmers, most of them will be asked questions, do you know the index? Do you know the classification of indexes? Do you know the difference between these indexes? How do you create a valid index. This chapter let everyone learn to ask the interviewer hold the audience. --_____--

Friendship Guest

Recent interview Articles compare fire guest I just ask 2 questions to see how many people can answer directly is a relatively basic

1. Differences between base class virtual functions, derived class overrides and base class common functions, derived class overrides (-____-)

2.Using closure of a try catch, thrown after exception capture, and whether a using resource can be released normally, overview reason (-____-)

The answer is announced at the end of the article

Basic Knowledge (interviewer will ask)

Index is God Horse

Generally speaking: An index is an on-disk structure associated with a table that can speed up the retrieval of rows from a table. The index contains the keys generated by one or more columns in the table. These keys are stored in a structure so that SQL Server can quickly and efficiently find the rows associated with the key values.

Hold said: The index is balanced tree (called B-tree) structure, multi-level, self-maintenance, node storage table Data identification information, if a record in the table occupies 500 bytes on disk, we have 10 bytes of one of the fields indexed, Then the size of the index block corresponding to the record is only 10 bytes. This retrieves much less access to the IO.

Classification of indexes

General parlance: clustered and nonclustered indexes

Hold statement: Clustered index, nonclustered index, unique index, inclusive column index, indexed view, full-text index, XML index

The difference between a clustered index and a nonclustered index

Generally speaking: A clustered index is based on the sorting and storage of the record content within the data table. A nonclustered index does not physically sort the data in the data table, but simply builds the index on the index page, where the record can be found from the index when querying the data.

Hold statement: The clustered index sorts and stores the data rows in the table based on the key values of the data rows. The index definition contains a clustered index column. Each table can have only one clustered index, because the data rows themselves can only be sorted in one order. Data rows in a table are stored in sorted order only when the table contains a clustered index. If the table has a clustered index, the table is called a clustered table. If a table does not have a clustered index, its data rows are stored in an unordered structure called a heap.
Nonclustered indexes have a structure that is independent of the data rows. Nonclustered indexes contain nonclustered index key values, and each key-value entry has a pointer to the data row that contains the key value. A pointer to a data row from an index row in a nonclustered index is called a row locator. The structure of a row locator depends on whether the data page is stored in a heap or in a clustered table. For heaps, a row locator is a pointer to a row. For clustered tables, the row locator is the clustered index key.

Several clustered and nonclustered indexes can be established in a single table

General statement: A clustered index and 249 nonclustered indexes

Hold said: Cold hum a sound, laugh without words.

Design principles for Indexes

General statement: Table additions and deletions to operate more than the index to check the operation of the appropriate indexing

Hold statement:

1. A large number of indexes can affect the performance of DML statements;

2. Indexing a small table may not produce an optimization effect;

3. Create nonclustered indexes on predicates and join columns that are frequently used in queries;

4. Overlay index can improve query performance;

5. Clustered index, preferably maintain a shorter index key;

6. Consider using filtered indexes on well-defined columns;

7. If the index contains more than one column, the order of the columns should be considered;

Advanced Knowledge (can be used to flirt with the interviewer)

Restrictions on creating indexes

A maximum of 16 key columns per index;
The index key is up to 900 bytes long;
Large object data types cannot be used as index key columns;

Unique Index: A unique index ensures that the index key does not contain duplicate values, so that each row in the table or view is to some extent unique. Both clustered and nonclustered indexes can be unique indexes.

Design principles:

A unique index guarantees that the index key does not contain duplicate values, making each row in the table unique in some way. Specifying a unique index makes sense only when uniqueness is a feature of the data itself. For example, if you want to ensure that the values in the Nationalidnumber column of the HumanResources.Employee table are unique, you can create a unique for the Nationalidnumber column when the primary key is EmployeeID Constraints. If the user tries to enter the same value for more than one employee in the column, an error message is displayed and the duplicate value cannot be entered.

A unique index ensures the data integrity of the defined columns and provides additional information useful to the query optimizer.

Inclusive Column index : a nonclustered index that expands to include not only key columns but also nonkey columns.

Design principles:

Re-design a nonclustered index with a large index key size so that only columns that are used for search and lookup are column keys. Sets all other columns that overwrite the query to include non-key columns. This will have all the columns required to overwrite the query, but the index key itself is small and efficient.

To overwrite a query, you must define each column in the index. Although you can define all columns as key columns, the key size is 334 bytes. Because the only column that is actually used as a search condition is a PostalCode column (30 bytes in length), a better index design should be PostalCode defined as a key column and contain all other columns that are non-key columns.

Use AdventureWorks; Gocreate INDEX Ix_address_postalcode on       person.address (PostalCode)       INCLUDE (AddressLine1, AddressLine2, city , StateProvinceID);

Indexed Views : The index of the View materializes (executes) the view and stores the result set permanently in a unique clustered index, with the same storage method as the table with the clustered index. After you create a clustered index, you can add nonclustered indexes to the view.

Design principles: Another benefit of creating an index on a view is that the optimizer can use the index of the view in a query that does not specify the views directly in the FROM clause. As a result, data can be retrieved from an indexed view without recoding, and the resulting efficiencies also benefit existing queries.

Indexed views can improve the performance of the following query types: 1. Joins and aggregations that handle a large number of rows. 2. Many queries frequently perform join and aggregation operations. 3. Decision support workload.

Full-text index : This worm will be opened separately in the Full Text Search section

XML: The split durable representation of an XML binary large object (BLOB) in an XML data type column. The maximum amount of data for an XML instance can reach 2GB, and if the query is in an XML field without an index, it is
Time consuming, the index created on the XML field is the XML index.
Design principles: Primary XML Index, secondary XML index. The first index of the XML type column must be the primary XML index. Three types of secondary indexes are supported when using the primary XML index. These types include PATH, VALUE, and property. Depending on the type of query, these secondary indexes may help improve query performance.

improve (irrelevant to the interview)

Index optimization

The database engine maintains indexes automatically whenever an INSERT, update, or delete operation is performed on the underlying data. These modifications can cause fragmentation in the index, and a very fragmented index can degrade query performance and cause the application to respond slowly.

1. regularly organize indexes;
2. Set the index parallelism level;
3. Analyze trace data and adjust index;

Clustered tables, heaps, and indexes

A clustered table is a table that has a clustered index. Data rows are stored sequentially based on the clustered index key. Clustered indexes are implemented by the B-tree index structure, which supports fast retrieval of rows based on clustered index key values. Pages at each level in the index, including leaf-level data pages, are linked in a two-way linked list. However, you perform navigation from one level to another by using key values.

A heap is a table without a clustered index. Data rows are not stored in any particular order, and the data pages do not have any special order. The data page is not linked in the linked list.

Rookie how to create an index efficiently

If you have access to the server on your wired-___-

First Open SQL Server Profiler to create a new trace

Select the attribute according to your choice

Run a period of time to save trace text

Turn on the optimization engine

Set according to your needs

Analysis and optimization based on the recommendations of the system

Because the worm runs in the local environment, the following steps are not going to be understood.

stored procedures associated with an index

Sp_helpindex reports information about indexes on a table or view.

exec sp_helpindex ' pps_app_infomation '

View index information According to the sys.indexes view

SELECT * FROM sys.indexes

As for the syntax of index additions and deletions check on the Web a search for a piece of the wheel is not built on the index there are many advanced applications such as virtual column index, select Index and so on, but everyone may not be interested in practical is the king if someone interested in these can leave a message to discuss

Guest Answer

If I didn't find the answer I wanted on the Internet. O (∩_∩) o

1. Virtual function rewriting is the runtime's override of a polymorphic derived class is the method of hiding the base class

The dispose principle of 2.using is the same as the finally of Try Catch

A lot of programmers will complain about the same length of their seniority as other people's salary. There are a lot of programmers who think they are bullish just don't meet bole actually when they are not calm when it is better to look back to learn again perhaps their views will change a lot

Hope this article to help everyone's support is the worm's power-____-

Go to Dba[mssql Article] interviewer's favorite question----index +c# face question Guest