III. TRANSACT SQL .

  • Data types
  • List system objects  and attributes
  • Creating a table
  • Altering a table
  • Table creation – Alternatives
  • Indexes
  • Clustered vs. non-clustered indexes
  • Creating constraints
  • Defaults
  • Table partitioning
  • Object permissions
  • Binding Rules
  • Modifying a Column
  • Moving an object
  • Views
  • Stored Procedures
  • Triggers

Datatypes:–

Note:–Table with contents to be drawn……..

.

.

.

.

.

.

.

.

List System Objects & Attributes:–

  • List all table names for current database

select name
from sysobjects where type = ‘U’
go
OR
use system SP sp_tables

  • List all trigger names for current database

select name
from sysobjects where type = ‘T’
go

  • List all procedure names for current database

select name
from sysobjects where type = ‘P’
go

  • Display column definitions and indexes for emp table

sp_help emp
go

  • Display space used for emp table

sp_spaceused emp
go

  • Display source code for emp_proc

sp_helptext emp_proc
go

Creating a table:–

  • Defines each column in the table.
  • The table definition provides the column name, datatype and specifies how each column handles null values.
  • Specifies which column, if any, has the IDENTITY property.
  • Defines column-level integrity constraints and table-level integrity constraints. Each table definition can have multiple constraints per column and per table.

Design of Table for creating a table :–

  • Decide what columns you need in the table, the datatype, length, precision, and scale, for each column.
  • Create any new user-defined datatypes before you define the table where they are to be used.
  • Decide which column, if any, should be the IDENTITY column.
  • Decide which columns should and should not accept null values.
  • Decide what integrity constraints or column defaults, if any, you need to add to the columns in the table. This also includes rules, indexes, and triggers to enforce data integrity.
  • Decide whether you need defaults and rules, and if so, where and what kind. Consider the relationship between the NULL and NOT NULL status of a column and defaults and rules.
  • Decide what kind of indexes you need and where. Indexes are discussed later

Examples of creating a table:–

1.)
create table emp (
emp_id    numeric(8,0)  identity,
emp_name    varchar(50)   not null,
salary    money         not null,
dept_cd   char(3)       not null,
fax_no    integer       null
)
go

2.)
create table invoice (
invoice_id     numeric(8,0)  identity,
sales_rep_id   numeric(8,0)  not null,
date           smalldatetime not null,
comment        varchar(255)  null )
on data_seg2
go

3.)
create table  err_cd (
err_id       integer       not null,
err_desc     varchar(60)   not null,
constraint pk_err_cd primary key clustered (err_id)
)
go

Altering a table:–

Alter table examples
- alter table employee add cell_no numeric(10) null
go
- alter table employee drop constraint     ‘emp_dept_constr’
go
Default
alter table charge_item replace price_overridable_ind default 0
go

Change column name (quotes required)
sp_rename ‘emp.dept’,dept_name
go

Table Creation – Alternatives:—-

If you already have a table and would like to create another with the same column definitions, use ‘select into’
select * into my_copy from source_table

To capture a subset of the columns, replace * with names
select col1, col2 into my_copy from source_table

The above also copy the data to the new table

To skip the data copy, use an ‘empty where clause’
select * into my_copy
from source_table
where 1=0

Default rules are not copied to the new table
Indexes are not created on the new table
The database option
“select into/bulkcopy/pllsort” must be on in this database to use ‘select into’

Indexes:—

  • create unique clustered index emp_idx

on emp (emp_id)
go

  • create index emp_name_idx

on emp (emp_name)
go

  • create unique clustered index pk_invoice_data on invoice_data  with  sorted_data on segment1

go /* Creates sorted index on a segment */

  • Clustered
  • Non-clustered

Typically,
Clustered index will be created on the primary key of a table

Non-clustered indexes are used where needed.

Clustered Vs Non-clustered indexes:—

Clustered indexes:

  • Records in table are sorted physically by key values
  • Only one clustered index per table
  • Higher overhead on inserts, if re-org on table is required
  • Best for queries requesting a range of records
  • Index must exist on same segment as table

Non-clustered indexes:

  • Leaves are stored in b-tree
  • Lower overhead on inserts, vs. clustered
  • Best for single key queries
  • Last page of index can become a ‘hot spot’
  • Clustered indexes are sorted physically only upon creation if a table is created with “lock datapages” or “lock datarows” . The clustered index, thereafter behave as a non-clustered index

Creating a constraint:—

Constraints are used to define primary keys, enforce uniqueness, and to describe foreign key relationships. By default, indexes are created upon creation of unique or primary key constraints.

  • Primary key for the employee table
    alter table emp add constraint
    emp_constr primary key(emp_id)
    go
  • Add unique requirement for invoice table
    alter table invoice add constraint
    inv_constr unique nonclustered(cust_id,inv_date)
    go
  • Add foreign key for relationship between invoice and emp tables
    alter table invoice add constraint inv_fk_emp
    foreign key (sales_rep_id)
    references emp(emp_id)
    go

Defaults:–

  • Creating Defaults
    create default def_highsal as 15000
    go
  • Binding defaults
    sp_bindefault def_highsal,’emp.salary’
    go
  • Creating your own custom defaults has an advantage that the name of the default can be chosen to reflect the application/functionality.

Table Partitioning:–
Insert performance on partitioned tables is improved, as multiple ‘entry points’ (last page entries) are created. Partitioned tables require slightly more disk space and small bit of additional memory.

  • Create partitions for a table

alter table invoice — creates 3 partitions on invoice table
partition 3
go

  • Tables which span multiple segments

Tables containing large amounts of data (> 2 GB) need to be spread across several devices, using sp_placeobject. Note that this procedure affects only         future operations – if a table load of more than 2 GB is to be performed, it would have to be split into two or more stages.

  • Command – Span Multiple Segments

sp_placeobject ‘data_seg2′,’invoice’
go

Object Permissions:–

Object security is fairly straightforward and is handled using the ‘grant’ Transact SQL statement.

  • grant all on emp to Srinath

go

  • grant select on emp to Prashanth

go

  • grant update on emp to Prashanth

go

  • revoke select on emp from Prashanth

go

Stored procedure security allows you to grant access on a business logic basis. For example, if you had a stored proc that updated the invoice table and selected data from the customer table, you could grant the execute privilege on the stored proc, and you’re done. The user would be able to run the procedure to update/select from the tables, but will not get to the tables directly.

  • grant execute

on proc_upd_invoice to Srinath
go

Binding Rules to Columns:–
Default values for columns can be specified at the time a table is created, or afterwards via the modify command.

Examples
create table emp (
emp_id   integer  not null,
emp_name varchar(50) null,
salary   money    default 15000
)
go

alter table emp replace salary default 19000
go

Modifying Column:–

To modify/alter a column type you need to be “dbo” and have select into option enabled, in the database defaults.

create table emp (
emp_id   integer  not null,
emp_name varchar(50)  null,
salary   money    default 0
)
go

/* make the name column longer */
alter table emp modify emp_name varchar(100) not null
go

/* rename the column */
sp_rename ‘emp.emp_name’, name
go

Move objects to segments:–

When databases contain more segments, it is often necessary to move tables between segments.

Examples

  • Move a table entirely to a new segment

drop index ‘employee.idx_employee’
go
create clustered index on emp (emp_id) on new_seg
go

  • Leaving the table in the existing location but future allocations go to the new segment

sp_placeobject new_seg , ‘emp’
go

  • To leave a table in the existing location and use a new segment for future allocations of the text column (emp_notes):

sp_placeobject new_seg, ‘emp.emp_notes’

go

Views:–
Views offer an alternative way to look at data and can also be used to enforce security by limiting the data that is seen by users.

  • Create Views

create view emp_view
as select emp_id, emp_name from emp
where emp_id > 1 and emp_id < 101

  • Retrieve data via Views

select * from emp_view

  • Updates on views are possible but there are restrictions. Some update restrictions are:
  1. Views created with computed columns in the view definition.
  2. Underlying table objects having NOT NULL columns.
  3. Check option used for creation of views.
  4. Deletes are not allowed on views created with multiple objects.
  • The data accessed via a view must require permissions irrespective of the permissions existing in the underlying tables that are used to create the view. The data in the underlying tables that is not included in the view are not visible to the users.
  • Advantages:
  1. Provide the data to view that is of interest to a particular set of users.
  2. In cases where joins must be used in queries frequently, create a view with the underlying objects and the joins once and use select * from view
  3. Display different data to different target users with multiple views created using the same underlying data.

Stored Procedures:–

Stored procedures are compiled versions of SQL statements. Performance benefits are significant as network traffic is reduced, and the optimizer does not need to re-parse the code.

Stored Procedures are classified into:

  • System Stored Procedures – Defined by SYBASE and delivered as part of ASE.

Examples:
- sp_who –Provides information on users on ASE
- sp_help – Provides a information/listing of all objects in the current database.
All of the System Stored Procedures start with “sp_” and are located in the ‘sybsystemprocs’ system database

  • User Stored Procedures – Defined by the users of ASE in a designated user database.
  • Stored procedure to retrieve

create procedure proc_rtv_invoice
(@inv_id numeric(8,0) ) as
select inv_id, inv_date, salesrep_emp_id
from invoice
where inv_id = @inv_id
return
go

  • Execute the stored procedure

exec proc_rtv_invoice 325
go

Triggers:–
Trigger is a special type of SP that gets executed automatically when any DML operation takes place on a table.

  • Triggers are used to enforce referential integrity.
  • Triggers are used to cascade changes to related tables.
  • Triggers can be used to apply complex restrictions than that enforced using rules.
  • Trigger can perform analysis before and after changes to the table.
  • Triggers cannot have the following:
  1. create and drop commands.
  2. alter table, alter database, truncate table.
  3. Load database and transactions.
  4. Grant and revoke statements.
  5. update statistics
  6. reconfigure
  7. disk init, disk mirror, disk refit, disk reinit, disk remirror, disk unmirror
  8. select into
  • Trigger creation

create trigger emp_trigger
on emp
for insert, update, delete
as

  • Trigger Example

create trigger emp_trigger
on emp
for delete
as
delete payment
from payment, deleted
where payment.empid = deleted.empid