If you have been working with Microsoft’s SQL Server Mangement Studio 2008 tool for any period of time, you have do doubt seen the message below:

Your only real option here is to click the cancel button and wonder aloud how the heck do you update your table. In short this message means that the update you are trying to perform on the table will require the table to dropped and created again. The odd thing about this message is that SQL Server will do all of that stuff for you in the background without you needing to do anything. So this message prevents it from helping you. There is a way to turn this message off, and by doing so you can easily save the updates to your table. While in the program just select the Tools option in the top menu, and browse down and click on the Options link. This will bring up the options window, where you will then see a section called Designers. Click that and uncheck the box labeled “Prevent saving changes that require table re-creation”. You can see this in image below. Click OK and you will no longer see this annoying message.

I saw a post yesterday that explains the simple process of setting up a MySQL database as a linked server in Microsoft SQL Server.  SQL Server has had the linked server functionality for years now.  In short it allows you to set up a data connection to another RDBMS and grab your data from it.  The post below is a great step by step guide to setting up a linked server to a MySQL database.

HOWTO: Setup SQL Server Linked Server to MySQL

I have blogged about the wonderful use of the SQL Profiler in SQL Server before.  The guys over at SQLServerCentral.com have another article with some awesome tips on using the Profiler to determine where deadlocks occur and in one particular case how to solve them.  Fantastic read if you do a lot of work with SQL Server.

I had an urgent request the other day to find all the pages on a site that contained a certain string of text.  The entire website is dynamic, so all the content lives in the database.  I came across an article from MSSQLTIPS.com that showed a nice little stored procedure to search for a string of text in a given table.  It worked like a charm and immediately showed me all my pages that contained the text I was looking for.  You can view the full article here from MSSQLTIPS.com.

Have you ever needed the ability to pass in your sort variable to a SQL Server stored procedure?  At first thought you would think you could do the following:

 T-SQL | 
 copy code |
?
 
1
ORDER BY @orderVar

But, no dice.  SQL Server will not allow you to set the ORDER BY clause with a variable.  There are two solutions to get around this.  The first is to use a CASE statement to perform the ORDER BY clause:

 T-SQL | 
 copy code |
?
 
1
DECLARE @SortVar TINYINT
2
    SET @SortVar = 2
3
    SELECT column1, column2, column3
4
    FROM TABLE
5
    ORDER BY CASE WHEN @SortVar = 1 THEN column1
6
    WHEN @SortVar = 2 THEN column2
7
    ELSE column3
8
    END

The second is make your entire query dynamic, by declaring a local variable that will hold your SQL statement:

 T-SQL | 
 copy code |
?
 
01
DECLARE @checkPaid BIT,
02
    @SQLStatement VARCHAR(500)
03
    IF @IsAll = 0
04
       SET @checkPaid = 1
05
    ELSE
06
       SET @checkPaid = 0
07
 
08
    SET @SQLStatement = 'Select d.* From [Items] d WHERE 1 = 1 AND Paid = ' + CAST(@checkPaid AS VARCHAR) + CHAR(10) + CHAR(9)
09
    IF LEN(@category) > 0
10
       SET @SQLStatement = @SQLStatement + ' AND category = ''' + @category + '''' + CHAR(10) + CHAR(9)
11
 
12
    IF LEN(@SIZE) > 0 AND @SIZE <> ''
13
       SET @SQLStatement = @SQLStatement + ' AND Size = ' + CAST(@SIZE AS VARCHAR) + CHAR(10) + CHAR(9)
14
 
15
    IF LEN(@qty) > 0 AND @qty <> ''
16
       BEGIN
17
          IF @qty = 1
18
             SET @SQLStatement = @SQLStatement + ' AND qty = 1 ' + CHAR(10) + CHAR(9)
19
          ELSE
20
             SET @SQLStatement = @SQLStatement + ' AND qty > 1 ' + CHAR(10) + CHAR(9)
21
       END
22
 
23
    SET @SQLStatement = @SQLStatement + ' ORDER BY ' + @sort
24
    -- Execute the SQL statement
25
    EXEC(@SQLStatement)

I prefer the second method, as you can also make your entire statement dynamic, for example using IF statements to perform different WHERE clauses.

I do not want to cover everything that can be done with the SQL Server Profiler here, but I do hope that most of the advanced developers out there who use SQL Server regularly are familiar with it.  It has many uses, but for me it has become an invaluable tool to help find some of the bottlenecks in an application.  We have all inherited many applications in our days that were written by a coworker, or even developers at another company.  It's always interesting to dive into the code and see what is going on.  It will be fairly obvious from looking at a few pieces of the application whether the developer was a novice or really advanced.  This not only holds true for the application code, but the SQL queries that reside in the application.  If you want to really bog down an application, throw some horrible SQL or some really bad looping over a query in there.  The results will not be pretty!

This brings me to the use of the SQL Server Profiler.  This tool will allow you to watch in real time the queries that are hitting your SQL Server instance.  You can set some filters and other things like that to get a quick view of the queries that are long running.  That is a good place to start looking if you are experiencing some slowdown in a database intensive application.  You can also copy and paste the query into Query Analyzer and being your optimization from there.  Below is a link to a video from SQLServerCentral.com that describes the basics of using the Profiler.  If you have never used this tool before, check it out and make it part of your debugging process.