Tuesday, October 31, 2006

Fine Tuning your Database Design in SQL 2005

today i found a nice artical on Fine Tuning your Database Design in SQL 2005

check it out @
http://www.simple-talk.com/sql/sql-server-2005/fine-tuning-your-database-design-in-sql-2005/

Friday, October 27, 2006

PIVOT dynamic column list

There was an intersting question posted on the .NET forum yday. it was about dynamically changing column list. In PIVOT you have to specify the output column list manually?
is it a bug ??

I found to achive it by doing hours of trying ....

DECLARE @colList VARCHAR(1024)

SELECT @colList = COALESCE(@colList + ',', '') + Subject FROM
(
SELECT DISTINCT '[' + Subject + ']' AS Subject
FROM StudentMarks
) AS StudentMarksColList

--get the column in the array format [maths], [science] ect ..
SELECT @colList


EXEC('SELECT * FROM StudentMarks
PIVOT
(
SUM(Marks)
FOR Subject
IN ('+ @colList +')
)AS p')

Wednesday, October 25, 2006

SQL Articals

Two greate articals by Ron Talmage


http://www.code-magazine.com/Article.aspx?quickid=060063
http://www.code-magazine.com/Article.aspx?quickid=060093

Wednesday, October 18, 2006

BCS Exam

I was buys last couple of days studing for the BCS exams .. I took 2 papers which are Information Systems and Core Paper. I nailed the BCS Information systems paper.. it was soo easy.. but the core paper was taugh.. I haven't given up the hopes still on it.

Wednesday, October 11, 2006

What's The Resolution?

should read this if you want to know what is dah above topic is all about..

http://www.code-magazine.com/Article.aspx?quickid=060083

Monday, October 09, 2006

Inserting way to insert data to the tabase

I found this code while surfing the web today. Concept is very intersting due to couple of resions.

1. Reduce round trips
2. cocurrency handing
3. More control over the logic
4. it is clear.. !!!!


create proc dbo.Item_Add_toOrderList
(
@orderNumber char(20)
@itemNumber char(20)
@processedBy varchar(64) = null
)
as
declare @errorNumber

-- Test processedBy
if (@processedBy is null) set @processedBy = system_user

-- Transaction
begin tran

-- Test orderNumber (makre sure an appropriate order exists)
if not exists(select OrderID from dbo.Order where (OrderNumber =
@orderNumber))
begin
rollback tran
raiserror ('Item Add failed: orderNumber %s unknown.', 16, 1,
@orderNumber)
return -1
/*
This is the first point at which the transaction will be rolled back if one
of the parameters is erroneous.
*/
end

-- Initialize errorNumber
set @errorNumber = 0

insert dbo.Item
(
orderNumber
,itemNumber
,processedBy
,processedOn
)
select @orderNumber as orderNumber
,@itemNumber as itemNumber
,@processedBy as processedBy
,getdate() as processedOn

-- store current error code
set @errorNumber = @@error

if (@errorNumber != 0)
begin
rollback tran
raiserror ('Item Add failed: an error has occured.', 16, 1)
return @errorNumber
/* Here the transaction is rolled-back if an error occured at insert. */
end
else
begin
commit tran
return 0
/* If no error present transaction is committed. */
end
go

Sunday, October 08, 2006

Life

Y'day I was thinking to my self what is the meanning of life??? well have you ever though about it? I bet you would have unless you are not living in a shit whole like me.

We all have a child hood that we can't forget or that we are trying to forget. Then we try to find a job that we can really enjoy, suddenly we get sick of it and you wish to go back to old that .. fun days!!! . And so on you get married and have kids and you watch how they grow up?.

finally when you look bouth you see that parents, brothers, relatives all are passed away.. And now it is your turn.. suddenly we are feeling the frear to die..

And one fine day you die.. That is what we all do ? have you ever though of doing some thing apart from that ?? those are the very basic things that common to all of us, We have to brake that chain... but how???

think who do whant to be in life that you will engoy.. otherwise one day we will feel that we have forgoten to enjoy our lifes so enjoy!

besides, if you figure out a way please let me know coz still i am thinking ....!

Thursday, October 05, 2006

Improve Performance with SQL 2005 Covering Index Enhancements

some thing that I have been expecting...

Improve Performance with SQL 2005 Covering Index Enhancements
http://www.mssqltips.com/tip.asp?tip1078

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

Wednesday, October 04, 2006

Passing parameters to SPs

I have head lot of people are asking how to pass paramerts to a stored proc ?? this is how i like to do

<args>

<customerid>12</customerid>

</args>