Index + C # interview questions

 

You can use the index technology to query a large amount of data. An index is a special type of database object. It stores the sorting results of one or more columns in a data table, and effectively uses indexes to improve data query efficiency. When interviewing Junior, intermediate, or senior programmers, most of them will be asked such questions. Do you know about indexing? Do you know the index category? Do you know the differences between these indexes? How do you create a valid index. This chapter allows you to ask the interviewer to hold the audience. --_____--

Friendship string

The articles for the recent interview are quite a hit. I just asked two questions to see how many people can answer them directly.

1. What is the difference between rewriting of base-class virtual functions and derived classes and overwriting of base-class common functions and derived classes (-____-)

2. Try catch in the using closure. if an exception is thrown after it is caught, can the using resource be released normally (-____-)

The answer is revealed at the end of the article.

 Basic knowledge(What the interviewer will ask)

Indexing is a magic horse

In general, indexes are the disk structures associated with the table, which can speed up row retrieval from the table. An index contains keys generated by one or more columns in a table. These keys are stored in a structure so that SQL server can quickly and effectively find the rows associated with the key value.

Hold Statement: To put it bluntly, the index is a balanced tree (B tree for short) structure, with multi-level, self-maintenance, and the node Stores Table data identification information, if a record in the table occupies 500 bytes on disk, we create an index for a field of 10 bytes, then the size of the index block corresponding to the record is only 10 bytes. In this way, the retrieval of Io traffic is much less.

Index category

General statement: clustered index and non-clustered Index

Hold statement: clustered index, non-clustered index, unique index, inclusive column index, index view, full-text index, XML Index

Differences between clustered index and non-clustered Index

In general, clustered indexes are based on the sorting and storage of Record Content in data tables. Non-clustered indexes do not physically sort data in the data table. They only create indexes on the index page. When querying data, you can also find the location where the records are stored in the index.

Hold statement: clustered indexes sort and store the data rows according to the key values of the data rows in the table. The index definition contains clustered index columns. Each table can have only one clustered index, because data rows can only be sorted in one order. Data rows in the table are stored in order only when the table contains clustered indexes. If a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in a unordered structure called a heap.

Non-clustered indexes have a structure independent of data rows. A non-clustered index contains a non-clustered index key value, and each key value item has a pointer to a data row containing the key value. A pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data page is stored in the heap or clustering table. For a heap, the row positioner is a pointer to a row. For clustered tables, the row locator is the clustered index key.

Several clustered indexes and non-clustered indexes can be created in a table.

General statement: One clustered index and 249 non-clustered Indexes

Hold statement: Cool, laugh, not speak.

Index Design Principles

General statement: index creation should be appropriate for tables with less index query operations for addition, deletion, and modification operations

Hold statement:

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

2. indexing small tables may not produce optimization results;

3. create non-clustered indexes for the predicates and join columns that are frequently used for queries;

4. Overwriting indexes can improve query performance;

5. Clustered indexes. It is best to maintain a shorter index key;

6. Use a screening index for well-defined columns;

7. If the index contains multiple columns, the column sequence should be considered;

Advanced knowledge(It can be used to tease the interviewer)

Restrictions on index creation

Each index can contain up to 16 key columns;
The index key can contain a maximum of 900 bytes;
Large Object Data Types cannot be used as index key columns;

Unique index: the unique index ensures that the index key does not contain duplicate values. Therefore, each row in a table or view is unique to some extent. Both clustered and non-clustered indexes can be unique indexes.

Design principles:

A unique index can ensure that the index key does not contain duplicate values, so that each row in the table is unique in some way. It makes sense to specify a unique index only when uniqueness is a feature of the data. For example, if you want to ensure that the value in the nationalidnumber column of the HumanResources. Employee table is unique, when the primary key is employeeid, it can be nationalidnumberColumn to create a unique constraint. If you try to enter the same value for multiple employees in this column, an error message is displayed and duplicate values cannot be entered.

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

Inclusive column Index: A non-clustered index that extends to include not only key columns but also non-key columns.

Design principles:

Redesign non-clustered indexes with a large index key size so that only the columns used for search and search are key columns. Set all other columns that overwrite the query to include non-key columns. In this way, all the columns required for the query will be overwritten, but the index key itself is small and efficient.

?

12345

USE AdventureWorks; GO SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN N'98000' and N'99999';

To overwrite the query, you must define each column in the index. Although all columns can be defined as key columns, the key size is 334 bytes. Because the only Column Used as the search condition isPostalCodeColumn (Length: 30 bytes), so a better index design should
PostalCodeIt is defined as a key column and contains all other columns that are not a key column.

?

12345

USE AdventureWorks; GO CREATE INDEX IX_Address_PostalCode        ON Person.Address (PostalCode)        INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Index view: the index of the view is embodied (executed) view, and the result set is permanently stored in a unique clustered index, the storage method is the same as that for tables with clustered indexes. After creating a clustered index, you can add a non-clustered index to the view.

Design Principle: Another benefit to creating an index for a view is that the optimizer can use the index of the view in a query that is not directly specified in the from clause. In this way, you can retrieve data from the index view without re-encoding. The resulting efficiency also benefits existing queries.

The index view can improve the performance of the following query types: 1. Handling the join and aggregation of a large number of rows. 2. Many queries often perform join and aggregate operations. 3. Decision support workload.

Full-text index: this bug will be introduced separately in the full-text search chapter.

XML: The sharded persistent representation of an XML Binary Large Object (BLOB) in an XML data type column. The maximum data size of an XML instance can reach 2 GB.
Time consumed. The index created on the XML field is the XML index.
Design Principle: The primary XML index is used to assist the XML index. The first index of an XML column must be the primary XML index. Three types of secondary indexes are supported when primary XML indexes are used. These types include path, value, and property. Depending on the query type, these secondary indexes may help improve query performance.

Improve(It has nothing to do with the interview)

INDEX OPTIMIZATION

The database engine automatically maintains indexes whenever basic data is inserted, updated, or deleted. These modifications can cause fragmentation in the index. A large number of indexes may reduce query performance and slow application response.

1. Sort indexes on a regular basis;
2. Set the index concurrency;
3. analyze trace data and adjust indexes;

Clustered tables, heaps, and indexes

A clustered table is a table with a clustered index. Data rows are stored in sequence based on the clustered index key. Clustered indexes are implemented based on the B-tree index structure. The B-tree index structure supports fast row search based on the clustered index key value. Each page (including the leaf-level data page) in the index is linked to a list of two-way links. However, you can use a key value to perform navigation from one level to another.

Heap is a table without clustered indexes. Data rows are not stored in any special order, and data pages do not have any special order. The data page is not linked in the Link List.

How can cainiao effectively create indexes?

The premise is your wired server permission -___-

First open SQL Server Profiler to create a trail

Select attributes based on your selection

Save the trace text after running for a period of time

Open Optimization engine

Set as needed

Analyze and Optimize Based on System suggestions

Because the steps in the local environment where the insects run cannot be understood.

 Stored Procedure associated with the index

Sp_helpindex reports information about indexes on tables or views.

?

exec sp_helpindex 'PPS_App_Infomation'

 

View index information based on SYS. Indexes View

?

select * from sys.indexes

As for the syntax for adding, deleting, and modifying indexes, there are still a lot of advanced applications on the Internet, such as virtual column indexes and selecting indexes. However, you may not be interested in the syntax. you can leave a message to discuss these issues.

 Objective answer

Is it true that I cannot find the expected answer O (cost _ cost) O on the Internet?

1. virtual function rewriting is a runtime multi-state derived class overwriting is a method to hide the base class

2. The dispose principle of using is the same as that of try catch finally.

Many programmers complain that others have the same length of service as themselves. Why are the salaries of others so high? Many programmers feel that they are arrogant, but they have not met bole. In fact, when they are not calm, it is better to look back and study again. your opinion may change a lot.

Auto: http://www.cnblogs.com/dubing/archive/2011/11/07/2237881.html