SELECT * and Views

Why is SELECT * bad? How shall we count the ways?

  • Index scans – forget those carefully crafted indexes you spent hours testing – because SQL Server will certainly forget them, as it reads every data page.
  • Increased IO and memory – The thought makes me sad
  • Hard to read code – when your code breaks, it is going to be that much more difficult to find the breaking point.

And then, there is the problem of unexpected results.  Usually, I’ve seen these with views.  So, to show you exactly what I mean, let’s go create a table, and then make a view to set on top of it.  You choose the (non-production) database to try this:

CREATE TABLE JustDontSelectAll
(
    Column1 VARCHAR(15)
);
GO

INSERT INTO JustDontSelectAll
VALUES
('Please'), ('Dont'), ('EVER'), ('Do'), ('This'), ('In'), ('Production');
GO

SELECT *
FROM dbo.JustDontSelectAll;
GO

-- Now, let's create a view, and check our work....

CREATE VIEW BringItALLBack
AS
SELECT *
FROM JustDontSelectAll;
GO


SELECT *
FROM dbo.BringItALLBack;
GO	
The more you know…

Are you wondering what the big deal is? Well……what if I add a column to the table?

ALTER TABLE JustDontSelectAll ADD WatchThis VARCHAR(15);
GO
UPDATE JustDontSelectAll
SET WatchThis = 'NowYouSeeMe';
GO

What do you think will happen?  Call it in the air…no cheating!

SELECT *
FROM BringItALLBack;
GO
Nothin’ to see here, folks…

The view doesn’t get refreshed every time it executes, so it still thinks it has one column.  If we do refresh it by either dropping and recreating it or using sp_refreshview, the extra column will show:

EXEC sp_refreshview N'dbo.BringItALLBack';

SELECT *
FROM BringItALLBack;
GO

Now, what happens if we drop a column?  (No peeking!)

ALTER TABLE JustDontSelectAll DROP COLUMN WatchThis;
GO

SELECT *
FROM BringItALLBack;
GO
(Now you see me) …. Now you don’t!

It’s the same problem, really…the view doesn’t “know” that the column is removed, so it goes to look for it and can’t find it.  And then it lets you know all about it.

If we refresh the view and try again….

EXEC sp_refreshview N'dbo.BringItALLBack';
SELECT *
FROM BringItALLBack;
GO
Voila!

DISCLAIMER: I’m not saying that I never use SELECT *.  Nor am I saying that no one should ever use it.  But if or when you do, use it with a little caution, and understand when it can cause more harm than good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s