非聚集索引不工作sql server
我有一个没有任何主键的表。数据已经存在。我做了一个非聚集索引。但是当我运行查询时，实际执行计划没有显示索引扫描。我认为非聚集索引不起作用。可能是什么原因。请帮我
I have a table that doesn't have any primary key. data is already there. I have made a non clustered index. but when i run query, actual execution plan is not showing index scanning. I think non clustered index is not working. what could be the reason. Please Help Me
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先 - 为什么没有主键？ 如果没有主键，它就不是表 - 只需添加一个即可！这将在很多层面上有所帮助……
其次：即使您有索引，SQL Server 查询优化器也会始终查看您的查询来决定使用索引是否有意义。如果选择所有列和大部分行，那么使用索引是没有意义的。
因此要避免的事情是：
SELECT * FROM dbo.YourTable几乎可以保证不会使用任何索引WHERE， 在查询中添加子句Gender列根本没有帮助。如果不了解更多关于表结构、这些表中包含的数据、行，以及您正在执行什么类型的查询，没有人可以真正回答您的问题 - 它太宽泛了......
更新：如果您想在表上创建聚集索引与您的主键不同，请执行以下步骤：
1) 首先，设计您的表
2) 然后打开索引设计器 - 在您选择的列上创建一个新的聚集索引。请注意 - 这不是主键！
3) 之后，您可以将主键放在
ID列中 - 它将创建一个索引，但该索引不是聚集的！First of all - why isn't there a primary key?? If it doesn't have a primary key, it's not a table - just add one! That will help on so many levels....
Secondly: even if you have an index, SQL Server query optimizer will always look at your query to decide whether it makes sense to use the index (or not). If you select all columns, and a large portion of the rows, then using an index is pointless.
So things to avoid are:
SELECT * FROM dbo.YourTableis almost guaranteed not to use any indicesWHEREclause in your queryGendercolumn with at most three different values doesn't help at allWithout knowing a lot more about your table structure, the data contained in those tables, the number of rows, and what kind of queries you're executing, no one can really answer your question - it's just way too broad....
Update: if you want to create a clustered index on a table which is different from your primary key, do these steps:
1) First, design your table
2) Then open up the index designer - create a new, clustered index on a column of your choice. Mind you - this is NOT the primary key !
3) After that, you can put your primary key on the
IDcolumn - it will create an index, but that index is not clustered !在没有更多信息的情况下，我猜测原因是表太小，不值得进行索引查找。
如果表的行数少于几千行，那么 SQL Server 几乎总是会选择执行表/索引扫描而不管该表上的索引，仅仅是因为索引扫描实际上更快。
索引扫描本身并不一定表明存在性能问题——查询实际上很慢吗？
Without having any more information I'd guess that the reason is that the table is too small for an index seek to be worth it.
If your table has less than a few thousand rows then SQL Server will almost always choose to do a table / index scan regardless of the indexes on that table simply because an index scan is in fact faster.
An index scan in itself doesn't necessarily indicate a performance problem - is the query actually slow?