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.