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