SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

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

Note: I have updated the title based on feedback of Davide Mauri (Solid Quality Mentors). Thank you for your help.

Let’s see another reason why I do not like Views. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, we will have to modify them first. This means any query that does not need this column will start having the column’s data additionally. This will lead to added network traffic as well as it will reduce the performance of the part where the View is used. This further leads to a conclusion: it may not be good idea to alter the View in order to add an additional column if the View happens to be used at multiple places. An alternative solution would be adding the column outside the View. However, this solution can be very expensive.

In today’s limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Let us first create a View using a sample database called AdventureWorks.

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
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

Now let us compare the performance of the view with same SELECT statement used in the view.

/* Now compare the execution plan of the view and same definition of T-SQL statement */
-- Filter View with WHERE condition
SELECT *
FROM vw_ViewLimit1
WHERE SalesOrderDetailID > 111111
GO
-- Regular SELECT statement with WHERE condition
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
WHERE SalesOrderDetailID > 111111
GO

You will notice that the performance of the View and the SELECT statement is the same since the query cost of both statements is 50%.

ViewLimit 2 SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

Let us assume that we have to retrieve one more row from the table used in the View. As explained in the first paragraph, altering the View by adding a column to that View may actually lead to unnecessary data for the query using the View, but not looking for that column. The natural alternative solution to this is to use JOIN and add the column to the Views. In the case of T-SQL, we would not have to do the same since we will just go ahead and add the column to the statement.

/* Now let us try to retrieve the column which is not in View */
/* When you compare the performance you will notice View is more expensive*/
-- View with extra column
SELECT v1.*
,
th.[Quantity]
FROM vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Select statement with extra column
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
,th.[Quantity]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Clean up
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO

We can now check the performance of both queries using the execution plan.  This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

ViewLimit 2 1 SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

Click on the image to enlarge it.

Again, if you are not using the View at multiple places, I suggest that you modify it just fine because doing so will not cause you to lose any performance.

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

SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

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

Recently, I was about the limitations of views. I started to make a list and realized that there are many limitations of the views. Let us start with the first well-known limitation.

Order By clause does not work in View. I agree with all of you  who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

Here is the quick example for the same. I have used sample database AdventureWorks for the example.

USE AdventureWorks
GO
-- First Run regular query and observe
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Create view with same T-SQL Script
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
CREATE VIEW vw_ViewLimit1
AS
SELECT
*
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
/*
Above Query will throw following error
Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
The ORDER BY clause is invalid in views, inline functions,
derived tables, subqueries, and common table expressions,
unless TOP or FOR XML is also specified.
*/
-- Create view with same T-SQL Script without ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
CREATE VIEW vw_ViewLimit1
AS
SELECT
*
FROM Sales.SalesOrderDetail
GO
-- Use Order by clause outside of the views
-- Create view with same T-SQL Script without ORDER BY
SELECT *
FROM vw_ViewLimit1
ORDER BY SalesOrderDetailID DESC
GO

If you try to include ORDER BY in View, it will throw the following error

Msg 1033, Level 15, State 1, Procedure vw_ViewLimit1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

The above error itself explains how one can use ORDER BY in view. It suggests that if we use ORDER BY with TOP, we can surely use ORDER BY. If we want all the rows of the table, we can use TOP with 100 PERCENT. Let us try to modify our view with the usage of TOP 100 PERCENT and ORDER BY. This does not throw any error.

-- Create view with TOP 100 PERECENT and ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
CREATE VIEW vw_ViewLimit1
AS
SELECT TOP
100 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Select from view
SELECT *
FROM vw_ViewLimit1
GO

However, when you observe the resultset, you will notice that table is not ordered DESC, which is specified by SalesOrderDetailID column, as it should be. Let us examine the execution plan. You will not notice that there is no SORT operation at all.

ViewLimit1 SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

ViewLimit2 SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

I have heard many people talking about workaround, where they use some other number less than 100 in the TOP clause. Let us do a small test with 99.99 PERCENT and see the type of result we get.

-- Create view with TOP 99.99  PERECENT and ORDER BY
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
CREATE VIEW vw_ViewLimit1
AS
SELECT TOP
99.99 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO
-- Select from view
SELECT *
FROM vw_ViewLimit1
GO

Now let us check the result.We can clearly see that the result is Ordered by Column specified in the view.

ViewLimit3 SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

However, as we are using TOP and 99.99, there is very little chance that we may not get all the rows from the table. Let us check the count of the rows in original table and Views.

-- Match the counts
SELECT COUNT(*) ViewCount
FROM vw_ViewLimit1
GO
SELECT COUNT(*) OriginalCount
FROM Sales.SalesOrderDetail
GO

From the count, it is clear that View has not returned all the rows because of the TOP specified. If table was a small table with less than 10,000 rows, this view might have not missed any rows, but in this case, where there are lots of rows, the View has missed rows.

ViewLimit4 SQL SERVER – ORDER BY Does Not Work – Limitation of the Views Part 1

Summary: It is not advisable to use ORDER BY in Views. Use ORDER BY outside the views. In fact, the correct design will imply the same. If you use TOP along with Views, there is a good chance that View will not return all the rows of the table or will ignore ORDER BY completely.

Please share your experience over here as comments.

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

SQL SERVER – FIX : Error : Msg 1949, Level 16: Cannot create index on view. The function yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

Recently, during my training session in Hyderabad, one of the attendees wanted to know the reason of the following error that he encountered every time he tried to create a view. He informed me that he is also creating the index using WITH SCHEMABINDING option.

Msg 1949, Level 16, State 1, Line 1
Cannot create index on view . The function  yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.

I could easily find out the reason for this error. He was using non-deterministic function in his view, which was leading to this error. Once the non-deterministic function was removed, the error disappeared.

The definition of the non-deterministic function is that it may return different results each time it is called with a specific set of input values. MSDN lists many functions as non-deterministic:

@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME

Now if you are using any of the above function in your view, it will not allow you to create index on the view. You will have to remove the function before creating the view. Following is a quick example for the same:

USE TempDB
GO
-- Create view with non deterministic function GETDATE
CREATE VIEW TempView
WITH SCHEMABINDING
AS
SELECT
GETDATE() AS CurrentTime, 1 FirstCol
GO
-- Check the data from the view
SELECT *
FROM TempView
GO
-- Attempt to create the view
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO
/* Above query will throw following error
Msg 1949, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.TempView'. The function 'getdate' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results.
*/
-- Alter View and remove non deterministic function GETDATE
ALTER VIEW TempView
WITH SCHEMABINDING
AS
SELECT
1 FirstCol
GO
-- Creating following view will work successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ClusteredIndex_TempView] ON [dbo].[TempView]
(
FirstCol ASC
) ON [PRIMARY]
GO

This is not the perfect solution as one need to remove the column from view; but if you want to create index, there is no way to create the index without removing the non-deterministic function.

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

SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY

Today we will go over a very simple, but interesting subject. The following error is quite common if you use ORDER BY while creating any view:

Msg 1033, Level 15, State 1, Procedure something, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

The error also explains the solution for the same – use of TOP. I have seen developers and DBAs using TOP very causally when they have to use the ORDER BY clause. Theoretically, there is no need of ORDER BY in the view at all. All the ordering should be done outside the view and view should just have the SELECT statement in it.

It was quite common that to save this extra typing by including ordering inside of the view. At several instances developers want a complete resultset and for the same they include TOP 100 PERCENT along with ORDER BY, assuming that this will simulate the SELECT statement with ORDER BY.

In SQL Server 2008, this behavior is corrected by ignoring the ORDER BY clause when TOP 100% is used. Let us see a quick script for the same.

USE AdventureWorks
GO

-- SELECT from Table Without TOP or ORDER BY
SELECT *
FROM HumanResources.Shift
GO
-- SELECT from Table With TOP and ORDER BY DESC
SELECT TOP 100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Create View with same as above statement
IF EXISTS (SELECT * FROM sys.views
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vwShift]'))
DROP VIEW [dbo].[vwShift]
GO
CREATE VIEW vwShift
AS
SELECT TOP
100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Select From View
SELECT *
FROM vwShift
GO

Let us see the result of above script here. It is quite clear from the result that in the view, ORDER BY clause is ignored and result is returned as similar as that of a regular SELECT statement without ORDER BY.

selecttop SQL SERVER   Interesting Observation   TOP 100 PERCENT and ORDER BY

Note that SQL Server 2008 view with ORDER BY clause does not throw an error; moreover, it does not acknowledge the presence of it as well.

If you have any similar ideas as the above one, please leave a comment here.

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

SQL SERVER – Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries

Working with SQL Server has never seems to be monotonous – no matter how long one has worked with it. Quite often, I come across some excellent comments that I feel like acknowledging them as blog posts. Recently, I wrote an article on SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location, which is well received in community.

Before you read this article further, I request you to read original article. I received very interesting comments from Bob on the blog, where he explained why this is happening. Further, he talked about a similar kind of example. Let us first read his comment, and then we will execute his example and see the resultset.

Comment from Bob

pinaldave,

If you want to see what’s going on here, I think you need to shift your point of view from an implementation-centric view to an ANSI point of view. ANSI does not guarantee processing order. Figure 2 is interesting, but it will be potentially misleading if you don’t understand the ANSI rule-set SQL Server operates under in most cases. Implementation thinking can certainly be useful at times when you really need that multi-million row query to finish before the backups fire off, but in this case, it’s counterproductive to understanding what is going on.

First off, your statements all return a single row, which were properly sorted according to your ORDER BY expression; which is to say that there was really no sorting to be done because only a single row is returned. So let’s forget about the ORDER BY. Forget that the execution plan shows 2 rows being sorted — that’s implementation specific. A different vendor’s product could have an optimizer smart enough to see that only a single row can ever be returned from this query and therefore, avoid the unnecessary sort altogether.

Secondly, you are not guaranteed that the string concatenation will occur in any particular order. The ORDER BY clause only pertains to the final results from an ANSI perspective (at least, for these queries). MS SQL Server is right in line with ANSI and will not guarantee you anything different. Don’t expect to get AB in every case.

Finally, SQL works using relational sets, so the construct varchar = varchar + varchar forms a relational set that is evaluated accordingly. I have seen this type of construct used many times and it works sometimes – until it doesn’t. This is why you get only B in one example where I suspect you wanted AB. After thinking about it a bit, I built this example which I hope is just different enough to show you the same mechanism is at work here:
DECLARE @sum INT
SELECT @sum = 0
SELECT @sum = @sum + num FROM (
SELECT 1 [num] UNION SELECT 2 [num]
) [a] ORDER BY (num * num)
SELECT @sum [Is it 3]

I tried to show two things here. The first is that the ORDER BY isn’t doing what you think it’s doing with respect to the order of concatenation in your query. The order here is nonsensical. The second thing I think the example shows is that 1+2 doesn’t equal 3 with this @ = @ + col construct in all cases. It’s a bad construct to use. Remove the ORDER BY and you get 3, leave it in and you get 2. Heck, someone else could get 1 in theory. There’s a case I see all the time where this is used, but I can’t quite recall it. I’ll post it if I think of it again.

I must admit that he has made excellent point and explained the concept very well as well. Just for clarity, we will see the implementation of the example mentioned by him to understand how it works.

DECLARE @sum INT
SELECT
@sum = 0
SELECT @sum = @sum + num FROM (
SELECT 1 [num] UNION SELECT 2 [num]
) [a] ORDER BY (num * num)
SELECT @sum WithOrderBy
GO
DECLARE @sum INT
SELECT
@sum = 0
SELECT @sum = @sum + num FROM (
SELECT 1 [num] UNION SELECT 2 [num]
) [a]
SELECT @sum WithoutOrderBy
GO

Let us see the result when we run the above two queries with and without ORDER BY clause.

orderby SQL SERVER   Interesting Observation   Execution Plan and Results of Aggregate Concatenation Queries

Again, it is interesting to see how results are different from each other when there is only change of inclusion of ORDER BY clause. Bob has explained why this is happening in his comment earlier. He also explains further in another comment in the same article that.

Comment from Bob:

One more item to note is that if you built:

SELECT @Str0 = @Str0 + C1 FROM T1

into a table expression (i.e. VIEW, FUNCTION, FROM (SELECT)), you may get the results you want over and over until the one day where you call the view or function with the combination of syntax that triggers the unexpected behavoir.

pinaldave,

On my final point, I think it’s unclear/weak. I’d like to add that the most clear reason as to why you can’t expect to get AB or BA, but might in fact get just A or just B, is that the construct @=@ +col is non-deterministic and therefore, for the same range of input values, is not guaranteed to produce the same result in every instance.

Bob, Thank you very much for excellent explanation and your participation in the community.

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

SQL SERVER – Outer Join in Indexed View – Question to Readers

Today I have question for you. Just a day ago I was reading whitepaper Improving Performance with SQL Server 2008 Indexed Views. Following is question and answer I read in the white paper.

Q. Why can’t I use OUTER JOIN in an indexed view?

A. Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

Here I would like to ask you one question, do you have example for kind of OUTER JOIN where you insert data into base table, it will make a row disappear from query?

Please post your answer as comment.

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

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

Syntax
sp_helptext @objname = 'name'

sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name
Displaying the definition of a trigger or stored procedure
sp_helptext 'dbo.nameofsp'

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

SQL SERVER – Restrictions of Views – T SQL View Limitations

UPDATE: (5/15/2007) Thank you Ben Taylor for correcting errors and incorrect information from this post. He is Database Architect and writes Database Articles at http://www.sswug.org.

I have been coding as T-SQL for many years. I never have to use view ever in my career. I do not see in my near future I am using Views. I am able to achieve same database architecture goal using either using Third Normal tables, Replications or other database design work around.SQL Views have many many restrictions. There are few listed below. I love T-SQL but I do not like using Views.

The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.

sp_dboption 'ANSI_NULLS', TRUE
sp_dboption 'QUOTED_IDENTIFIER', TRUE

The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.

All the tables referenced by the view must be in the same database as the view.

All the tables referenced by the view must have the same owner as the view.

Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.

Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.

Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.

A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.
Illegal:
SELECT qty, orderid, qty
Legal:
SELECT qty, orderid, SUM(qty)

You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.

The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.

A SUM() that references a nullable expression isn’t allowed.

CONTAINS and FREETEXT aren’t allowed in the SELECT statement.

If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.

You can’t modify more than one table at a time through a view.

If your view is based on aggregate functions, you can’t use it to modify data.

If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.

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