More on SQL Server 2008 Variable Enhancements

Let's continue where we left last week. I want to point out another enhancement around assigning values to parameters in SQL 2008 and above. It's called compound assignment operator, starting with SQL 2008 we can declare a parameter and assign value and also we can do mathematical operations.

-- SQL Server 2008 and 2008 R2

DECLARE @DueAmount MONEY = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount *= 0.9

END

SELECT @DueAmount

GO

-- SQL Server 2005 and before

DECLARE @DueAmount MONEY

SELECT @DueAmount = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount = @DueAmount * 0.9

END

SELECT @DueAmount

If you pay attention how the value is being set inside the if logic you will understand how compound assignments works. Here is the link to msdn page for more detailed info around setting the values to variables. It does help with less coding in your T-SQL but if there is sql server 2005 or earlier versions exist in your environment be careful to remember which environments that statement will be used.

HTH,

Bulent

SQL Server 2008 Variable Declaration

Another day a developer approached me stating that the code works in local development server but it does not work when deployed to QA. I know that QA server is running SQL Server 2005. I heard that lately the development team was getting new workstations with new set up. So I was sure that they were running something other than SQL server 2005. I asked the developer if the sql server running on the workstation was SQL 2008. The answer was yes and I then asked how variables were declared and assigned the valued. It was just the first thing that came to my mind. And I was right.

So, starting with SQL Server 2008 Microsoft made improvements on declaring variables and assigning values to them. Here is an example for SQL Server 2008 and newer versions.

DECLARE @Startdate DATETIME = GETDATE()

DECLARE @AccountID INT = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

However if you run the above statement in SQL 2005 or earlier you will get an error message. The only way above statement will work on SQL 2005 and earlier versions is;

DECLARE @Startdate DATETIME

DECLARE @AccountID INT

SELECT @Startdate = GETDATE(),

@AccountID = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

As you see it is less of t-sql code for SQL Server 2008 and newer versions. There is some more enhancements but I will cover that in my next blog.

HTH,

Bulent

70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance

Hello all,

I have been pretty quite lately. There are many reasons for that. One of them is I was preparing for the 70-432 exam. I see a lot of questions in the net about the preparation and books. So I wanted to blog something around that.

Good news is I passed the exam and scored 833 out of 1000. It's not too high of a score but if you score 700 or above, you're certified.

Now the preparation part. I have used MS Press book by Mike Hotek. I like how each chapter is divided into lessons that are not too long to complete. From my experience standpoint the book pretty much covers the material in the exam. The study gave me a chance to look at the features of SQL Server that I don't use day in and day out. I have been working with SQL Server since 2005 including version 2000/2005/2008/2008 R2. I did pass the 2000 Admin and developer certifications back in 2007. At the time where I work we did not have anything but SQL Server 2000. Since then a lot changed. However I still wanted to grab a book and study. However I do think that this book is not just enough. The questions at the end of each chapter are quite easy so it may give you a false sense of confidence (at least for me it might have been). So I turned to practice exam by Transcender. When I started the practice exam it was a lot tougher than the questions in the book. Transcender exam practice really helped me tune more towards the exam format. I definitely recommend the exam practice since it also helps with the explanation of the answers for each question. I also kept the BOL (Books On Line) open.

So one exam is down and I am trying to decide which MS SQL Server certification exam will be the next. Let me know about your certification thoughts. By the way I don't work for Transcender.

Good day,

Bulent