Simple TSQL - Calculating Age in TSQL

This is a stored procedure for calculating the age in Number of years and days

create procedure calculate_age
@birthday datetime
as
declare @yeardiff int
declare @daydiff int
declare @result varchar(100)

set @yeardiff = datediff(yy,@birthday,getdate())

if dateadd(yy,@yeardiff,@birthday) > getdate()
set @daydiff = datediff(dd,dateadd(yy,@yeardiff-1,@birthday),getdate())
else
set @daydiff = datediff(dd,dateadd(yy,@yeardiff,@birthday),getdate())
set @result = 'The age is '+convert(varchar,@yeardiff)+' years and '+ convert(varchar,@daydiff)+' days '

select @result

System Tables : Part 1

/* Security Context of the Database */

--sql logins for the Database
---------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntname = 0

-- windows logins for Database
--------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntname = 1

--windows groups for the Database
--------------------------------
select uid,status,name,convert(varchar(20),createdate)as Createddate ,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntgroup = 1

--Finding the Sql users not having logins
------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where issqluser = 1

--Find the useraccounts having dbaccess
----------------------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where hasdbaccess = 1


--Stored Procedures
--Displaying the Name and Text of the Stored Procedures.
-----------------------------------------------------------------------------
select so.id,so.name,sys.text from sysobjects sojoin syscomments sys on so.id = sys.idwhere so.xtype ='p'and so.base_schema_ver = 0

--Displaying the Stored Procedures which are Encrypted.
----------------------------------------------------------------------------
select so.id,so.name from sysobjects sojoin syscomments sys on so.id = sys.idwhere so.xtype ='p'and so.base_schema_ver = 0and sys.encrypted = 1

--Displaying all the Columns for a particular Table in T-SQL
---------------------------------------------------------------------------

select sys.name from syscolumns sys join sysobjects soon sys.id = so.id where so.name ='Orders'

--Displaying the Columns in the Table having the Identity Columns
----------------------------------------------------------------------------------
select name from syscolumns where status = 0x08

General Sql Server Backup in my organisation

The Sql Server Backup Plan

a.Scheduling a FullDiskBackup of the Databases at 8:00 AM in the Morning (before the office starts)
b.Doing a Transactional log for every 3 hours for each database applications alternatively one hour
Eg : Database Application A - 9:00 AM ; 12:00AM ; 3:00 PM;6:30PM
Database Application B - 9:30 AM ; 12:30AM ; 3:30 PM ;6:30PM

As the Number of database applications increase we try to decrease the time slot between the 2 databasesThe entire Disk Backup is then Copied back to the Main Veritas Backup System(Regular Backup System)

We take the Daily Backup tapes with us and we store the weekly Backups in a Remote LocationWhen the Month Ending tapes are ready we store these tapes and get back the weekly backups for the Next weekly usage

Performance :Sql server Performance on Windows 2003

Sql server has far better performance in Windows 2003 than in windows 2000.

1.If you are running in an Sql server dedicated server you can remove all the other services that are not needed for the Sql server.

Some of the services may be SmartCard,SmartCardHelper,QOS ,Clipbook,Telnet.

It all depends on what application the Sql Server are using

2.By Applying QOS across the servers in the organisation we can increase the Network Bandwidth for the Sql Server users thereby utilising the bandwidth efficiently across all other servers.

3.Most companies use the Windows environment so its better to remove all the other protocols except TCP/IP

4. Windows 2003 server has included some good command line performance related tools to better monitor performance

They are

a. Logman
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx?mfr=true

b.relog
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx?mfr=true

c.tracerpt
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_tracerpt.mspx?mfr=true

d.typeperf
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx?mfr=true

Simple TSQL Scripting -2 : Generating Fibonacci Series

Fibonacci series

The Fibonacci Series is a sequence of numbers first created by Leonardo Fibonacci (fi-bo-na-chee) in 1202. It is a deceptively simple series, but its ramifications and applications are nearly limitless. It has fascinated and perplexed mathematicians for over 700 years, and nearly everyone who has worked with it has added a new piece to the Fibonacci puzzle, a new tidbit of information about the series and how it works. Fibonacci mathematics is a constantly expanding branch of number theory, with more and more people being drawn into the complex subtleties of Fibonacci's legacy

1 1 2 3 5 8 13 21


create procedure vic_fibonacci(@num int)as

declare @a int ,@b int,@c int
set @a = 1
set @b = 1
while @num >0
begin
set @c = @a + @b
select @c
set @a = @b
set @b = @c
set @num = @num - 1
end

exec vic_fibonacci 7

Sql Server ShortCuts -1:Executing a stored procedure whenever sql server starts

I found this interesting option under the Sql server 2000 Enterprise manager.

If a DBA wants to execute any system procedure when the Sql Server starts
go to

Enterprise Manager-->Databases-->Master-->StoredProcedures

Right Click any system stored procedure and there is an option below which indicates

Execute whenever Sql Server Starts

and check it

To check whether any procedures are running when sql server starts execute the following

USE master
GO
SELECT
name
FROM
sysobjects
WHERE
OBJECTPROPERTY(id, 'ExecIsStartup') = 1

or

USE master
GO
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsStartUp') = 1

Simple TSQL Scripting -1: Generating Prime Numbers

Create the procedure as follows:

create procedure primegen(@k integer)
as
declare @a int
declare @b int
declare @p int
declare @i int
declare @n int
set @a =2
set @n =1
set @p = @k
set @i =2
print @a
while @n < @p - 1-- Checking for the number of terms
begin --1
set @i = 2
set @a = @a + 1 --incrementing the prime number
while(@i < @a) -- checking whether the dividend is less than the divisor
begin --2
if(@a % @i) <> 0 --- whether the modulo is not zero,if not zero increment i for the next dividend
begin --3 --and check the same modulo..Repeat it till the dividend one less than the divisor
set @i = @i +1
if @i = @a --check whether all the dividends have been divided whose modulo is not zero and print the
begin -- 4 number which is prime
print @a
set @n = @n + 1 -- increment the number of prime terms
break
end --4
end--3
else
begin--5 --if its modulo is zero increment the dividend and come out of the if modulo checking loop
set @i = @i + 1
break
end--5
end--2
end--1

--Execute the First 100 prime numbers

Exec primegen 100

Using System Tables 1 : Oh!!! which processes are taking most CPU time

Master...sysprocesses

This command gives you an inside depth of which process is running the CPU process very high

SELECT * FROM master..sysprocesses WHERE status = 'runnable' ORDER BY cpu desc

When you get the spid you can get the input command from the following

DBCC INPUTBUFFER(spid)

which gives you what statement the end user has been running.

KILL spid would close or kill the process automatically

The other notable columns in this table are

• nt_username
• loginname
• hostname

About me

Hi,My Full name is Vivekanand Serou.Friends and family call me Vic.I am currently working as a Database Administrator in Uswitch(DBA). I will be posting the various stuff i learnt in IT as well as in Relational Databases.
If you have any comments you can mail me to svicky9@yahoo.com