907
技术社区[云栖]
SqlServer基础--Select *与Select 指定列的区别
select * from table
- 有聚集索引：聚集索引扫描
- 没有聚集索引 :表扫描
select 所有的列名 from talbe
有聚集索引：聚集索引扫描
没有聚集索引 :表扫描
select 索引中的列名 from talbe
- 指定索引的扫描
以文本的展示执行计划
SET SHOWPLAN_TEXT ON
GO
测试Sql 语句
CREATE DATABASE Test6;
GO
USE Test6;
GO
Create table BusinessInfoTable
(
BuniessCode1 varchar(50),
BuniessCode2 varchar(50),
BuniessCode3 varchar(50),
BuniessCode4 varchar(50),
BuniessStatus1 tinyint,
BuniessStatus2 tinyint,
BuniessDateTime1 Datetime,
BuniessDateTime2 Datetime,
OtherColumn1 varchar(50),
OtherColumn2 varchar(50),
OtherColumn3 varchar(50)
)
declare @i int=0
while @i<1000000
begin
insert into BusinessInfoTable
values
(
NEWID(),NEWID(),NEWID(),NEWID(),RAND()100,RAND()100,
DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()
)
set @i=@i+1
end
CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable
(BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2)
INCLUDE(OtherColumn2)
--或者这样，只是索引前导列顺序不一样
CREATE NONCLUSTERED INDEX IDX_2 ON BusinessInfoTable
(BuniessDateTime1,BuniessDateTime2,BuniessStatus1,BuniessStatus2)
-
如果没聚集索引
● 显示所有列
USE [Test6] GO SELECT top 10 [BuniessCode1] ,[BuniessCode2] ,[BuniessCode3] ,[BuniessCode4] ,[BuniessStatus1] ,[BuniessStatus2] ,[BuniessDateTime1] ,[BuniessDateTime2] ,[OtherColumn1] ,[OtherColumn2] ,[OtherColumn3] FROM [dbo].[BusinessInfoTable] GO
SELECT top 10 *
FROM [dbo].[BusinessInfoTable]
GO
● 索引中的列
```js
SELECT top 10
BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2
,[OtherColumn2]
FROM [dbo].[BusinessInfoTable]
GO
SELECT top 10 *
FROM [dbo].[BusinessInfoTable]
GO
- 聚集索引 创建聚集索引: ```js CREATE CLUSTERED INDEX IDX_3 ON BusinessInfoTable (BuniessCode1)
● 显示所有列
```js
USE [Test6]
GO
SET SHOWPLAN_TEXT ON
GO
SELECT top 10
[BuniessCode1]
,[BuniessCode2]
,[BuniessCode3]
,[BuniessCode4]
,[BuniessStatus1]
,[BuniessStatus2]
,[BuniessDateTime1]
,[BuniessDateTime2]
,[OtherColumn1]
,[OtherColumn2]
,[OtherColumn3]
FROM [dbo].[BusinessInfoTable]
GO
SELECT top 10 *
FROM [dbo].[BusinessInfoTable]
GO
● 索引中的列
SELECT top 10
BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2
,[OtherColumn2]
FROM [dbo].[BusinessInfoTable]
GO
SELECT top 10 *
FROM [dbo].[BusinessInfoTable]
GO
最后更新：2017-11-05 16:03:41