Avoid SELECT * FROM TABLENAME

Posted: August 27, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , ,

Select * from TableName in your PRODUCTION code or Stored Procedure is problematic because if a column is added or deleted, you can expect a sure bug.

It is still generally advisable not to use SELECT * because the definition of the table may change over time. For example, a table may have a column added, removed, renamed or repositioned relative to other columns,such changes can result in SQL queries returning data that are never used, attempting to implicitly access data that do not exist, implicitly relying upon a column with a given name or relying upon the relative ordering of the table’s columns. While explicitly indicating only those columns one needs does not prevent problems when a column is removed or renamed, it does prevent problems when a column is added or repositioned and makes it easier to find a problematic query when one is able to search on the text of the column name.

Lets see an example here,

—- Lets create a Test Table.

CREATE
TABLE
[dbo].[VirendraTest](

    [ID]
[int]
IDENTITY(1,1)
NOT
NULL,

    [Name]
[varchar](50)
NOT
NULL,

    [Age]
[int]
NULL

)

GO

—- Lets Insert some data in this Table.

Insert
into
VirendraTest
values

(‘Naresh’,40),

(‘AShok’,42),

(‘Ashish’,27),

(‘Paul’,35),

(‘Smitha’,29)

—- Now Create a View on This Table

Create
View
VWTest

as

(

Select
*
from
VirendraTest

)

go

—- See Data as per View

Select
*
from
VWTest

—- Now, Add a new Column in Table / Position Columns orders / Drop Any Coumn etc..

Alter
table
VirendraTest
add
Gender
varchar(1)
default
‘M’

—- See Data as per View, ha ha ha … its not as per Table.

Select
*
from
VWTest

—- But in Table its as

Select
*
from
VirendraTest

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s