| Downloads |
|---|
| 42971.zip |
Back doors are undocumented features of an application that let you do things the application wasn't intended to support. With this article, I conclude my series on T-SQL back doors by discussing three back doors to SQL Server views: rolling your own INFORMATION_SCHEMA views, creating sorted updateable views, and circumventing update limitations that views impose. I also tell you which undocumented techniques I think could be useful for T-SQL programmers if SQL Server supported them.
INFORMATION_SCHEMA Views
INFORMATION_SCHEMA views return metadata information about databases and user objects within the databases. These views display information in a standard ANSI-defined format; they contain system-table queries that return the ANSI-defined output. The views work similarly to the way that special procedures work. That is, you create them only in the master database, yet they're available from any database and return information from system tables in the context of the database you refer to them from. For example, if you select from INFORMATION_SCHEMA.TABLES from the Northwind database, you get the list of tables and views in Northwind. Unfortunately, SQL Server doesn't support the ability to create your own views with behavior similar to INFORMATION_SCHEMA views, even though such a capability would be useful. You might want to create your own views that query system tables and return metadata information that the ANSI INFORMATION_SCHEMA views don't—for example, information about indexes. However, a T-SQL back door lets you create INFORMATION_SCHEMA views.
You can create your own INFORMATION_SCHEMA views by turning on the allow updates server configuration option, then creating the view in the master database. For example, to create a view called INFORMATION_SCHEMA.USERTABLES, which returns the owners and table names of all the tables in the database (without listing the views), run the code that Listing 1 shows.
To test your new view, run the following code to query the view once in Northwind and once in Pubs:
USE Northwind SELECT * FROM INFORMATION_SCHEMA.USERTABLES USE pubs SELECT * FROM INFORMATION_SCHEMA.USERTABLES
You'll first get a list of the user table names in Northwind, then the ones in Pubs.
Sorted Views
ANSI disallows specifying an ORDER BY clause for the SELECT statement within a view definition. The reasoning behind this limitation is that a view should represent a table, and a table doesn't have a predetermined order to its rows. A SELECT query with an ORDER BY clause doesn't return a table as a result the way one without an ORDER BY clause would; it returns a cursor instead. According to ANSI rules, if you want to sort a view's rows, you need to specify an ORDER BY clause in a SELECT query that refers to the view and not try to change the view. However, I often see questions in the public SQL Server forums (such as news://msnews.microsoft.com/microsoft.public.sqlserver.programming) about how to create a sorted view. Although I describe the back doors that can circumvent this ordering limitation, I urge you to refrain from using them. I recommend that you alter any of your views whose definition uses the ORDER BY clause and that you instead specify the ORDER BY clause in the queries that access rows from the view.
One back door that lets you create a sorted view uses TOP 100 PERCENT in the SELECT clause. The trick here is that SQL Server lets you specify an ORDER BY clause within the view only when you also specify a TOP clause, because the ORDER BY clause has a logical meaning for TOP. However, specifying TOP 100 PERCENT renders the TOP clause meaningless because it returns all the rows, sorted in the requested order.
To test this back door, run the script that Listing 2 shows. The script creates the T1 table, including the keycol and data-col columns, populates it with three rows, and creates the VSortedT1TOP view, which returns T1's rows sorted by keycol when you query it. Now, run a SELECT * query against the VSortedT1TOP view. Notice in Figure 1 that you get three rows sorted by keycol.
This technique has other downsides besides not being ANSI-compliant. If you now query the view and request an ORDER BY in the query, SQL Server sorts your data twice, incurring a serious performance penalty. To see this behavior, run the code that Listing 3 shows; it turns on SHOWPLAN_TEXT, queries T1 and VSortedT1TOP (requesting a descending sort by keycol), then turns off SHOWPLAN_TEXT.
Figure 2 contains the execution plans for both queries. Notice at callout A that when you query T1 directly, the optimizer makes an ordered scan in the clustered index on keycol to return a sorted output. In fact, if you don't use an ORDER BY clause in the view but instead use it in the query that refers to the view, you'll get the same plan. In contrast, notice at callout B that the query against the VSortedT1TOP view generates a plan that first performs an ordered scan of the clustered index against T1, then re-sorts the result to specify the sort request in the query against the view. In short, SQL Server treats each ORDER BY request—the one within the view and the one outside it—separately, and you pay for an extra sort.
Another disadvantage of using this back door is that when you use TOP, your view becomes non-updateable. If you run the following UPDATE query, you'll get an error message saying the update isn't allowed:
UPDATE VSortedT1TOP SET datacol = 'bb' WHERE keycol = 2
A fellow Microsoft Certified Trainer (MCT), Zoltan Kovacs, discovered a back door that lets you create an updateable sorted view. The trick is to make sure your SELECT query has an ORDER BY clause in the OPENQUERY() function. To invoke OPENQUERY() and issue a query against your own server, you first need to turn on the data access server option:
EXEC sp_serveroption \[<your_server_name>\], 'data access', true
You then create the view by running this code:
CREATE VIEW VSortedT1OPENQUERY AS SELECT * FROM OPENQUERY(\[<your_server_name>\], N'SELECT * FROM tempdb..T1 ORDER BY keycol') AS T
If you query the view, you get a sorted output, but you can also update the view. Run the following UPDATE statement to set datacol to bb in the row for which keycol equals 2:
UPDATE VSortedT1OPENQUERY SET datacol = 'bb' WHERE keycol = 2
Your modification should run successfully. The downside of using this technique—besides it not being ANSI-compliant—is that any reference to the view invokes a distributed query, which is more costly than a local one.
VIEW_METADATA
The last back door I discuss causes your views to behave in unexpected ways when you manipulate them from graphical tools. I first describe three seemingly unrelated characteristics of a view, then correlate them all through the back door.
The first characteristic is a view's use as a security mechanism. You can grant a user permission to select, insert, update, or delete data from a view without granting access to the base table the view refers to as long as the view and the table have the same owner.
The second characteristic has to do with modifying a view that accesses several tables. Although views are supposed to look and act much like tables do, ANSI intentionally imposes some differences that SQL Server implements. If a view joins tables, you're not allowed to modify more than one base table in one UPDATE or INSERT statement you issue against the view.
The third characteristic has to do with issuing modifications through a view (not to the base table directly) that conflict with the view's query filter. When you issue such modifications, inserted or modified rows seem to disappear from the view. Suppose you created a view that selects all rows from the T1 table I used earlier, using the filter data-col LIKE 'a%'. If you insert into the view a row that has a value of d in datacol, the insertion succeeds. But when you query the view, you don't get the newly inserted row because it doesn't qualify for the view's query filter. To disallow modifications that conflict with the view's query filter, add WITH CHECK OPTION when creating the view. Run the following code to create the view VT1OnlyA, specifying the CHECK option:
CREATE VIEW VT1OnlyA AS SELECT * FROM T1 WHERE datacol LIKE 'a%' WITH CHECK OPTION
This view retrieves all rows in which the datacol value starts with a. Run the following INSERT statement to add a row that doesn't conflict with the filter:
INSERT INTO VT1OnlyA VALUES(4, 'aa')
The statement completes successfully. If you query the view, you get two rows with the keycol values 1 and 4 and the datacol values a and aa, respectively. Next, try to run INSERT and UPDATE statements that conflict with the view's query filter:
INSERT INTO VT1OnlyA VALUES (5, 'd') UPDATE VT1OnlyA SET datacol = 'e' WHERE keycol = 1
You get an error in both cases, indicating that the statements conflict with the specified CHECK option.
Now let's try the back door. If you perform the modifications through a graphical interface such as Enterprise Manager, the view behaves differently than you intended in respect to the three characteristics I specified. If you have access to the view but not to the base table, you get a security error when you try to modify data through Enterprise Manager. If you try to change several tables by referring to them at the same time through the view in Enterprise Manager, you are successful. But if you insert or modify underlying data through the view in Enterprise Manager, although the modification conflicts with the view's CHECK option, SQL Server won't reject the modification.
The reason views behave differently is that Enterprise Manager requests browse-mode metadata information from SQL Server through the client-side database interface and gets the base tables' metadata information. Enterprise Manager then constructs a query against the base tables instead of against the view, causing the problems.
You can capture the query that Enterprise Manager submits by running a SQL Server Profiler trace while modifying the view. For example, if you run a trace and modify the VT1OnlyA view through Enterprise Manager, you capture the statement that Figure 3 shows. Notice that Enterprise Manager issues an UPDATE statement against the T1 table directly instead of against the view. Fortunately, you can easily prevent such problems. You simply alter the view by using the VIEW_METADATA option:
ALTER VIEW VT1OnlyA WITH VIEW_METADATA AS SELECT * FROM T1 WHERE datacol LIKE 'a%' WITH CHECK OPTION
This option causes SQL Server to send metadata information of the view as if it were a table instead of the base tables' metadata. In this way, you close the back door.
Shut That Door
Although using back doors can be dangerous, you need to be able to recognize them when you run across them in your production databases and be able to provide alternatives. I hope that Microsoft's developers will realize that some of these SQL Server back doors could be of great value to T-SQL programmers as supported features.
