Monday, February 15, 2010

RENAME objects

Here in this Blog I have mentioned how to rename a object in the database.

while renaming a database name It should be changed to single user.

Renaming a table,Stored procedure,function,view,trigger uses same the way sp_renamedb
how ever renaming stored procedure,function,view and trigger is not a good option.

SQL server will not update sys.syscomments table while renaming objects.

so when ever we use sp_helptext to retrieve a Stored procedure or a function.
you can clearly see that the Stored procedure or the function name is a old one.

If you want to rename a object better drop and create again.

see for samples below..

Here we GO...!!!

Renaming a Database

use master
go
exec sp_dboption DBname_OLD ,'Single User',True
go
exec sp_renamedb 'DBname_OLD','DBname_NEW'
go
exec sp_dboption DBname_NEW,'Single User',False


Renaming a Table
sp_rename 'Tablename_OLD','Tablename_NEW'

Renaming a column
sp_rename 'Tablename.column_OLD','column_NEW','column'

Renaming a Stored Procedure,Function,View,Trigger
sp_rename 'Old_Name','New_Name'

In the below image I have created a procedure named SPTEST and renamed to SPTEST_NEW
But still when I use sp_helptext to retrieve the procedure it shows us the old Procedure name.
this is because sys.syscomments table has not been updated.



here either we can drop and create the procedure or we can alter the procedure from the SSMS window by right click on it.


Tuesday, February 9, 2010

Select TOP N Records

Most times while retrieving records from database we like to fetch top N records
There are couple of ways to do it..
here in this blog I have tried with

1) TOP
2) ROWCOUNT
3) ROW_NUMBER()

out of all the three I could prefer to use TOP.
ROWCOUNT will take priority if its specified smaller than TOP.
ROWCOUNT cannot be used in Functions.
ROW_NUMBER() can be used to fetch top N records with partition.

Here We Go..!!

USE AdventureWorks

Using TOP:

SELECT TOP 10 * FROM Person.StateProvince

with variable:

DECLARE @N INT
SET @N = 10
SELECT TOP (@N) * FROM Person.StateProvince

Using ROWCOUNT:

SET ROWCOUNT 10
SELECT * FROM Person.StateProvince

--TO RESET THE ROWCOUNT
SET ROWCOUNT 0

Using ROWNUMBER:

SELECT * FROM
( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM
Person.StateProvince
) TAB
WHERE ROW_NUM <=10

Monday, February 8, 2010

Ranking Functions

Ranking functions are used to partition the records based on the needs.
There are 4 types of ranking functions available in SQL server.

ROW_NUMBER()
Used to generate identity numbers for each rows.
RANK()
Rank function will partition the record but skip the numbers.
DENSE_RANK()
This function will partition the record with out skipping the numbers.
NTILE()
This will equally divide the record set into number of parts based on the number which we specify.


Lets use all those things in a single query to see the different..

In the below example we can partition the records by giving any of the column
Name (Multiple columns can also be given with comma seperator), I have given the integer value 1 to get the identity rownumbers

Here We GO..!!!

USE ADVENTUREWORKS

GO

SELECT LOGINID,
TITLE,
ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY TITLE) AS ROWNUM,
RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS RANK,
DENSE_RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS DENSERANK,
NTILE(4)OVER(PARTITION BY 1 ORDER BY TITLE) AS NTILE
FROM HUMANRESOURCES.EMPLOYEE

Result window:

Sunday, February 7, 2010

Calculating Running total

Here in this Blog I have shown how to calculate Running totals and compared two ways of caculating it.

I have tried with INNER JOIN and CTE. Both these methods brings the correct result,
But I could clearly see that CTE took only 27% of the total execution time compared to INNER JOIN, INNER JOIN took remaining 73% to bring the same result.

I have provided the Example below....

Here We Go...!!!!

sample table for testing.

CREATE TABLE PRO (ID INT IDENTITY(1,1),PRO_NAME VARCHAR(100),PRO_QTY INT)

INSERT PRO VALUES ('A',10)
INSERT PRO VALUES ('B',20)
INSERT PRO VALUES ('C',30)
INSERT PRO VALUES ('D',20)
INSERT PRO VALUES ('E',10)
INSERT PRO VALUES ('F',50)
INSERT PRO VALUES ('G',5)

METHOD 1:
using INNER JOIN


SELECT A.ID,A.PRO_NAME,A.PRO_QTY,SUM(B.PRO_QTY) as RUNNING_TOTAL
FROM PRO A
INNER JOIN PRO B
ON (B.ID <= A.ID)
GROUP BY A.ID,A.PRO_NAME,A.PRO_QTY

METHOD 2:
CTE


;WITH CTE(ID,PRO_NAME,PRO_QTY,RUNNING_TOTAL)
AS
(
SELECT ID,PRO_NAME,PRO_QTY,PRO_QTY AS RUNNING_TOTAL FROM PRO
WHERE ID =1
UNION ALL
SELECT P.ID,P.PRO_NAME,P.PRO_QTY,P.PRO_QTY+C.RUNNING_TOTAL FROM PRO P
JOIN CTE C ON C.ID+1 = P.ID
)
SELECT * FROM CTE

DROP TABLE PRO

Results from both methods.



Execution Plan of both Methods . click on the Image to view

Saturday, February 6, 2010

Time alone in SQL server

Most the times we need to format the Datetime column while displaying the record,
although formatting date in Backend is not a good practice, because it changes the datatype of the Date to varchar

We can split datetime in number of formats.
Microsoft has listed the possible format to get date alone in the below link..

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Let me go with time part alone,
Below I have provided 4 different time formats.

This codeworks fine in SQL server 2005

Here We GO...!!!

To get the time part alone from given date

HH:MM:SS
SELECT CONVERT(VARCHAR, GETDATE(), 108)
Result : 20:43:28

HH:MM:SS.MS
SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 121) ,12)
Result : 20:43:28.327

HH:MM(AM/PM)
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),6)
Result : 8:43PM

HH:MM:SS:MS(AM/PM)
SELECT RIGHT(CONVERT(VARCHAR(30), GETDATE(), 109),13)
Result : 8:43:28:327PM

Wednesday, February 3, 2010

Backp and Restore Dates

Some times we need to know when the database had its last backup or restore

msdb Database holds the details of all the Backups and Restores happened on a server.

backupset table holds the details of Backups
restorehistory table holds the details of Restores.

Below scripted code will bring the list of backup and Restore for the given database.

Here we GO..!!

Code to list Backup.
select database_name,backup_finish_date ,user_name from msdb..backupset
where database_name='DatabaseName'
order by backup_finish_date desc

Code to list Restore.
select destination_database_name,restore_date,user_name from msdb..restorehistory
where destination_database_name='DatabaseName'
order by restore_date desc

Tuesday, February 2, 2010

Table Definitions from a Database.

Some times we might need all the information about tables and columns in a Database.

Below scripted Code Defines the properties of all the columns from all tables, Foreign key specifications as well.

One can export the result to an Excel for Database Design overview.

This code works fine in SQL server 2005.


Here we GO...!!!!

/*****Lists all the Primary key and Indexs from all the Table*****/

select so.object_id,
object_name(so.object_id)as tbl_name,
is_unique ,
is_primary_key,
sc.name,
colid ,
i.type_desc
into #primary
from sys.objects so
join sys.columns sc
on sc.object_id=so.object_id
join sysindexkeys ik
on ik.id=so.object_id and ik.colid=sc.column_id
join sys.indexes i
on i.object_id=ik.id and i.index_id=ik.indid
where so.type='U'
order by 1,4

/*****Table to Store basic info for all the tables*****/

Create table #TableList
(
tablename nvarchar(100),
table_id int,
ColumnName nvarchar(100),
column_id int,
DataType nvarchar(100),
max_length int,
[precision] int,
[scale] int,
is_nullable int,
is_identity int,
is_computed int,
default_object_id int,
is_unique int,
is_primary_key int
)
insert into #TableList
select so.name TableName,
so.object_id,
sc.name as ColumnName,
sc.column_id,
sst.name as Datatype,
max_length,
precision,
sc.scale,
is_nullable,
is_identity,
is_computed,
default_object_id,
is_unique=(select is_unique from #primary where tbl_name=so.name and name=sc.name and is_unique=1 and is_primary_key=0),
is_primary_key=(select is_primary_key from #primary where tbl_name=so.name and name=sc.name and is_primary_key=1)
from sys.columns sc
join sys.objects so on sc.object_id=so.object_id
join sys.systypes sst on sc.user_type_id=sst.xusertype
where so.type='U'
ORDER BY object_name(sc.object_id),sc.name

/*****Table to store Foreign key informations.*****/

Create table #ForeignKeys (
[Table_id] int,
TableName nvarchar(100),
Column_id int,
ColumnName nvarchar(100),
ForeignKey nvarchar(100),
ReferenceTableName nvarchar(100),
ReferenceColumnName nvarchar(100),
ReferenceObjectId int,
ReferenceColumnDatatype nvarchar(100),
ReferenceColumnMax_length int
)
Insert into #ForeignKeys
SELECT
f.parent_object_id,
OBJECT_NAME(f.parent_object_id) AS TableName,
fc.Parent_column_id,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
f.name AS ForeignKey,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName,
so.object_id,
sst.name as Datatype,
sc.max_length
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
inner join sys.objects so on f.referenced_object_id = so.object_id
inner join sys.columns sc on f.referenced_object_id = sc.object_id and fc.referenced_column_id = sc.column_id
inner join sys.systypes sst on sc.user_type_id=sst.xusertype
order by OBJECT_NAME(f.parent_object_id)

/*****Displays Details of all the Tables from a database.*****/

Select a.tablename,
a.ColumnName,
a.DataType,
a.max_length,
a.[precision],
a.[scale],
a.is_nullable,
a.is_identity,
a.is_computed,
a.default_object_id,
a.is_unique,
a.is_primary_key,
b.ForeignKey,
b.ReferenceTableName,
b.ReferenceColumnName,
b.ReferenceColumnDatatype,
b.ReferenceColumnMax_length
from #TableList a
left outer join #ForeignKeys b on a.table_id = b.table_id and a.column_id = b.column_id
order by a.Tablename,a.ColumnName

drop table #TableList,#ForeignKeys,#primary

--Thanks to Ram and Karthikeyan for Sharing this...