Views Behaviours in MS SQL Server

I am going to explain some abnormal behaviors of the Database Views in MS SQL Server 2008 and will compare the with MYSQL Database....

As per the MSDN the definition of the view is : "A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced".
 So if we see the above definition from  the MSDN then we can say that views are not stored in the database as table. It is only the reference. But practically it have some different behavior. Lets try the following code in MS SQL Server 2008.

USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDatabase')
DROP DATABASE [TestDatabase]
GO

CREATE DATABASE [TestDatabase] 

GO

USE [TestDatabase]

GO
IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[TestView]')
      AND type in (N'U'))
DROP TABLE [dbo].[TestView]

GO

CREATE TABLE [dbo].[TestView](
 [Col1] [int] NULL,
 [Col2] [varchar](50) NULL,
 [Col3] [varchar](50) NULL,
 [Col4] [varchar](50) NULL
)

GO
INSERT INTO [TestDatabase].[dbo].[TestView]([Col1],[Col2],[Col3],[Col4])
     VALUES(1,'val1','val2','val3')

INSERT INTO [TestDatabase].[dbo].[TestView]([Col1],[Col2],[Col3],[Col4])
     VALUES(2,'val01','val02','val03')

INSERT INTO [TestDatabase].[dbo].[TestView]([Col1],[Col2],[Col3],[Col4])
     VALUES(3,'val11','val12','val13')

INSERT INTO [TestDatabase].[dbo].[TestView]([Col1],[Col2],[Col3],[Col4])
     VALUES(4,'val21','val22','val23')
GO

Now creating view on the table TestView with statement Select * from table as follows :

USE [TestDatabase]
GO
IF  EXISTS (SELECT * FROM sys.views 
WHERE object_id = OBJECT_ID(N'[dbo].[VW_TestView]'))
DROP VIEW [dbo].[VW_TestView]
GO

CREATE VIEW [dbo].[VW_TestView]
AS
SELECT * FROM dbo.TestView

GO

Now we will select the values from the view as follows:

USE TestDatabase
GO
SELECT * FROM VW_TestView
GO

So think what should be the output? Yes you are right , it will give the same as the select statement on the table. It will return all the columns and rows from the table.See below result from SQL Server 2008

Now let us change one thing, Add one more column to the table and update the value of the column with some data as follows:

USE TestDatabase
GO
ALTER TABLE TestView ADD Col5 VARCHAR(50)
GO
UPDATE TestView SET Col5='New Value'
GO

Now Again Run the query to select values from the view as we did earlier. Now think what should be the output of the query? Ideally as per the view definition, It should return all the columns and rows from table or the select statement on the view should be same as the select statement on the table.But the behavior is little bit different here. I don't know why it is so but it returns only those columns which was at the time of creation of the view here it was 4 columns. So the query will return only 4 columns with values. Now think why the fifth column we have added is not coming in the view. It should be there because we have created the view with Select * from the table statement and as per the view definition it stores the query only, So I am considering here that when we created the view with select * from table statement then it might stores the query with column names like Select col1,col2.... etc so that's why it is not selecting the fifth column.But when I queried the database for the view definition actually stored using the below query then result was amazing. It stores the query as it is i.e. select * from table .. see the below query and output

USE TestDatabase
GO
SELECT TEXT,* FROM SYSCOMMENTS 
WHERE ID IN(SELECT object_id FROM SYS.VIEWS WHERE NAME ='VW_TestView')
GO

Now , lets do the reverse of what we did i.e. remove the one column from the table and then again check for the view. Using the below query

USE [master]
GO
ALTER TABLE TestView DROP  COLUMN  Col2
GO
SELECT * FROM VW_TestView
GO

Now guess what should be the out put of the select * query on view? If you are thinking that it will not return the Col2, then you are wrong. It will return the same result as above. see the below screen shot 

 

Now it is a very big question, from where the view is getting the deleted column? And more bigger question is that,From where it is showing the data of the deleted column? Is view is getting stored anywhere physically like tables? So many more questions arises ..... you need to explore more and more ... For me it is still a question, looking for the answer. If you have find anything related to this , then please let me know so that I can clear my doubt too. Now lets discuss the other side of the view. Lets create a view by specifying the column names in the select statement and after creating it, select * from the view, using below piece of the code.

USE [master]
GO
CREATE VIEW VW1_ViewTable
AS
SELECT Col1,Col3,Col4,Col5 FROM TestView
GO
SELECT * FROM VW1_ViewTable

So its clear from this step that it will return all the columns specified in the view. Now lets do a experiment, Add one more column to the table and again run select * query on the view, So it again return only those columns which was used in creating the view. Now lets delete one column from the table which was specified in the view. So view should through error when we run select * from view query as follows.

I have also tested this on MYSQL database and found little bit difference between both databases. In MS SQL Server when we created view with select * from table query and after that added one more column to the table then view does not return that newly added column in select query,same happened with MySQL also, but the second result was shocking ... When I deleted the column from the table and perform the select query on the view then it returns error which was not in case of the MS SQL Server. See below screen print
print.