| Groups | Blog | Home |
sql server (alternate) : Using "SELECT * " is a bad practice even when using a VIEW instead of a table?
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
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". [quoted text, click to view] 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 >
serge (sergea@nospam.ehmail.com) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
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) [quoted text, click to view] >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 --
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
serge (sergea@nospam.ehmail.com) writes:
[quoted text, click to view] > 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. [quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[quoted text, click to view]
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'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
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 --
[quoted text, click to view] "serge" <sergea@nospam.ehmail.com> wrote in message news:6Cu5f.25444$Ms1.963793@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. [quoted text, click to view] > 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. [quoted text, click to view] > 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? [quoted text, click to view] > 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. [quoted text, click to view] > 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. [quoted text, click to view] > > 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. [quoted text, click to view] > > What do you think? > > Thank you > >
On 20 Oct 2005 02:27:37 -0700, "David Portas"
[quoted text, click to view] <REMOVE_BEFORE_REPLYING_dportas@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
Steve Jorgensen (nospam@nospam.nospam) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |