Sybase 101

DDL Overview


Transact-SQL Primer




Back to top page









email ... js99@rocket99.com

Copyright 1998-2006 © Citisoft, Inc. All Rights Reserved.












Sybase data types


data type           storage      range/length            comments
-----------------   --------     -------------------     ----------------------------
integer             4            +/- 2.1 billion
smallint            2            +/- 32768
tinyint             1            0 .. 255
float               4                                   storage req is machine dependant
real                4
double precision    8
smallmoney          4            +/- 214,748            4 decimal places
money               8            +/- 922 trillion       4 decimal places
decimal/numeric     varies
  decimal(9,0)      4
  decimal(12,0)     5

char(n)             n            length <= 255
varchar(n)          varies       length <= 255  ( over 4000 allowed in ASE 12.5 )

text                varies       length up to 2 GB     16 bytes stored in record (default)
image               varies       length up to 2 GB     16 bytes stored in record (default)

datetime            8            1/1/1753 .. 12/31/9999    precision to 1/300 second
smalldatetime       4            1/1/1900 .. 6/6/2079      precision to minutes
timestamp           8                                      same as varbinary(8)

bit                 1            0/1                       up to 8 bit fields stored within 1 byte

binary(n)           length n
varbinary(n)        length n

Only numeric data types with a precision of zero can be used for an identity column.


Historical Note on the Datetime Datatype
It starts with 1753 because the English-speaking world converted from the Julian to the Gregorian calendar in 1752. To do that, the day after September 2, 1752 was decreed to be September 14, 1752.


Listing object names and attributes

Examples below are formatted to run using the isql utility.
/* list all table names for current database */
select name from sysobjects where type = 'U'
go
sp_tables
go
/* 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 employee table */
sp_help employee
go
/* display spaced used for employee table */
sp_spaceused employee
go
/* display source code for proc_rtv_employee */
sp_helptext proc_rtv_employee
go

Creating a table

Table create examples:
create table employee (
emp_id    numeric(8,0)  identity,
fname     varchar(10)   not null,
lname     varchar(25)   not null,
salary    money         not null,
dept_cd   char(3)       not null,
fax_no    integer       null
)
go
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

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


Creating a proxy table

Proxy table create example:

create proxy_table invoice_items at 'SERVERXXX01.dbxxx001.dbo.invoice_items'
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

/* add default */
alter table charge_item replace price_overridable_ind default 0
go

/* change column name -- quotes are required */
sp_rename 'employee.dept',dept_name
go


Creating an index

Sample index creates:
create unique clustered index emp_idx
  on employee (emp_id)
go
create index emp_name_idx
  on employee (lname)
go


-- With sorted data!

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



Clustered vs non-clustered indexes

Typically, a clustered index will be created on the primary key of a table, and non-clustered indexes are used where needed.

Non-clustered indexes
Clustered indexes

Note! With "lock datapages" or "lock datarows" ... clustered indexes are sorted physically only upon creation. After that, the indexes behave like non-clustered indexes.

Creating a constraint

Contraints are used to define primary keys, enforce uniqueness, and to describe foreign key relationships. Note that unique or primary key constraints create indexes upon creation.
/* primary key for the employee table */
alter table employee 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 employee */
alter table invoice add constraint inv_fk_emp
  foreign key (sales_rep_id)
  references employee(emp_id)
go

Creating a stored procedure

Stored procedures a compiled versions SQL statements. Performance benefits are significant as network traffic is reduced, and the optimizer does not need to re-parse the code.
/* stored procedure to retrieve an invoice */
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

/* now, execute the stored procedure */
exec proc_rtv_invoice 325
go

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 need a chunk of memory also.
/* create 4 partitions for the invoice table */
alter table invoice
partition 4
go

Tables which span multiple segments

Tables containg 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 the 2 GB is to be performed, it would have to be split into two or more stages.
/* Future inserts will reside on data_seg2 */
sp_placeobject 'data_seg2','invoice'
go

Object Permissions

Object security is fairly straightforward. Here are some examples:
grant all on invoice to jsmith
go
grant select on invoice to wriker
go
grant update on invoice to wriker
go
revoke select on invoice from wriker
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 priviledge on the stored proc, and you're done. The user would be able to run the procedure to update/select from the tables, but could not get at the tables directly.
grant execute on proc_upd_invoice to jsmith
go

Binding Rules to Colunms

Default values for colunms can be specified at the time a table is created, or afterwards via the modify command.

/* Employee will have a default salary of 10000, and a hire date of today */

create table employee (
  emp_id   integer  not null,
  salary   money    default 0,
  hire_dt  datetime default getdate()
)
go

/* alter the table, so salary starts at 49000 */

ALTER TABLE employee REPLACE salary DEFAULT 49000
go


/* create a new default;  bind the column to your custom default */


create default def_highsal as 55000
go 
sp_bindefault def_highsal,'employee.salary'
go 


-- creating your own custom defaults has a significant advantage:
--  you can actually choose the name of the default.





Modifying a Colunm

With Sybase 12.1.X and higher, a column type can be altered. You need to be dbo and have select into turned on, in the database defaults.

create table employee (
  emp_id   integer  not null,
  salary   money    default 0,
  hire_dt  datetime default getdate(),
  last_name varchar(5)  null
)
go


/* make the name column longer */

alter table employee modify last_name varchar(80) not null
go


/* rename the column (works with Sybase 11 and 12) */

sp_rename 'employee.last_name',last_nm
go



Moving an object to another segment

When databases contain more segments than the usual default, it is often necessary to move tables between segments.

/* move a table, in its entirety, to the new segment */

drop index 'employee.idx_employee'
go
create clustered index on employee (emp_id) on new_seg
go


/* leave table where it is, but future allocations go to the new segment */

sp_placeobject new_seg , 'employee'
go


/* leave table where it is, but future allocations for the 
   text column (employee_notes) go to the new segment */

sp_placeobject new_seg , 'employee.temployee_notes'
go





Transact SQL: numeric functions

Mathematic Functions

abs            absolute value             abs(-5) = 5
ceiling        next highest int           ceiling(5.3) = 6
floor          next lowest int            floor(5.7) = 5
power          exponential                power(2,8)=256
rand           random number              rand=0.315378 for example
round          round to n places          round(5.6,0)=6   round(5.66,1)=5.7
sign           -1,0,1                     sign(-5)=-1

Trigonometric and Log Functions

If you've advanced to the level where you're using trig operations within
a database, then you're probably not going to click on a link labeled
Sybase 101.

acos
asin
atan
atn2
cqos
cot
degrees
pi
radians
sin

exp            exponential e
log            log function
log10          log function base 10


Transact SQL: string functions

plus sign (+)      concatenation             'one'+'two'='onetwo'
ascii              char->ascii value         ascii('A')=65
char               ascii->char               char(65)='A'
charindex          similar to instring       charindex('two','onetwothree')=4
char_length        length of string          charlength('onetwo')=6
lower              lower case                lower('ONE')='one'
ltrim              trim left blanks          ltrim('   one')='one'
replicate          repeat chars              replicate('-',8)='--------'
reverse            flip string               reverse('salad')='dalas'
right              right chunk of string     right('Chicago',2)='go'
rtrim              trim right blanks         rtrim('test   ')='test'
space              spaces                    space(5)='     '
str                float->char               str(5.6,12,2)='        5.60'
stuff              insert chars within str   stuff('onetwothree',4,3,'-----')='one-----three'
substring          get piece of string       substring('sybase',1,2)='sy'
upper              upper case                upper('one')='ONE'

Transact SQL: date/time functions

datepart*        get part of a date         datepart(MM,'10/21/98')=10
dateadd*         manipulate a date          dateadd(DD,10,'10/21/98')= 10/31/98
getdate          todays date and time       getdate()=Nov 16 1998-2000  7:27PM

* date parts are MM,DD,YY,HH,MI,SS,MS

Transact SQL: date/time formats

Use the convert function to format the date into the style of your choice.

Examples:
 select convert(char(20),getdate(),101)

 select emp_id,convert(char(20),hire_dt,101)
 from employee


Summary:

 Sample Date            Format
 -------------------- -----------
 04/05/2000                   101

 -------------------- -----------
 2000.04.05                   102

 -------------------- -----------
 05/04/2000                   103

 -------------------- -----------
 05.04.2000                   104

 -------------------- -----------
 05-04-2000                   105

 -------------------- -----------
 05 Apr 2000                  106

 -------------------- -----------
 Apr 05, 2000                 107

 -------------------- -----------
 11:33:24                     108

 -------------------- -----------
 Apr  5 2000 11:33:24         109

 -------------------- -----------
 04-05-2000                   110

 -------------------- -----------
 2000/04/05                   111

 -------------------- -----------
 20000405                     112



Transact SQL: misc functions

convert          convert between data types      convert(float,'5.50')=5.50
suser_name()     current login id
getdate()        current date

Transact SQL: Conditionals

Conditional statements allow branching within stored procedures in a fashion similar to other languages, like Visual Basic. The example below returns the matching invoice if a non-zero value is passed to it, otherwise it raises an error.
create procedure proc_rtv_invoice (@inv_id numeric(8,0) as

if @inv_id > 0
   select inv_id, inv_date, sales_rep_id
   from invoice
   where inv_id = @inv_id
else
   raiserror 99999 'Error: invalid invoice #'

return
go
Another example, which illustrates the begin and end constructs
create procedure proc_rtv_invoice (@inv_id numeric(8,0)) as

declare @date   datetime

if @inv_id > 0
   begin
   select @date = getdate()

   select inv_id, inv_date, sales_rep_id,@date
   from invoice
   where inv_id = @inv_id
   end
else
   raiserror 99999 'Error: invalid invoice #'

return
go
Note how the variable @date was declared, and given a value.

Transact SQL: looping constructs

The while ().. begin..end is the best way to preform loops within a stored procedure. Note that declared variables in T-SQL need to be initialized.
create proc proc_looper (@loops int) as

declare @count   integer,
        @power2  float

select @count = 0, @power2 = 1

while (@count < @loops)
begin
  select @power2 = @power2 * 2

  select @count = @count + 1
end

select 'Result is: ', @power2

return
go

Transact SQL: Cursors

Database cursors allow row by row processing to occur within a stored procedure.
create procedure proc_upd_commiss as

declare @inv_id       integer,
        @sales_rep_id integer

declare cursor1 cursor for
   select inv_id,sales_rep_id
   from invoice

open cursor1

fetch cursor1 into @inv_id,@sales_rep_id

while (@@sqlstatus=0)
   begin

   update employee
   set commiss_tot = commiss_tot + 15
   where emp_id = @sales_rep_id

   fetch cursor1 into @inv_id,@sales_rep_id
   end

close cursor1

return
go

Transact SQL: Complex Updates

This example illustrates how to perform an update while joining to another table.
 update employee
 set t1.dept = t2.dept
 from employee t1, old_employee t2
 where t1.emp_id = t2.emp_id

Transact SQL: Finding duplicate rows in a table

This example finds cargo records with have duplicate destination ids.
3> select cargo_id, dest_id
4> from routing t1
5> where
6>     ( select count(*)
7>       from routing t2
8>       where t2.dest_id = t1.dest_id ) > 1
9>
10> go

Using Temporary Tables

Temp tables allow developers to create and scan tables within a stored procedure - and have the tables totally isolated from all other database connections. This is very valuable when results need to be processed several times within a loop, or when a complex result set is expected (like a crosstab). Note that temp table transactions are logged within tempdb (exception: select into create statements).

create proc proc_gen_report (@region_id   integer) as

declare @total   money


/* standard create */

create table #rpt (
store_id    integer   not null,
store_cd    char(5)   not null,
inv_count   integer   not null,
total_sales money     not null
)


/* create using select into - make sure 'select into' is turned on */

select t1.cus_id, t1.cus_name, sum(t2.inv_amount) 'inv_summary'
into #cus_invoices
from customer t1, invoice t2
where t2.cus_id = t1.cus_id


/* Processing occurs, using temp table(s) where needed. */
/* Temp tables can be used in joins, aggregates, updates, etc. */


drop table #rpt
drop table #cus_invoices

return
go



Inner/Outer Joins


This will display sales rep's names, and their territory.
It will also display names that do not have a territory.

1> select t1.srep_name, t2.terr_name
2> from salesrep t1, territory t2
3> where t1.srep_id *= t2.srep_id
4> go
srep_name terr_name
-------------------- --------------------
Jim Smith Wisconsin
Jake Blues Rhode Island
Fred Green New York
Sam Jones Illinois
Rob Trent Florida
Sally Sales NULL
Mary Market NULL


Now, this will display all territories,
even those that have no salesman assigned ...

1> select t1.srep_name, t2.terr_name
from salesrep t1, territory t2
where t1.srep_id =* t2.srep_id
2> 3> 4>
5> go
srep_name terr_name
-------------------- --------------------
Jim Smith Wisconsin
Jake Blues Rhode Island
Fred Green New York
Sam Jones Illinois
Rob Trent Florida
NULL California
NULL Washington


And this, of course, will display
only salesreps and territories for which
there is a link ...

select t1.name, t2.territory_name
from salesrep t1, territory t2
where t1.srep_id = t2.srep_id

1>
2> select t1.srep_name, t2.terr_name
3> from salesrep t1, territory t2
4> where t1.srep_id = t2.srep_id
5> go
srep_name terr_name
-------------------- --------------------
Jim Smith Wisconsin
Jake Blues Rhode Island
Fred Green New York
Sam Jones Illinois
Rob Trent Florida


Isolation Levels

Setting the isolation levels can eliminate contention problems, when reports are run on the same database as the online application.

Three flavors to choose from, for troublesome reports, queries, and updates.


1)
select cus_id
from customer_location
where cus_id< 1000000
at isolation  read uncommitted

-- > Allows table to be read (ala Oracle) even when update page locks are pending.


2)
select cus_id from
customer_location noholdlock
where cus_id< 1000000

-- > Allows big queries to run without locking pages / tables.


3)
/* For updates: */

set transaction isolation level 0

begin transation

update invoice_item
set discount_amt = 0
where invoice_id < 2000000

commit transaction


Queries against invoice_item will NOT be blocked.
Updates against invoice_item pages included in the transaction WILL be blocked.



*** Mixing  1 & 2 from above is not recommended



Reporting: SQL Performance and Tuning

This is a list of some techniques used successfully at several different sites.

Getting Maximum Index Usage

1) Verify tables have had "update statistics" on them ;
   Verify tables have had "sp_recompile" on them.

2) Verify any declared variables have the same data
   type as their corresponding columns - this is a common
   pitfall.

3) Force index usage as follows, with a hint:
        from customer (index idx_customer2)

4) Use SET TABLE COUNT
   Example:   set table count 6
   Then, compile the procedure, in the same session.

5) If temp tables are being used, put the temp table
   creation statements in one procedure, and the
   processing SQL in another procedure.  This allows
   the optimizer to form a query plan on the already
   established tables.
   Example:
     proc_driver  calls    proc_create_temp_tables
   then, 
     proc_driver  calls    proc_generate_report




General SQL Programming

- Plan for growth.  Assume the driver table doubled or tripled in size; would
  the report still function ?

- Avoid dumb comparisons in the where clause, like 
       where @emp_id > 0

- use "WHERE EXISTS ( )" rather than "WHERE NOT EXISTS"

- use "!=" rather than "<>"

- use "IS NOT NULL" rather than "<>NULL"

- use "IS NULL" rather than "=NULL"

- avoid distinct if possible ; see cursor loop option below

- use meaningful names for temp tables ... don't use #temp (lame)


Report Structure Approaches   

1) Single query

Single query reports are rare - usually they involve getting a simple list
together.  

- Don't try to 'shoehorn' SQL into one statement.  Shorter programs are 
  great for C or Perl applications, but this is not the case in SQL.
  Think "Bigger is Better" (and more maintainable).

- Keep queries from using more than four tables if possible.



2) Cursor on driver table(s), with IF..THEN processing in loop

Using a cursor for complex reports almost always increases performance
when large tables and a lot of joins are involved.

- Keep cursor queries from using more than two tables if possible,
  make sure this query performs well on its own.  
  
- Try to have a unique key of some sort available within the tables involved.
  Strange results have been known to occur when a cursor is scanning
  rows that are exactly alike.

- Don't use cursors for updating.

- Use IF statements for filtering results even further.  In most cases:

  A code construct like the one below is better than cramming the
  logic in a where clause.
  

       IF 
           BEGIN

           IF  and 
               .....
           ELSE
               ....           
           
           END


3) Set processing without cursors

   This technique should be attempted when even a cursor construct fails to 
   achieve the desired performance.

   Basically, the driver query is re-run with each iteration of the loop.

   Sample, with cursor:
   
        declare cursor1 cursor for
        select emp_id, last_name, salary
        from employee
   
        open cursor1
   
        fetch cursor1 into @emp_id, @last_name, @salary
   
        while (@@sqlstatus = 0)
          begin
     
          < processing >
     
          fetch cursor1 into @emp_id, @last_name, @salary
          end
   
        close cursor1   
   
   
   Sample, with set processing:
   

        select @emp_id = 0, @loop = 1
   
        while (@loop > 0)
            begin

            set rowcount 1
   
            select     
            @emp_id      = emp_id, 
            @last_name   = last_name, 
            @salary      = salary
            from employee
            where emp_id > @emp_id
            order  by 1
   
            select @loop = @@rowcount
   
            set rowcount 0
     
            if @loop > 0
               begin
     
               < processing >
     
               end

               
            end
   

Transaction Log Filling Up ?

If the transaction log is filling up, for tempdb or the main database, there
is likely something wrong with the report logic.

Things to check:

- Instead of repetitively updating each row, can the values be obtained
  ahead of time, and then inserted with a single transaction ?
  
- Are the "joined" updates occuring on each row once ?  When updating
  using a join statement, make sure that the tables in question 
  are joined in a way that avoids duplicate rows.  Try running the
  SQL statement as a SELECT - check it out.
  
- Are you cramming 500,000 rows from a temp table into a db table ?
  Try elminating the temp table.

- Create indexes on updated/inserted tables after the fact.

- Use "set rowcount" along with "waitfor delay" if log problems persist


*** A proper DBA will never extend the log segment, based on the needs of a 
    single process.









Case Statement

Sample for case statement usage appears below.


SELECT emp_name,
        (CASE WHEN salary = 0 
              THEN "not hired" 
              ELSE "has salary" END) AS salary_note,
       hire_dt
  FROM emp_table 




Custom query plans

On rare occasions, it is necessary to create and use a custom query plan.


/* run the delete, force a table scan */

delete from perm_employee_notes  plan "(t_scan perm_employee_notes)"
go













Back to top page



email ... js99@rocket99.com

Copyright 1998-2006 © Citisoft, Inc. All Rights Reserved.