Simple SQL SERVER view with example

What is sql server view ?

Views are also known as virtual table in sql server that contain columns and rows. Multiple queries are combined together to define sql server view, it uses the data from one or more tables in sql database.

We can insert data through view into table that you have seen so many times. Let’s go through with an example which will demonstrate behavior of the view if you made any changes in table definition(drop of column, adding new column).

Example

We are creating table and inserting 2000 rows in it. Run the below scripts which will insert and populate data in the table.

Create table stinfo
(
St_ID int,
Sub_code varchar(10),
SAddress varchar(8)
)

insert into stinfo
values(102121,’BMP01′,’NJ’)
GO 2000

Now we will create view on the stinfo table

How to create simple view ?

CREATE VIEW vStinfo
AS
SELECT * FROM dbo.stinfo

Lets drop one column from the table by using ALTER TABLE option. This option will allow to drop existing column from the table.

alter table stinfo
drop column Sub_code

Lets see the output after executing select query on view.

select * from vStinfo

Msg 4502, Level 16, State 1, Line 1
View or function ‘vStinfo’ has more column names specified than columns defined.

It will throw above error, after droping existing column of the table in sql server. This is because when we executed first time it generated execution plan as well as logical structure of the view. based on that view has three column where as in actual table it has only two column.

After running UPDATE STATISTICS Stinfo table than also it was throwing error.

We ran the UPDATE STATISTICS against view vStinfo than also it will throw below error.

Msg 1994, Level 16, State 2, Line 2
Cannot create or update statistics on view “dbo.vStinfo” because both FULLSCAN and NORECOMPUTE options are required.

Finally we have to alter the view for reflecting the changes has been 0n reference table.

How to modify or alter sql server view  ?

ALTER VIEW vStinfo
AS
SELECT * FROM dbo.stinfo

Leave a Reply

  

  

  


5 − = 4