Stored procedure

What is stored procedure?

• Basically, it is a "module" or "process" that is written in SQL statement and it sits inside database. It is executable by the database server and within the monitor/management of the database server.

Will it run faster if we move the data processing codes from the application into stored procedure?

• Yes. It will be faster because the process is running within the database server. The application sends only one request to the database server and then wait for the response. This reduces the number of data transfer between the database server and the application.

Trigger

Reference:
http://www.mssqltips.com/tip.asp?tip=1591
http://www.mssqltips.com/tip.asp?tip=1380

Database design (I)

Some guidelines for the database design:

1. All tables must have the following fields to store the information of the record creator and the last update information.

- created_on datetime
- created_by nvarchar(255)
- modified_on datetime
- modified_by nvarchar(255)

2. All tables must have a primary key with 1 field only. This is to ensure that the record can be call out easily. (Note: this might violate the normalization design that you have learned from the text book!)

3. Use GUID type for the primary key type instead of integer type - this will ease the page navigation when you are putting the value in the navigation URL. Not easy to be hack by the "normal" user. This also reduce the chance for the user to create "GUID" value manually and insert the record to the table.

4. Reduce the use of self-referencing table design unless you have no other option - this is to simplify the SELECT statement that you are going to write especially when you are developing reports.

5. For generating report, the data should be stored in the predefined reporting table. This splits the development job from the application coding and move the data extraction process into SQL server by using stored procedure. It eases the testing on the data extraction and also speed up the application development (ie., parallelly doing the job).

Get the number of years, months & days for the given 2 dates

declare 
 @dt1 datetime,
 @dt2 datetime,

 @yr int,
 @mth int,
 @day int,

 @yr_count int,
 @mth_count int,
 @day_count int,

 @month_end datetime,
 @month_begin datetime

-- test cases
select 

-- 30 days  <== same yr/month
--@dt1 = '20101202', @dt2 = '20101231' 

-- 1 month <== same yr/month
--@dt1 = '20101201', @dt2 = '20101231'    

-- 1 year.<== same yr & diff mth, start date is 1st.
--@dt1 = '20100101', @dt2 = '20101231'    

-- 2 month, 25 days<== same yr & start date is 1st.
--@dt1 = '20100101', @dt2 = '20100325'   

-- 1 month, 1 day <== diff yr & diff mth
--@dt1 = '20101202', @dt2 = '20110102'   

-- 3 months, 0 day. <== same yr & diff mth
--@dt1 = '20100502', @dt2 = '20100801'   

-- 1 month, 30 day   <== same yr & diff mth
@dt1 = '20101102', @dt2 = '20101231'   

-- 2 months, 0 day   <== diff yr & diff mth
--@dt1 = '20101102', @dt2 = '20110101'   

-- 1yr, 2 months, 0 day   <== diff yr & diff mth
--@dt1 = '20101102', @dt2 = '20120101'   

select 
 @dt1 as 'start date',
 @dt2 as 'end date'

-- get all the differences for further analysis
select
 @yr_count = datediff(year, @dt1,@dt2),
 @mth_count = datediff(month, @dt1,@dt2),
 @day_count = datediff(day, @dt1, @dt2)

-- see if we should retain the year counter.
if (@yr_count - 1 >= 0)
begin
 select @yr_count = @yr_count - 1 
end

-- ensure that the month counter is less than 12.
if @mth_count % 12 <> 0
begin
 select @mth_count = @mth_count % 12
end

-- get first day of the month for the 2nd date.
select
 @month_begin = cast(year(@dt2) as varchar(4)) 
    + case when month(@dt2) > 9 then cast(month(@dt2) as varchar(2))
     else '0' + cast(month(@dt2) as varchar(2))
     end
    + '01'

-- get the last day of the month for the 2nd date.
select 
 @month_end = dateadd(day, -1, dateadd(month, 1, @month_begin))

if (@dt1 = @month_begin) and (@dt2 = @month_end) 
begin
 select 
  @mth_count = @mth_count + 1,
  @day_count = 0

--select @month_begin, @month_end
end
else 
begin
 if @dt1 > @month_begin
  select @month_begin = @dt1

--select @month_begin, @month_end, @dt2

 -- if the end date is the last day of the month
 -- and the start date is the first day of the month,
 -- the day counter will be zero and the month counter increase by 1.
 if (@dt2 = @month_end) and day(@dt1) = 1
 begin
  select 
   @mth_count = @mth_count + 1,
   @day_count = 0

--select @day_count

 end
 else
 begin
  -- update the day counter.
  select @day_count = datediff(day, @month_begin, @dt2) + 1 

 end
end

if  (@mth_count <> 0) and (@mth_count % 12 = 0)
begin

 declare @i int
 select  @i = @yr_count +(@mth_count / 12)

 select 
  @mth_count = 0,
  @yr_count = @i

end

if month(@dt1) <> month(@dt2)
begin

 if day(@dt1) <> 1 and @day_count > 0
  select @day_count = @day_count - 1

end

-- ----------------------------------------------
select
 @yr_count as '# of years',
 @mth_count  as '# of months',
 @day_count as '# of days'

Backing up the database using dump device

Once you have created the dump device, the next nice thing to have is to create a stored procedure that is able to backing up the database to the dump device based on current week day. You have to execute the following stored procedure in SQL Server Management Studio. Then, create a job in SQL Agent and setup the schedule properly.

create proc mgt_backup_db (
 @db_name sysname
)
as
begin

declare @d int

select @d = datepart( weekday, getdate())

if (@d = 2)
 backup database @db_name
 to db_mon_bak
 with init

else if (@d = 3)
 backup database @db_name
 to db_tue_bak
 with init

else if (@d = 4)
 backup database @db_name
 to db_wed_bak
 with init

else if (@d = 5)
 backup database @db_name
 to db_thu_bak
 with init

else if (@d = 6)
 backup database @db_name
 to db_fri_bak
 with init

else if (@d = 7)
 backup database @db_name
 to db_sat_bak
 with init

end

go

Create 7 days cycle backup devices

This is the stored procedure that creates 7 days cycle backup devices. The idea is to create a maximum of 7 backup files and recycle it every week. This backup concept is handy when you need a simple backup.

create proc mgt_create_backup_device (
 @db nvarchar(255),
 @folder nvarchar(100) 
)
as

begin

declare @device_name sysname,
 @file_name sysname

 select @device_name = @db + '_mon_bak', 
   @file_name = @folder + @db + '_mon_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_tue_bak', 
   @file_name = @folder + @db + '_tue_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_wed_bak', 
   @file_name = @folder + @db + '_wed_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_thu_bak', 
   @file_name = @folder + @db + '_thu_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_fri_bak', 
   @file_name = @folder + @db + '_fri_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sat_bak', 
   @file_name = @folder + @db + '_sat_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

 select @device_name = @db + '_sun_bak', 
   @file_name = @folder + @db + '_sun_bak.bak'
 exec sp_addumpdevice 'disk', 
       @device_name, 
       @file_name

end
go

View the database size

To view the current database size, execute the following TSQL:

declare @db sysname
select @db = db_name()
exec sp_helpdb @db