SharpDeveloper

For some reason SQL Server doesn’t have an easy way to "Create TO" for database diagrams, unlike stored procedures, functions ,etc.

Here is how you can achieve moving a database diagram (or copying a database diagram) in SQL Server 2005

use Old_Database

go

--this will copy your database diagrams into a temporary table

select * into dbo.#tempsysdiagrams from sysdiagrams

use New_Database

go

insert into sysdiagrams ([name],principal_id,version,definition)
select [name],principal_id,version,definition from dbo.#tempsysdiagrams where [name]='Name_of_your_Diagram'

That’s it, so easy.

Summary:

1. How to find out if a record exists in a table efficiently using the IF EXISTS keyword, and 
2. How to test two queries to see which is faster using SQL Server Management Studio.  This articles teaches you how to tune and tweak your queries.

If you are writing a query to find out if a record exists in your table, you might not be doing it the fastest way if you are using COUNT(*).
i.e. SELECT COUNT(*) FROM USERS where USERID=@UID
and then from your code you are checking if the count is greater than 0.

This can be efficiently re-written as

IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select 0


That is very easy.  It is much more efficient.
How do we know its more efficient?

You can use SQL Server Management Studio to find out.  What you have to do is write your two queries,

--testing variables
declare @UID int
set @UID = 1
--actual query 1
SELECT COUNT(*) FROM USERS where USERID=@UID
--actual query 2
IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select 0

Select all, Click on the button to generate the query execution plan (screenshot below - click to enlarge)

And you will see that it will give you a percentage breakdown for each query.  The first two lines, creating testing variables, you can ignore.  However, looking at the last two queries will show you the breakdown of effort required to run those queries.  It will be similar to 99% for query 1, and 1% for query 2.  This is a quick way to find which query should run faster and you can tweak the queries until you get the results you want.

What that means is 98% of the work has to go into the first query (out of 2) and 2% for the second.  That gives you a rough … idea… of which one is faster.  Sometimes it will be more evenly broken down, closer to 50/50, and in that case they are roughly equivalent.

By Ashiq Alibhai

Note: This article applies to T-SQL; it may or may not work on other varients of SQL.

Many of us are familiar with T-SQL aggregate functions–COUNT, SUM, AVG, MIN, and MAX.  However, one often-neglected feature is that you can use these with the DISTINCT keyword.

Imagine you have the following SQL:

CREATE TABLE #TEMP (
  VAL INT)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (1)

INSERT INTO #TEMP
VALUES     (2)

INSERT INTO #TEMP
VALUES     (3)

INSERT INTO #TEMP
VALUES     (3)

INSERT INTO #TEMP
VALUES     (7)

SELECT COUNT(VAL),
       COUNT(DISTINCT (VAL))
FROM   #TEMP

…will give you the results 7 and 4.  Similarly, SUM(DISTINCT(val)) will give you 13 while the non-distinct gives you 18.

Note that while MIN and MAX are also DISTINCTable, doing so doesn’t really add any additional value.

This article describes how to cache your SqlParameters.  If you have code that is regularly executed, and that uses a stored procedure, you can benefit from this.  This information, as well as how to properly use this, is very hard to find, even on Google. (Don’t worry, you don’t need to spend all that time looking, the information is already here for you!)

There is a function that Microsoft built that will allow you to get the parameter information from the database stored procedure, and that doesn’t require you to re-build your parameter array every time.  The only thing you will need to do is set the values of the parameters.

What some people don’t realize, is that a similar approach can be taken with regular SQL queries that are not stored procedures!

The downside is that the first call will be slower, because it will need to cache the parameters, however, for each extra call, it will be faster because the parameters are already created.

How can you do this?  You first create your parameters, and store it in the call by calling SqlParameterCache.CacheParameterSet()

Then you call SqlParameterCache.GetCachedParameterSet() to get the cached result.  If the result is null, then you can store the parameters in the cache, otherwise you already have it cached!

Here is an example in VB.NET of how to use SqlParameterCache.CacheParameterSet()

Dim cachedParams as SqlParameter() = SqlHelperParameterCache.GetCachedParameterSet(Config.connstring,strSql)

if cachedParams is nothing then

Dim params as SqlParameter() = { _
    New SqlParameter("@userID", userID), _
    New SqlParameter("@timestamp", timestamp), _
    New SqlParameter("@email",email), _
    New SqlParameter("@timesLoaded",5), _
    New SqlParameter("@country",country), _
    New SqlParameter("@source",source) _
}

SqlHelperParameterCache.CacheParameterSet(Config.connstring,strSql,params)
    cachedParams = params
else
    cachedParams(0).Value = userID
    cachedParams(1).Value = timestamp
    cachedParams(2).Value = email
    cachedParams(3).Value = 5
    cachedParams(4).Value = country
    cachedParams(5).Value = source
End If

SqlHelper.ExecuteNonQuery(Config.connstring, CommandType.Text, strSql, cachedParams)

Now the down side to this, is you lose your ability to dynamically create your SQL parameters (i.e. it depends on the order of your SQL parameters).

Related articles:

• Using Dynamically Created SqlParameter Arrays
• Creating Maintainable Database Queries In C# (with source code)
• SqlHelper Source Code, Microsoft Application Blocks

If you want to search triggers for text in SQL Server 2005, here is how you can do it.  You would execute this in the case that you are looking for a trigger that updates a certain table, but you cannot find it, and you do not want to go through all the tables one by one, here is what you can execute:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%your_search_here%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1
GROUP BY OBJECT_NAME(id)

And here is how you can search SQL server stored procedures (external link).

By Ashiq Alibhai

Many applications (and websites, especially) rely on some sort of database to get their work done. As projects and teams grow beyond a one-person-working-in-his-garage model, you can’t rely on the fact that you, or someone who knows the query well, will be the one in charge of modifying it when the time comes. So what are some of the practises you can implement in order to create easily-maintainable and understandable queries?

If you have a website, aim for three-tiered architecture and create objects in your App_Code folder (data/application tier) that call the database—don’t code the query directly in the class! Or, if you have an application, create some centralized folders/classes to perform database queries. The more you can abstract (eg. SomeObject.FetchData vs. SqlHelper.Execute…), the easier it will be if you have to switch data-sources later.

Stored procedures–while offering a dubious performance upgrade–also come with the tag that they are very difficult to maintain. Not only will newbies to SQL become befuddled by the syntax, you need, but they require database-level permissions to modify it. And, in terms of releasing, it adds the extra maintenance of having to propagate stored procedures along with data—as opposed to a query embedded in a .cs file.

C# string-concatenation requires creating a new buffer of the total length of both strings, then copying both into it. If you use concatenation in several places, especially if your code gets run frequently, it may become a bottleneck. Avoid it by using stringBuilder.Append(…) instead of string1 + string2.

As much as possible, keep your query in one piece and avoid breaking it up into parts as much as possible. This makes your query more readable, and also more efficient (in terms of string-concatenation, which is very slow because it requires allocating space for a new string and copying over the original). (You can even use the @ sign in front of your constant to allow you to break up the assignment over multiple lines without concatenating at each line – which, if you use proper indenting, also makes your query much easier to read.)

The only exception to keeping your query together is if you have if-conditions in a where clause, such as the following example:

            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.Append(@"
                SELECT
                    *
                FROM
                    Contacts
                    INNER JOIN ContactPurchases ON
                        Conacts.Id = ContactPurchases.ContactId
                    INNER JOIN Purchases ON
                        Purchases.Id = ContactPurchases.PurchaseId
                WHERE
                    Purchaser.Id = @purchaserId
            ");

            if (targetDate != DateTime.MinValue)
            {
                queryBuilder.Append(" AND Purchases.Date = @targetDate");
            }

            // ... similar checks and additions to the query

Of course, there are always reasons to break up query-building into more complicated logic—such as for-loops, cases, etc. as your application requires. The trade-off is yours to consider!

Wherever possible, code logic into the SQL query instead of the page—so functions like while-loops, cases, checking for null, etc. Should be done inside the query.

Why? Just like keeping your query in one piece, it allows anyone sufficiently skilled in SQL to read and understand the query without the control flow jumping all over the place. Plus, it makes it easy to copy-paste the query and execute it.

If you have parameters to pass to your query, you should be using SqlParameter instances in order to avoid potential SQL injections. Additionally, instead of building your query line-by-line, like this:

            SqlParameter[] parameters = newSqlParameter[2];
            parameters[0] = newSqlParameter("@purchaserId", purchaserId);
            parameters[1] = newSqlParameter("@targetDate", targetDate);

Build them using the array initializer, like this:

            SqlParameter[] parameters = {
                new SqlParameter("@purchaserId", purchaserId),
                new SqlParameter("@targetDate", targetDate)
            };

This has one main advantage over the array: it doesn’t require you to manually maintain the list (and count) of parameters, which can become a maintenance headache if the number goes over three or four parameters.  Read more about using SqlParameter arrays.

Above all, trust your judgement and the judgement of your peers. Sometimes, past (or existing) coders may choose to write queries certain ways. Or maybe you feel that this one piece of logic really should be inside a C# for-loop.

It’s up to you! Ultimately, it’s all a trade-off, and the goal here is not to create the "ultimate" style of writing queries, but only to guide you towards building something that creates readable and maintainable queries.

Here’s a SQL power tip - to speed up your query and stored procedure executions a bit.  Prefix your tables and stored procedures with dbo.

Select * from dbo.[Users] where UserID = @UserID


or

EXEC dbo.UsersLookup ….

By prefixing with dbo, Our database makes one less call.  Normally, when you do not use the dbo keyword, on a query such as Select * from Users where UserID = @UserID, it will first check the user’s schema to see if that table exists for them. 

i.e. if you are logged on as server administrator (sa), it will attempt to do a
Select * from sa.Users where UserID = @UserID

Then, if that table does not exist under sa, it will try again under dbo (main schema)
Normally, this is 1 extra redundant call that you want to avoid.

This helps the database engine encourage re-use and caching of queries.  The database engine will have an easier time recognizing that this is the same query that was just called, and will be more likely to cache and re-use your execution and decrease execution run time.