Performance Impact of Using SELECT *

/* *************************** Create TestDB Database **************************** */

IF NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
    CREATE DATABASE [TestDB];
END
GO

USE [TestDB];
GO

/* *************************** Create TestTable Table ***************************** */

IF EXISTS (SELECT * FROM sys.sysobjects WHERE type = 'U'AND name = 'TestTable')
BEGIN
    DROP TABLE [dbo].[TestTable];
END

CREATE TABLE [dbo].[TestTable]
(
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Data] [int] NOT NULL,
    [InsertedDate] [datetime] NOT NULL DEFAULT GETDATE()
)

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'PK_Individual_Id')
BEGIN
    DROP INDEX PK_Individual_Id ON [dbo].[TestTable];
END

CREATE CLUSTERED INDEX PK_Individual_Id
    ON [dbo].[TestTable] (Id);
GO

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_Individual_Data')
BEGIN
    DROP INDEX IX_Individual_Data ON [dbo].[TestTable];
END

CREATE NONCLUSTERED INDEX IX_Individual_Data
    ON [dbo].[TestTable] (Data);
GO

/* ************************* Insert Test Data using CTE *************************** */

WITH TestData
AS (
    -- Anchor Query
    SELECT 1 AS SampleId, 1 AS SampleData
    -- Union All
    UNION ALL
    -- Recursive Query
    SELECT td.SampleId + 1, CAST(ABS(CHECKSUM(NEWID())) % 300 AS int)
    FROM TestData td
    WHERE td.SampleId < 30000
)
INSERT INTO [dbo].[TestTable] (Data)
    SELECT SampleData
    FROM TestData
    OPTION (MAXRECURSION 30000);
GO 10

/* *************************** Clear Buffers and Cache **************************** */

DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE ('ALL');
DBCC DROPCLEANBUFFERS;
GO

/* *********************************************************************************
As we are requesting all the rows in the table, here a Clustered index scan is
inevitable. Examining Execution plan shows 81% of total cost towards batch. So
sending unnecessary data is going to give poor performance.
********************************************************************************* */

SELECT * FROM [dbo].[TestTable] WHERE [Data] = 251;

/* *************************** Clear Buffers and Cache **************************** */

DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE ('ALL');
DBCC DROPCLEANBUFFERS;
GO

/* *********************************************************************************
As we are only requesting only data which is present in the non-clustered index
itself. This query is not returning unnecessary data and will give more
performance. Examining Execution plan shows it got 0% cost towards complete
batch cost.
********************************************************************************* */

SELECT [Data] FROM [dbo].[TestTable] WHERE [Data] = 251;

/* **************************** Drop TestTable Table ****************************** */

IF EXISTS (SELECT * FROM sys.sysobjects WHERE type = 'U'AND name = 'TestTable')
BEGIN
    DROP TABLE [dbo].[TestTable];
END
GO

/* **************************** Drop TestDB Database ***************************** */

USE master;
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
    DROP DATABASE [TestDB];
END
GO