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
Prev. page
[1]
2
next page 
