break "select *" code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

break "select *" code

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-04-17 : 12:12:51

I found several objects utilizing "select *". I have read in several locations that this is a discouraged practice - that in some cases the code may break. I tried several ways of breaking "select *" code, but I was unsuccessful. Can someone show me an example how to break code with "Select *" in it?

Thanks,
Ray

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-17 : 12:56:59

create table a(i int)
go
insert a select 1
go
create view av as select * from a
go
go
select * from a
select * from av
go
alter table a add j int
go
select * from a
select * from av
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-04-18 : 07:35:04

Adding a TEXT column (or several) to the original table, and then putting large blobs of data in those columns will probably break exiting applications that do SELECT * - they will retrieve the additional columns that they are not using, which will lead to increased, and unnecessary, network load increase and possibly timeouts, out-of-memory and so on.

If you only need three columns, say, there is no point retreiving the other 100 columns from the table!

(Also, each time you save SELECT * the server has to work out what columns that means ...)

Kristen

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-04-18 : 11:15:28

Thanks. How about an example to break a stored procedure? Adding a column to a table doesn't seem to an adverse effect on stored procedures like views.

Kristen
Test

22859 Posts

Posted - 2005-04-18 : 11:46:53

Doing a SELECT * in an SProc is just as bad as doing it anywhere else!

SProcs are more "immune" to change because their cached query plans are thrown away now & again - e.g. if the server is rebooted, or the SProc is recreated, or the cache entry becomes stale, or statistics are updated (in certain ways).

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources