Creating and Altering Database Objects - Chapter 6



    

  1. Creating Database Objects
There are two ways to create and maintain SQL Server Objects:
. Writing Transact-SQL (T-SQL) statements
. Using the graphical interface (SQL-EM)

It is easier and faster to create and maintain objects with
T-SQL statements.
The SQL-EM can't do everything (limitation)

SQL Server supports a variety of database objects that enable you
to better utilize, access, and care for your data.

SQL Server object types:
. Tables, to store SQL Server data
. Temporary tables, to store temporary result sets
. Views, to provide a logical depiction of data from one or more tables
. Rules, to validate column data
. Defaults, to provide a column value when none is provided by the 
  client application
. Constraints, to validate column data, and to maintain consistency
  between tables.


  2. Creating a database

sp_helpdb test
* to check for the existence of test database
* if the database exists, try different name
* You may use SQL-EM to check for existing databases.

create database test on default = 2

This creates a two-megabyte database named test on a device that was 
set up for default use.


  3. Tables

create table table_name
      (column name datatype {identity|null|not null}
       [,...]
      )

create table demographics
      (user_id numeric (10,0) identity,
       last_name varchar (30) not null,
       first_name varchar (30) not null,
       comments varchar (255) null)


* In SQL-EM, open database, objects, tables, new table

To remove a table and its structure, use the drop command.

drop table table_name

drop table demographics

*WARNING: There is no UNDROP. Once it's gone, it's gone.

* In SQL-EM, select the table, drop (from the menu)

to get information about objects, use sp_help (stored procedure)

sp_help 

sp_help demographics

Tables are composed of up to 250 user-defined columns, each of which
has three characteristics: a name, a datatype, and a property.


  4. SQL Server Object Names

All SQL Server object names are up to 30 characters in length, and are
case-sensitive.

Keywords are case-insensitive


  5. Column Properties
Columns can have the following properties:
. Null (a value does not need to be specified for a column)
. Not null (a value must be specified for the column)
. Identity (the server is to maintain a row counter on the table)

create table table_name
      (column_name {int | smallint | tinyint | numeric (p,0)} identity [,...])

/* this table has first, last names and an automatic key */
create table names2
      (auto_key int identity,
       first varchar (30) not null,
       last varchar (30) not null
      )

create table names3
      (auto_key tinyint identity,
       first varchar (30) not null,
       last varchar (30) not null
      )

create table names4
      (auto_key int identity (-10000,1),
       first varchar (30) not null,
       last varchar (30) not null
      )

In this example, the identity column will start with the value 
-10,000 and incremented by 1.

If an upper or lower limit is reached for a datatype, inserts can
no longer be processed on that table. SQL Server does not automatically
reuse values that have been skipped or deleted, nor does the counter 
"cycle" back to the beginning to hunt for available values. In that way,
your identity fields are limited by the datatype you select.


  6. Views
Views are a logical way of looking at the physical data located in the 
tables. In fact, to a select statement, a view looks exactly like a table.

create view view_name [ (col_name, ...)]
as select statement
[with check option]

/* make view with two columns from authors table */
create view author_name as
       select last = au_lname, first = au_fname
       from authors

/* retrieve all rows and columns from view */
select * 
from author_name

last                  first
--------------------------------------------

To remove a view, use drop view statement.

drop view view_name

drop view author_name.

*Dropping the view has no effect on the data.


  7. Examining Views in SQL-EM
SQL-EM offers a Views folder similar to the Tables folder
for a particular database.


  8. Views as Security-Vertical (certain columns)
You can use a view to limit access to selected columns in a base table.

When you use a view for security, grant the user permission to select
from the view, but not from the base table.


  9. Views as Security-Horizontal (certain rows)

/* create view with only California publishers */
create view cal_publishers as
       select *
       from publishers
       where state = "CA"

/* retrieve all rows and columns from the view */
select *
from cal_publishers


  10. Views to Ease SQL 
Views can be used to simplify queries. They are useful for
hiding complex joins from end users.

/* create a view to handle a three-way join */
create view titles_and_authors 
       as  select title, au_lname, au_fname, type
           from titles t, ta_limited ta, authors a
           where t.title_id = ta.title_id
           and a.au_id = ta.au_id

/* retrieve all rows and columns from the view */
select *
      from titles_and_authors
      where type = "business"


  11. Views can contain the following:
. Aggregate functions and grouping
. Joins
. Other views (up to 16 levels of nesting)
. A distinct clause
. Union


  12. Views cannot include the following:
. select into
. A compute clause
. An order by clause

* You can use an order by statement when selecting from the view.


  13. Data Modifications and Views
SQL Server allows you to insert, update, and delete from views, 
with some restrictions.

insert adds rows to one base table, 
update and delete affect rows in one base table.

/* delete rows from a view..
** corresponding rows in the table are deleted */

delete author_name
       where last = "Smith" and first = "Joseph"

/* change the name of a publisher */

update cal_publishers
   set pub_name = "Joe's Books and Magazines"
   where pub_id = "1389"

When a view includes columns from more than one table, you can't 
do the following:

. Delete rows from the view (this would effect multiple base tables)
. Update columns  from more than one table in a single update statement.

Inserts are not allowed into views unless all underlying columns in the 
base table not included in the view either are defined to allow null
values or have a default defined on the columns.

Inserts are allowed on views containing joins as long as all columns
being inserted into the view belong to a single base table.

Finally, you can't update, delete, or insert into a view containing
the distinct clause.


  14. Views with check option
In previous releases of SQL Server, it was possible for users to insert
or update a row, creating a row they could not retrieve with select:

insert into cal_publishers
       (pub_id, pub_name, city, state)
values
       ("1234","Joe's Books", "Canton","OH")

/* update creates an "invisible" row */
update cal_publishers
set state = "OH"
/* would make ALL rows "invisible" */

/* create view with only California Publishers */
create view cal_publishers_ck as
       select *
       from publishers
       where state = "CA"
with check option


update cal_publishers_ck
set state = "OH"

-->>> will give an error message.


  15. Getting View Information

sp_help lists all objects in a database, including views and tables.
to list the views only, run the following command

select name 
from sysobjects
where type = "V"


  16. Renaming Objects

sp_rename old_name, new_name

After a table has been created, columns cannot be removed, datatypes
cannot be changed, and null status cannot be changed. However,
columns can be renamed by using sp_rename:

sp_rename 'authors.au_lname','last_name'


  17. Adding Columns to a table

/* add middle name and fax columns to name table */
alter table names2 add
middle_name varchar (20) null,
fax varchar(15) null

New columns must either be identity columns or allow null values.


  18. Rules
Rules provide a mechanism for enforcing domain constraints for columns 
or user-defined datatypes. The rules are applied before an insert or 
update command, prior to the execution of the command.


  19. Creating Rules
create rule rule_name as
       @variable operator expression
       [{and|or}...]

/* orders must fall into a range */
create rule order_quantity as
       @quantity between 100 and 150000

/* specify a list of valid colors */
create rule color_rule as
       @color in ('black', 'brown','red')

If a rule isn't bound to any columns or user-defined datatypes,
you can drop it with drop rule.

/* remove rule from database */
drop rule key_rule


  20. Rule Usage
A rule is a separate and distinct database object. In order for a 
rule to take effect, you must bind the rule to a column in a table 
by using sp_bindrule:

sp_bindrule rule_name, 'table.column_name'

/* bind key_rule to user_id column in demographics table */
sp_bindrule key_rule, 'demographics.user_id'

You can instruct the server to stop applying a rule (unbind a rule)
by using sp_unbindrule.

/* unbind rule from user_id in demographics table */
sp_unbindrule 'demographics.user_id'


  21. Defaults
Defaults provide a value for a column when one is not supplied at
insert time. Like rules, they exist only as database objects; 
after creation, they must subsequently be bound to columns.

create default default_name
       as constant_expression

/* default country value id "USA" */
create default country_default as 'USA'

drop default default_name
 

  22. Default Usage
You bind a default to columns in a table or tables by using sp_bindefault.

/* apply the default to country is demographics */
sp_bindefault country_default, 'demographics'

To unbind a default, use sp_unbindefault 

/* remove default from country in demographics */
sp_unbindefault  'demographics.country'


  23. Declarative Defaults
SQL Server also permits the declaration of a default value for
a column during table creation.

create table items
     (item_id char (6) not null,
      price money default $12.95 not null)

Use the alter table statement to add or drop a default clause from a table:
/* To remove the default defined on price */
alter table items
     replace price default null


  24. Indexes
The primary purpose of an index is to provide faster access to data pages
than scanning every page. Secondarily, it is sometimes used as a mechanism
for enforcing uniqueness.


  25. Index Types
SQL Server provides two types of indexes: Clusterred and nonclustered.
Both are B-Tree indexes. Only one clustered index per table can exist.
249 nonclustered indexes per table, because nonclustered indexes maintain
pointers to rows (not data pages=blocks).

An index can contain from 1 to 16 columns, but the total index entry width 
must be no greater than 255 bytes.


  26. Clustered Index
With a clustered index, there is one entry on the last intermediate 
index-level page for each data page. This means that the data page is the leaf.


  27. Nonclustered Index
The nonclustered index has an extra leaf index level for page/row pointers.


  28. Clustered Versus Nonclustered
A clustered index tends to be one I/O faster than a nonclustered index for
a single-row lookup, because they tend to be fewer index levels. Clustered indexes are excellent
for retrieving ranges of data, because the server can narrow down a range of 
data, retrieve the first row, and scan the data without returning to the index
for more information.

Nonclustered indexes are a bit slower and take up much more disk space, but
are the next best alternative to a table scan.

When creating a clustered index, you need free space in your database 
approximately equal to 120 percent of the table size.


  29. Creating Indexes
Create [unique][clustered|nonclustered]index
       index-name on table_name (column [,...])

Create unique clustered index name_index
      on authors (au_lname, au_fname)

create index fname_index
      on authors (au_fname, au_lname)

* By default, an index is nonunique and nonclustered.


  30. Notes on Indexes
. Only one index can be defined on an ordrered set of columns in a table.

. When an index is defined as unique, no two rows can have the same index value.
  The uinqueness check is performed at index creation, on insert, and on update.
 
. Having a clustered index on all tables is a good idea; otherwise, space is not
  reused from deleted rows. If the table doesn't have a clustered index, all
  new rows (and updated rows) are placed at the end of the table.

. As the number of indexes increases (past five or so) the overhead at update 
  to maintain the indexes gets excessive.


  31. Managing Indexes with SQL Enterprise Manager

. SQL EM adds many important index management features into its interface.

. Creating new indexes by using EM can be more efficient than using SQL syntax,
  because you can view the size and relative efficiency of that index before
  it is created.
 

  32. Constraints
Constraints provide an alternative to rules and defaults. SQL Server enforces
three general types of constraints:

. Primary and unique key constraints
. Check constraints
. Referential integrity constraints


  33. Primary-Key and Unique Constraints
SQL Server permits the declaration of primary key or a unique constraint 
at table definition time.

/* create names table: promary key on ssn (table level) */
create table names
     (ssn varchar (9) not null,
      name varchar (20) not null,
      constraint names_pk primary key (ssn))

create table names
     (ssn varchar (9) not null
          constraint ssn_pk primary key nonclustered,
      name varchar (20) not null)


  34. Foreign-Key Constraints

create table salesdetail
  (stor_id (6),
   ord_num char (10),
   title_id tid
       constraint tid_fk_constraint
       references titles (title_id),
   qty int,
   discount real,
   constraint sales_fk-constraint
        foreign key (stor_id, ord_num)
        references sales (stor_id, ord_num)
   )

Last update January 2, 1998.