Welcome to Microsoft SQL Server

Overview of Client/Server Client/server is a style of computing where a client process requests services from a server process. In the simplest terms, a server is a program that makes available any kind of service, such as e-mail, files, ftp, Web, or data in the form of a database server. A client is an application that connects to a server to make use of the service it provides. Clients and servers have different jobs. Some examples of server responsibilities are providing backups to ensure data is safe, security against unwanted intrusion, timely access to the service, and maintenance of reliable storage facilities to ensure high availability of the service. Some examples of client responsibilities are providing a pleasing user interface, making use of the limited server resources in a responsible and resource-economical way, and, of course, fulfilling the goals of the application. Client applications include mail clients, such as Eudora and Microsoft Outlook. These applications connect to a mail server to retrieve e-mail messages. Internet Explorer is a client that connects to a Web server. SQL Server client applications that ship with the product include the Query Analyzer, SQL Server Enterprise Manager, Profiler, SQL Agent, and even Data Transformation Services. Each of these applications connects to the database engine and uses the engine's services in a different way. To use the service, each of these clients sends a query to the server. The server processes the query and sends back results.

SQL Server 2000 Components and Features SQL Server 2000 is more than just the database engine. While the database engine is at the core of the product, and obviously the most important component, there are a number of additional applications bundled with the database engine, such as the tools and utilities used to manage the SQL Server environment, as well as other components and server applications that extend the capabilities and features of SQL Server 2000. This section provides an overview of the components and features that are included with the SQL Server 2000 product. Each of these components is subsequently explored in greater detail in the rest of this book. SQL Server Database Engine SQL Server's database engine is the primary server application in the SQL Server package. Following are the main responsibilities of the database engine: Provide reliable storage for data sent to the engine Provide a means to rapidly access this data Provide consistent access to the data Control access to the data through security Enforce data-integrity rules to ensure the data is accurate and consistent Each of these points will be examined in detail later in the book. I will touch on each of these points to show how Microsoft SQL Server fulfills these core responsibilities.

SQL Server 2000 Environments

SQL Server, in addition to being available in a number of editions, also runs on a number of Windows platforms. However, certain editions will only run on certain platforms. This section takes a look at the platforms on which SQL Server runs and the editions it supports.

Windows 98 and Windows Me

Windows 98 and Windows Me are designed primarily for the home PC user. Windows 98 is still in use on the desktop in many corporate shops as well. Windows 98 and Windows Me are intended to support mobile users who are disconnected from the network but need to run applications that require SQL Server data storage, or for users who need to run local applications that require local SQL Server data storage on the computer.

For these purposes, the Windows 98 and Windows Me platforms support the SQL Server 2000 Standard Edition and the SQL Server 2000 Desktop Engine. Although the Full Text Search and Analysis Services are included with the Personal Edition of SQL Server, they cannot be installed on Windows 98 or Windows Me. Also, because Windows 98 and Windows Me do not support applications running as services, SQL Server and SQL Task Manager run as standard executables on Windows 98 and Windows Me rather than as services, as they do under Windows NT or Windows 2000.

The Windows 98 and Windows Me environments also support the installation of the SQL Server client tools, such as Query Analyzer, Enterprise Manager, and SQL Profiler. The connectivity components needed to connect to any SQL Server 2000 edition running on a Windows NT or Windows 2000 workstation or server can be installed on Windows 98, Windows Me, or Windows 95.

SQL Server 2000 Editions

You can choose from several editions of SQL Server 2000. The edition you choose will depend on your database and data processing needs, as well as the Windows platform on which you want to install it.

For actual deployment of SQL Server in a live deployed server environment, you can only choose from two editions of SQL Server: Standard Edition and Enterprise Edition. In addition, SQL Server also is available for special uses in a Developer Edition, Personal Edition, Desktop Engine Edition, or Windows CE Edition.

This chapter will examine the different editions of SQL Server and discuss their features and capabilities. Using this information, you will be better able to choose which edition provides the appropriate solution.

SQL Server 2000 Standard Edition

The Standard Edition of SQL Server 2000 is the version intended for the masses—those running small- to medium-sized systems that don't require the performance, scalability, and availability provided by Enterprise Edition. The Standard Edition runs on any of the Windows 2000 or Windows NT 4.0 Server platforms with scalability limited to up to four processors and 2GB of memory. Standard Edition includes the following features:

• Analysis Services

• Data Transformation Services

• Full Text Search

• English Query

• Built-in XML support

• SQL Profiler and performance analysis tools

• Graphical DBA and Developer tools

• Replication

• Data mining tools

SQL Server Licensing Models

In addition to feature sets, one of the determining factors in choosing a SQL Server version is cost. With SQL Server 2000, Microsoft has changed the licensing model to include a processor-based licensing model intended for Web-based environments in which the number of clients or user connections is indeterminate.

Processor licensing requires a single license for each central CPU in the machine running a Microsoft Server product. This type of license includes unlimited client device access. Additional server licenses, seat licenses, and Internet connector licenses are not required. You must purchase a processor license for each installed processor on the server on which SQL Server 2000 will be installed even if some processors will not be used for running SQL Server. The only exception is for systems with 16 or more processors that allow partitioning the processors into groups so the SQL Server software can be delegated to a subset of the processors.

For those who prefer the more familiar Server/Per-Seat Client Access License (CAL), or for those environments in which the number of client devices connecting to SQL Server is known, server or CAL-based licensing models are still available. This licensing model requires purchasing a license for the computer running SQL Server 2000, as well as a license for each client device that accesses any SQL Server 2000 installation. A fixed number of CALs are included with a server license and the server software. Additional CALs can be purchased as needed.

Server/Per Seat CAL licensing is ideal for those environments in which the number of clients per server is relatively low, and access from outside the firewall is not required. Be aware that using a middle tier or transaction server that pools or multiplexes database connections does not reduce the number of CALs required. A CAL is still required for each distinct client workstation that connects through the middle tier. (Processor licensing might be preferable in these environments due to its simplicity and affordability when the number of clients is unknown and potentially large.)

SQL Server Tools and Utilities

BCP

BCP (bulk copy program) is a tool that is used to address the bulk movement of data. The utility is bidirectional, allowing for the movement of data in and out of a SQL Server database.

The SQL Server 2000 version of the Bulk copy program (BCP) utilizes the ODBC bulk copy API instead of the DB-LIB API that was used in earlier versions of SQL Server. The ODBC bulk copy API was used to support new data types that the DB-Library API does not support. Backward compatibility options are provided with the SQL Server 2000 BCP utility to allow bulk copy of data types supported in earlier versions.

BCP uses the following syntax:

BCP {[[database_name.][owner].]{table_name| view_name} | "query"}{
   in | out | queryout | format} data_file
[-F first_row] [-L last_row]
[-t]
[-E]

ISQL/OSQL

The ISQL and OSQL command-line utilities can be used to execute SQL statements, stored procedures, and script files from the command prompt. Commands that can be entered interactively via Query Analyzer can be placed in batch files and executed via ISQL/OSQL. In addition, the output from these files can be routed to a file that allows daily reports and tasks of this nature to be run unattended.

The ISQL utility uses DB-LIB to communicate with SQL Server, whereas OSQL uses ODBC. Because ISQL is based on the DB-LIB interface, it does not support Unicode data. DB-Library has not been enhanced to support any of the new features in SQL Server 7.0 or 2000 and is only maintained for backward compatibility. Microsoft recommends that you use OSQL instead of ISQL for any command-line procedures.

The command-line parameters for ISQL and OSQL are similar with only a few differences. The command-line parameters for both utilities allow you to specify the query or file you want to execute, the target server and database, as well as the location for the resulting output.

ODBCcmpt ODBCcmpt allows you to enable or disable the 6.5 or 7.0 ODBC compatibility option, using the following syntax: ODBCcmpt filename The filename is the name of an application's executable file for which you want to set the compatibility level. This option permits the SQL Server ODBC driver (Version 3.7) to be compatible with previous drivers. You might need to enable this option when running an application that SQL Server 2000 should treat as a 6.5-level or 7.0-level application. This allows backward compatibility for applications using either the ODBC 2.x or ODBC 3.x API. The ODBCCMPT.EXE file is located, by default, with the SQL Server tools in Microsoft SQL Server\80\Tools\Binn\.

odbcping The odbcping utility is a good diagnostic tool that allows you to test a client machine's ODBC connectivity to your database server. It uses this syntax: odbcping { -Sserver_name | -Ddata_source } Unlike makepipe and readpipe, odbcping runs only on the client. You can use odbcping whenever you need to verify that a client's ODBC connectivity is properly configured. You can also incorporate the use of odbcping as part of an installation procedure. You can run odbcping in two ways. You can execute odbcping-Sserver_name to test a client's direct connectivity to SQL Server. You can also run odbcping-Ddata_source to use the specified data source to connect to SQL Server. In either case, if successful, odbcping displays the version of SQL Server and the version of the SQL Server ODBC driver. The following example shows a simple odbcping execution (using the SQL Server name) and the resulting output: >odbcping –Swin2000svr\instance1 CONNECTED TO SQL SERVER ODBC SQL Server Driver Version: 03.80.0380 SQL Server Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 4.0 (Build 1381: Service Pack 6) Query Analyzer Query Analyzer is a fairly straightforward and simple tool to use. Since the user interface is user-friendly and easy to navigate, the purpose of this section of the chapter is not to provide a basic tutorial on using Query Analyzer, but to help you get more out of Query Analyzer by pointing out some of its more useful features that you might not be aware of. Establishing Connections When Query Analyzer first starts up, you are presented with a connection dialog as shown Within this dialog, you can choose to connect using Windows authentication or using a SQL Server specific login ID and password. The SQL Server: field provides a drop-down list of the servers you've most recently connected to, or you can simply type in the name of the SQL Server. If you click on the button to the right of the server name box, it will display a list of SQL Servers that it detects are currently running on your network. If you have the appropriate administrative privileges, a check box is available that will allow you to automatically start the specified SQL Server if it is not running. The SQL Debugger One of the most significant features in Query Analyzer is the SQL Debugger. With the various commands and programming constructs in Transact-SQL (T-SQL), stored procedures can perform some pretty complex operations. You might have as much programming logic built into stored procedures as in some of your other application routines written in other programming languages. As you develop and test your application code written in Visual C++ or Visual Basic (or whatever programming language you might be using), it is likely that you will use a debugger to step through the code and verify that it is working correctly or to identify where the logic is breaking. When your stored procedures are not executing correctly or they are returning the wrong result, you need to debug them as well. In the old days, the only way to debug stored procedures and step through them line by line was to rewrite them as SQL batches and execute the statements individually or in small groups. The problem with this approach was that the contents of local variables didn't carry over between batches, so you had to redeclare and reinitialize the variables for each batch and set the values to what they were at the end of the previous batch. This process was tedious, and the SQL statements would optimize differently when passed a variable rather than what would have been a stored procedure parameter. For more information on how queries are optimized, see Chapter 35, "Understanding Query Optimization." The only other way to track what a stored procedure was doing was to litter it with print statements to display the status of the procedure and the contents of local variables and parameters at various points. I remember using this approach one time to debug what looked like a simple problem. I had a stored procedure that needed to delete a parent record, and then all related child records. Everything worked just fine by using local variables and separating each SQL statement. My print/debug statements didn't shed any light on the matter, either. After spending about half a day trying to track it down, I finally identified the problem—I was missing a parameter to the stored procedure that was being called within the main procedure, shifting the parameter values over one and causing the wrong value to be used to find the matching rows. (I could have avoided this by passing the parameters by name rather than position. Live and learn!) If only I had had a debugger available to debug the stored procedure, I could have identified the problem in a minute instead of a day. The First T-SQL Debugger Stored procedure debugging was available for SQL Server prior to SQL Server 2000, but it wasn't part of the SQL Server installation. Since the release of Visual Studio 6, Visual Interdev has included a T-SQL Debugger. Prior to this, it was available inside Visual Basic Enterprise 5.0 and Visual C++ Enterprise 4.2. However, getting the debugger up and running was an arduous process. You had to have all the following pieces in place: You had to have the Enterprise Edition of Visual Studio installed. You had to be running at least SQL Server 6.5 with Service Pack 2 or later. The server running SQL Server had to be running under Microsoft Windows NT 4.0 or later. The client workstation had to be running Windows 95 or Windows NT 4.0 or later. In addition, for SQL Server versions prior to SQL Server 2000, the debugging components were not installed on the server by default. To get the debugger to work, these pieces had to be installed manually. This caused a lot of headaches: Programmers who wanted to use stored procedure debugging had to bother the administrator, who might or might not have seen the need to install these pieces. It's a rare administrator who will upset the delicate balance of his smoothly running SQL Server to indulge the whim of a developer, unless some sort of bribe is involved. You also needed to ensure Distributed COM (DCOM) was installed properly on the server and the client. You had to make sure the DCOM configuration on a Windows 98/Me machine supported debugging. The main problem with using this debugger was that you had to install Visual Interdev. This was a pretty large, involved install if all you wanted to do was use a SQL Debugger. Also, the debugger was not part of a standard query tool like Query Analyzer, nor was it a standalone tool. It had to be brought up via a Data View window in Visual Studio. People needed a T-SQL Debugger that could be installed along with the SQL Server Client Tools that was integrated with Query Analyzer. That finally came along in SQL Server 2000. Using the T-SQL Debugger in Query Analyzer Query Analyzer that ships with SQL Server 2000 now includes an integrated T-SQL Debugger. This is a full-featured debugger that lets you step through stored procedure code a line at a time, set breakpoints, view the contents of local and global variables, and see the results of the T-SQL statements as they execute.

SQL Server Administration

What Needs to Be Maintained

The core tasks related to the maintenance of a SQL Server database are backing up the database and log (including shipping the logs to a standby server), rebuilding indexes, updating statistics, and running integrity checks against the database. These are the ongoing repetitive tasks, which are best run on a scheduled basis and are the backbone of the maintenance plan. Other tasks related to maintenance involve managing access by the users, maintaining data files, and monitoring performance. These tasks are more apt to be performed on an ad hoc basis when the need arises.

Selecting Databases

The first screen allows you to select which databases to back up. The option buttons allow you to select all databases, all system databases, or all user databases, or to build a custom selection of databases. New for SQL Server 2000 is the check box to ship the logs to another server. If this is selected, you will be presented with a new set of screens in the log section of the wizard where you can choose a network share from which to ship the logs, and a remote server and database to act as the standby database. Log shipping is used to keep a standby server up to date. This check box is disabled if the database is in simple recovery mode

SQL Agent Overview

The SQL Server Agent is responsible for automation tasks in SQL Server. The Agent, which runs as a Windows service, is responsible for running scheduled tasks, notifying operators of events, and responding with a predefined action to errors and performance conditions. SQL Server Agent performs these functions using alerts, jobs, and operators. Alerts respond to SQL Server or user-defined errors, and can also respond to performance conditions. An alert can be configured to run a job as well as notify an operator. A job is a predefined operation or set of operations, such as transferring data or backing up a transaction log. A job can be scheduled to run on a regular basis, or called to run when an alert is fired. An operator is a user to notify when an alert fires or a job requests notification. The operator can be notified by e-mail, pager, or the NET SEND command.

Jobs

Jobs are the cornerstone of SQL Server automation. Managed from the Jobs folder of the SQL Server Agent folder, jobs can be single or multistep operations, involving any combination of T-SQL, operating system commands, ActiveX scripts, or replication tasks.

Defining Job Properties

Right-clicking the Jobs folder and selecting New Job will bring up the Job Property dialog box. On the General tab, enter a name for the job and make sure the Enabled check box is selected. You can then select a category for the job. Categories are used to organize similar jobs; after you select a category, clicking the Ellipse button (…) beside it will show other jobs in the same category. Next is the Owner drop-down box. By default, this is the ID of the SQL Login creating the job, but if you are the administrator, you can assign the job to someone else. A Description field is also available and, if multiserver jobs are enabled, you can select on which servers the job will run. Multiserver jobs are covered in a following section of this chapter.

Operators

Operators are the accounts that will receive notification when an event occurs, such as the completion of a job or the firing of an alert. Operators are configured from the Operators folder under SQL Server Agent in Enterprise Manager. To create a new operator, right-click the Operators folder and select New Operator. To manage an existing operator, double-click the operator in the Details pane. This will bring up the Properties dialog box for the operator

Setting Up an E-Mail Client/Profile

Before you can use SQL Mail or SQLAgentMail, a MAPI-enabled e-mail client must be installed on the same server as SQL Server 2000. Although this can be any MAPI-enabled mail provider, it is recommended that you install Outlook 2000. Next, you must have an e-mail account on an available mail server (either a Microsoft Exchange or Internet mail server) that is linked to the domain user account under which SQL Server is running.

The following are the steps to follow for setting up an e-mail profile to support SQL Mail or SQLAgentMail:

1. Install a MAPI-compliant mail client that can connect to your mail host. Outlook 2000 is recommended because it can connect to either Exchange servers or Internet Mail providers.

2. Make sure that the SQL Server and SQL Agent services are configured to run under a user account that has access to the e-mail system rather than the default local System Account. This account, whether a domain user ID or local user ID, will need to have administrative rights on the SQL Server machine. For more information on setting up SQL Server and SQL Agent to run under a user account,
   

3. Make sure an e-mail account is set up on your mail server for the SQL Server user account. Details on how to set up these accounts for Exchange Server, Internet Mail servers, and Lotus Notes are covered later in this section.

4. Log into the server on which SQL Server is running using the user account specified as the startup account for SQL Server and SQL Agent.

5. After you are logged in, open the Mail Control Panel and click on the Add button to create a mail profile for the SQL Server user account. Choose a profile name that will be meaningful to the recipients of the e-mail messages. Configure an e-mail account for the mail profile that SQL Mail and SQLAgentMail will use for sending e-mail. This can be an account for an Exchange Server, a POP3 server, an IMAP server, an HTTP server, or another installed mail provider such as Lotus Notes. If any of your mail servers require a password for sending e-mail, be sure to save the password for the e-mail account in the mail profile when you set up the account as SQL Mail and SQLAgentMail. Do not prompt for a password when sending e-mail from SQL Server.

Transact-SQL

T-SQL and ANSI/ISO SQL

The ANSI SQL standard has evolved over the years and provides standards for commands for data definition, data access, and data manipulation. Each new iteration of the ANSI SQL standard incorporates more features and capabilities than the previous standards. The most recent version of the ANSI SQL standard is SQL-99. ANSI SQL-92 was the last major ANSI SQL standard released. Three levels of compliance are defined for the ANSI SQL standards: entry level, intermediate, and full.

SQL Server 2000 is entry-level compliant with the ANSI SQL-92 standard. What this means is that SQL Server provides all of the core features defined in the ANSI SQL-92 standard to achieve any level of compliance. In addition, SQL Server already provides some features defined in the ANSI-99 standard, such as user-defined roles.

Like any vendor that develops a product that conforms to a standard, Microsoft incorporates additional features into SQL Server to extend the capabilities of the SQL language. Examples of such features include the following:

• Operating-system–dependent statements, such as defining physical database storage.

• Legacy syntax and commands—Even if the current version supports the ANSI way of expressing a command, backward compatibility is still necessary.

• Extensions to the ANSI standards—All vendors strive to implement competitive features. Microsoft is no exception.

Some of the extensions that Microsoft provides in T-SQL to provide it with a competitive advantage include the following:

• Server-resident programs such as user-defined functions, stored procedures, and triggers

• Control of flow statements

• Additional datatypes, including user-defined datatypes

• Various types of built-in integrity, such as rules, defaults, and triggers

• Additional built-in functions

SELECT, INSERT, UPDATE, and DELETE Four basic SQL statements allow you to retrieve and modify data in tables. SELECT retrieves data from one or more tables, INSERT inserts rows into one table, UPDATE modifies rows in one table, and DELETE removes rows from one table.

The SELECT Statement

The SELECT statement has the following basic syntax:

SELECT [DISTINCT] [TOP N [PERCENT]] column1 [AS column_heading]
      [, column2 [AS column_heading], ...]
[INTO new_table_name]
FROM table1 [ [AS] table_alias ]
[ [INNER | { LEFT | RIGHT | FULL} [OUTER] ] JOIN table2 [ [AS] table_alias2 ]
      on ( join_conditions )] [...]
[WHERE search_conditions]
[GROUP BY aggregate_free_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
[ compute row_aggregate(column_name) [, ... ]
     [ by column_name [ , column_name ] ... ] ]

To return all the Utah authors' first and last names from the authors table and rename the column heading in your result, you would execute the following:

SELECT au_lname AS 'First', au_fname AS 'Last'
  FROM authors
  WHERE STATE = 'UT'

By default, SELECT returns all the rows that meet the search conditions. If you specify SELECT DISTINCT, any duplicate rows will be removed. Be careful not to use DISTINCT unnecessarily because it can slow query performance having to put the resultset into a work table to remove duplicates before returning the final resultset.

The WHERE Clause

The columns that you base your search condition on do not have to be returned in the resultset. You can filter rows in several ways with the WHERE clause. The following expressions are available for the WHERE clause:

Operators: =, <> (not equals), <, >, >=, and >=.

BETWEEN expression1 AND expression2. Between is inclusive.

IN(element1, element2, …). Returns all rows whose values are equal to the elements specified in the list.

LIKE string_expression. Used for pattern matching. Table 26.2 lists the available wildcard characters.

Table 26.2. Wildcards and LIKE

Wildcard	Meaning
%	Any number of characters
_	Any single character
[]	Any character listed in the bracket

Logical OR and AND are used to connect multiple search arguments. AND takes precedence over OR, so use parentheses to provide the proper logical grouping. For example, you might want to write a query to return all the business books for which the price is less than $10 or ytd_sales greater than $10,000. If you don't use parentheses, notice what happens when you run the following query:

SELECT substring(title, 1, 30) as title, type, price, ytd_sales
  from titles
  where type = 'business'
    and price < $10 or ytd_sales > 10000
go

title                             type          price     ytd_sales
--------------------------------- ------------- --------- ---------
You Can Combat Computer Stress    business      2.9900    18722
The Gourmet Microwave             mod_cook      2.9900    22246
Fifty Years in Buckingham Pala    trad_cook     11.9500   15096

You didn't get what you wanted. You only got business books with a price less than $20 and all books with ytd_sales greater than $10,000. Using parentheses, you get the result you want:

SELECT substring(title, 1, 30) as title, type, price, ytd_sales
  from titles
  where type = 'business'
    and (price < $10 or ytd_sales > 10000)
go

title                           type        price    ytd_sales
------------------------------- ----------- -------- ----------
You Can Combat Computer Stress  business    2.9900   18722

The ORDER BY Clause

Definition of Views

A view is simply a query stored as an object in the database. Essentially, a view behaves like a virtual table. Therefore, a view, with a few exceptions, can be referenced in Transact-SQL statements in the same manner as actual tables. A view can be defined to return a subset of data—namely selected columns and/or rows from a table—or a superset of data, in which two or more tables are combined using a JOIN or UNION operator to look like a single table. Views are also often used for returning summary values.

Because views are implemented as stored SELECT statements, they have virtually no overhead because they don't actually store data. This is a key point in understanding views. It is a common misconception that views have storage requirements that are commensurate with the data they return; this causes some database developers to avoid views. With the exception of indexed views, the only real overhead in a view is creating it and then applying the appropriate user permissions.

There are many uses for views, the most common of which include:

• Simplifying data retrieval for complex queries

• Hiding the underlying table structures

• Controlling access to data at the row or column level

SELECT Statement Defining the information_schema.table_privileges View

select
       user_name(p.grantor)  as GRANTOR
      ,user_name(p.uid)             as GRANTEE
      ,db_name()                            as TABLE_CATALOG
      ,user_name(o.uid)             as TABLE_SCHEMA
      ,o.name                                     as TABLE_NAME
      ,case p.action
              when 26  then 'REFERENCES'
              when 193 then 'SELECT'
              when 195 then 'INSERT'
              when 196 then 'DELETE'
              when 197 then 'UPDATE'
       end                                         as PRIVILEGE_TYPE
      ,case
              when p.protecttype = 205 then 'NO'
              else 'YES'
       end                                        as IS_GRANTABLE
from
       sysprotects p,
       sysobjects o
where
       (is_member(user_name(p.uid)) = 1
       or
              p.grantor = user_id())
  and (p.protecttype = 204 or  /*grant exists without same grant with grant */
  (p.protecttype = 205
              and not exists(select * from sysprotects p2
                             where p2.id = p.id and
                             p2.uid = p.uid and
                             p2.action = p.action and
                             p2.columns = p.columns and
                             p2.grantor = p.grantor and
                             p2.protecttype = 204)))
      and p.action in (26,193,195,196,197)
      and p.id = o.id
      and o.xtype in ('U', 'V')
      and 0 != (permissions(o.id) &
              case p.action
         when 26  then  4               /*REFERENCES basebit on all columns */
         when 193 then  1               /*SELECT basebit on all columns     */
         when 195 then  8               /*INSERT basebit */
         when 196 then  16              /*DELETE basebit */
         when 197 then  2               /*UPDATE basebit on all columns     */
              end)

Views and Query Performance Views typically do not add any additional overhead to query performance. A SELECT statement against a view is just as fast as the underlying query used to define the view. When a view is combined with other search arguments or join conditions in a query, the query optimizer combines the source of the view and the SQL statement into a single execution plan. Since it doesn't generate separate query plans for the view and the SQL statement, the view result doesn't have to be materialized first before it can resolve the rest of the query.