[聚合文章] 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

  ● 索引中的列

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


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

1. 聚集索引 创建聚集索引: ```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