Being a SQL Server DBA

We start the program SQL Query Analyzer which comes standard with SQL server. The equivalent program that lets you interact with a database in Oracle is called SqlPlus. We see two windows on our screen, the left pane tells us all the databases created and their contents, the right pane allows us to write sql commands and then allows us to run them. We create a table m1 as follows.

Create table m1 ( vno integer, name char(5))

This creates a table called m1 with two fields vno with data type integer and name with data type character with a maximum width of 5. Lets add some data here.

insert into m1 values ( 1, 'vijay')

This adds one record for us and if we run the select command

select * from m1

we get the following output

1 vijay

insert into m1 values ( null , 'mukhi')

The value null is a reserved value in SQL and it means no value or we do not know what value to write at this point in time. Thus whenever we do know what value to place in a database we specify null. A zero means zero null means no value. Our table allows us to specify a null value for a fields. Lets change this.

Create table m2 ( vno integer not null, name char(5) null)

insert into m2 values ( 1 , null)

insert into m2 values ( null , 'vijay')

Server: Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'vno', table 'master.dbo.m2'; column does not allow nulls. INSERT fails.

The statement has been terminated.

We create a table m2 with some conditions. The null identifier allows us to tell the system that values in this field can be null. Thus the name field can take null values. This is why the first insert statement gives us no errors. The not null identifier means that null values are not allowed for this field. When we execute the second insert statement we get an error as the vno field has a null value.

The point we are making is that we should build error checks or constraints at the data base level so that we do not have to code the error checks in our program. Thus whatever checks we can build at the create table level we should and as far possible avoid data entry checks in the code.

Create table m3 ( vno integer primary key, name char(5) )

insert into m3 values ( 1, 'hi')

insert into m3 values ( null , 'bye')

Server: Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'vno', table 'master.dbo.m3'; column does not allow nulls. INSERT fails.

The statement has been terminated.

insert into m3 values ( 1 , 'hi')

Server: Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__m3__1CFC3D38'. Cannot insert duplicate key in object 'm3'.

The statement has been terminated.

1 hi

Thus we see that only one record gets added as only the first insert statement is valid. The second fails as we have given a value of null to vno. The third insert statement is in error as the vno field has the same value of 1. Thus nulls or duplicates are not allowed as valid values in a field that has the identifier primary key. This means that the system does the error checks not an external program written by a programmer. This also means that we do the error check only once at the level of the database.

create table m4 ( vno integer, amt integer , check(amt > 0))

insert into m4 values ( 1, 100)

insert into m4 values ( 2, -100)

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN CHECK constraint 'CK__m4__amt__155B1B70'. The conflict occurred in database 'master', table 'm4', column 'amt'.

1 100

The check allows us to specify a condition which if not met gives us an error. The condition above says that the amt field must be larger than 0. The first insert passes the condition, the second fails as the amt field has a negative value.

create table m5 ( vno integer, amt integer , amt1 integer , check(amt > 0 and amt1 > amt))

insert into m5 values ( 1, 10 , 20)

insert into m5 values ( 1, 10 , 2)

INSERT statement conflicted with TABLE CHECK constraint 'CK__m5__1837881B'. The conflict occurred in database 'master', table 'm5'.

insert into m5 values ( 1, -10 , 2)

INSERT statement conflicted with TABLE CHECK constraint 'CK__m5__1837881B'. The conflict occurred in database 'master', table 'm5'.

1 10 20

Our conditions or the check can be as complex as we like. In the case of table m5 we are using the and to combine two conditions. The first specifies that the amt field must be positive and the second the field amt1 must be larger than the field amt1. The first insert statement passes muster, the second fails as the amt1 filed is smaller than the amt field. The third insert statement fails the first condition as its value is negative.

create table m6 ( vno integer, name char(1), check (name in ('d' , 'c')) )

insert into m6 values ( 1, 'd')

insert into m6 values ( 1, 'C')

insert into m6 values ( 2, 'E')

INSERT statement conflicted with COLUMN CHECK constraint 'CK__m6__name__1C0818FF'. The conflict occurred in database 'master', table 'm6', column 'name'.

1 d

1 C

The in identifier allows us to specify a range of fixed values. A financial transaction can either be debit or credit. Thus the only valid values for the name field are d or c, caps and small included. This is why the first two insert statements are ok, the third sets the value of the name field to E which is not valid.

create table m7 ( vno integer, name char(30), check (name like '[a-z][a-z]-[0-9][0-9][fm]'))

insert into m7 values ( 1, 'ab-67f')

We are also allowed to use regular expressions to test the input. [a-z] in square brackets stands for any letter of the alphabet, [0-9] stands for a number, [fm] stands for a f or a m and the minus outside the square brackets for a minus. Thus in english we must type two characters, followed by a minus sign followed by two digits and then a f or a m. Anything else is an error.

1 ab-67f

You cannot specify complex conditions using the + and the * as we can use with regular expressions.

create table m8 ( vno integer, name char(10) default('vijay'))

insert into m8 values ( 1, 'hi')

insert into m8( vno ) values ( 2 )

insert into m8 values ( 3, null )

1 hi

2 vijay

3 NULL

The default identifier allows us to specify a value for a field when the user does not specify one. In the first insert statement we get no error, the second we do not specify a value for field name, the value stored on disk becomes vijay. Nulls are valid values for a field. The name of the fields may appear after the name of the table. This is optional and we do not supply one, the default is the field names in the order that we have specified in the table.

insert into m8 values ( 10)

Insert Error: Column name or number of supplied values does not match table definition.

When we do not want to give values for all fields we cannot leave them blank as we will get an error. The way out is to then specify the names of the fields that we are specifying values for.

create table banknames ( bankcode char(1) primary key, bankname char(20))

insert into banknames values ( 'b', 'Bank of Baroda')

insert into banknames values ( 'c', 'Citi Bank')

insert into banknames values ( 's', 'State Bank')

b Bank of Baroda

c Citi Bank

s State Bank

We have created a table banknames that stores the bank codes of the banks we deal with and the name of the bank.

create table m9 ( vno integer, bankc char(1) references banknames(bankcode))

insert into m9 values ( 1, 'b')

insert into m9 values ( 1, 'z')

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__m9__bankc__2E26C93A'. The conflict occurred in database 'master', table 'banknames', column 'bankcode'.

In table m9, the field bankc will have values that are already present in the table banknames. Thus we use the references keyword and supply the name of the table and the name of field within that table. This field will normally be the primary key of the table. Thus each time we key in a value for the field bankc in m9 it will check that this value is present in the table banknames field bankcode. The field bankcode has a value b but does not have a value of z and hence the second insert gives us an error. Any field in a table that references a primary field in another table is called a foreign key.

1 b

delete from banknames where bankcode='b'

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__m9__bankc__33DFA290'. The conflict occurred in database 'master', table 'm9', column 'bankc'.

We cannot delete from the table banknames as there are records that are referring to the primary key with a value of ‘b’.

delete from banknames where bankcode='c'

There is no record in table m9 that refers to the bankcode field of value c and hence we can delete this record with impunity. Thus the simple rule is that whenever the system delete a record, it checks, is there any table that refers to this records value. If the answer is yes, then it does not delete the record.

create table m10 ( vno integer, bankc char(1) references banknames(bankcode) on delete cascade )

insert into m10 values ( 1, 'b’)

insert into m10 values ( 2, 's')

delete from banknames where bankcode = 's'

select * from m10

1 b

select * form m9

1 b

delete from banknames where bankcode = 'b'

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__m9__bankc__33DFA290'. The conflict occurred in database 'master', table 'm9', column 'bankc'.

b Bank of Baroda

When we do not write anything after On delete, the default is no action. If we replace it with Cascade, all records in all tables that refer to this field value will also be deleted. Earlier we were not allowed to delete a record if somewhere a record referenced it. Now the system will delete all records that reference this value. We have record number 2 that refers to bank s. When we delete bank code s from banknames, this record automatically gets deleted. We cannot delete bank code b as table m9 refers to it. Thus cascade can be dangerous as if a 1000 records someplace references bank code s then deleting this record will delete all records that reference it.

create table m12 ( vno integer , name char(10) unique)

insert into m12 values ( 1, 'vijay' )

Violation of UNIQUE KEY constraint 'UQ__m12__37B03374'. Cannot insert duplicate key in object 'm12'.

insert into m12 values ( 1, 'Vijay' )

Same error

insert into m12 values ( 1, null )

insert into m12 values ( 1, 'vijay1' )

1 vijay

1 NULL

1 vijay1

The unique constraint maintains the value of a field as unique. Does when we try and add another value of vijay with v in caps we get an error. Vijay1 is allowed as well as null values. The unique keyword creates a index for us.

create table m13 ( vno integer, amt integer , aa as vno * amt)

insert into m13 values ( 10, 20)

10 20 200

We can also specify computed columns where field aa is the product of two fields vno and amt.

insert into m13(vno, amt , aa) values ( 10, 20 , 200)

Column 'aa' cannot be modified because it is a computed column.

Once a field is computed we cannot supply it a value using the insert statement.

create table m14 ( vno integer, name char(10))

insert into m14 values ( 1, USER_NAME())

1 dbo

We get a large number of functions like USER_NAME that give us the current user who has added the record.

select user_name()

dbo

We can directly call a function using the select statement.

select user_name(), host_name() , app_name() , CURRENT_TIMESTAMP , @@MAX_CONNECTIONS , @@LANGUAGE , @@SERVICENAME ,@@VERSION

dbo VMCI SQL Query Analyzer 2005-08-29 11:55:50.233 32767 us_english VMUKHI Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Sql server has over a zillion functions that we can use. These comes under all types of flavors. We can also create our own functions.

Joins

create table cust1 ( custid char(10), custname char(20))

insert into cust1 values ( 'c1’ , 'vijay')

insert into cust1 values ( 'c2' , 'sonal')

c1 vijay

c2 sonal

create table prod1 ( pcode char(10) , pname char(20))

insert into prod1 values ( 'p1' , 'copper')

insert into prod1 values ( 'p2' , 'silver')

insert into prod1 values ( 'p3' , 'gold')

p1 copper

p2 silver

p3 gold

create table sales1 ( vno integer, custc char(10) , pcode char(10), amt integer)

insert into sales1 values ( 1, 'c1' , 'p1' , 10)

insert into sales1 values ( 2, 'c2' , 'p2' , 20)

insert into sales1 values ( 3, 'c2' , 'p3' , 30)

insert into sales1 values (4, 'c1' , 'p3' , 40)

1 c1 p1 10

2 c2 p2 20

3 c2 p3 30

4 c1 p3 40

select * from sales1 , cust1

1 c1 p1 10 c1 vijay

2 c2 p2 20 c1 vijay

3 c2 p3 30 c1 vijay

4 c1 p3 40 c1 vijay

1 c1 p1 10 c2 sonal

2 c2 p2 20 c2 sonal

3 c2 p3 30 c2 sonal

4 c1 p3 40 c2 sonal

For the first time we write the name of two tables after the from. All that sql does is gives us an output where we get 6 columns, an addition of the columns in the individual tables and a multiplication of rows. As sales1 has 4 rows and cust1 has 2, we get a total of 8 rows. Thus when we join two tables we get far too many records to handle. Thus we bring in the where to bring down the number of records. Also most joins are primary key – foreign key joins. In these cases the number of records is decide by the table that supplies the foreign key, the primary key simply adds to the number of columns. In the above case, the table cust1 and sales1 have a field custid and custc that come from the same domain. The table sales1 has custc as the FK as the table cust1 has custid as the PK. Thus the number of records will be 4 as the FK decides this number.

select * from sales1, cust1 where custid = custc

1 c1 p1 10 c1 vijay

2 c2 p2 20 c2 sonal

3 c2 p3 30 c2 sonal

4 c1 p3 40 c1 vijay

We thus get access to the customer name stored in another table as well as access to all columns of the FK. We can do the same for product id also.

select * from sales1, cust1, prod1 where custid = custc and sales1.pcode = prod1.pcode

1 c1 p1 10 c1 vijay p1 copper

2 c2 p2 20 c2 sonal p2 silver

3 c2 p3 30 c2 sonal p3 gold

4 c1 p3 40 c1 vijay p3 gold

Same number of records as cust1 yet provides the PK. We now get the name of the products also. We can join as many tables as we like.

View

A view is a select statement that looks and acts as a table.

create view zzz as select vno, custc , sales1.pcode, amt from sales1, cust1, prod1 where custid = custc and sales1.pcode = prod1.pcode

select * from zzz

1 c1 p1 10

2 c2 p2 20

3 c2 p3 30

4 c1 p3 40

There is now way of knowing that zzz is a view or a table.

To create a new user, we first start the program Enterprise manager. We then keep clicking on the plus in the left pane till we come to the security tab. Here we click on the plus and then right mouse button on Logins. Here we choose new logins. At the dialog box we specify our name as vijay and password vijay but we choose the radio button sql authentication. When we click on OK we are asked to confirm our new password. We now see our user name in the pane on the right.

We now log in as vijay but we are not allowed to access any tables from master.

select * from sales1

SELECT permission denied on object 'sales1', database 'master', owner 'dbo'.

We next double click on our user name and then choose the tab server roles and click on the first role system adminstrator. This gives us all rights to the database. Now we can access any entity that sa can access.

At the same level of security is option management that has an option sql server logs. This is where we can see a log of all that happens.

Choosing databases allows us to view all the tables, views etc that have been created.

Creating a database

create database vijay

The CREATE DATABASE process is allocating 0.63 MB on disk 'vijay'.

The CREATE DATABASE process is allocating 0.49 MB on disk 'vijay_log'.

A database is where we place all our tables. We use the create database command to create a database called vijay. We could have also carried out the above command using the enterprise manager. Now when we click on the down arrow for a list of database we see our name vijay. When we move to the enterprise manager and choose database and then vijay, right mouse button, properties, we can see and change all the properties of our database.

create table vijay.dbo.vij1 ( vno integer )

The name of a table is the name of the database, the name of the user and then the name of the table.

insert into vijay.dbo.vij1 values (100)

select * from vijay.dbo.vij1

Thus we do not have to activate the database if we preface it with the full name of the database and the user. The system tables shows us the names of the tables the system creates. The user tables go into the user tables options.

We have a large number of procedures that allow us to do lots of common database tasks. One of them allows us to rename our database.

sp_renamedb vijay , mukhi

Simply running the above command renames database vijay to mukhi.

If we click on master in the lest pane, then stored procedures, we see a zillion stored procedures. One of them is sp_renamedb. The power of a database is the number of functions and the number of stored procedures it offers us. Thus lets now create our own stored procedure in master.

create procedure a1 as select * from sales1

We will now see that the first stored procedure is a1. When we write

and press execute we see the data from sales 1.

create procedure a2

select * from sales1

select * from prod1

We now see data from two tables sales1 and prod1. We call use the exec command to execute a stored procedure.

When we click on a1 right mouse button and then we choose open, we see that both a1 and a2 have no parameters.

create procedure a3

@prod varchar(20)

select * from sales1 where pcode = @prod

a3 'p1'

1 c1 p1 10

a3 'p2'

2 c2 p2 20

Whenever we create a procedure we can specify the names of parameters to be passed at run time. All these parameter names must come before the keyword as. We have created one parameter prod. All parameter names must start with a @ sign. In our sql statements we can refer to these parameters by the same name. When we call the stored procedure we specify the parameter names separated by commas. When we now click right mouse button on the procedure name a4 and then open we get a dialog box that allows us to interactively specify the parameters. We click on the name of the parameter and then give a value and then execute the sp.

create procedure a4

@prod varchar(20) , @vo int

select * from sales1 where pcode = @prod and amt > @vo

print 'vijay mukhi'

a4 'p1',5

1 c1 p1 10

(1 row(s) affected)

vijay mukhi

We now specify two parameters, prod as before and vo which is a int or integer. Now we supply two parameters to our select statement. The print command allows us to write a message that will be displayed in the messages tab which is at the bottom of your screen.

If the edit option is on when we click on the right mouse button we can edit the sp and see how it was written.

create procedure a5

@prod varchar(20) ='p1' , @vo int = 5

select * from sales1 where pcode = @prod and amt > @vo

print 'vijay mukhi'

1 c1 p1 10

We can use the = sign to specify a default value if we do not specify a value. Thus when we run a5 with no parameters, the default values are p1 and 5.

a5 'p2'

2 c2 p2 20

We have all the right to override these values by specifying them on the command line.

create procedure a6

declare @@tot int

select @@tot = sum(amt) from sales1

print @@tot

100

We can declare a variable tot with two at signs, one is must, more than one is at your risk. We then store the value returned by sum(amt) which is 100 into tot which we display using the print. Thus we are allowed to create variables and use them later on in our code as we used input variables earlier.

create procedure a7

declare @@tot int

select @@tot = sum(amt) from sales1

if @@tot < 40

begin

print 'tot less then 40'

end

else

print 'tot larger than 40'

tot larger than 40

We have a if statement in sp. We specify a condition and then a begin and end if we have more than one line of code. If we do not then it is optional. The else in our case is true and as we have only one line of code, we do not use a begin and end.

create procedure a8 with encryption

select * from sales1

Using the with encryption keyword, when we click on the right mouse button, the edit option is yet on but when we select it our select statement is not visible. This is how we prevent others from peeking at our code.

create procedure a9

return 100

print 'hi'

create procedure a10

declare @a1 int

exec @a1 = a9

print @a1

a10

100

We first create a procedure a9 which uses the keyword return to return a value 100. when we run the procedure a9, we see no output as the print after the return does not get executed. No code gets called after the return statement. We then create a procedure a10 where we first create a variable a1 of type int. We then execute the sp a9 using the exec command. The return value of this procedure is stored in the variable a1 which we print out. This is how a stored procedure can return back a value to us that we can use. We could have also passed parameters to the procedure a9 by specifying them after the name of the procedure.

CREATE PROCEDURE a11 (@a1 int OUTPUT , @a2 int OUTPUT )

SELECT @a1 = Count(*) FROM sales1

SELECT @a2 = Count(*) FROM cust1

CREATE PROCEDURE a12

declare @a3 int

declare @a4 int

EXEC a11 @a1 = @a3 OUTPUT, @a2 = @a4 OUTPUT

select @a3, @a4

a12

4 2

We first create a procedure a11 which takes two variables a1 and a2 which we mark as output and input. We then use the count(*) function to store the number of records from the sales1 and cust1 table into a1 and a2. These variables being marked as output allow other programs to access them. We then create a procedure a12 which first declares a3 and a4 as variables and then initializes a3 to a1 and a4 to a2. We once again specify the output keyword. Thus exec run sp a11 and then sets a3 to a1 and a4 to a2 which we print out using the select statement. This does what a return does, a return returns a single value, output allows us to return multiple values.

create procedure a13

declare @a1 int

set @a1 = 3

while ( @a1 <= 7 )

begin

print @a1

set @a1 = @a1 + 1

end

The above sp demonstrates a while loop. We first create a variable a1, and then give it an initial value. The variable a1 has no initial value. We use the set command to give it a value of 3. We then use a while loop to iterate until the value of a1 is 7. In the while loop we use the begin and end to loop multiple statements. We first print out the value of a1 and then use the same old set to increase it value by 1. Thus sp’s have variables, if statements, loops and everything else that a programming language needs.

Stored procedure

create table adummy1 ( vno integer, amt integer)

insert into adummy1 values ( 1, 50)

create procedure b1

declare @amt int

set @amt = 100

if ( @amt > (select sum(amt) from adummy1 ))

print 'true'

else

print 'false'

true

insert into adummy1 values ( 2, 500)

false

We create a table adummy1 with two fields and then add one record where the amt field has a value of 50. We next create a sp b1, if you have noticed we have changed the naming convention from the a series. We create a int variable amt and set its value to 0. The main point of this example is that we can use a select statement where ever a number is needed. Sum(amt) returns a number and the comparison operator > requires a number. Whether we specify a number as a variable or a select statement is of no consequence to a stored procedure. We get true as the total sum of the amt field is less than 100. We then add a record where the amt value is 500, the if statement becomes false. This is how we can write complex sp’s.

create procedure b2

declare @amt int

set @amt = 20

print 'value is ' + @amt

Syntax error converting the varchar value 'value is ' to a column of data type int.

Lots of times we need to display the value of a int variable. We cannot use the above syntax as we are using a string and a int data type. A sp tries to convert the string into a int.

create procedure b3

declare @amt int

set @amt = 20

print 'value is ' + convert(char(20) ,@amt) + 'AA'

value is 20 AA

The way out is to use the convert function which takes two parameters, the final data type we need to convert to , in our case a char which is 20 characters wide. The second parameter is the original variable whose data type needs to be converted. Thus we get the value of the amt variable as a string 20 characters large.

create procedure b4

print 'hi' + char(13) + 'bye'

bye

The number 13 stands for a enter as 65 stands for a A. Using the char function we can convert a number into its ascii equivalent. Thus by using the char function we can introduce a enter in our print command. The \n of c does not work its magic here.

create procedure b5

begin transaction

delete from adummy1

rollback

When we run the sp b5 we see a message 2 records effected but when we display the records from adummy1 we see the records intact. A transaction is defined as a logical unit of work. A transaction allows us to tell the system that either do the following 6 sql statements for do none of them. We can now treat a bunch of sql statements as a group, either all get done or none. If a single one fails, they all fail.

Thus in sp b5, two records get deleted, but because we say rollback, the changes get undone. We have only one sql statement, the same holds good for multiple sql statements

create procedure b6

@a1 int

begin transaction

delete from adummy1

if ( @a1 = 1)

rollback

else

commit

b6 1

b6 0

Our sp b6 now takes a int parameter a1. If this value is 1, the keyword rollback gets called and thus the first time we run sp b6, no records get deleted. However the second time as we call b6 with 0, the command commit gets called which actually saves the data to disk. Commit is the opposite of rollback, once data gets committed, we cannot roll it back. Most of the time when we work with sql query a implicit commit is done at the end of every statement.

insert into adummy1 values ( 1, 10)

delete from adummy1

rollback

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The rollback will only work if we use a begin transaction command. It s this command that signals the sql engine to allow our sql statements to be rolled back.

insert into adummy1 values ( 1, 10)

begin transaction

delete from adummy1

select * from adummy1

no records

rollback

select * from adummy1

1 10

This is how we can rollback our transactions.

begin transaction

delete from adummy1

select * from adummy1

no records

commit

select * from adummy1

no records

rollback

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The minute we say commit, the begin transaction is closed and hence the rollback will not work. Thus the minute we use commit or rollback we cannot now change our mind at all.

create procedure b7

raiserror('vijay mukhi', 12, 4)

print 'bye'

Server: Msg 50000, Level 12, State 4, Procedure b7, Line 3

vijay mukhi

bye

The raiserror function allows us to display an error with a error message, followed by a level in our case 12 , followed by a State 4, The next two values the name of the procedure and line number are beyond our control. This is how we can display custom error messages. The flow of control does not stop as the bye also gets displayed. This is how we can create our own custom error messages like sql server does.

create procedure b8

raiserror(13001, 10, 4)

print 'bye'

data page

bye

select * from sysmessages

We can also specify a error message number instead of our own error message text. All error messages are stored in the table sysmessages.

create procedure b9

delete from adummy1 where vno >= 10

print @@error

The @@error is a system variable that tells us whether the last sql statement resulted in a error. A value of zero means no error. In this case even though no records got deleted, this is not a sql error.

sp_helptext 'b1'

This sp will display for us the entire text of our sp b1.

sp_helptext 'sp_addlogin'

will display the entire text for the sp_addlogin sp which is not encrypted. This is how we learnt what his stored procedures do. Encrypted stored procedures do not display their code.

create procedure b10

@a1 int,

@a2 int

print @a1

print @a2

b10 1,2

b10 @a2=7, @a1 = 8

We do not have to specify the parameters in the order that we have created them. If we specify the name of the parameters, the order now does not matter.

create procedure b11

print 'hi'

waitfor delay '00:00:30'

print 'bye'

bye

The waitfor does not work the way we thought. We thought the system would print hi, wait for 30 seconds and then print bye. It waited for 30 seconds and then printed hi and bye together.

insert into adummy1 values (1,10)

insert into adummy1 values (2,20)

insert into adummy1 values (3,30)

1 10

2 20

3 30

create procedure b12

declare @tot int , @amt1 int

set @tot = 0

declare c cursor for select amt from adummy1

open c

fetch c into @amt1

while ( @@fetch_status = 0)

begin

set @tot = @tot + @amt1

print 'tot is ' + convert(char(5),@tot) + ' and amt is ' + convert(char(5),@amt1)

fetch c into @amt1

end

print 'Final value of tot is ' + convert(char(5),@tot)

b12

tot is 10 and amt is 10

tot is 30 and amt is 20

tot is 60 and amt is 30

Final value of tot is 60

The most complex part of stored procedures is the concept of a cursor. We declare two int variables tot and amt1 and we set tot to 0. A cursor is nothing but an sql select statement. We create a cursor c using the same declare keyword and associating a select statement with it. We choose only one field amt from this table adummy1. We then open the cursor using the open statement. Unless we open a cursor we cannot access members from it. The fetch statement is used to access each record and we use it to store the first records amt value into variable amt1. As we were successful, the system variable FETCH_STATUS returns 0. We use this variables value in a while loop and quit when we get a non zero value. We use the tot variable to store the running total of the amt field. We display the values of tot and amt and then print them out. At the end of the loop we once again the next value from out table into the amt1 variable. The fetch command automatically moves the record pointer we do have to. A cursor is used in sql so that we can now have access to each and every record in the table. Using sql, we cannot get access to each and every record. What we do with each record is for us to decide. Oracle and all other databases also support the same syntax for cursors.

Triggers

A trigger is a stored procedure that get called by SQL server and not by us. We create a trigger in the same way that we create a stored procedure, but as said before we do not call it, the system calls it. We will first create a sinple table as follows

create table ab1 ( vno integer)

create trigger a1

on ab1

for insert

print 'vijay mukhi'

insert into ab1 values ( 1)

Message Window

vijay mukhi

(1 row(s) affected)

We create a trigger a1 using the create trigger command. The on clause needs the name of a table ab1, The for clause needs the sql statement that will trigger off this stored procedure. VAlid options are insert, update or delete, we use insert. After the as clause we will write the code that will be called whenever a record is inserted into the table ab1. Thus when we execute the insert statement, we see vijay mukhi in the message window. Once again we could write any code that we ahve learnt when we created stoored procedures. Normally we would write code that deletes data from data bases, create an audit trial etc.

create trigger a2

on ab1

for insert , update , delete

print 'vijay mukhi1'

update ab1 set vno = 2

vijay mukhi1

delete from ab1

vijay mukhi1

Trigger a2 gets called whenever we run a insert , update or delete sql command on ab1.

select * from sysobjects where type ='TR'

The table sysobjects stores all objects that we create . Each object has a field called type and TR stands for trigger. This is how we can view all teh triggers created.

create table ab2 ( a1 integer, a2 integer , a3 integer , a4 integer)

create trigger a3

on ab2

for update

print COLUMNS_UPDATED()

insert into ab2 values ( 1, 2, 3, 4)

update ab2 set a1 = 2

0x1

update ab2 set a2 = 2

0x02

update ab2 set a3 = 2

0x04

update ab2 set a4 = 2

0x08

update ab2 set a1=2, a2 = 2

0x03

update ab2 set a1=2, a2 = 2, a3 = 3

0x07

update ab2 set a1=2, a2 = 2, a3 = 3, a4 = 2

0x0f

The columns_updated function tells us which fields have been updated. Each column that is changed will set a bit. Thus when we update the first column, bit 1 will be set and the function returns 1, when we update the first columns, the firts two bits wil be set and the value returned is 3. When all four columns are updated, we get 0xf or 15 as the answer. This is how we can figure out which columns have changed.

create table ab3 ( vno integer, name char(10) , age integer)

insert into ab3 values ( 1, 'vijay', 46)

insert into ab3 values ( 2, 'mukhi', 76)

create trigger a4

on ab3

for update

select * from inserted

select * from deleted

We now create a table a3 with three fields and add two records. We create a trigger a4 where we simply dispaly from two tables that we have not created from, inserted and deleted.

update ab3 set age = 100 where vno = 1

1 vijay 100

1 vijay 46

select * from ab3

1 vijay 100

2 mukhi 76

We then update the first record in ab3 changing the age from 43 to 100. The inserted table has one record, the newly added value and the deleted table has the old values of the record. Thus the system stores for us all the new data that has been added in teh inserted table and all the old data in the deleted table. There is only one record in each table as the number of records affected by the update is only one.

update ab3 set age = 1000

1 vijay 1000

2 mukhi 1000

1 vijay 100

2 mukhi 76

We now write a update statement that changes two records, the inserted table gets both new records where the age is 1000 and the deleted table has the two old records.

create trigger a5

on ab3

for insert , delete

select * from inserted

select * from deleted

delete from ab3

inserted

no records

deleted

1 vijay 1000

2 mukhi 1000

We now create a fresh trigger a5 where we replace the update keyword with delete and insert. Now when we delete the two records, as we are not chaning any recirds, the inserted table is blank and the deleted table actually contains the two records we deleted. It now now acts as a table for deleted records. Deleted really stores records before teh change is made, inserted after the changes are being made.

insert into ab3 values ( 10 , 'hi', 100)

inserted

10 hi 100

deleted

no records

Now when we add a records, teh inserted shows us teh record added, as we have made no changes in the rtable, deleetd shows us blank.

create table ab4 ( vno integer)

insert into ab4 values(1)

create trigger a6

on ab4

for insert , delete, update

begin transaction

rollback

delete from ab4

select * from ab4

insert into ab4 values ( 2)

select * from ab4

We create a table ab4 and add one record only. We then create a trigger a6 where we rollback for any insert, update or delete statement. Now whenever we try to insert update or delete we get no error but no changes are seen in our database. The idea is that whenever we do not want the changes to be made to our database we simply rollback the changes.

Creating functions

create function f1()

returns int

begin

return(100)

end

SELECT f1()

'f1' is not a recognized function name.

select dbo.f1()

100

We use the sql create function to create a function called f1. We start with the value it returns in our case int and place the code of the function within a begin and end. In our case we have no code at all and we simply return a value 100 using the return keyword. To call this function we use the select statement and get an error. The reason being that we need to specify the name of the user dbo along with the function name. A function can we used anywhere and everywhere.

create procedure c1

print dbo.f1()

100

We create a stored procedure c1 where we print out the return value of the function f1. As we said earlier anywhere and everywhere.

create function f2(@a1 int , @a2 int)

returns int

begin

return(@a1+@a2)

end

select dbo.f2(amt,vno) from adummy1

We create a function f2 where we specify two parameters a1 and a2 of type int. We return the sum of these values. We now use this function f2 in a select statement passing the field names amt and vno as parameters. Thus sp’s and triggers and functions use the same coding principles.

We have not seen the end of functions as we can return a table data type. The possibilities are unlimited at what we can do.

CREATE FUNCTION f3 (@amt2 int)

RETURNS @t1 TABLE (vno1 int, amt1 int)

BEGIN

DECLARE @tt1 TABLE (vno2 int, amt2 int)

INSERT @tt1

SELECT vno , amt FROM adummy1 WHERE amt > @amt2

INSERT @t1

SELECT * FROM @tt1

RETURN

END

select * from dbo.f4(10)

vno1 amt1

2 20

3 30

The last example for functions. We create a function f3 that returns a table t1 which has two fields vno1 and amt1. The function f4 also takes a parameter amt2 and int. We first create a table tt1 by specifying the data type as table and specifying the field names.

We use the insert statement to fill up this table tt1 by using the select statement which uses the amt2 parameter passed to this function. We then once again copy all the data from tt1 to t1. This is not necessary but what we are trying to say is that we could iterate though the table tt1 and make whatever changes w want and then copy the data to t1.

The return statement knows that the table t1 has to be returned and we do not specify its name with the return keyword. We can iterate using the system variable @@ROWCOUNT which gives us the number of records copied into table tt1. Now that we return a table and not a value we can use this function f3 only in those places where we require a table.

Thus select dbo.f3(10) will give us an error. The above example simply shows us that a function can return a table which in turn can be used in place of a table name in a select select. Imagine what complex code we can now write. Thus being a dba means knowing programming if you want to be the best.