Over a million developers have joined DZone.

SELECT * Statement in View Not Working

· Java Zone

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

Introduction

Views have some limitations. Using SELECT * within a view is not a good Idea. If we create a 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_REFRESHVIEWview_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.

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}