|
|
Posted by Steve Jorgensen on 10/14/05 17:31
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" <sergea@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
>
Navigation:
[Reply to this message]
|