How to identify real and can automatically create index index

Q: I found a lot of sysindexes index table entries are not created my own. I heard they are not true index, but the SQL Server query optimizer automatically creates statistics. How can I identify which is the real index, which is automatically created in SQL Server available?

A: By default, if the table is not a column index, SQL Server automatically creates statistics for columns. Then, the query optimizer to assess the distribution of column data in the range of statistical information to select a more efficient query processing program. Automatically created statistics tell a very simple, in SQL Server 7.0 and SQL Server 2000, the auto-created statistics prefix _WA_Sys.

You also can use the INDEXPROPERTY () function IsAutoStatistics property to distinguish between real or an index is automatically created in the statistics, so that SQL Server optimizer choose need to create statistics. You can also enable you to manage the database, "automatically create tables" option.

Many people ignore the following conclusions. Statistics created automatically mean the existence of the index may be a real benefit. Please consider the following code in the output:

USE tempdb GO IF OBJECTPROPERTY (OBJECT_ID ('dbo.orders'), 'IsUserTable') = 1 DROP TABLE dbo.orders GO SELECT 1 10 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 3 30 31 32 4 5 6 7 8 9 INTO tempdb .. orders FROM northwind .. orders GO SELECT 1 10 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 3 30 31 32 4 5 6 7 8 9 FROM tempdb .. orders WHERE orderid = 10248 GO SELECT 1 10 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 3 30 31 32 4 5 6 7 8 9 FROM tempdb. . sysindexes WHERE id = object_id ('orders') AND name LIKE '_wa_sys%' GO

Copy the code in the tempdb in the Northwind Orders table, select the row, and then check the SQL Server is added to a statistical. Obviously, the table does not OrderId column index, so SQL Server automatically creates a named _WA_Sys_OrderID_58D1301D statistics. OrderId out the existence of statistical tables that Northwind Orders table would benefit from additional indexes.

The following query shows the database table automatically created for each user the number of statistics, the database is automatically created at least one of the statistics.

SELECT object_name (id) TableName, count (*) NumberOfAutoStats FROM sysindexes WHERE OBJECTPROPERTY (id, N'IsUserTable ') = 1 AND INDEXPROPERTY (id, name,' IsAutoStatistics') = 1 GROUP BY object_name (id) ORDER BY count (* ) DESC

Not all of the statistics can be replaced by the real index. In some cases, SQL Server will automatically create a table more than 50 statistics. Clearly, these tables index strategy is bad. Automatically created on the table and the statistics associated with the rapid count can help you determine which table to index.







Recommended links:



FLV to 3GP



Career 12 fatal flaws



Samsung's NAND flash memory capacity greatly INCREASED



TS to WMV



Finaldata recovery partition from experience



Hot hot behind



Hong Kong PCCW Privatization Commission will Vote by sealed-depth inquiry



Affect China's media forum held on the 14th



Comment Audio CD Players



Dealers sustained Growth Quartet



Eu to consider microsoft 39 s browser is expected



DivX to VOB



Easy Astrology Or Biorhythms Or Mystic



10 training managers to work effectively



Comparison Reference Tools



Do they only quick Ctrl + Alt + End defects more quickly Shutdown