October 5, 2007 at 5:25 pm
Hoping this trickles back to the lair of the Architect...
One of the most disparaging things I run across in SQL code is the use of;
SELECT *
Now there are many reasons why this is bad practice including, but not limited to;
-Unnecessary calls to the system tables
-Significant increase in the number of locks required
-Microsoft promises to deprecate this syntax in forthcoming releases of SQL Server.
What is really disparaging is to find this syntax throughout the library of system stored procedures in SQL 2005. For example, the system stored procedure MASTER..sp_password, which might very well be used in a distributed enterprise application, consists of the following SQL code;
CREATE procedure [sys].[sp_password]
@old sysname = NULL, -- the old (current) password
@new sysname, -- the new password
@loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @exec_stmt nvarchar(4000)
-- RESOLVE LOGIN NAME
if @loginame is null
select @loginame = suser_sname()
if @new is null
select @new = ''
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_password')
return (1)
end
-- CHECK IT'S A SQL LOGIN --
if not exists (select * from master.dbo.syslogins where
loginname = @loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
if @old is null
set @exec_stmt = 'alter login ' + quotename(@loginame) +
' with password = ' + quotename(@new, '''')
else
set @exec_stmt = 'alter login ' + quotename(@loginame) +
' with password = ' + quotename(@new, '''') + ' old_password = ' + quotename(@old, '''')
exec (@exec_stmt)
if @@error <> 0
return (1)
-- RETURN SUCCESS --
return (0)-- sp_password
Notice the section where the existence of the login name is validated. This section could be replaced with a valid column name or a simple static value. The check for existence relies on the simple fact that a recordset was returned or no records where returned.
So what happens when SQL encounters a SELECT *? A very expensive trip to the system catalog with costly locks on the system tables. Indeed, replacing this code with the following;
if not exists (select 1 from master.dbo.syslogins where
loginname = @loginame and isntname = 0)
Reduces the number of locks acquired from 200+ down to 20 and the system tables are not affected.
In addition, another best practice is to use the built-in system views rather than access the system tables directly. Such a query might look like this;
if not exists (SELECT 1 FROM sys.sql_logins WHERE [type]='S' AND [name]='SA')
It would be great to see best practices enforced in coming versions of SQL as well as the Worlds SQL code base.
Remember, don't be lazy! Enumerate your columns. Never access system tables directly if you can avoid it.
Cheers!
October 6, 2007 at 11:22 am
I dont agree with you
1. Just imagine, that to browse data instead of SELECT * you have to type 50 long column names... I dont believe that Microsoft will depreciate it.
2. Resolving * does not require a lot of time: usually, during the first execution only.
3. SELECT indentity(int,1,1) as ID, * into #tab from T are very common.
4. exists(select * and exists(select 1 product THE SAME execution plan - check it out.
October 6, 2007 at 4:05 pm
The use of EXISTS implies that the column list is not even checked only the WHERE predicate is taken into account.
So with EXISTS: select 1, select 'x' and select * are just the same.
* Noel
October 6, 2007 at 8:01 pm
3. SELECT indentity(int,1,1) as ID, * into #tab from T are very common.
Shoot, that's one of my favorites. If the boys in Redmond ever get rid of Select *, I'm making a trip to Redmond... with a bat... with a nail in it 😛
--Jeff Moden
October 7, 2007 at 10:49 am
In fact, using SELECT * might be dangerous in SOME cases.
But it is like GOTO in conventional languares.
Too many GOTOs are bad, but prohibiting it completely IS WRONG.
The same for SELECT *
Insert into T (A,B,C) select * from X
is bad: we dont see an order of columns in X, and even not sure there are 3 columns
SELECT * into CopyTable from Table where 0=1
to create a table with the same list of column is RIGHT
BTW, the nick name of a topic starter is DevTeamLead,
Poor guys, I guess in his team nobody can use SELECT * 🙂
October 8, 2007 at 5:42 am
you always worry when you read posts like this, I had a serious problem with a major software release where developers had picked up something on the web ( which was totally incorrect and somewhat absurd - but that's another story )
the " exists select * " is a special case I believe and has nothing to do with an actual select *. Select * is actually worst practice - but again this is not what the post is about.
the two queries are identical but the select 1 is bad practice as the column 1 does not exist and should someone add a column named 1 to the table this code may produce an unexpected result.
I'd love to know how you can get different results and I wonder about the testing regimes employed ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 10:28 am
DevTeamLead (10/5/2007)
Hoping this trickles back to the lair of the Architect...One of the most disparaging things I run across in SQL code is the use of;
SELECT *
[...]
-Microsoft promises to deprecate this syntax in forthcoming releases of SQL Server.
Where do people hear this crap, and why do they believe it? I've seen postings here and heard elsewhere all sorts of stuff that 'Microsoft says will be removed...', mostly referring to something the person simply doesn't like (SELECT *, !=, CONVERT(), etc., etc.).
The deprecation list is well documented:
http://msdn2.microsoft.com/en-us/library/ms143729.aspx
-Eddie
Eddie Wuerch
MCM: SQL
October 9, 2007 at 11:26 am
I doubt Microsoft will deprecate select * because, like it or not, it is part of the SQL standard. I agree with dots67's comparison between select * and goto. It can easily be overdone but sometimes it is necessary.
Any feature or tool can be compared to poison. A substance is a poison not based on what. but based on how much. Everything has its own different threshold, some high -- some low, but anything can be bad if it is overdone or done in an inappropriate context.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 11, 2007 at 6:06 pm
select * is ANSI standard.
And there is places where it shouldn't matter what is returned - as it is up to the DBMS to handle.
An example is the following: -
select id, abbr
from x
where not exists
(
select *
from y
where x.Id = y.id
)
I assume that it is the same in SQL Server, but I was told many years ago when working with Ingres that this syntax is actually quicker than the following: -
select id, abbr
from x
where not exists
(
select id
from y
where x.Id = y.id
)
and that is because the DBMS is checking the ROWS not the actual column. Realistically the query plan should end up being the same.
Dismissing "select *" is the not the solution for your problems. Code reviews are. Look into it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply