As most of you know the general rule in performance tuning is …..

“measure, measure, measure” and “measure again”

But it doesn’t hurt to give out some warning signs from time to time. And here goes two: [more]

1) be careful in doing anything fancy on JOIN predicates (that is the condition in the “ON” section)

   most of the time anything more complex than comparing one columns from each of the tables being joined will have some performance impact.

   again measure, but in experience I find that even creating a common table expression, or a subquery to come up with the derived column and use it to join is faster than doing it in the JOIN predicate.

   also, the less columns involved in a JOIN the better.

2) always prefer set operations than row-by-row operations

    for the same reason that cursors should be avoided

    what many don’t know however is that if you have a UDF (user-defined function) used in a SELECT query result set then that function will be called for every row and thus there will be an overheed. Again measure but for performance sensitive cases it will be significant.

     so if you have to squeeze out any performance gain you can have then get rid of the function if you and although redundant have the logic on the query itself.

     maintainability wise this is not advisable. so you have to feel the trade-off here

     For example:

     SELECT fullname = dbo.GetFullName(FirstName, LastName) FROM Employee (NOLOCK)

     regardless of the complexity of the GetFullName function’s implementation would be slower than having it handled in the query directly. say

     SELECT fullname = FirstName + ‘ ‘ + LastName FROM Employee (NOLOCK)

Hope to share more soon. But again, don’t forget, measure3x

TSQL script to get common SQL Server properties (edition, instancename et al) [more]

SELECT
  SERVERPROPERTY('Collation') Collation,
  SERVERPROPERTY('Edition') Edition,
  SERVERPROPERTY('Engine Edition') EngineEdition,
  SERVERPROPERTY('InstanceName') InstanceName,
  SERVERPROPERTY('IsClustered') IsClustered,
  SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled,
  SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly,
  SERVERPROPERTY('IsSingleUser') IsSingleUser,
  SERVERPROPERTY('IsSyncWithBackup') IsSyncWithBackup,
  SERVERPROPERTY('LicenseType') LicenseType,
  SERVERPROPERTY('MachineName') MachineName,
  SERVERPROPERTY('NumLicenses') NumLicenses,
  SERVERPROPERTY('ProcessID') ProcessID,
  SERVERPROPERTY('ProductVersion') ProductVersion,
  SERVERPROPERTY('ProductLevel') ProductLevel,
  SERVERPROPERTY('ServerName') ServerName

Taken from: http://sqlserver-qa.net/blogs/t-sql/archive/2007/12/21/2994.aspx

Recently I ran into a script instead a stored procedure

IF OBJECT_ID(tempdb..#temp1) DROP TABLE #temp1

Basically, the object of this script is to check if #temp1 (regular temporary table) exists. If so drop it.
However, I think it can have unintended consequences and maybe safer not to include. [more]

Say you have a script that includes the call to the stored procedure (eg. SampleStoredProc)
If the script (let’s call this “caller”) creates a table #temp1 and at the top of SampleStoredProc you have if object_id(tempdb..#temp1) drop table #temp1, what will happen is that the #temp1 table of the “caller” will be dropped.
And the caller might not want that (or won’t expect that the #temp1 table he/she created will be dropped). It is possible that after calling SampleStoredProc the caller would still want to use/access #temp1.
On the other hand if no drop table #temp1 is executed inside SampleStoredProc and a create table #temp1 is made even if the “caller” has a #temp1 already it will not be a problem. The #temp1 of the caller and #temp1 of BehavClusDOM1 will be separately identified.
Since BehavClusDOM1 is a stored procedure it is a scope for temp tables and safe to assume that at the start of the stored procedure no temp tables are present in that scope.
Basically the idea is that BehavClus should not touch whatever is beyond its scope.

So in my opinion the inclusion of this code can cause unexpected behavior to the caller while removing it poses no risk not to mention shortening the code and decreasing complexity and readability. The author of the stored procedure (eg. SampleStoredProc) involving creation of #temp1 should know when it is present and shouldn't worry about clashing with another #temp1 in another session. You can explicitly DROP TABLE #temp1 if you want but only after you have created your own #temp1 so your sure that you'd be dropping the one you created and not that in other scripts.

I've been working "full-time" on TSQL scripts for the past month (no with .NET windows/web apps) and mostly on optimization. And I feel that I should share with everyone this article about temp tables and table variables and some of my own notes. Go read the article below then you may come back here. Take careful note of the Conclusion part at the end of the article. [more]

Should I use a #temp table or a @table variable?

Things to generally consider are speed, disk space and cpu utilization, persistence. And here are some short hopefully helpful notes.

1) persistence – if you need the data to persist even after the execution then no doubt you need to use permanent tables.

2) cpu utilization – always review indexes. effect of the use of regular temp, global temp tables, table variables to performance is not as significant as a missing index. Among other things, it is safe to say, always ensure you have a primary key. and also if you will perform queries with ORDER BY then always consider your clustered indexes are correct. you can use estimated or actual execution plans to analyze your queries. I recommend SQL profiler too but correct me if I'm wrong but they are only able to profile permanent tables.

3) speed – first thing, indexes again too (see above). Secondly and very important – Although table variables may seem (and actually common) faster than temp tables, my observation is that if you are dealing with large datasets then temp tables are way way faster than table variables. I could not quantify giving the dataset I'm working on but suffice to say that it took quite a number of folds faster that I cancelled the execution.

4) disk space – temp tables, global temp tables and table variables take up the same space as permanent tables would. But they should be cleared once the procedure/function goes out of scope. The tempdb transaction log is less impacted than with #temp tables;
table variable log activity is truncated immediately, while #temp table
log activity persists until the log hits a checkpoint, is manually
truncated, or when the server restarts

This is a script to conver a delimited/separated values given a delimiter into a table, via a User Defined Function.[more]

This has saved me a lot of work already including just now so I gfigured this is a good share

My apologies as I could not determine where I based this script from but definitely I got it from someone else rather than created my own. Had a few revisions along the way.

CREATE FUNCTION [dbo].[Split]
(
  @String VARCHAR(200),
  @Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
  Ordinal SMALLINT IDENTITY(1,1) PRIMARY KEY,
  SplitValue VARCHAR(MAX)
)
AS
BEGIN
  DECLARE @SplitLength INT
  WHILE LEN(@String) > 0
  BEGIN
    SELECT @SplitLength =
      (CASE CHARINDEX(@Delimiter,@String)
        WHEN 0 THEN LEN(@String)
        ELSE CHARINDEX(@Delimiter,@String) -1
      END)
    
    INSERT INTO @SplittedValues
      SELECT SUBSTRING(@String,1,@SplitLength)
      SELECT @String =
        (CASE (LEN(@String) – @SplitLength)
          WHEN 0 THEN ''
          ELSE RIGHT(@String, LEN(@String) – @SplitLength – 1)
        END)
  END
  RETURN
END
GO

Not much to say but just wanted to share a common development/deployment task/concern which is to allow windows firewall (and also relevant information for any other firewall) from the following link: Configuring the Windows Firewall to Allow SQL Server Access

Not mucht to say but just wanted to share a common development/deployment task/concern which is to allow windows firewall (and also relevant information for any other firewall) from the following link: Configuring the Windows Firewall to Allow SQL Server Access