|
This is just a rant. If you're going to give a tip, you should give reasons why your suggestion is worth following - the only reason you give is a single example of someone using Select * in a way that broke *your* code because they used it in a particular way. Select * can be used safely and there are many situations where it's use is valid - though I agree it's generally wasteful on bandwidth and the number of rows returned should be limited. And using it to transfer data between tables is a *BAD* idea.
Incidentally, it's also a *BAD* idea to alter the schema and code on a live site without validating the changes on a test system. Don't you have any test/QA procedures in place?
|
|
|
|
|
Yes, there is a way. I just can't recall that now... I haven't been doing much work with SQL these days.
|
|
|
|
|
Yes, it's kind of implied that it is "select * where (some limiting condition)" to get the rows you want, or if you just want the schema you can use top 0 (but isn't there a proper way to get the schema with column types and everything for that?).
|
|
|
|
|
Reason for my vote of 3
John's later post indicates that SELECT * is the least of his problems. It sounds like everything about the project is a cluster thing. Chris's comment articulates it well.
Lashing out against SELECT * in frustration is understandable but unwarranted. We don't ban every SQL statement variation that might be misused.
|
|
|
|
|
Reason for my vote of 5
I couldn’t agree with you more, and to Chris’s point, I always, always alias my tables so its absolutely clear where that data point is coming from.
|
|
|
|
|
Reason for my vote of 4
Not entirely true. E.g. select top 1 * from myTable where key = myValue
|
|
|
|
|
This article is right but doesn't explain why... There are a few reasons, the main one being that you want your queries to be predictable:
- If a column is added, (poorly written) client code that uses column indexes may fail.
- If a column is added with a lot of data (say big documents) this may create a big performance impact.
- SQL may use different indexes (or no index at all) if it needs all columns since it may decide that e.g. an index seek + bookmark lookup is more work then doing a table scan.
Exceptions (since they return a predictable number of columns and are optimized properly by SQL):
- Using * in subqueries (such as SELECT A FROM (SELECT * FROM T)).
- Using * from a subquery (such as SELECT * FROM (SELECT A FROM T)).
Cheers,
Michel
|
|
|
|
|
Reason for my vote of 5
Everybody learns this the hard way
|
|
|
|
|
Reason for my vote of 5
I agree, except "cluster is 2 syllables, but the following word is only 1.
|
|
|
|
|
Reason for my vote of 4
Good read, interesting perspectives, especially in the alternatives.
Good input from alot of sources here.
|
|
|
|
|
Also always list columns on an insert
INSERT INTO mytable
SELECT col1, col2, col3 from modtable
If you add a column to modtable this will break.
|
|
|
|
|
'Cluster' is two syllables.
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.
|
|
|
|
|
I *know* "cluster" is two syllables. It's part of my endearing yet sardonic wit.
|
|
|
|
|
Reason for my vote of 5
It is the evil death* and should always be avoided.
|
|
|
|
|
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.
|
|
|
|
|
Very wise. I think you really hit the underlying problem here...
|
|
|
|
|
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
END
|
|
|
|
|
Reason for my vote of 5
So true so many debug sessions that I have done with SQL have been down to a SELECT * moment
|
|
|
|
|
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)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
