SQL Server 笔记：Standard Operation

Concatenation (||)
The || (double pipe) symbol concatenates two strings. Try this:

INPUT:
SQL> SELECT FIRSTNAME || LASTNAME ENTIRENAME
2 FROM FRIENDS;
OUTPUT:
ENTIRENAME
----------------------
AL BUNDY
AL MEZA
BUD MERRICK
JD MAST
FERRIS BULHER
ALTON PERKINS
SIR BOSS

7 rows selected.

============================================
Underscore (_)
The underscore is the single-character wildcard. Using a modified version of the table FRIENDS, type this:

SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE LIKE 'C_';
============================================
EXISTS is TRUE for those rows that have corresponding names in CUSTOMER located in NE. Otherwise, it returns FALSE.

Closely related to EXISTS are the keywords ANY, ALL, and SOME. ANY and SOME are identical in function. An optimist would say this feature provides the user with a choice. A pessimist would see this condition as one more complication. Look at this query:


============================================
Creating a Table from an Existing Table
The most common way to create a table is with the CREATE TABLE command. However, some database management systems provide an alternative method of creating tables, using the format and data of an existing table. This method is useful when you want to select the data out of a table for temporary modification. It can also be useful when you have to create a table similar to the existing table and fill it with similar data. (You won't have to reenter all this information.) The syntax for Oracle follows.

SYNTAX:
CREATE TABLE NEW_TABLE(FIELD1, FIELD2, FIELD3)
AS (SELECT FIELD1, FIELD2, FIELD3
FROM OLD_TABLE


============================================
Restrictions on Using SELECT
SQL places certain restrictions on using the SELECT statement to formulate a view. The following two rules apply when using the SELECT statement:

You cannot use the UNION operator.
You cannot use the ORDER BY clause. However, you can use the GROUP BY clause in a view to perform the same functions as the ORDER BY clause.
============================================
Problems with Modifying Data Using Views
Because what you see through a view can be some set of a group of tables, modifying the data in the underlying tables is not always as straightforward as the previous examples. Following is a list of the most common restrictions you will encounter while working with views:

You cannot use DELETE statements on multiple table views.

You cannot use the INSERT statement unless all NOT NULL columns used in the underlying table are included in the view. This restriction applies because the SQL processor does not know which values to insert into the NOT NULL columns.

If you do insert or update records through a join view, all records that are updated must belong to the same physical table.

If you use the DISTINCT clause to create a view, you cannot update or insert records within that view.

You cannot update a virtual column (a column that is the result of an expression or function).

============================================
SQL> CREATE INDEX index_name
2 ON table_name(column_name1, [column_name2], ...);

Index created.

As you have seen many times before, the syntax for CREATE INDEX can vary widely among database systems. For instance, the CREATE INDEX statement under Oracle7 looks like this:

SYNTAX:
CREATE INDEX [schema.]index
ON { [schema.]table (column [!!under!!ASC|DESC]
[, column [!!under!!ASC|DESC]] ...)
| CLUSTER [schema.]cluster }
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE integer]
[NOSORT]

The syntax for CREATE INDEX using Sybase SQL Server is as follows:

SYNTAX:
create [unique] [clustered | nonclustered]
index index_name
on [[database.]owner.]table_name (column_name
[, column_name]...)
[with {fillfactor = x, ignore_dup_key, sorted_data,
[ignore_dup_row | allow_dup_row]}]
[on segment_name]

Informix SQL implements the command like this:

SYNTAX:
CREATE [UNIQUE | DISTINCT] [CLUSTER] INDEX index_name
ON table_name (column_name [ASC | DESC],
column_name [ASC | DESC]...)

Notice that all of these implementations have several things in common, starting with the basic statement

CREATE INDEX index_name ON table_name (column_name, ...)

SQL Server and Oracle allow you to create a clustered index, which is discussed later. Oracle and Informix allow you to designate whether the column name should be sorted in ascending or descending order. We hate to sound like a broken record, but, once again, you should definitely consult your database management system's documentation when using the CREATE INDEX command.

For instance, to create an index on the ACCOUNT_ID field of the BILLS table, the CREATE INDEX statement would look like this:


============================================
Indexing Tips
Listed here are several tips to keep in mind when using indexes:

For small tables, using indexes does not result in any performance improvement.

Indexes produce the greatest improvement when the columns you have indexed on contain a wide variety of data or many NULL values.

Indexes can optimize your queries when those queries are returning a small amount of data (a good rule of thumb is less than 25 percent of the data). If you are returning more data most of the time, indexes simply add overhead.

Indexes can improve the speed of data retrieval. However, they slow data updates. Keep this in mind when doing many updates in a row with an index. For very large updates, you might consider dropping the index before you perform the update. When the update is complete, simply rebuild your index. On one particular update, we were able to save the programmers 18 hours by dropping the index and re-creating it after the data load.

Indexes take up space within your database. If you are using a database management system that enables you to manage the disk space taken up your database, factor in the size of indexes when planning your database's size.

Always index on fields that are used in joins between tables. This technique can greatly increase the speed of a join.

Most database systems do not allow you to create an index on a view. If your database system allows it, use the technique clause with the SELECT statement that builds the view to order the data within the view. (Unfortunately, many systems don't enable the ORDER BY clause with the CREATE VIEW statement either.)

Do not index on fields that are updated or modified regularly. The overhead required to constantly update the index will offset any performance gain you hope to acquire.

Do not store indexes and tables on the same physical drive. Separating these objects will eliminate drive contention and result in faster queries.

============================================
Indexes and Joins
When using complicated joins in queries, your SELECT statement can take a long time. With large tables, this amount of time can approach several seconds (as compared to the milliseconds you are used to waiting). This type of performance in a client/server environment with many users becomes extremely frustrating to the users of your application. Creating an index on fields that are frequently used in joins can optimize the performance of your query considerably. However, if too many indexes are created, they can slow down the performance of your system, rather than speed it up. We recommend that you experiment with using indexes on several large tables (on the order of thousands of records). This type of experimentation leads to a better understanding of optimizing SQL statements.

Using Clusters
Although we originally said that indexes can be used to present a view of a table that is different from the existing physical arrangement, this statement is not entirely accurate. A special type of index supported by many database systems allows the database manager or developer to cluster data. When a clustered index is used, the physical arrangement of the data within a table is modified. Using a clustered index usually results in faster data retrieval than using a traditional, nonclustered index. However, many database systems (such as Sybase SQL Server) allow only one clustered index per table. The field used to create the clustered index is usually the primary key field. Using Sybase Transact-SQL, you could create a clustered, unique index on the ACCOUNT_ID field of the BANK_ACCOUNTS table using the following syntax:

=========================
Trigger

create trigger tr_s on s
after update
as
declare @name char(10)
select @name=name from s
print @name


=========================
User WITH ENCRYPTION

create proc gets
with encryption
as
select * from s