Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

[serge]

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?

I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:

SELECT * FROM View1
WHERE ID = @ID

Is it a good idea to do this by moving the
common SELECT statement to a View? Will it be
less performant compared to before?
Will this approach cause new problems on
the long run?

I would appreciate your help.

Thank you very much

Please find below a sample code for this.
USE Northwind
GO

if exists (select * from sysobjects where name = 'EMPLOYEE' and xtype = 'U')
drop table EMPLOYEE
GO
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR(25) NOT NULL,
LASTNAME VARCHAR(25) NOT NULL,
AGE TINYINT NOT NULL,
NOTES VARCHAR(200) NOT NULL,
SPECIALID INT NOT NULL
)
GO
INSERT INTO EMPLOYEE
SELECT 'ABC', 'ABC1', 35, 'Abc', 1 UNION ALL
SELECT 'DEF', 'DEF1', 36, 'Def', 1 UNION ALL
SELECT 'GHI', 'GHI1', 37, 'Ghi', 1 UNION ALL
SELECT 'JKL', 'JKL1', 38, 'Jkl', 1 UNION ALL
SELECT 'MNO', 'MNO1', 39, 'Mno', 1 UNION ALL
SELECT 'PQR', 'PQR1', 40, 'Pqr', 1
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYID' and xtype =
'P')
drop procedure EMPLOYEEBYID
GO
CREATE PROCEDURE dbo.EMPLOYEEBYID
@ID INT
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRSTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAME
@FIRSTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLASTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAME
@LASTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYID 5
GO
EXEC EMPLOYEEBYFIRSTNAME 'PQR'
GO
EXEC EMPLOYEEBYLASTNAME 'DEF1'
GO
-- Now if I use a View instead?
if exists (select * from sysobjects where name = 'EMPLOYEESEARCHVIEW' and
xtype = 'V')
drop view EMPLOYEESEARCHVIEW
GO
CREATE VIEW EMPLOYEESEARCHVIEW
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
GO

-- And I create new Stored Procedures
if exists (select * from sysobjects where name = 'EMPLOYEEBYIDNEW' and xtype
= 'P')
drop procedure EMPLOYEEBYIDNEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYIDNEW
@ID INT
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRSTNAMENEW'
and xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAMENEW
@FIRSTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLASTNAMENEW' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAMENEW
@LASTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYIDNEW 5
GO
EXEC EMPLOYEEBYFIRSTNAMENEW 'PQR'
GO
EXEC EMPLOYEEBYLASTNAMENEW 'DEF1'
GO

[Steve Jorgensen]

Serge - IMO, it's not the * that's bad, it's the lack of a qualifier. I'd
write it as ...

SELECT View1.* FROM View1
WHERE View1.ID = @ID

Now, if you add a join, you don't automatically get all columns from -all-
tables, just the additional columns you specify.

The SELECT in your example could not be moved to a view because views cannot
take parameters, so you can't say "WHERE View1.ID = @ID".

On Thu, 13 Oct 2005 22:30:33 -0400, "serge" <se****@nospam.ehmail.com> wrote:

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?

I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:

SELECT * FROM View1
WHERE ID = @ID

Is it a good idea to do this by moving the
common SELECT statement to a View? Will it be
less performant compared to before?
Will this approach cause new problems on
the long run?

I would appreciate your help.

Thank you very much

Please find below a sample code for this.
USE Northwind
GO

if exists (select * from sysobjects where name = 'EMPLOYEE' and xtype = 'U')
drop table EMPLOYEE
GO
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR(25) NOT NULL,
LASTNAME VARCHAR(25) NOT NULL,
AGE TINYINT NOT NULL,
NOTES VARCHAR(200) NOT NULL,
SPECIALID INT NOT NULL
)
GO
INSERT INTO EMPLOYEE
SELECT 'ABC', 'ABC1', 35, 'Abc', 1 UNION ALL
SELECT 'DEF', 'DEF1', 36, 'Def', 1 UNION ALL
SELECT 'GHI', 'GHI1', 37, 'Ghi', 1 UNION ALL
SELECT 'JKL', 'JKL1', 38, 'Jkl', 1 UNION ALL
SELECT 'MNO', 'MNO1', 39, 'Mno', 1 UNION ALL
SELECT 'PQR', 'PQR1', 40, 'Pqr', 1
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYID' and xtype =
'P')
drop procedure EMPLOYEEBYID
GO
CREATE PROCEDURE dbo.EMPLOYEEBYID
@ID INT
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRSTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAME
@FIRSTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLASTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAME
@LASTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYID 5
GO
EXEC EMPLOYEEBYFIRSTNAME 'PQR'
GO
EXEC EMPLOYEEBYLASTNAME 'DEF1'
GO
-- Now if I use a View instead?
if exists (select * from sysobjects where name = 'EMPLOYEESEARCHVIEW' and
xtype = 'V')
drop view EMPLOYEESEARCHVIEW
GO
CREATE VIEW EMPLOYEESEARCHVIEW
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
GO

-- And I create new Stored Procedures
if exists (select * from sysobjects where name = 'EMPLOYEEBYIDNEW' and xtype
= 'P')
drop procedure EMPLOYEEBYIDNEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYIDNEW
@ID INT
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRSTNAMENEW'
and xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAMENEW
@FIRSTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLASTNAMENEW' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAMENEW
@LASTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYIDNEW 5
GO
EXEC EMPLOYEEBYFIRSTNAMENEW 'PQR'
GO
EXEC EMPLOYEEBYLASTNAMENEW 'DEF1'
GO

[Hugo Kornelis]

On Thu, 13 Oct 2005 22:30:33 -0400, serge wrote:

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
Hi Serge,

Yes. For several reasons.

First, you should never include columns that are not needed in the end
results. Sending extra data to the client wastes network bandwidth.
Using extra columns in the query might preclude the use of an efficient
covering index and force the optimizer to choose a more expensive
execution plan.
Note that this does not apply if you ALWAYS include a column list in
each query that uses the view - unless you index the view, in which case
it will apply anyway.

Second, using SELECT * in a view might cause unexpected errors if the
table is ever modified. Run the following repro to see a proof:

-- Set up table and view
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY,
c numeric(8,2) NOT NULL)
go
CREATE VIEW Test1
AS
SELECT *
FROM Test
go
-- Insert some data
INSERT INTO Test (a, c)
SELECT 1, 3.14
UNION ALL
SELECT 2, 17876.08
-- Show that view looks okay, and that we can do math with column c
SELECT *
FROM Test1
SELECT a, c, c * 2
FROM Test1
-- Add an extra column - note that this one goes at the end
ALTER TABLE Test
ADD b varchar(20) NOT NULL DEFAULT 'x' WITH VALUES
-- We want alphabetic column order, so we temporarily rename the table,
....
EXEC sp_rename 'Test', 'TestOld', 'OBJECT'
-- ... recreate the table in the desired column order, ...
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY,
b varchar(20) NOT NULL DEFAULT 'x',
c numeric(8,2) NOT NULL)
-- ... copy existing data over from old table, ...
INSERT INTO Test (a, b, c)
SELECT a, b, c
FROM TestOld
-- ... and remove the original table.
DROP TABLE TestOld
go
-- Now look what happened to our view!!
SELECT *
FROM Test1
SELECT a, c, c * 2
FROM Test1
go
-- Clean up the garbage
DROP VIEW Test1
DROP TABLE Test
go
(snip)CREATE VIEW EMPLOYEESEARCHVIEW
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
GO

There's very little gain in this example. Note that the actual execution
plan will more than likely be exactly the same. The only advantage you
would have is that part of the logic is included in the view and doesn't
have to be duplicated in all queries. Of course, in this example the
logic is so simple that there's no reason to do this. If your actual
view is quite complex, then you could certainly consider this approach.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?

Yes. SELECT * is bad in production code because:

1) The behaviour of the code may change unexpectedly when colunms are
added or dropped.
2) You cannot trace whether a column is actually used or not.
3) Unnecessary data is sent to the client.

There are a few exceptions where SELECT * is permissible:

1) In subqueries with EXISTS/NOT EXISTS of course.
2) From temp tables created in the same procedure.
3) Debug things like IF @debug = 1 SELECT * FROM #tmp.

I would also like to add that you should return columns the client acutally
uses. We are suffering from have several stored procedures where about
every column is included in the result set, and some of these procedures
returns data to clients outside our system. The problem I have, is that I
can't tell whether the column is actually there because of a requirement,
or by routine. This makes it difficult for me, if I need to change or
remove that column.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

Thank you all for the answers.

If I may continue adding more information as I am learning
by discussing with some of our developers in our shop.

Our developers are using .NET and I know very little .NET.
I am not even an advanced SQL person to know if what
they want to do is actually good and not bad.

Here's what they are doing. They want to use SPs calling
a single common View and in the view return ALL the columns all the
time. Maybe the biggest table we have has 50 columns maximum.
If I can summarize their points:
1- They are writing our application's SDK and they are standardizing
all the SPs by using "SELECT * from View1 WHERE a = b"
2- They will have our application use these SPs.
3- Only if we encounter performance problems they will deal with
accordingly by adding new SPs. Actually before they even select
that approach they will tackle the performance problem as a DBA
issue and not a developer issue. That is they will expect the DBAs
to deal with the performance problem by making changes on the
tables, indexes etc... before the DBAs get to do anything with the
SPs' code.
4- One of their reasons to use this approach is to guarantee that a
developer will not mess up by adding a new column to the UI and
forget to add it also to x number of SPs.

Now they seem confident that their approach is very good and does
not have any known problems now or down the road.

I am skeptical that the way they're looking at this is as simple as
they're explain it and don't have any complications down the road. I am
not an expert in performance so I couldn't even make my argument
about network traffic that is returning all columns for them to consider
as a problem.

What do you think?

Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Here's what they are doing. They want to use SPs calling
a single common View and in the view return ALL the columns all the
time. Maybe the biggest table we have has 50 columns maximum.
If I were in that shop, it would come to blows, if they were to insist
on this.

We have just too many stored procedures of that kind in our system. They
don't do SELECT *, but they return too many column. And there is a serious
maintenance problem with this. Not all data models are perfect, and every
once in a while you find columns that no longer are in use. Or you suspect
are not in use. Or that may be in use, but you want to change how it is
used. To be able to do this, I need to track down all references to
the column, and if the column is selected from some general procedure,
and into the application - or even worse in some external interface, it's
hard to tell.
4- One of their reasons to use this approach is to guarantee that a
developer will not mess up by adding a new column to the UI and
forget to add it also to x number of SPs.

Yes, in the short run, this saves some time. In 4-5 years timeframe,
they are causing an ever-growing burden of legacy problems.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"serge" <se****@nospam.ehmail.com> wrote in message
news:6C********************@weber.videotron.net...

Thank you all for the answers.

If I may continue adding more information as I am learning
by discussing with some of our developers in our shop.

Our developers are using .NET and I know very little .NET.
That's ok, sounds like they know even less about proper programming.

You're at least asking the right questions.
I am not even an advanced SQL person to know if what
they want to do is actually good and not bad.

Here's what they are doing. They want to use SPs calling
a single common View and in the view return ALL the columns all the
time.
This is VERY bad design. I'm not even sure where to begin.

Basically it sounds like they're taking a database and throwing out any
semblance of Codd's ideas.

Maybe the biggest table we have has 50 columns maximum.
If I can summarize their points:
1- They are writing our application's SDK and they are standardizing
all the SPs by using "SELECT * from View1 WHERE a = b"
2- They will have our application use these SPs.
3- Only if we encounter performance problems they will deal with
accordingly by adding new SPs. Actually before they even select
that approach they will tackle the performance problem as a DBA
issue and not a developer issue.
And the DBA will start by telling them "DON'T DO THIS!"

But as they're already decided that they're going to ignore the advice of
numerous DBAs, why would they listen then?
That is they will expect the DBAs
to deal with the performance problem by making changes on the
tables, indexes etc... before the DBAs get to do anything with the
SPs' code.
As a DBA I'd break the view. Oops, that means them fixing their code.

4- One of their reasons to use this approach is to guarantee that a
developer will not mess up by adding a new column to the UI and
forget to add it also to x number of SPs.
But, this actually completely negates that goal.

What happens when they add a new column to one of the base tables?

Do they recompile the view? What do they do now when select * returns the
columsn in a different order than before. All their code has to be
rewritten.

Now they seem confident that their approach is very good and does
not have any known problems now or down the road.

I am skeptical that the way they're looking at this is as simple as
they're explain it and don't have any complications down the road. I am
not an expert in performance so I couldn't even make my argument
about network traffic that is returning all columns for them to consider
as a problem.
Network traffic is a definite issue. This won't scale well.

But not only that, they are actually making their maintenace problems far
worse in my opinion.


What do you think?

Thank you

[Steve Jorgensen]

On Thu, 13 Oct 2005 22:30:33 -0400, "serge" <se****@nospam.ehmail.com> wrote:

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?

....

I've read through this thread and compared it to the practices I've evolved
over time, and I think I have a somewhat different take on it.

1. I find that, in my applications, most tables don't have a very large number
of columns, and most of the columns in a table are likely to be useful in
cases where any of them are.

2. Most stored procedures and views end up getting used from multiple places
in the client code, each with similar, but not identical needs. Most of thems
need most columns, but not exactly the same ones.

3. I add fields to tables relatively often and freely, but remove them
sparingly and with consideration.

4. I (mostly) have unit tests in the client code that will fail if dependent
columns go missing from server query results. Where unit tests are not
present, I sill try to write code with little waste, so most all the code gets
executed in most cases, so breakages will be found early.

5. I almost never write client-side code that will care if extra, unexpected
fields are present in a result.

Under these circumstances, ...

1. I usually find it a good practice to go ahead and select <table>.* for the
most significant table in a select, and select specific fields from other
tables as needed.

2. I don't find that transferring an average of a few extra fields is a
performance burden because the effects of that are completely swamped by other
unavoidable bottlenecks that occur in real-world applications. If there is
one query that is called very often and returns a large number of rows, then
it makes sense to optimize the columns returned for -that- case, not for all
cases just on GP.

3. Limiting the columns to just what's needed in a specific case leads to a
proliferation of views and procedures, mostly the same, but differing in a few
details. That's more server-side objects to maintain when the schema does
change, more obsolete objects being left laying around, and more objects to
paw through to find the one you're looking for.

4. If the issue does exist that a large number of unused fields is present in
a high fraction of queries, that's a strong indication that the schema design
needs some work, not that column output of each query should be better
constrained.

[David Portas]

SELECT * in views is buggy even if you require every column:

CREATE TABLE T1 (x INT PRIMARY KEY, y INT NOT NULL)
GO
CREATE VIEW V1 AS SELECT * FROM T1
GO
ALTER TABLE T1 DROP COLUMN y
ALTER TABLE T1 ADD z INT
INSERT INTO T1 VALUES (1,2)
GO
SELECT x,z FROM T1
SELECT x,y FROM V1

Result (SP4):

(1 row(s) affected)

x z
1 2

(1 row(s) affected)

x y
1 2

(1 row(s) affected)

This alone means it isn't an option in my book. Given that it takes
less than two seconds to past the column list into a view definition I
don't see what the excuse is for SELECT *.

--
David Portas
SQL Server MVP
--

[Steve Jorgensen]

On 20 Oct 2005 02:27:37 -0700, "David Portas"
<RE****************************@acm.org> wrote:

SELECT * in views is buggy even if you require every column:

CREATE TABLE T1 (x INT PRIMARY KEY, y INT NOT NULL)
GO
CREATE VIEW V1 AS SELECT * FROM T1
GO
ALTER TABLE T1 DROP COLUMN y
ALTER TABLE T1 ADD z INT
INSERT INTO T1 VALUES (1,2)
GO
SELECT x,z FROM T1
SELECT x,y FROM V1

Result (SP4):

(1 row(s) affected)

x z
1 2

(1 row(s) affected)

x y
1 2

(1 row(s) affected)

This alone means it isn't an option in my book. Given that it takes
less than two seconds to past the column list into a view definition I
don't see what the excuse is for SELECT *.

--
David Portas
SQL Server MVP

I run a program that drops and rebuilds all the stored procedures, views, and
functions. Before it runs, it checks to see if any previously existing
objects have been added or removed, copies the definitions of new objects, and
flags the deleted objects. I had to write this anyway to deal with the
occasional renamed object or column, since that can create a real tangled mess
otherwise.

[Erland Sommarskog]

Steve Jorgensen (no****@nospam.nospam) writes:

1. I find that, in my applications, most tables don't have a very large
number of columns, and most of the columns in a table are likely to be
useful in cases where any of them are.

Serge mentioned in one of his posts, that there were tables with
50 columns. Maybe this in itself is a token of poor design, but we have
several tables in our system in the 50-100 range. (In fact, I found
the other day, in the corner of the database that I am not responsible
a 213-column table!)

But with this size, the risk for columns that have grown obsolete is
starting to increase.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp