SELECT * Statement in View NOT Working

Introduction

View has some limitation. Using SELECT * within the view is not a good Idea. If we create view we must use the Columns name in the Select statement.  We also recommended using to make materialized view (WITH SCHEMABINDING options).

Here in this article we are going to discuss about the adverse reaction of using SELECT * within a view.

Let's see a simple example to illustrate our point

Step – 1 [ Create a Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

    (

      EMPID    INT,

      EMPNAME  VARCHAR(50)

    );

GO

Step – 2 [ Insert some Records in the base Table ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

     (EMPID, EMPNAME)

VALUES(1, 'Joydeep Das'),

      (2, 'Sukamal Jana');

GO

Step – 3 [ Create A VIEW from this Base Table ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE', N'V') IS NOT NULL

   BEGIN

     DROP VIEW [dbo].[view_EMPLOYEE];

   END

GO  

CREATE VIEW [dbo].[view_EMPLOYEE]

AS

SELECT * FROM [dbo].[tbl_EMPLOYEE]; 

GO

Step – 4 [ Run both Table and View ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME

----------- --------------------------------------------------

1           Joydeep Das

2           Sukamal Jana

EMPID       EMPNAME

----------- --------------------------------------------------

1           Joydeep Das

2           Sukamal Jana

Step – 5 [ Add another columns to Base table ]

ALTER TABLE [dbo].[tbl_EMPLOYEE]

ADD [GRADE] CHAR(1); 

GO

Step – 6 [ Update the New column with Data ]

UPDATE [dbo].[tbl_EMPLOYEE]

SET [GRADE] = 'A';

GO

Step – 7 [ Again Run both Table and View – Do we find any difference ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME                GRADE

----------- ---------------------  ----------

1           Joydeep Das            A

2           Sukamal Jana           A

EMPID       EMPNAME

----------- ---------------------------------

1           Joydeep Das

2           Sukamal Jana

Step – 8 [ Run the sp_refreshview stored procedure ]

EXEC SP_REFRESHVIEW view_EMPLOYEE;

Step – 9 [ Again compare - Run both Table and View ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME                            GRADE

----------- ---------------------------------

1           Joydeep Das                  A

2           Sukamal Jana                 A

EMPID       EMPNAME                      GRADE

----------- ----------------------------------

1           Joydeep Das                  A

2           Sukamal Jana                 A

Hope you like it.

Posted by: MR. JOYDEEP DAS