SQL SERVER – CTE can be Updated

Today I have received a fantastic email from Matthew Spieth. SQL Server expert from Ohio. He recently had a great conversation with his colleagues in the office and wanted to make sure that everybody who reads this blog knows about this little feature which is commonly confused.

Here is his statement and we will start our story with Matthew’s own statement: “Users often confuse CTE with Temp Table but technically they both are different, CTE are like Views and they can be updated just like views.

Very true statement from Matthew. I totally agree with what he is saying. Just like him, I have enough, time came across a situation when developers think CTE is like temp table. When you update temp table, it remains in the scope of the temp table and it does not propagate it to the table based on which temp table is built. However, this is not the case when it is about CTE, when you update CTE, it updates underlying table just like view does.

Here is the working example of the same built by Matthew to illustrate this behavior.

Check the value in the base table first.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

Now let us build CTE with the same data.

;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')

Now let us update CTE with following code.

-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';

Now let us check the BASE table based on which the CTE was built.

-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

That’s it! You can update CTE and it will update the base table.

Here is the script which you should execute all together.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';
-- Build CTE
;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')
-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

If you are aware of such scenario, do let me know and I will post this on my blog with due credit to you.

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

About these ads

SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer

Earlier this week, I was surfing various SQL forums to see what kind of help developer need in the SQL Server world. One of the question indeed caught my attention. I am here regenerating complete question as well scenario to illustrate the point in a precise manner. Additionally, I have added added second part of the question to give completeness.

Question:

I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer.

Here is my query script: (I use AdventureWorks as a sample database)

SELECT Person.Address.AddressID
FROM Person.Address INNER JOIN
Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID
ORDER BY Person.Address.AddressID

This script automatically replaces by following query:

SELECT TOP (100) PERCENT Person.Address.AddressID
FROM Person.Address INNER JOIN
Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID
ORDER BY Person.Address.AddressID

However, when I try to do the same from New Query Window it works totally fine. However, when I attempt to create a view of the same query it gives following error.

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

It is pretty clear to me now that the script which I have written seems to need TOP (100) PERCENT, so Query . Why do I need it? Is there any work around to this issue.

I particularly find this question pretty interesting as it really touches the fundamentals of the T-SQL query writing. Please note that the query which is automatically changed is not in New Query Editor but opened from SSMS using following way.

Database >> Views >> Right Click >> New View (see the image below)

Answer:

The answer to the above question can be very long but I will keep it simple and to the point. There are three things to discuss in above script 1) Reason for Error 2) Reason for Auto generates TOP (100) PERCENT and 3) Potential solutions to the above error. Let us quickly see them in detail.

1) Reason for Error

The reason for error is already given in the error. ORDER BY is invalid in the views and a few other objects. One has to use TOP or other keywords along with it. The way semantics of the query works where optimizer only follows(honors) the ORDER BY in the same scope or the same SELECT/UPDATE/DELETE statement. There is a possibility that one can order after the scope of the view again the efforts spend to order view will be wasted. The final resultset of the query always follows the final ORDER BY or outer query’s order and due to the same reason optimizer follows the final order of the query and not of the views (as view will be used in another query for further processing e.g. in SELECT statement). Due to same reason ORDER BY is now allowed in the view. For further accuracy and clear guidance I suggest you read this blog post by Query Optimizer Team. They have explained it very clear manner the same subject.

2) Reason for Auto Generated TOP (100) PERCENT

One of the most popular workaround to above error is to use TOP (100) PERCENT in the view. Now TOP (100) PERCENT allows user to use ORDER BY in the query and allows user to overcome above error which we discussed. This gives the impression to the user that they have resolved the error and successfully able to use ORDER BY in the View. Well, this is incorrect as well. The way this works is when TOP (100) PERCENT is used the result is not guaranteed as well it is ignored in our the query where the view is used. Here is the blog post on this subject: Interesting Observation – TOP 100 PERCENT and ORDER BY. Now when you create a new view in the SSMS and build a query with ORDER BY to avoid the error automatically it adds the TOP 100 PERCENT. Here is the connect item for the same issue. I am sure there will be more connect items as well but I could not find them.

3) Potential Solutions

If you are reading this post from the beginning in that case, it is clear by now that ORDER BY should not be used in the View as it does not serve any purpose unless there is a specific need of it. If you are going to use TOP 100 PERCENT with ORDER BY there is absolutely no need of using ORDER BY rather avoid using it all together. Here is another blog post of mine which describes the same subject ORDER BY Does Not Work – Limitation of the Views Part 1. It is valid to use ORDER BY in a view if there is a clear business need of using TOP with any other percentage lower than 100 (for example TOP 10 PERCENT or TOP 50 PERCENT etc). In most of the cases ORDER BY is not needed in the view and it should be used in the most outer query for present result in desired order. User can remove TOP 100 PERCENT and ORDER BY from the view before using the view in any query or procedure. In the most outer query there should be ORDER BY as per the business need.

I think this sums up the concept in a few words. This is a very long topic and not easy to illustrate in one single blog post. I welcome your comments and suggestions.

Click to Download Scripts

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

SQL SERVER – Get File Statistics Using fn_virtualfilestats

Quite often when I am staring at my SSMS I wonder what is going on under the hood in my SQL Server. I often want to know which database is very busy and which database is bit slow because of IO issue. Sometime, I think at the file level as well. I want to know which MDF or NDF is busiest and doing most of the work. Following query gets the same results very quickly.

SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
vfs.NumberReads, vfs.NumberWrites,
(
Size*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId
GO

When you run above query you will get many valuable information like what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes. Due to IO if there has been any stall (delay) in read or write, you can know that as well.

I keep this handy but have not shared on blog earlier.

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

SQL SERVER – Index Created on View not Used Often – Observation of the View – Part 2

Earlier, I have written an article about SQL SERVER – Index Created on View not Used Often – Observation of the View. I received an email from one of the readers, asking if there would no problems when we create the Index on the base table.

Well, we need to discuss this situation in two different cases.

Before proceeding to the discussion, I strongly suggest you read my earlier articles. To avoid the duplication, I am not going to repeat the code and explanation over here.

In all the earlier cases, I have explained in detail how Index created on the View is not utilized.

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

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

SQL SERVER – Indexed View always Use Index on Table

As per earlier blog posts, so far we have done the following:

  • Create a Table
  • Create a View
  • Create Index On View
  • Write SELECT with ORDER BY on View

However, the blog reader who emailed me suggests the extension of the said logic, which is as follows:

  • Create a Table
  • Create a View
  • Create Index On View
  • Write SELECT with ORDER BY on View
  • Create Index on the Base Table
  • Write SELECT with ORDER BY on View

After doing the last two steps, the question is “Will the query on the View utilize the Index on the View, or will it still use the Index of the base table?

Let us first run the Create example.

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
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
ORDER BY ID2
GO
-- Create Index on Original Table
-- On Column ID1
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- On Column ID2
CREATE UNIQUE NONCLUSTERED INDEX [IX_OriginalTable_ID2] ON mySampleTable
(
ID2
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
ORDER BY ID2
GO

Now let us see the execution plans for both of the SELECT statement.

Before Index on Base Table (with Index on View):

After Index on Base Table (with Index on View):

Looking at both executions, it is very clear that with or without, the View is using Indexes.

Alright, I have written 11 disadvantages of the Views. Now I have written one case where the View is using Indexes. Anybody who says that I am being harsh on Views can say now that I found one place where Index on View can be helpful.

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

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

I always enjoy writing about concepts on Views. Views are frequently used concepts, and so it’s not surprising that I have seen so many misconceptions about this subject. To clear such misconceptions, I have previously written the article SQL SERVER – The Limitations of the Views – Eleven and more….

I also wrote a follow up article wherein I demonstrated that without even creating index on the basic table, the query on the View will not use the View. You can read about this demonstration over here: SQL SERVER – Index Created on View not Used Often – Limitation of the View 12. I promised in that post that I would also write an article where I would demonstrate the condition where the Index will be used. I got many responses suggesting that I can do that with using NOEXPAND; I agree. I have already written about this in my original summary article.

Here is a way for you to see how Index created on View can be utilized.

We will do the following steps on this exercise:

  • Create a Table
  • Create a View
  • Create Index On View
  • Write SELECT with ORDER BY on 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
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
ORDER BY ID2
GO

When we check the execution plan for this , we find it clearly that the Index created on the View is utilized. ORDER BY clause uses the Index created on the View.

I hope this makes the puzzle simpler on how the Index is used on the View. Again, I strongly recommend reading my earlier series about the limitations of the Views found here: SQL SERVER – The Limitations of the Views – Eleven and more….

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

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

I have previously written on the subject SQL SERVER – The Limitations of the Views – Eleven and more…. This was indeed a very popular series and I had received lots of feedback on that topic. Today we are going to discuss something very interesting as well.

During my recent performance tuning seminar in Hyderabad, I presented on the subject of Views. During the seminar, one of the attendees asked a question:

We create a table and create a View on the top of it. On the same view, if we create Index, when querying View, will that index be used?

The answer is NOT Always!

(There is only one specific condition when it will be used. We will write about that later in the next post).

Let us see the test case for the same. In our script we will do following:

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
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

Let us check the execution plan for the last SELECT statement.

You can see from the execution plan. That even though we are querying View and the View has index, it is not really using that index.

In the next post, we will see the significance of this View and where it can be helpful.

Meanwhile, I encourage you to read my View series: SQL SERVER – The Limitations of the Views – Eleven and more….

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

SQLAuthority News – Last Day to Participate in my Questions at SQL Quiz

My very good friend, Jacob Sebastian, is running a month-long SQL Quiz Series where the best-of-the-best experts from around the globe would be the quiz masters. They will ask one question every day, and users are expected to answer them correctly. The winning prizes include cool gadgets like iPAD, Kindle and many more.

I am one of the quiz masters, and my question is published here: The View, The Table and The Clustered Index Confusion. I have asked there three questions.

Q1. Does the table use an index created on itself?

Q2. Does the view use an index created on itself?

Q3. Do both queries use the same index? If yes, why? If no, why not?

Today is the last day to participate in the SQL Quiz.

I have already written the answer as a hint over here: SQL SERVER – Indexed View always Use Index on Index

Additionally, if above problem interest you, I suggest you read my article series on limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

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