SQLAuthority News – Monthly Roundup of SQLAuthority Blog Posts

Since I started the monthly round up of the blog post, I have received many positive feedback. I plan to continue doing this month refresher every month now. This rounds ups are my mirror and informs me what I have been doing whole month. Here is quick look at the last month.

The month started very interesting with my daughter’s birthday SQLAuthority News – Fathers and Daughters. As this was very first birthday it was very special for me. I had great time enjoying with her quality time and it was all fun. I am an MVP and I am one proud one. I think MVP demonstrates community dedication and extreme passionate for community, I explained my understanding in the article SQLAuthority News – What is an MVP? – How to become an MVP?.

There was very interesting conversation on the subject of Soft-Delete SQL SERVER – Soft Delete – IsDelete Column – Your Opinion and really think community participated very well on the subject. I am also involved in training very unique fast track data ware house course and I have made few notes over here SQL SERVER – Few Notes on Fast Track Data Warehouse.

Not everything was as great as I expected during the whole month, I was really abused by Indian Airlines Spicejet I finally wrote about their harassment over SQLAuthority News – Spicejet Complaint – Update – No Outcome.If you want to read complete story, you can read my earlier article on the same subject SQLAuthority News – Spicejet Complain – Do Not Fly with Spicejet.

All DBA face common problems when their log file grow too big, I tried to answer the question in article SQL SERVER – How to Stop Growing Log File Too Big.

How can I not write on my favorite subject of indexing in any month.

I have also written series of limitation of the view. Here is the complete series.

I would like to request your feedback on the limitation of the view blog post series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

When I wrote the article about SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2, I had received a comment that said:

“If joining column is expensive to the view, why can’t I create a view over the view and create an index on it?”

The answer is simple: It’s actually another limitation of the View.

You cannot create an Index on a nested View situation. The following example where we can demonstrate the issue is attached below. In this example, there is already one view, and another view is created on the top of the view. When attempting to create an index on the outer view, it would not be allowed and would throw an error.

Let us see the example:

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit2]'))
DROP VIEW [dbo].[vw_ViewLimit2]
GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
WITH SCHEMABINDING
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
GO
-- Another View created on the view using the same view cread earlier
CREATE VIEW vw_ViewLimit2
WITH SCHEMABINDING
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],v1.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],v1.[ReferenceOrderID]
,th.[Quantity]
FROM dbo.vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Following statement will fail as view contains another view
CREATE UNIQUE CLUSTERED INDEX [IX_vw_ViewLimit1] ON [dbo].[vw_ViewLimit2]
(
[ProductID] ASC
)
GO
/*
Msg 1937, Level 16, State 1, Line 1
Cannot create index on view 'AdventureWorks.dbo.vw_ViewLimit2' because it references another view 'dbo.vw_ViewLimit1'. Consider expanding referenced view's definition by hand in indexed view definition.
*/

So here it is — one more limitation. The reason for this is that another view over a view is difficult to maintain. The workaround is very simple as explained in the error messages itself. Instead of creating nesting View, just bring over the code of the inner view to the outer view. After this, it will work just fine, letting you create an index (considering the earlier 10 limitations). How many of you know this issue?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

I have recently wrote many articles on the limitation of the views. In this article, I have tried to sum up all the keywords which are not allowed in the indexed view. If any of the following keyword is used in the View, Index is not possible to create on the same.

I think this list can be used as quick reference for anybody who wants to create view and index it to get best out of the views.

  • ANY, NOT ANY
  • Arithmetic on imprecise (float, real) values
  • COMPUTE, COMPUTE BY
  • Contradictions SQL Server can detect that mean the view would be empty (for example, where 0=1 and …)
  • CONVERT producing an imprecise result
  • COUNT(*)
  • Derived tables (subquery in FROM list)
  • DISTINCT
  • EXISTS, NOT EXISTS
  • Expressions on aggregate results (for example, SUM(x)+SUM(x))
  • Full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  • GROUP BY ALL
  • Imprecise constants (for example, 2.34e5)
  • Inline or table-valued functions
  • MIN, MAX
  • Nondeterministic expressions
  • Non-Unicode collations
  • OPENROWSET, OPENQUERY, OPENDATASOURCE
  • OPENXML
  • ORDER BY
  • OUTER join
  • References to a base table with a disabled clustered index
  • References to a table or function in a different database
  • References to another view
  • ROWSET functions
  • Self-joins
  • STDEV, STDEVP, VAR, VARP, AVG
  • Subqueries
  • SUM on nullable expressions
  • Table hints (for example, NOLOCK)
  • text, ntext, image, filestream, or xml columns
  • TOP
  • UNION

After looking at the long list which contains Self Join, TOP, UNION, OUTER JOIN and many other useful keywords, one has to wonder how limited the usage of the query is in the view. One can not utilize the full potentials of the views.

Views have many limitations and the list is limitless!

Reference : Pinal Dave (http://blog.SQLAuthority.com), MSDN Article

SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
Previously, I wrote an article about SQL SERVER – The Self Join – Inner Join and Outer Join, and that blog post seems very popular because of its interesting points. It is quite common to think that Self Join is also only Inner Join, but the reality is that it can be anything. The concept of Self Join is very useful that we use it quite often in our coding. However, this is not allowed in the Index View. I will be using the same example  that I have created earlier for the said article.

Let us first create the same table for an employee. One of the columns in this table contains the ID of the manger, who is an employee of that company, at the same time. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need to use Self Join.

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

selfjoini1 SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9

We will now utilize Inner Join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

selfjoini2 SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9

Now let us try to create View on the table. This will allow well construction of the View without any issues associated with it.

-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create a Clustered Index on the View.

-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO

Unfortunately, the above attempt will not allow you to create the Clustered Index, as evidenced by an error message. It will throw following error suggesting that SELF JOIN is now allowed in the table.

Msg 1947, Level 16, State 1, Line 2
Cannot create index on view “tempdb.dbo.myJoinView”. The view contains a self join on “tempdb.dbo.Employee”.

The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.

If any of you has a better explanation of this subject, please post it here through your comments, and I will publish it with due credit.

The complete script for the example is given below:

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT
e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO
/*
Msg 1947, Level 16, State 1, Line 2
Cannot create index on view "tempdb.dbo.myJoinView". The view contains a self join on "tempdb.dbo.Employee".
*/
-- Clean up
DROP VIEW myJoinView
DROP TABLE Employee
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

This blog post was previously published over here. I am republishing it in the series Limitation of the Views with a few modifications.

While reading the white paper Improving Performance with SQL Server 2008 Indexed Views, I noticed that it says outer joins are NOT allowed in the indexed views. Here, I have created an example to demonstrate why this is so.

Rows can logically disappear from an Indexed View based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement. In addition, the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

The reader was confused with my answer and wanted me to explain it further. Here is the example that I have quickly put together to demonstrate the behavior described in the above statement:

USE tempdb
GO
-- Create Two Tables
CREATE TABLE BaseTable (ID1 INT, Col1 VARCHAR(100))
CREATE TABLE JoinedTable (ID2 INT, Col2 VARCHAR(100))
GO
-- Insert Values in Tables
INSERT INTO BaseTable (ID1,Col1)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO
INSERT INTO JoinedTable (ID2,Col2)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
GO
-- Use Outer Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO

The script above will give us the following output:

outerjoinmiss1 SQL SERVER   Outer Join Not Allowed in Indexed Views   Limitation of the View 8

-- Now Insert Rows in Base Table
INSERT INTO BaseTable (ID1,Col1)
SELECT 3,'Third'
GO
-- You will notice that one row less retrieved from Join
SELECT jt.*
FROM BaseTable bt
RIGHT OUTER JOIN JoinedTable jt ON bt.ID1 = jt.ID2
WHERE bt.ID1 IS NULL
GO
-- Clean up
DROP TABLE BaseTable
DROP TABLE JoinedTable
GO

outerjoinmiss2 SQL SERVER   Outer Join Not Allowed in Indexed Views   Limitation of the View 8

After running this script, you will notice that as the base table gains one row, the result loses one row. Going back to the white paper I mentioned earlier, I believe this is an expensive way to manage the same issue as to why it is not allowed in Indexed View.

Additionally, SQL Server Expert Ramdas provided excellent explanations regarding NULL and why resultset maintenance is expensive, over here.

“A disadvantage of outer joins in SQL is that they generate nulls in the result set. Those nulls are indistinguishable from other nulls that are not generated by the outer join operation. There is no “standard” semantics for nulls in SQL but in many common situations, the appearance of nulls in outer joins doesn’t really correspond to the way nulls are returned and used in other places. Therefore, the presence of nulls in outer joins creates a certain amount of ambiguity.”

This series is indeed getting very interesting. What are your suggestions?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

One of the requirements of Indexed View is that it has to be created ‘WITH SCHEMABINDING’. If the View is not created with that clause, it would not let you create an index on that View. Moreover, if you try to create a View with schemabinding, it would not allow you to create the database.

-- Create DB
USE MASTER
GO
CREATE DATABASE TEST1
CREATE DATABASE TEST2
GO
-- Table1
USE Test1
GO
CREATE TABLE TABLE1 (ID INT)
GO
USE Test2
GO
-- Table2
CREATE TABLE TABLE2 (ID INT)
GO
USE Test1
GO
-- Create View
CREATE VIEW CrossDBView
WITH SCHEMABINDING
AS
SELECT
t1.ID AS t1id, t2.ID AS t2id
FROM Test1.dbo.Table1 t1
INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.ID
GO
/*
Error:
Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view 'CrossDBView' because name 'Test1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
*/
-- Clean up
DROP VIEW CrossDBView
GO
USE MASTER
GO
DROP DATABASE TEST1
DROP DATABASE TEST2
GO

When you try to create the View, it would throw the following error:

Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view ‘CrossDBView’ because name ‘Test1.dbo.Table1’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

After looking at all these limitations, have you thought of other constraints that View has? If you know any other interesting limitations, please share them here through your comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

If you want to create an Indexed View, you ought to know that UNION Operation is now allowed in Indexed View. It is quite surprising at times when the UNION operation looks very innocent and seems that it cannot be used in the View.

Before an in-depth understanding this subject, let me show you a script where UNION is now allowed in Indexed View:

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000
UNION
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID2 < 1000
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.SampleView' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000 OR ID2 < 1000
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO

During the script, the following ERROR would occur if you try to create the index while UNION operation is in the view:

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘tempdb.dbo.SampleView’ because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.

In contrast to this converting the UNION to OR operation would give the same result, plus it would allow you to create an index on the View. Well, our example is one in which we are able to re-write the script with OR clause. However, keep in mind that there can be cases where it is not possible to re-write and you might end up not using Views with Index.

In this series, I have been writing about many limitations and their explanation. Now here are my questions for you:

  • What do you think is the reason behind these limitations?
  • Why UNION is not allowed in the View?

I will publish your answer with due credit on the blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
COUNT(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
COUNT_BIG(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO

Here is a screenshot of an error that occurred when Views used COUNT(*) and there was an attempt to create an Index on it.

limitview 5 SQL SERVER   COUNT(*) Not Allowed but COUNT BIG(*) Allowed   Limitation of the View 5

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this. Itzik Ben-Gan explained me the reason for this situation. He has also clarified the reasons in his book series, ‘Inside T-SQL‘. Here is a concise summary of the explanation. This response from Itzik is produced unaltered and with his permissions:

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

I hope it is clear now. If you want to learn more about this, you can continue reading his book Inside T-SQL.

Let me know what you think of these limitations, as well as your opinions about the example.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

This is very well known limitation of the View.

Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

To test this, we will create a view where we will use SELECT * and select everything from the table. Once the view is created, we will add a column to the view. We will test that even though we have used SELECT *, the view does not retrieve the newly added column. Once we refresh the view using SP_REFRESHVIEW, it will start retrieving the newly added column.

Run the following T-SQL script in SQL Server Management Studio New Query Window:

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[LimitView4]'))
DROP VIEW [dbo].[LimitView4]
GO
-- Create View
CREATE VIEW LimitView4
AS
SELECT
*
FROM HumanResources.Shift
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Add Column to original Table
ALTER TABLE HumanResources.Shift
ADD AdditionalCol INT
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Refresh the view
EXEC sp_refreshview 'LimitView4'
GO
-- Select from original table
SELECT *
FROM HumanResources.Shift
GO
-- Select from View
SELECT *
FROM LimitView4
GO
-- Clean up
ALTER TABLE HumanResources.Shift
DROP COLUMN AdditionalCol
GO

Above query will return following resultset.

LimitView 4 SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4

The same limitation exits in the case of deleting the column as well. This is a very well-known issue with the Views. The resolutions of these issues are as follows:

  1. Refresh the views using sp_refreshview stored procedure
  2. Do not use SELECT * but use SELECT columnnames
  3. Create view with SCHEMABINDING; this way, the underlying table will not get modified.

I am enjoying writing this series of blog posts on the limitation of the views. Do let me know if there is any other limitation you would like to read on this blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

I have heard many people saying that if they create view and index on it, this will reduce the load on original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.

In the following example, we will create the base table first. We will then create the view on top of it and create the index on the view. Subsequently, we will run the simple SELECT statement on base table and on the view. It is noticed that view does not pick up the index created on the view, but uses the base index created on the original table. It will also expand the underlying table and use the index created on base table. If you assume that after creating the index on view, the view is materialized and has no relation with original table, then this can very well be incorrect.

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- Select from table
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
GO

Now let us observe the execution plan and the usage of index along with query.

Querying Original Table

viewlimit 3 1 SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

Querying View

viewlimit 3 2 SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

You might have noticed that the View used the same index as original table and did not use the index created on the table. The reason is as follows: the optimizer has decided that the original index will be more beneficial in query plan than the newly created index. I tried clearing the cache, but the same result is obtained every time.

If you really want your view to act as an independent table and have no relation with original underlying table, then you can use the hint [noexpand] and the view will not expand. We will see this in some other article in future.

Let me know if you want to read about any other limitation of the view.

Reference: Pinal Dave (http://blog.sqlauthority.com)