Select * is okay when you really do want the whole table schema, typically for taking a temporary copy of some rows for a complex update operation or for displaying it to the user. In business logic it is almost always a mistake, and if you are making assumptions about what it returns it can be a dangerous one.
Select * isn't okay if you only want the whole table schema.
Back in the days when I was still new at [the company] and doing data analyst (with a little background in programming) work, one of the application keeps getting slower and slower by the week. Every time we loaded some data into SQL, it would take longer to run the app - from 1 minute to 5 minutes then 10 minutes... I was so frustrated and started to dig into the code and found the culprit - "Select *".
It turned out, one of our programmers wanted to get the schema of the table and used Select * to retrieve it. That is okay if the table is small but if your table is growing constantly then this will be a huge problem.
After discovering the problem, I changed the code to Select Top 0 * and the problem was gone and the app ran faster than ever. So if you really want to get the schema of the table and do not know it before hand and need to used Select *, make sure you limit the number of rows returned.
Reason for my vote of 5
Totally agree with John! Using SELECT* is also a bad practice from the performance prospective. Thanks for sharing this useful tip/use case!
Just to be Devil's advocate here - well, not quite - doing a Select Column1,..., ColumnN from Table can be just as dangerous. Was the issue because you had two tables both with a column name with the same name? Select Table.Column1,..., Table.ColumnN from Table will at least allow the stored proc to compile, but even then your rowset will contain duplicate column names.
Correct database change management is what will save you here. Select * is not the problem, it's a symptom.
We didn't have the time to figure out exactly why the queries went nuts. We have until 08/08 to get the changes I'm working on implemented, tested and deployed. All I know is that I added some new columns to an existing table, and another part of the site (AND an associated desktop app) got buggered up as a result. Removing the columns brought the universe back into alignment. None of MY queries use SELECT *.
Regarding change mamangement, that would be great if we had an actual DBA on the team. Essentially, there is NO management regarding the source code, and it even appears that much of it isn't even in source control. The only documentation associated with any of the projects we're working on is the documentation I'VE written, and I would venture to guess that fully 99% of the code I didn't write has absolutely NO comments. What we need is three more programmers and a full-time DBA on the team to completely rwrite everything from scratch and get rid of the external processes (like DTS scripts and batch files that move data from a hodgepodge of disparate Access databases into SQL server. The problem with that plan is that we work for a defense contractor, so not only are we not getting anyone new, one of the seats on our contract is going to be deleted, meaning one of the four existing programmers will no longer be with us by next June. Since I'm the new guy, it's highly probable it will be me.
There is a script floating aroun that will search the procs and functions for a substring, this will help locate the offending procs. The code that uses select * is a whole nuther problem.
This is the one I "borrowed" from the net. Searches SPs and Views:
CREATE PROCEDURE [dbo].[Util_FindTextInSP]
(
@StringToSearch varchar(100)
)
AS
BEGIN
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT DISTINCT so.Name
FROM sysobjects so (NOLOCK)
INNER JOIN syscomments sc (NOLOCK) on so.Id = sc.ID
AND (so.Type = 'P' OR so.type='V')
AND sc.Text LIKE @StringToSearch
ORDER BY so.Name
Reason for my vote of 5
Agree, causes much confusion and extra maintenance. I think the only situation where * is beneficial is when you have no other means to figure out the table's structure (columns, data types etc)
Last Visit: 31-Dec-99 18:00 Last Update: 26-Apr-24 0:31