SQL Server queries table indexes and SQL Server Indexes

Source: Internet
Author: User

SQL Server queries table indexes and SQL Server Indexes

SELECT index name = a. name

, Table name = c. name

, Index field name = d. name

, Index field location = d. colid

FROM sysindexes a JOIN sysindexkeys B ON. id = B. id AND. indid = B. indid JOIN sysobjects c ON B. id = c. id JOIN syscolumns d ON B. id = d. id AND B. colid = d. colid WHERE. indid not in (0,255) -- and c. xtype = 'U' and c. status> 0 -- Query all user tables AND c. name = 'message' -- query the specified table order by c. name,. name, d. name

You need to create an index, for example:

Checks whether duplicate records exist based on a column. If the column is not a primary key, an index is created.

Create an index based on frequently queried Columns

No need to create an index

Most fields have the same content, such as male and female.

Do not create indexes for all columns. This increases the maintenance overhead time when creating new records.

Oracle queries User table Indexes

Select index_name, index_type, table_name from user_indexes where table_name = 'table name'

SQL Server queries the index of a table

SELECT TableId = O. [object_id], TableName = O. name, IndexId = ISNULL (KC. [object_id], IDX. index_id), IndexName = IDX. name, IndexType = ISNULL (KC. type_desc, 'index'), Index_Column_id = IDXC. index_column_id, ColumnID = C. column_id, ColumnName = C. name, Sort = CASE INDEXKEY_PROPERTY (IDXC. [object_id], IDXC. index_id, IDXC. index_column_id, 'isscending') WHEN 1 THEN 'desc' WHEN 0 THEN 'asc 'else' 'end, PrimaryKey = case when idx. is_primary_key = 1 THEN n' √ 'else n' END, [UQIQUE] = case when idx. is_unique = 1 THEN n' √ 'else n'' END, Ignore_dup_key = case when idx. ignore_dup_key = 1 THEN n' √ 'else n' END, Disabled = case when idx. is_disabled = 1 THEN n' √ 'else n'' END, Fill_factor = IDX. fill_factor, Padded = case when idx. is_padded = 1 THEN n' √ 'else n'' ENDFROM sys. indexes idx inner join sys. index_columns idxcon idx. [object_id] = IDXC. [object_id] and idx. index_id = IDXC. index_idLEFT JOIN sys. key_constraints kcon idx. [object_id] = KC. [parent_object_id] and idx. index_id = KC. unique_index_idINNER JOIN sys. objects oon o. [object_id] = IDX. [object_id] inner join sys. columns con o. [object_id] = C. [object_id] and o. type = 'U' and o. is_ms_shipped = 0AND IDXC. column_id = C. column_id where O. name = 'cz201 '-- cz201 is the table you want to query

The above content is all described in this article and I hope you will like it.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.