Posted by Hugo Kornelis on 10/15/05 00:09

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)

[Back to original message]