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

Hello all,

Microsoft released the Service Pack 4 for SQL Server 2005 to RTM on 12/17/2010 kind of quietly before the holidays. The version number is 9.00.5000.00 once the sp4 is installed. Here is the link that will take you to download site and this link is for what's fixed with sp4.

HTH,

Bulent

SSIS Warning: Truncation May Occur

I was working on SSIS package to load data from several sources into final destination table. And I see a yellow exclamation mark on data flow task from one of the sources. The message was 'Warning: Truncation may occur due to inserting data from data flow column "Description" with a length of 255 to database column "Description" with a length of 250. When I checked the source column length and destination it was obvious that the lengths of the columns were different and SSIS was correct about complaining. I verified that there was not a record that was inserted with the length greater than 175 char (the field is populated by app in one of the 5 possible defined descriptions). So I did the changes below to get SSIS stop complaining about the length of the column. To fix the issue follow the steps below:

Open the Data Flow task

On the source task right click and select Advanced Editor

Click on Input and Output Properties tab

Expand OLE DB Source Output

Expand External columns and check the columns we need

Expand Output Columns

Click on corresponding column

Change Length property of the column to desired length and refresh.



Also on Destination side:

Open Advanced Editor (on OLE DB Destination)

Click on Input and Output Properties tab

Click on Refresh button

Check the value of the length is correct



HTH,

Bulent

SQL Server Error 5042 Can Not Remove File X Because It's Not Emty

Couple weeks ago one of the job failed with the message stating that the file can not be removed because it is not empty. I have implemented quite a few partitioned tables and created a stored procedure which switches data from a 3 months old partition and then merge the partition. The process has been working fine but suddenly it failed. I check the partitions, but there was nothing in the file. I started to search the Internet and I found a help in here by Jeffrey Yao at SQLServerCentral.com. In my case the file was created originally 5 GB in size but during the month it grew to 5.5 GB in size and the file size was still 5.5 GB. I followed Jeffrey's steps and shrank the file to it's original size of 5 GB and then the process ran successfully to merge the partition.

HTH

SQL Server Collate Clause And Case Sensitivity

The servers in the current environment I am working has been configured with case insensitive collation by default during the installation. I had to help a developer who was trying to write a clr function to compare values stored in a database. We had to find out all the uppercase first and last names stored in one of the tables. The columns in the table have been created case insensitive. I thought of using built in feature instead of doing it in CLR and finding out the results. As you see in the simple script below I am creating 4 columns 2 case insensitive and 2 case sensitive. Populate it with sample data and finally compare the columns against each other. Note that once you create a columns like this you can not compare case insensitive column against so pay attention the where clause in the select statements. With this simple solution we were able to easily produce all the records stored in uppercase in the table. For more information look in the Books Online and search for collate

HTH,


USE tempdb
GO

CREATE TABLE dbo.CaseSensitiveTest(
RowId TINYINT IDENTITY (1,1),
CIFName VARCHAR (30),
CILName VARCHAR (30),
CSFName VARCHAR (30) COLLATE Latin1_General_CS_AS,
CSLName VARCHAR (30) COLLATE Latin1_General_CS_AS

)

GO

INSERT INTO dbo.CaseSensitiveTest
SELECT 'MICKEY', 'MOUSE', 'Mickey','Mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'mickey','mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'Mickey','Mouse'
UNION ALL
SELECT 'MICKEY', 'MOUSE', 'MICKEY','MOUSE'
UNION ALL
SELECT 'bunny', 'rabbit', 'Bunny','Rabbit'
UNION ALL
SELECT 'BUNNY', 'RABBIT', 'BUNNY', 'RABBIT'

GO

SELECT * FROM dbo.CaseSensitiveTest

SELECT *
FROM dbo.CaseSensitiveTest
WHERE CIFName COLLATE Latin1_General_CS_AS = CSFName
AND CIFName COLLATE Latin1_General_CS_AS = UPPER(CIFname)


DROP TABLE dbo.CaseSensitiveTest