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:



Operating system software authentication and



Evaluation Nature - Screen Savers



adobe at the end of acquiring, at a Price well



RMVB to MP4



Visuanl C # 2005 Quick START of the while statement (1)



Magic Assistant: can not CONTROL the computer keystrokes



Hot Stocks Head Invasion: With Input For The Stock Market Frenzy



Describes the properties of the problem MIDP



Cursors And Fonts Introduction



Comment Shell Tools



STORM infringement advice to the court sentenced the company abandoned online play



DVR-MS to MPG



Recommendations china.com Home



Good Browsers



Baidu pressed into the C2C site Taobao payment or ISM



MPG to AVI