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
WikiDBA 