Thanking Mail from dotnetspider moderator

OOPs, Today morning i got a thanking mail from dotnet spider moderator for helping them. Below is the mail,

From: asha mathews [mailto:ashamathew19@yahoo.co.u]
Sent: Tue 4/29/2008 8:03 AM
To: Venkatesan Prabu J
Cc: webmaster
Subject: message from .net spider

Hello Venkatesh,
Good morning, Thanks for supporting dotnetspider in tracing copied contents and encouraging genuine articles. The copied article which you have reported is deleted. We are looking forward to get the same help and support from you.
Thanks
Asha mathews
Web Master @ dotnetspider

Dynamically naming the table

I've got a peculiar problem to create tables with dynamic table name.

Problem statement:

Consider an employee database in which i have to create table for each employee on each date. The table name should be given dynamically at run time. I want to create based table name based on the input.

Solution statement:

Create a stored procedure with input parameters as table name.
Construct a dynamic query for creating the table.
Execute the dynamic query.

CREATE PROCEDURE TableNameAssigningProcedure (@EmployeeTableName varchar(100)) AS BEGIN DECLARE @CMD nVARCHAR(100),@AppendString VARCHAR(8) SELECT @AppendString = CONVERT(VARCHAR(8), GETDATE(), 12) SELECT @CMD=N'CREATE TABLE '+ @EmployeeTableName+ @AppendString + '(ID INT,NAME VARCHAR(50))' PRINT @CMD exec SP_EXECuteSQL @CMD END

EXEC TableNameAssigningProcedure 'SANTHI'

SELECT * FROM "Newly created table"

Regards,
Venkatesan Prabu . J

Cleared my MCITP exam

On 25th April, i have cleared my microsoft exam 70-444 with 98.4%

Checked with microsoft site for MCITP degree holders. I am one among the 4,433 candidates to complete this certification world wide. Good news naaa...........
For getting MCITP, we have to write the below two exams,
70-443 - PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
70- 444 - PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

Happy learning!!!
Regards,
Venkatesan Prabu . J

Download Adventureworkdw database

I have got a query regarding installation of adventureworksdw.

Please check the below link provided by Microsoft to install these sample databases,

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

Download Adventure works Database,

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

Regards,
Venkatesan Prabu . J

Subquery Vs Joins

Got a request from dotnet spider user regarding query performance. In the below query, which one is best?

• select * from table1 where id in (select id from table2)
• select a.* from table1 a,table2 b where a.id=b.id

1. Subqueries are usually slower when compared to the ordinary SQL queries.
2. Two seperate execution happens in the first query, first the subquery will execute to fetch the data from table2, second the comparison with the data from the table1 happens to obtain the exact output.

Regards,

Venkatesan Prabu . J

Retrieving information about last backup date of all databases in the server

We can get the general information about the databases from master.sys.sysdatabases

select * from master.sys.sysdatabases

For every backup process an entry will be added into the msdb.dbo.backupset. Below is the query to retreive the backup details.

select * from msdb.dbo.backupset

To retrieve the backup information, we can query backupset table from msdb database.

select Database_name, COALESCE(Convert(varchar(20), MAX(backup_finish_date), 101),'Backup Not Taken') as LastBackUpTakenDate, COALESCE(Convert(varchar(20), MAX(user_name), 101),'NA') as BackupTakenUser from msdb.dbo.backupset GROUP BY Database_name

Else, we can go for joining two tables(one from master database and another from msdb database). Below is the query to achieve the same,

SELECT Tab1.Name as DbName, COALESCE(Convert(varchar(20), MAX(Tab2.backup_finish_date), 101),'Backup Not Taken') as LastBackUpTakenDate, COALESCE(Convert(varchar(20), MAX(Tab2.user_name), 101),'NA') as BackupTakenUser FROM sys.sysdatabases Tab1 LEFT OUTER JOIN msdb.dbo.backupset Tab2 ON Tab2.database_name = Tab1.name GROUP BY Tab1.Name ORDER BY Tab1.Name

Sample Output:











Happy learning!!!
Regards,
Venkatesan Prabu. J

Atlast Cleared my MCP 070-443 exam

I am very happy to share this info, i've cleared one of the MCITP paper

070-443 - Designing a database server infrastructure by using microsoft sql server 2005.

It's little bit tough to clear this exam. However, i have managed to hit 940 out of 1000.

Regards,
Venkatesan Prabu . J

Boosting replies on my blog

Sure, I will make it out from my next posts.

Regards,

Venkatesan Prabu . J

T-SQL Challenges - Part 3 - Temporary tables

Digging into the logics behind Temporary tables in sql server:
I have noticed many interesting articles on temporary table in sql server.
But, this article provides some unknown facts on sql server.
Assuring you an interesting fact on sql server temporary table.
Know about Temporary table:
Temporary tables are special tables created in temp database. It has a prefix '#' for local temporary table
or "##" for global temporary table.
The temporary table will get deleted if the session is expired or the server restarts.
Local temporary table: This can be used with in a specific object and cannot be
shared with other system objects. Each table is associated with a specific session id
and we cannot use it in another session.
For ex: I am creating a temporary table A in the stored procedure SP1, If i try to
use the same temporary table in another stored procedure SP2. It won't work because
its not in this specific scope. To recover this problem, we have to Global temporary table.
Global temporary table: Its nothing but a global table accessed by different
system objects.
Ok, am stopping all those basics and lets enter into some R&D work,
I had a situation to construct a temporary table using dynamic SQL. OOPS!! am finding some
strange problem to achieve it. Let's see the code,

Code Snippet

declare @string varchar(1000) set @string = 'SELECT * INTO #TempTable FROM dbo.SAMPLE' exec (@string) SELECT * from #TempTable

I am getting the following error,
"Invalid object name '#TempTable'."
I wondered to see this message and started checking the syntax, but everything is
ok and i don't find any way to fix this problem.
I've tried with ordinary table,

Code Snippet

declare @string varchar(1000) set @string = 'SELECT * INTO TempTable FROM dbo.SAMPLE' exec (@string) SELECT * from TempTable

OOPS, its working fine.
But my business functionality forces me to create a temporary table. I don't find
any other way to go,

Code Snippet

declare @string varchar(1000) create table #TempTable(id int) set @string = 'insert INTO #TempTable select id FROM dbo.SAMPLE' exec (@string) SELECT * from #TempTable

Atlast, i got some other way to achieve the same. But, i dont know what's the exact problem lies behind.
Digging towards it, i found some other way.
I resolved the problem by using global temporary table instead of local temporary table
It's working fine.

Code Snippet

declare @string varchar(1000) set @string = 'SELECT * INTO ##TempTable FROM dbo.SAMPLE' exec (@string) SELECT * from ##TempTable

Solution statement:
After a thorough search, i found that the problem lies with the exec statement.
Exec statement will try to create a seperate session and my local temporary table
won't lie in this newly created session.
Interesting right!!!!!
Happy learning.
Regards,
Venkatesan Prabu . J

T-SQL Challenges - Part 2

Query Problem:
Got this problem from MSDN user in microsoft site. Wish to solve this problem.
Is it possible to append the result sets as a single column or result set seperated by comma.

Table Structure

Name Age venkat 10 arun 20 prabu 30

Output:
venkat,10,arun,20,prabu,30
Sample Query to solve this problem:

Code Snippet

create table VenkatSampleTable(name varchar(100),age int) insert into VenkatSampleTable values ('venkat',10) insert into VenkatSampleTable values ('arun',20) insert into VenkatSampleTable values ('prabu',30) declare @values as varchar(500) set @values = '' select @values = @values + name + ','+cast(age as varchar(50)) + ','from VenkatSampleTable select substring(@values,1,len(@values)-1)

Its the easiest and fastest way to append the result set.
Happy learning!!!
Regards,
Venkatesan Prabu . J

T-SQL Challenges - Part 1 -Pivot Operator

Problematic statement:
Consider two table 1 and 2 and they are linked by a common column "ID".
Table1 had the details about the person and table2 list down the scores availed by the person.
My problem is to combine these two table listing out the person and their marks in each subjects.
Assumptions:
In table2, the rows are added as per the subjects, first mark will be considered as subject1,
second mark will be considered as subject2,Thrid mark will be considered as subject3.
Below are the sample inputs and expected outputs,
SQLTable1:

Code Snippet

ID StudentName 1 Venkatesan 2 Arun 3 Santhi 4 Vijay

SQLTable2:

Code Snippet

ID Marks 1 90 1 20 1 80 2 78 2 67 3 89 3 65 3 98 4 78 4 76 4 45

Expected output from the above tables

Code Snippet

ID StudentName Sub1 Sub2 Sub3 --------------------------------------------------------- 1 Venkatesan 90 20 80 2 Arun 78 67 NULL 3 Santhi 89 65 98 4 Vijay 78 76 45

Solution:
Microsoft sql server 2005 provides a very handy option of altering the rows to columns
using pivot property. To solve the above problem,
1. First we have to create a cte listing the rownumber partitioned by ID.
2. Pivot the data based on rownumber.

Code Snippet

--Creating table variables declare @SQLTable1 table (id int,Studentname varchar(100)) declare @SQLTable2 table (id int,Marks int) -- inserting the data into the table variables insert into @SQLTable1 select '1','Venkatesan' union all select '2','Arun' union all select '3','Santhi' union all select '4','Vijay' insert into @SQLTable2 select '1','90' union all select '1','20' union all select '1','80' union all select '2','78' union all select '2','67' union all select '3','89' union all select '3','65' union all select '3','98' union all select '4','78' union all select '4','76' union all select '4','45' --select * from @SQLTable1 --select * from @SQLTable2 --Creating rownumber in the cte ;with VenkatCTE as( select a.id,a.Studentname, b.Marks, row_number() over ( partition by a.id order by a.id) as rn from @SQLTable1 a inner join @SQLTable2 b on a.id=b.id ) --select * from VenkatCTE -- pivoting the rows in the cte select id,Studentname,[1] as Subject1,[2] as Subject2,[3] as Subject3 from VenkatCTE pivot ( min(Marks) for rn in ([1],[2],[3]) ) pvt order by id

Happy learning!!!
Regards,
Venkatesan Prabu . J

Table value parameters in SQL Server 2008

USER DEFINED TABLE TYPES IN SQL SERVER 2008:

It's a new T-SQL enhancements done in sql server 2008 which allows us to pass the table as parameters for our stored procedure. In the client server architecture we used to pass individual rows from the front end and its get updated in the backend. Instead of passing individual rows, Microsoft released a new enhancement referred to as table value parameters where they are providing a flexibility to pass the table as a parameter from the front end.

Features:

1. Processing speed will be comparitively very faster.
2. We have to declare the table as a Readonly one. DML operations cannot be done on the table.
3. From the front end we have to pass the data in the form of structures.
4. Reduces roundtrip to the server
5. Processes complex logics at a stretch in one single routine.

Code Snippet

-- Am trying to create a table "EmployeeTable" with three fields. CREATE TABLE EmployeeTable (id int, [name] varchar(100), designation varchar(100)) -- Creating a stored procedure "TableValuedSampleProc" to insert the rows. CREATE PROCEDURE TableValuedSampleProc (@id int, @name varchar(100),@designation varchar(100)) AS BEGIN insert into EmployeeTable values (@id,@name,@designation) END -- Executing the stored procedure EXEC TableValuedSampleProc 1,'VENKAT','LEAD ENGINEER' EXEC TableValuedSampleProc 2,'ARUN','DEVELOPER' EXEC TableValuedSampleProc 3,'SUBA','LEAD ENGINEER' SELECT * FROM EmployeeTable -- Am trying to create a table type "EmployeeTableType" CREATE TYPE EmployeeTableType AS TABLE (ID int, [name] varchar(100),designation varchar(100)) -- Creating the stored procedure in insert the data using Table type. CREATE PROCEDURE EmployeeTableTypeProc (@EmployeeTempTable EmployeeTableType READONLY) AS BEGIN INSERT INTO EmployeeTable SELECT * FROM @EmployeeTempTable END -- Building a temporary table type DECLARE @TempEmployee EmployeeTableType INSERT INTO @TempEmployee VALUES (1,'VENKAT','LEAD ENGINEER') INSERT INTO @TempEmployee VALUES (2,'ARUN','DEVELOPER') INSERT INTO @TempEmployee VALUES (3,'SUBA','LEAD ENGINEER') -- Executing the stored procedure by passing the temporary table type EXEC EmployeeTableTypeProc @TempEmployee -- Checking the existence of data SELECT * FROM EmployeeTable

Please provide me your valuable feedback.
Regards,
Venkatesan Prabu . J