SQL通配符:性能开销? [英] Sql wildcard: performance overhead?

问题描述

我已经用谷歌搜索了这个问题，似乎找不到一致的意见，也找不到很多基于可靠数据的意见.我只是想知道在SQL SELECT语句中使用通配符是否会引起比单独调用每个项目更多的开销.我已经在几个不同的测试查询中比较了两者的执行计划，并且似乎估算值始终是相同的.可能是其他地方产生了一些开销，还是它们的处理方式完全相同?

I've Googled this question and can't seem to find a consistent opinion, or many opinions that are based on solid data. I simply would like to know if using the wildcard in a SQL SELECT statement incurs additional overhead than calling each item out individually. I have compared the execution plans of both in several different test queries, and it seems that the estimates always read the same. Is it possible that some overhead is incurred elsewhere, or are they truly handled identically?

我具体指的是:

SELECT *

vs.

SELECT item1, item2, etc.

推荐答案

SELECT * FROM...

和

SELECT every, column, list, ... FROM...

将执行相同的操作，因为两者都是未经优化的扫描

will perform the same because both are an unoptimised scan

区别是:

• sys.columns中的额外查询来解决*
• 表模式更改时，合同/签名也会更改
• 无法创建覆盖索引.实际上，根本没有任何调整选项
• 如果未进行架构绑定，则必须刷新所需的视图
• 无法使用*索引或架构视图绑定
• ...和其他东西

关于同一主题的其他SO问题...

Other SO questions on the same subject...

• 不使用select *的原因是什么?
• 选择之间是否有区别*并选择列出每个列
• SQL查询问题-选择*从视图中选择或从视图中选择col1，col2…
• 从表中选择* "与从表中选择colA，colB等"在SqlServer2005中的有趣行为

• What is the reason not to use select * ?
• Is there a difference betweeen Select * and Select list each col
• SQL Query Question - Select * from view or Select col1,col2…from view
• "select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005

这篇关于SQL通配符:性能开销?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！