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
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
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
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 elog log functionlog10 log function base 10plus 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'
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
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 112convert convert between data types convert(float,'5.50')=5.50
suser_name() current login idgetdate() current date
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_idelse
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 datetimeif @inv_id > 0
begin select @date = getdate() select inv_id, inv_date, sales_rep_id,@date from invoice where inv_id = @inv_id endelse
raiserror 99999 'Error: invalid invoice #'return
go
Note how the variable @date was declared, and given a value.
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 + 1end
select 'Result is: ', @power2
return
go
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 integerdeclare cursor1 cursor for
select inv_id,sales_rep_id from invoiceopen 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 endclose cursor cursor1
return
go
This example illustrates how to perform an update while joining to another table.
update employeeset t1.dept = t2.dept
from employee t1, old_employee t2
where t1.emp_id = t2.emp_id
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 t28> where t2.dest_id = t1.dest_id ) > 19>
10> go
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) asdeclare @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
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
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 recommendedThis 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 theprocessing 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 BEGINIF and
..... ELSE....
END3) 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_idorder 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.
This section provides some sample Transact-SQL relating to multiple databases in an SQL Server. Note that "isql" (from unix) requires a "go" after each of the SQL statements.
To look at your current database:
select db_name()
Looking at the list of available databases:
sp_helpdb
Changing your default database to the pubs database:
use pubs
Doing a select against a table in the current database (assuming the current database is pubs):
select * from authors
Doing a select against a table in another database:
select name from tempdb..sysobjects
select name from tempdb.dbo.sysobjects
In the above query, tempdb refers to a special database within each server. More on tempdb later. The optional "dbo" in the above query refers to the user owning the table in the tempdb database. We'll talk more about this in the security section later. For now just use the ".." notation.
The above examples use select statements, but they could just have easily used insert, update, or delete statements.