Will be back in August !

As you already know that taking off from your busy life is very essential so I am taking off :) Going on Holidays!!
Will be back after 1 month so please visit my blog again for more new stuff in August!

Thanks for taking time to read my blog.

Namwar Rizvi

Arrays in SQL Server 2005

Arrays are one of the most popular data structures for several solutions. Unfortunately, SQL Server does not support arrays in TSQL. As an alternative, you can use Table variable or temporary tables as a work around for arrays.
During my research about various possible approaches for array implementation, I found following article very interesting and informative, I will suggest to all my readers to read this.
Article is available here as Arrays in SQL Server 2005

Quick delete technique for huge tables

Deleting a huge table like purging old transaction records of millions of rows takes quite a lot of time and since DELETE is a fully logged operation therefore, if something goes wrong during deletion the whole process will be rolled back which itself is a time consuming process.
SQL Server 2005 enhanced the functionality of TOP operator and it can be used with Data modification statements like INSERT,UPDATE and DELETE.

By using the TOP operator, you can divide your time consuming delete operation into the batches of small deletions like 5000 records per deletion. The benefit of this approach is:

– Log space will be re-used again and again which will keep your log file size in control.
– If operation fails at any time then only last batch will be rolled back and you can start purging again quickly after recovering from the failure.

Following is the TSQL code to perform batch deletions:

Use AdventureWorks
Go

–Create a copy of Sales.SalesOrderHeader as a test table
Select *
into testOrderHeaderTable
from Sales.SalesOrderHeader

–Delete 5000 Rows at a time
While 1=1
Begin
Delete Top(5000) testOrderHeaderTable

—Check for exit condition
If @@rowcount <5000 break
End

–Remove the test table
Drop table testOrderHeaderTable

Statement Level Recompilation–Excellent new feature of SQL Server 2005

Recompilation of the Query plan sometimes harms your server performance more than you expect. Prior to SQL Server 2005, SQL Server had the algorithm which may decide to recompile the full stored procedure because of just one statement. This strategy of full recompilation makes DBA life difficult because he/she needs to investigate the full stored procedure and sometimes may break it into separate small stored procedures to reduce recompilations.

SQL Server 2005 has a great feature of statement level recompilation. This feature automatically detects the statements needs to be recompiled and only these statements will be recompiled resulting less CPU cycles and no need of breaking the stored procedure into sub-stored procedures.

An excellent article about recompilations is available at Microsoft here

T-SQL DATETIME : Last Day of the Month

Getting the last day of the month, for the given date, by TSQL is bit tricky. Following user defined function provides you a handy general purpose function to get the last day of the month.
It gets the last day of the month as follows:
For example we have a given date 17-March-2007

1. Add a month in the given date by using DateAdd function. It will become 17-April-2007
2. Get the Day part of the date from the above step. It is 17
3. Subtract number of days of the second step from the date of step 1. It will become 31-March-2007
4. Resulting date in step 3 is our answer.

Following is the TSQL code of the function

Create function
udf_GetLastDayOfTheMonth(@m_GivenDate datetime)
returns datetime
as
Begin
Return dateadd(day,-1* day(dateadd(month,1,@m_GivenDate)),dateadd(month,1,@m_GivenDate))

End

Vardecimal Storage Format

In SQL Server 205, Microsoft has introduced a new storage format for storing decimal values. This format is called vardecimal. This format addresses the issue of inefficient space management for the decimal and numeric data types.
SQL Server uses the format (p,s) to decide about the space to allocate for a numeric or decimal data type where
p=precision or digits before decimal and
s=scale or digits after decimals

In simple words, If you have specified high precision but you are storing small numbers then remaining space will still be allocated and therefore, will be wasted because small value does not require all bytes to represent its value.

Vardecimal is the solution of this. It allocates only as much space as needed by the value to be stored. In simple words we can say Vardecimal does the same for decimal or numeric data types what varchar does for char data types Vardecimal is not the data type instead it is a table option.
For detailed discussion of Vardecimal please refer Vardecimal on MSDN

Changing the Object Schema in SQL Server 2005

In SQL Server 2000 there was no concept of SCHEMA. The ownership of the given object was maintained directly through user. Changing of the owenership could be performed by using the system stored procedure called sp_changeobjectowner.

Now, in SQL Server 2005 scenario has been changed. Objects are no longer directly linked to the user; instead they are linked to the SCHEMA. Any user who has the proper rights to the required SCHEMA can access the objects of it.

So now the question is what is the replacement of sp_changeobjectowner in SQL Server 2005? It is ALTER SCHEMA command.
You can change the schema of an object by using ALTER SCHEMA command. This command takes two arguments:

1. Target Schema name you want to transfer the object to
2. Object Name which you to transfer

Following is an example of ALTER SCHEMA command

ALTER SCHEMA
siteadmin —Target Schema
TRANSFER
dbo.testTable –Object to be transferred

Please ensure that you have scripted all the permission of original object before transferring to new schema because permissions granted to original object e.g. dbo.testTable will be removed during transfer.

Finding all Managers of the given employee thourgh Recursive CTE

Common Table Expressions or CTE are not just for writing more manageable queries. They are lot more than this, recursion is one of the features you can now use by CTE based queries. Remember your school days! Recursion is the solution of many complex or lengthy algorithms like factorial, finding employee hierarchy, finding parent nodes, finding child nodes etc.

SQL Server 2005 CTE based recursive queries are native to the TSQL engine and are very efficient also. Recursive CTE query is composed of two parts:

1. Anchor query, this query starts the recursion or you can say provides seed row for the recursion
2. Recursive query, this query returns the result based on the join condition with the anchor query.

Non-Recursive condition:
As you may already know that recursion always need a non-recursive condition or exit condition otherwise it will go in infinite loop. In Recursive CTE, Non-recursive condition is implicit; it will automatically stop when last run will not return any row.

You can better understand this by running the following query which can be used to return all managers of the given employee.

Use AdventureWorks
Go
With OrgChart as
(
–Acnhor Query
Select EmployeeId,LoginId,ManagerId from HumanResources.Employee Where EmployeeId=4
Union all
–Recursive Query
Select E.EmployeeId,E.LoginId,E.ManagerId from HumanResources.Employee E
Inner Join OrgChart M –Joining with anchor member
On E.EmployeeId=M.ManagerId
)

–Returning all managers of EmployeeId 4
Select * from OrgChart

For detailed study of Recursive CTE please refer to the following resources:

Recursive CTE on MSDN
Excellent article for Recursive CTE on SQL Server Central

Extracting FileName through TSQL

Today I am sharing with you a utility UDF function to extract the file name from the given file path. This TSQL function also demonstrates the use of finding last occurance of a given character by using REVERSE function. Following is the TSQL code:

Create function dbo.udf_GetFileName(@m_FullFilePath varchar(255))
Returns varchar(50)

as

Begin

Return
Reverse(Left(Reverse(@m_FullFilePath),Charindex(‘\’,

Reverse(@m_FullFilePath))-1))

End

Now lets test this function:

Select dbo.udf_GetFileName(‘C:\Program Files\Adobe\Acrobat5.0\Help\ENU\ACROBAT.PDF’)

Result:
ACROBAT.PDF

SQL Server Performance Dashboard Reports

Out of many good things I love following two great features in SQL Server 2005

1. SQL Server Dynamic Management Views or DMVs
2. SQL Server Reporting Services or SSRS

SQL Server 2005 exposes too much information about its internal working for a DBA to better tune the server but this information is hidden in different dynamic management views, counters and other SQL Server 2005 components. If you really want to master SQL Server 2005, you must have a very good knowledge of dynamic management views or DMVs.
Plus, if you want to master Reporting Services 2005 i.e. SSRS with techniques like:

1. Charting
2. Linked Reports
3. Creating Reports Portal
4. Passing Parameters between reports etc.

then look no further and download SQL Server Performance Dashboard Reports from here.
These reports not just exposes SQL Server 2005 internal statistics in an excellent graphical manner, they also provide queries to find SQL Server statistics by DMVs. In addition with these queries, you will also learn how Reporting Services works. You will learn how you can implement graphs, linked reports etc. So don’t waste more time:):) Start downloading SQL Server Performance Dashboard Reports.