| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
(Workplace UNIX) Introducing SQL Server |
![[-]](/dwicons/i_colpse.gif)
| Chapter 3: Managing SQL Server Data |
This chapter provides information about managing SQL Server data. It describes:
A user having the System Administrator role may grant permission to other users to create databases. In many SQL Server installations, only System Administrators can create databases, so they can control database placement and database device allocation. In these situations, System Administrators create new databases on behalf of other users and then transfer ownership to those users.
All users should consider developing scripts to automatically re-create databases, tables and other database objects, especially stored procedures. In the event of database corruption resulting from media or power failure, the scripts can rebuild these objects quickly and accurately.
SQL Server uses the model database as a template for new user-created databases in SQL Server. When a new database is created, SQL Server makes a copy of the model database.
The model database contains the required system tables for a user database. A new database is created the same size as or larger than model, but it cannot be smaller. As System Administrator, you can update model, as you would any other database, to add:
Responsibility for managing an individual database rests with the Database Owner. The Database Owner can:
The owner of a database can remove the database from SQL Server. Removing (or dropping) a database does the following:
Only the Database Owner can drop a database, and only from the master database. You cannot drop a database that is open for reading or writing by any user.
If tables in a database are referenced from other databases, you must remove these references before you can drop the database.
The create table statement has many options that you can use to define a table in a SQL Server database. When creating a table, some of the information you might specify includes:
By default, you create a new table in the currently open database. Table names must be unique for each user. However, since table names can be qualified by database name and user name, different users can create tables of the same name.
You can create a table in a database other than the current database by qualifying the table name with the name of the other database. However, you must be an authorized user of the database in which the table is being created, and you must have create table permission in that database.
A table index is similar in concept to the index of a book. It is a sorted list that points directly to the location of data, just as an index entry in a book points the reader to a particular page. The advantage of an index is that SQL Server does not have to scan an entire table to find a specific data item, just as an index in a book enables a reader to find specific information without having to scan the entire book.
In general, an index speeds up data retrieval by letting SQL Server directly access specific rows in a table. Without an index, SQL Server scans each row, a lengthy process when querying large tables.
Tables may have more than one index. SQL Server decides whether or not to use the indexes for each query submitted for that table. Whenever the data in a table changes, SQL Server automatically changes the table's indexes to reflect those modifications.
When you drop a table, SQL Server removes the specified table, its contents, and all the indexes and privileges associated with it.
You must own a table to be able to drop it. However, no one can drop a table while it is in use¾being read or written by a user or a client application.
The datatype of a column specifies what kind of data¾characters, numbers, or dates¾the column holds. For example, the integer (int) datatype is used to hold whole numbers.
SQL Server datatypes fall into the following categories:
SQL Server and Open Client support their own sets of datatypes. In most cases, there is a direct correspondence between a server datatype and an Open Client datatype. Open Client datatypes are discussed in "Datatypes".
For each column in a table, you can specify whether or not to allow the NULL value. A NULL value is not the same as zero or blank. Defining columns to allow the NULL value provides the option of leaving that column empty; data may be added at a later time. For example, a NULL entry in the price column of the titles table does not mean that the book is being given away free, but rather that the price information has not been entered for some reason.
SQL Server allows you to name and design your own datatype based on one of the system datatypes. When you define a datatype, you give it a name and associate the following attributes with it:
After defining a datatype, you can use it for any column in the database. For example, tid is a user-defined datatype in the pubs2 sample database that allows up to 6 characters and does not accept the NULL value. For columns in several tables in the pubs2 sample database, tid is used in the definition of other datatypes.
If you add user-defined datatypes in the model database, they can be used, by name, in more than one database. When added to model, user-defined datatype definitions are known to all new databases.
SQL Server automatically handles many conversions from one datatype to another. Transact-SQL functions allow you to explicitly request other conversions not handled automatically.
For example, SQL Server automatically converts char expressions to datetime for the purposes of comparison, but you must explicitly request conversion of char to int. Similarly, you must explicitly convert integer data if you want SQL Server to treat it as character data.
You cannot convert between some datatypes because of inherent incompatibilities between them.
The primary key is the column or combination of columns that uniquely identifies a row. A primary key must contain data; it cannot be NULL. Also, data in every row of a primary key column must be unique.
For example, in the pubs2 database, the title_id column is the primary key of the titles table. Each row in the table has a unique value in its title_id column that exactly identifies that row.
A foreign key is a column or combination of columns whose values exactly match the primary key, usually in another table. A foreign key need not be unique within its table.
To ensure that each row of a table is uniquely identifiable, you can add an IDENTITY column to the table when you create it.
A SQL Server IDENTITY column holds a value that is generated by SQL Server and uniquely identifies each row in that table. The IDENTITY column can be used to store sequential numbers¾such as invoice numbers, employee numbers, or record numbers¾that are guaranteed to be unique.
The first time you insert a row into a table, SQL Server assigns the IDENTITY column a value of 1. For each new row, the IDENTITY value is incremented by one.
You can change a table's structure as well as rename a table or a column within a table.
You can modify a table's structure by:
You can create a view that is derived from rows or columns (or both) of one or more tables. The tables from which a view is derived are called its base or underlying tables. A view can also be derived from other views. The definition of a view is stored in the database.
To a user, a view looks exactly like a database table. Users can be given permissions to operate on data available in a view that may be the same or different from permissions granted for that same user on the underlying tables.
Using views provides the following benefits:
When you create a view, you can leave out irrelevant data.
You can predefine complex conditions and qualifications so that users do not have to each time they perform operations on that data.
This ability is particularly important when users with different information needs and skill levels share the same database.
Through a view, users can query and modify only the data they can see. The rest of the database is neither visible nor accessible.
Data integrity refers to the correctness and completeness of data within a database. To enforce data integrity, you can constrain or restrict the data values that users can insert, delete, or update in the database.
For example, the integrity of data in the pubs2 database requires that a row in the titles table must have a publisher in the publishers table. You cannot insert books into titles that do not have a valid publisher, because doing so violates the data integrity of pubs2.
Listed below are the types of data integrity SQL Server can enforce.
Integrity Type | Description |
|---|---|
Requirement | Requires that a column contain a valid value in every row; the column does not allow NULL values. |
Check or Validity | Limits or restricts the data values inserted into a table column. |
Uniqueness | Requires that no two table rows have the same non-NULL value for a column or combination of columns. |
Referential | Requires that data inserted into a column must already have matching data in the column of another table or another column in the same table. |
A default is a value that SQL Server inserts into a column if the user does not explicitly enter a value. For example, if most of your customers live in New York, you can specify "New York" as the default value for the State field on an order form. When an order is processed, the user can simply leave the default value in the field or change it if the customer lives in another state.
In a relational database management system, every data element must contain some value¾even if that value is NULL. When you assign a default value to a column, SQL Server automatically inserts that value whenever a user does not enter information for the column.
A rule specifies what values users may or may not enter in a specific column or in any column with a given user-defined datatype. For example, you can create a rule to ensure that users enter all the digits of a product code.
You can connect a rule to a specific column, to several columns, or to a specified user-defined datatype. Whenever a user enters a value using a Transact-SQL insert or update statement, SQL Server checks the value against the most recent rule that has been bound to the specified column.
To use SQL Server Manager to enable a rule:
The pub_idrule rule in the pubs2 database is an a example of a simple rule. It constrains the values in the pub_id column to either one of four specific values or to values beginning with the digits 99 optionally followed by one or two digits each between 0 and 9.
create rule pub_idrule
as @pub_id
in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]""
To apply a rule to a particular column in a particular table, use the sp_bindrule procedure and to remove its applicability to a specific column use the sp_unbindrule procedure.
To remove a rule from a database use the drop rule command.
A trigger is a stored procedure that takes effect when you insert, delete, or update data in a specified table. Triggers help maintain consistency among logically related data in different tables.
Triggers are automatic; they work no matter what the source of the data modification, from a user's manual data entry or an application's action. A trigger is specific to one or more of the data modification operations: update, insert, or delete. The trigger is executed once per Transact-SQL statement; it fires immediately after the data modification statements are completed.
The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire transaction is cancelled.
Triggers are most useful in the following situations:
For example, a delete trigger on the title_id column of the titles table could cause a corresponding deletion of matching rows in other tables.
For example, you could create an insert trigger on titleauthor.title_id that rolls back an attempted insert if the inserted value does not match some value in titles.title_id.
For example, a trigger can roll back updates that attempt to increase a book's price by more than 1 percent of the advance.
For example, a trigger can compare the state of a table before and after a data modification, and take actions based on that comparison.
The table below lists sources of information for topics discussed in this chapter.
Topic | Source(s) of Information |
|---|---|
SQL Server Manager |
|
Data integrity |
|
Database and object names |
|
Database options |
|
Datatype conversion |
|
Indexes |
|
Modifying tables and other database objects |
|
pubs2 sample database |
|
Views |
|
|
|