SQL server-focus on using views several restrictions/recommendations, view query performance problems, are you crazy? (25)

Objective

In the previous section we briefly described the 4 types of table expressions, which we talked about using the view restrictions, short content, in-depth understanding, always to review the basics.

Avoid using order by in views

In the previous section we also talked about the 3 requirements that must be met by using table expressions, where there is a problem with an order by that cannot be guaranteed, and we will focus on the limitations in the view. In a regular query we do this for the sort.

* FROMSales.salesorderdetailorder by Salesorderdetailid DESC

Next we sort the data in the view, and we create the view to see

* from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]'* fromsales.salesorderdetailORDER by Salesorderdetailid DESCGO 

At this point we will find the following error when we execute the CREATE view

In this case, you cannot use order by in the view-we create the view and use order by in the external view

* from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]'** fromview_limitorder by Salesorderdetailid DESC

Let's take a look at the error that occurs when you make an order by in the view, which indicates that you can use top, offset, and so on, and then we use top to see what the actual results are.

* from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]' PERCENT * fromsales.salesorderdetailORDER by Salesorderdetailid DESCGO

Let's look at the view again to see the result set returned.

* FROM Dbo.view_limit

When we created the view, we used the order by to sort the result set in descending order, the data returned was not in descending order at all, and we saw that the query plan did not appear at all. Let's look at another situation. Set the returned data to a little less than 100% to try.

IF EXISTS (SELECT * from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]'99.9 PERCENT * fromSales.salesorderdetailorder by Salesorderdetailid Descgo

At this point, the descending sort is done, which means that the use of top and offset in the view works? We did not make any restrictions on the above query, we looked up the total number of data in the table and how much data was returned when using the view query.

Use adventureworks2012goselect count (*) as Originalcountfrom sales.salesorderdetailselect count (* ) as Viewcountfrom dbo.view_limit

Although in these cases we restrict the returned data to be sorted in descending order, which is relative to the small table, if the data volume in the table is larger, then the order by in the view will default many values, so it is recommended not to order in the view By instead of an order by outside the view. Okay, here's the first limitation we're talking about, and we're giving a conclusion.

(1) Avoid the use of order by in the view, when the table data compared to the hour, although through top or offset to solve the problem, but when the amount of data is relatively large when the use of order by in the view of the interior will result in more data rows missing, we recommend an order by outside the view.

Avoid using SELECT * in the View

First we look at the problem by creating a view.

* from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]'* fromHumanresources.shiftgo

Next we look at the data returned by looking at the original table and the view

Use adventureworks2012go--* fromhumanresources.[ Shift]go--* fromView_limitgo

Well, it's okay, next we'll add extra columns to the table.

Use Adventureworks2012goalter TABLE HumanResources. [Shift] ADD Additionalcol Intgo

Let's do the above query again to see the result set returned

At this point we find that the view is not displayed after adding extra columns, and of course the data is not displayed. At this point we're looking at the refresh before using the view query

Use adventureworks2012go--* fromhumanresources.[ Shift]go' view_limit'

--* fromView_limitgo

The correct result can be returned at this time. So what causes extra columns to be added through the view query can have unexpected results, because the view is compiled on the column is enumerated, and the new table column is not automatically added to the view, that is, if we add additional columns, the column is not added to the view at this time, so we can sp_ Refreshview or sp_refreshsqlmodule the way to refresh the view's metadata. So we have the following conclusions

(2) Avoid using SELECT * in the view, adding extra columns to the table will cause the view to not be automatically added, although we can refresh the view by Sp_refreshview or sp_refreshmodule, but to avoid confusion, It is best to explicitly list the name of the required column in the view definition, and if additional columns are added and we need additional columns in the view, we can modify the view definition by Alter VIEW.

View query returns extra columns resulting in inefficient query performance through the join table

Let's take a demonstration directly from the example below.

IF EXISTS (SELECT * from sys.views WHERE object_id = object_id (N'[dbo].[ View_limit]'= th. Referenceorderidgo

We're doing regular SQL queries and view queries.

*111111=111111GO

The above uses the general query and the view query overhead sample, but now we have such a scenario the above view is written by other colleagues, but when we use to return additional columns, so in order not to return other redundant data and the colleague tear force, we need to again in the view outside the join to get our extra columns, Let's take a look below.

Use adventureworks2012goselect v1. *,th.[ Quantity]=111111goselect [salesorderid],[salesorderdetailid],[ Carriertrackingnumber], [Orderqty],sod. [Productid],[specialofferid],[unitprice],[unitpricediscount], [Linetotal],[referenceorderid],th.[ Quantity]=111111GO

At this point the extra quantity column is returned to the view to join again, and we look at the query plan overhead

At this point it is more expensive to take advantage of the view query, but the regular query simply adds one more column without any change. Let's keep looking down.

An invalid index is created on the view by default

We've been talking about the establishment of indexes, and the indexes are all built on the table, so what happens when we build the index on the view, is the query efficiency improved? We first create the test table and insert the data

*= object_id (N'[dbo].[ Viewtable]') and TYPE in (N'U')) DROP TABLE [dbo]. [Viewtable] Gocreate TABLE viewtable (ID1 int, ID2 int, somedata VARCHAR)
100000 row_number () over (order by O1.name), Row_number () over (order by O2.name), O2.namefrom sys.all_objects O1cross JOIN s Ys.all_objects O2go

Above we created the Test View chart viewtable and inserted 100,000 test data, next we index the table.

Use adventureworks2012gocreate UNIQUE CLUSTERED INDEX [idx_original_table] on dbo. Viewtable (ID1 ASC)

Next we'll create the view and create an index on the view

Use Adventureworks2012gocreate VIEW viewlimit withSCHEMABINDINGasselect id1,id2,somedatafrom dbo. Viewtablegocreate UNIQUE CLUSTERED INDEX [idx_view_table] on [dbo]. [Viewlimit] (ID2 ASC) GO

We need to note that when you create an index on a view you must specify with schamabinding, otherwise the index on the view is not allowed to be created. We finally look at the query plan through regular queries and view queries

Use Adventureworks2012goselect id1,id2,somedatafrom dbo. Viewtablegoselect id1,id2,somedatafrom dbo. Viewlimitgo

At this point we find that the index used by the view query is not the index idx_view_table we created, primarily because the view and table are associated, so the query plan determines that the index on the table is more efficient than the index created on the view. When we force the specified noexpand in with, the index created on the view will be executed, because the view is not related to the original table, it is independent, as follows:

Use Adventureworks2012goselect id1,id2,somedatafrom dbo. Viewtablegoselect id1,id2,somedatafrom dbo. Viewlimit with(NOEXPAND)GO

Create an index on a view the problem is more complex, we do not discuss, generally through the general query can solve the problem why bother to view it. We need to take a look at this, please.

Summarize

In this section we talked about some of the limitations of using views and suggestions, and we'll discuss the other limitations of using views, short content, deep understanding, and so on.

SQL server-focus on using views several restrictions/recommendations, view query performance problems, are you crazy? (25)