blocks|key|5423363|text|你可以写一本关于它的书。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5423364|您应该熟悉这一点。|5423365|Table+and+Index+structures|offset|length|5423366|Understanding+and+design+indexes|5423367|然后，如果你仍然没有捕捉到一些东西，可以在这里询问一些细节。|5423368|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms180978.aspx|1|http://msdn.microsoft.com/en-us/library/ms189271.aspx^0|0|0|0|Q|0|0|0|W|1|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@$F|Y|G|Z|1|10]]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@$F|12|G|13|1|14]]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

You can write a book about it. 

You should get familiar with this 

<a href="http://msdn.microsoft.com/en-us/library/ms180978.aspx" rel="nofollow">Table and Index structures</a>

<a href="http://msdn.microsoft.com/en-us/library/ms189271.aspx" rel="nofollow">Understanding and design indexes</a>

And then if You still don't catch something ask here for some details.

blocks|key|1421514|text|查看：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1421515|1421516|SQL+Server+Index+Basics|unordered-list-item|offset|length|1421517|SQL+Server+Indexes:+The+Basics|1421518|1421519|寻找一些好的介绍材料|1421520|entityMap|0|LINK|mutability|MUTABLE|url|http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/|1|http://www.sqlteam.com/article/sql-server-indexes-the-basics^0|0|0|0|N|0|0|0|U|1|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Y|8|@]|9|@$F|Z|G|10|1|11]]|A|$]]|$1|H|3|I|5|E|7|12|8|@]|9|@$F|13|G|14|1|15]]|A|$]]|$1|J|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]]]

Check out:

<ul>
<li><a href="http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/" rel="nofollow">SQL Server Index Basics</a></li>
<li><a href="http://www.sqlteam.com/article/sql-server-indexes-the-basics" rel="nofollow">SQL Server Indexes: The Basics</a></li>
</ul>

for some good intro material

blocks|key|3955463|text|简而言之：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3955464|索引更快，因为它是缓存在内存中的表的子集。|3955465|此外，如果请求的所有数据都在索引中，服务器甚至不必查询磁盘。|3955466|群集与非群集：|3955467|一个表只能有一个聚集索引，因为这意味着磁盘上的数据与索引的顺序相同。如果您插入新数据，表格将被调整以保持此顺序。|3955468|聚集索引的搜索速度通常更快，特别是当您需要从每一行中获得更多数据，但在插入时性能受到影响时。|3955469|通常在ID列上有聚集索引。|3955470|您可以使用许多非聚集索引，它们用于加快查询速度。|3955471|通过将最常查询的字段组合在一起，并对索引中的字段进行排序，以便将变化最大的字段放在第一位，您可以获得很大的性能提升，但请阅读其他人提供的链接，您始终可以了解有关索引的更多信息，即使您构建了自己的数据库=)|3955472|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|10|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|11|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

In short:

An index is faster because it is a subset of the table that is cached in memory.

Also, if all data requsted is in the index the server does not even have to query disk.

Clustered vs. unclustered:

A table can only have one clustered index as it means that the data on disk is ordered in the same order as the index. If you insert new data the table will be adjusted to keep this order.

Clustered index is faster often to search, especially if you need more data from each row but gets a performance hit on inserts.

Usually you have clustered index on the ID column.

Unclustered indexes you can have many of and they are for speeding up queries.

By combining the fields that most often are queried together and order the fields in the index so that the most varied field is first you can get big performance boosts, but read the links the other supplied, you can always öearn more about indexing, even if you build you own database =)

blocks|key|1421568|text|让我尝试解释一下索引的概念。这种解释不是特定于SQL+Server的，我可能把事情简单化了一点。但是，它可能会帮助您了解索引为什么有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1421569|假设您存储由ID和名称组成的简单记录。数据存储在行中，其中第一列(#)是记录号或地址，使我们能够指向特定的行。|1421570|#++Id++Name
------------
0++1+++Lorem
1++2+++Ipsum
2++3+++Dolor
3++5+++Sit
4++6+++Amet|code-block|syntax|javascript|1421571|假设您想要查找具有特定名称的行，请说"Sit“。找到它的唯一方法是扫描整个表，查找那个特定的名称。在大桌子上做这件事是很耗时的。|1421572|要解决这个问题，您可以在Name列上创建一个索引。索引类似于表：|1421573|Name+++#
--------
Amet+++4
Dolor++2
Ipsum++1
Lorem++0
Sit++++3|1421574|请注意，名称是经过排序的，这使得搜索特定名称变得更容易。查找名称不再需要完整的表扫描，而是可以通过搜索索引来加快速度。当找到名称时，可以查找相应的记录号，并且可以检索相关联的记录。通常，索引将使用平衡树，这使得搜索非常有效。|1421575|总而言之，如果您有索引，搜索速度会更快，但您必须维护一个额外的数据结构，该结构会消耗空间，并且在修改底层表时需要更新。|1421576|聚集索引是一种特殊的索引，它定义了表的存储方式。在ID列上建立聚集索引将确保表中的行按Id排序。您不需要额外的索引数据结构来维护此索引。索引已经由表的存储方式定义。|1421577|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Let me try to explain the concept of an index. This explanation is not SQL Server specific, and I'm perhaps over simplifying things a bit. However, it may help you get an idea why an index is useful.

Assume you store simple records consisting of an ID and a name. Data are stored in rows where the first column (#) is a record number or an address enabling us to point to a particular row.

<pre><code># Id Name
------------
0 1 Lorem
1 2 Ipsum
2 3 Dolor
3 5 Sit
4 6 Amet
</code></pre>

Assume that you want to find a the row having a specific name, say "Sit". The only way you can find it is by scanning the entire table looking for that particular name. Doing that on a big table is time consuming.

To solve that problem you can create an index on the Name column. An index is like a table:

<pre><code>Name #
--------
Amet 4
Dolor 2
Ipsum 1
Lorem 0
Sit 3
</code></pre>

Note that the names are sorted which makes it easier to search for a particular name. Finding a name no longer requires a complete table scan but can instead be speeded up by searching the index. When the name is found the corresponding record number can be looked up and the associated record can be retrieved. Often the index will use a balanced tree which makes it quite efficient to search.

To sum up, search is faster if you have an index but you will have to maintain an additional data structure that consumes spaces and requires updating when the underlying table is modified.

A clustered index is a special index that defines how the table is stored. Having an clustered index on the Id column will ensure that the rows in the table are ordered by ID. You don't need an additional index data structure to maintain this index. The index is already defined by how the table is stored.

Could somebody please explain how indexing works in SQL Server and what is a clustered and non clustered index?

When we create an index on some column how does it increase the performance and how are the values of column stored to increase the performance?

Indexing in SQL Server

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

请解释一下SQL Server中的索引是如何工作的，什么是聚集索引和非聚集索引？当我们在某些列上创建索引时，如何提高性能?如何存储列的值以提高性能？

问SQL Server中的索引
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server中的索引EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server中的索引
EN