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)

1. 如果没聚集索引

● 显示所有列

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

● 索引中的列

SELECT top 10 
     BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2
      ,[OtherColumn2]
  FROM [dbo].[BusinessInfoTable]
GO


SELECT top 10 *
  FROM [dbo].[BusinessInfoTable]
GO

1. 聚集索引

    创建聚集索引:

   CREATE CLUSTERED INDEX IDX_3 ON BusinessInfoTable 
   (BuniessCode1)
   

● 显示所有列

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