Navigation

RSS: Matt Pavey RSS Feed

Tuesday, May 4, 2010 @ 8:48 am,SQL Server,Matt Pavey

I had an issue this morning when email notifications were not working for a SQL Server Agent Job. I found this entry in the error logs:
 
[264] An attempt was made to send an email when no email session has been established
 
A quick Google search led me to this article:
 
 
To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.

  2. Right-click SQL Server Agent, and then click Properties.

  3. Click Alert System.

  4. Select Enable Mail Profile.

  5. In the Mail system list, select Database Mail.

  6. In the Mail profile list, select a mail profile for Database Mail.

  7. Restart SQL Server Agent.

Following these steps and configuring the mail profile accordingly allowed me to be able to successfully send the email notifications.

Sunday, December 28, 2008 @ 10:16 pm,SQL Server,Matt Pavey

Here is the error I received:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal "UserName" is not able to access the database "DatabaseName" under the current security context. (Microsoft SQL Server, Error: 916)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3239&EvtSrc=MSSQLServer&
EvtID=916&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
 
After doing some digging I found the answer on Aaron Bertrand's SQL blog.
 
 
"For those of you who connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer."
 
"It seems that the ability to do so is hinged upon the columns that are set up in Object Explorer Details by default.  In the case I came across yesterday, the offender was "Collation."  The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL.  Well, that's not really the problem... the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission."
 
"And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error.  And this is true whether or not you have Object Explorer Details even open (my guess here is that the contents of OED are cached behind the scenes, even when it is disabled... which I speculate may be part of the reason behind the sluggishness that many have complained about).  You can read more about this in Connect #354322 and in Connect #354291."
 
"For those of you that connect to databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible, you are first going to have to go to Object Explorer Details, right-click the column header list, and un-check Collation.  At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access.  Strangely enough, OED still shows you some other data by default, that it probably shouldn't (e.g. recovery model, last backup, owner)."

Thursday, October 30, 2008 @ 12:49 pm,SQL Server,Matt Pavey

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
 
 
"This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck Prevent saving changes that require table re-creation."

Tuesday, October 21, 2008 @ 6:47 pm,SQL Server,Matt Pavey

Here's another good article article written by Scott Mitchell discussing the new date data types available in Microsoft SQL Server 2008.
 
"This article explores the time and date data types and shows how they can be used and formatted from within an ASP.NET page. This article also includes a short discussion on the datetime2 and datetimeoffset and compares and constrasts SQL Server 2008's six different date data types."
 
 
Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Thursday, September 11, 2008 @ 1:03 am,SQL Server,Matt Pavey

"Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005."

http://www.sqldbatips.com/showarticle.asp?ID=8

Tuesday, August 12, 2008 @ 11:30 pm,SQL Server,Matt Pavey

 
"SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:"
 
EXEC sp_spaceused
 
"This will return two result sets, the first one containing the database name, size, and unallocated space and the second containing a breakdown of the database's size into how much size is reserved and how much of that is taken up by data, how much by indexes, and how much remains unused."
 
"To return information about a particular table, simply call sp_spaceused passing in as the first parameter the name of the table. To determine the space used by the Orders table in the Northwind database use:"
 
EXEC sp_spaceused 'Orders'
 
This will return a single result set that provides the following information:
  • Name - the name of the table
  • Rows - the number of rows in the table
  • Reserved - amount of total reserved space for the table
  • Data - amount of space used by the data for the table
  • Index_Size - amount of space used by the table's indexes
  • Unused - amount of usused space in the table

"While sp_spaceused can be used to return the space usage for a single table, more than likely we want a web page where we can view the space used for all tables in a database. There are a number of ways to accomplish this, but in short we need to execute sp_spaceused once for every table in the database. In order to accomplish that we could do one of two things:"

  • Query the sysobjects table to get a list of tables in the database, then use a CURSOR to iterate through these table results one at a time, executing sp_spaceused for each one.
  • Use the undocumented sp_MSforeachtable stored procedure, which takes in a command and executes that command against all of the user tables in the database.

"The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:"

EXEC sp_MSforeachtable @command1="command to run"

"In the command to run put a ? where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use:"

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will execute EXEC sp_spaceused 'TableName' for each user table in the database.
 
"An alternative option is to create a stored procedure from which a temporary table is created and populated with one record for each result set returned by sp_spaceused. The entire results, then, can be returned to the ASP.NET page as a single result set by selecting the entire contents of the temporary table at the end of the stored procedure."

create table #spaceused (name varchar(128), rows bigint, reserved varchar(25), data varchar(25), index_size varchar(25), unused varchar(25))

EXEC sp_MSforeachtable @command1="insert into #spaceused EXEC sp_spaceused '?'"

select * from #spaceused

drop table #spaceused

The above SQL code courtesy of Toby.
 
Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Thursday, July 31, 2008 @ 6:50 pm,SQL Server,Matt Pavey

"One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server."
 
This article provides an easy solution for using the SQL BACKUP command to backup a database and shows you how to use SQLCMD.EXE with Scheduled Tasks to schedule it to run automatically, thus giving you the scheduled backups functionality you would typically have in SQL Server Agent.
 

Thursday, May 1, 2008 @ 8:54 am,SQL Server,Matt Pavey

When programming in SQL sometimes you need to generate temporary tables inside a stored procedure. That's pretty straight forward with code like this:
 
SELECT     TrackingGroupID,
               Tag
INTO        #temp
FROM       MyTable
 
And you can access #temp in the rest of your stored procedure and drop the temp table when complete.
 
-- testing
SELECT COUNT(*) FROM #temp
 
-- drop temp table
DROP TABLE #temp
 
But if you ever have a situation where you also need to use dynamic SQL you will likely have scope issues. In my particular case I ran into a situation where I needed to apply a dynamic filter to a query and store that data in a temporary table so I could access it after the filter was applied and do further manipulation and analysis and grouping with the data.
 
Note: Typically when I work with temp tables I try to use tables-variables like so:
 
-- create temp table
DECLARE @Temp TABLE
(
     TrackingGroupID  INT,
     Tag                    VARCHAR(50) NOT NULL DEFAULT ''
)
 
However, in this case I was not able to get a table-variable to work. So I ended up using a #temp table instead.
 
My first attempt looked something like this:
 
-- declare variables
DECLARE @SQL VARCHAR(8000)
 
-- apply filter
SET @SQL = 'SELECT      TrackingGroupID,
                                   Tag
                   INTO         #temp
                   FROM        MyTable
                   WHERE       1=1 ' + @Where + '
                   GROUP BY  TrackingGroupID, Tag'

-- apply filter
EXEC(@SQL)

-- do further manipulation or analysis on #temp table
....

-- drop temp table
DROP TABLE #temp
 
But I received this error:
 
Msg 208, Level 16, State 0, Line 18
Invalid object name '#temp'.
 
I knew it was a scoping issue, but I wasn't sure how to work around it exactly. Researching on Google I came across this article:
 
 
"The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed."
 
So I modified my code like so:
 
-- create temp table
CREATE TABLE #temp
(
     TrackingGroupID  INT,
     Tag                    VARCHAR(50) NOT NULL DEFAULT ''
)
 
-- declare variables
DECLARE @SQL VARCHAR(8000)
 
-- apply filter
SET @SQL = 'INSERT INTO #temp
                        (TrackingGroupID, Tag)
                        SELECT       TrackingGroupID,
                                         Tag
                        FROM         MyTable
                        WHERE       1=1 ' + @Where + '
                        GROUP BY  TrackingGroupID, Tag'
 
-- apply filter
EXEC(@SQL)
 
-- do further manipulation or analysis on #temp table
....
 
-- drop temp table
DROP TABLE #temp
 
Now I had access to the #temp table throughout the scope of the stored procedure!
 
I've been told that ##temp might have solved it also since it's global; however, I'm not as familiar with ## so I didn't go down that road.

Friday, April 18, 2008 @ 9:46 am,SQL Server,Matt Pavey

"The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well."

http://www.sql-server-performance.com/articles/dev/datetime_2008_p1.aspx

Thursday, April 17, 2008 @ 9:56 pm,SQL Server,Matt Pavey

Learn more about SQL Server 2008 through these demos and videos.

http://www.microsoft.com/sqlserver/2008/en/us/demos.aspx

I found the Application Development Videos particularly useful.

Friday, January 11, 2008 @ 4:55 pm,SQL Server,Matt Pavey

This one is one of the basics that all SQL programmers learn at some point... Sometimes the hard way.
 
@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.
 
To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.
 
 
Luckily today when some strange behavior occurred during our QA testing this was the first thing that I thought of, and sure enough, the original developer used @@IDENTITY to get the newly inserted identity value, which at the time was perfectly correct to do; however, we've recently added some trigger functionality, which caused this behavior to produce incorrect results. Switching to SCOPE_IDENTITY()  fixed the problem. But this one is definitely one you want to remember if you are using triggers in your code.

Friday, January 11, 2008 @ 4:54 pm,SQL Server,Matt Pavey

I don't have to do this very often, but it definitely comes in handy in certain situations.
 
-- setting a variable from dynamic sql
DECLARE @MyValue INT
EXEC sp_executesql N'SELECT @MyValue = 999', N'@MyValue INT OUTPUT', @MyValue OUTPUT
SELECT @MyValue
 
-- setting output parameter from dynamic stored procedure call
DECLARE @OutputParameter VARCHAR(100)
DECLARE @Error INT
DECLARE @SPName VARCHAR(128)
DECLARE @SPCall NVARCHAR(128)
DECLARE @RC INT
 
SELECT @SPCall = 'EXEC ' + @SPName + ' @OutputParameter OUTPUT'
EXEC @RC = sp_executesql @SPCall, N'@OutputParameter VARCHAR(100) OUTPUT', @OutputParameter OUTPUT
SELECT @Error = @@Error
 
One example where this was useful for me was for converting a demormalized set of horizontal data to a normalized set of vertical data.
 
The denormalized data contained a series of column names such as "200701", "200702", "200703", etc. for each month of the year. The file changes month to month and to minimize re-writing code each time a new file comes in I am able to import the data generically, determine the columns that are in the file, and get the value by setting a variable using dynamic SQL.
 
-- declare variables
DECLARE @Total FLOAT
DECLARE @SqlStatement NVARCHAR(1000)
 
-- set default values
SET @Total = 0
SET @SqlStatement = 'SELECT @Total = [' + @ColumnName + '] FROM RawData WHERE RecordID = ' + CONVERT(VARCHAR, @RecordID)
 
-- get the specified column value for the current record
EXEC sp_executesql @SqlStatement, N'@Total FLOAT OUTPUT', @Total OUTPUT
 
Enjoy!

Friday, January 11, 2008 @ 4:52 pm,SQL Server,Matt Pavey

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetAge] (@DOB DATETIME, @Today DATETIME)
RETURNS INT
AS
BEGIN
     -- declare variables
     DECLARE @Age INT

     -- set defaults
     SET @Age = Year(@Today) - Year(@DOB)

     -- if it is a previous month subtract one off the age
     IF Month(@Today) < Month(@DOB)
     BEGIN
          SET @Age = @Age -1
     END

     -- if it is the current month but a previous day subtract one off the age
     IF Month(@Today) = Month(@DOB) AND Day(@Today) < Day(@DOB)
     BEGIN
          SET @Age = @Age - 1
     END 

     -- return value
     RETURN @Age
END

Usage

-- declare variables
DECLARE @Today AS DATETIME

-- set default values
SET @Today = GetDate()

-- get data
SELECT   ID,
             DOB,
             'Age' = dbo.GetAge(DOB, @Today)
FROM     MyTable

Monday, October 29, 2007 @ 11:17 am,SQL Server,Matt Pavey

The following link contains the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2005 components.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Tuesday, October 16, 2007 @ 9:48 am,SQL Server,Matt Pavey

I found this function on the SQL Team website. It is helpful if you need to find the first day of the week for a specified date. Keeping in mind that the start of the week might be defined differently for various clients or applications this function has a parameter that allows you to specify which day of the week should be used as the starting point.
 
 
CREATE FUNCTION [dbo].[GetStartOfWeek]
(
     @MyDate       DATETIME,
     @WeekStarts INT = 1 -- Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, Sat = 7 (Default to Sunday)
)
RETURNS DATETIME
AS
BEGIN
     -- declare variables
     DECLARE @FirstDayOfTheWeekForMyWeek DATETIME
     DECLARE @FirstDayOfTheWeekForFirstWeek DATETIME

     -- Check for valid day of week
     IF @WeekStarts BETWEEN 1 AND 7
     BEGIN
          -- find first day on or after 1753/1/1 (-53690) matching day of week of @WeekStarts
          -- 1753/1/1 is earliest possible SQL Server date.
          SELECT @FirstDayOfTheWeekForFirstWeek = CONVERT(DATETIME, -53690 + ((@WeekStarts + 5) % 7))

          -- verify beginning of week not before 1753/1/1
          IF @MyDate >= @FirstDayOfTheWeekForFirstWeek
          BEGIN
               SELECT @FirstDayOfTheWeekForMyWeek = DateAdd(dd, (DateDiff(dd, @FirstDayOfTheWeekForFirstWeek, @MyDate) / 7) * 7, @FirstDayOfTheWeekForFirstWeek)
          END
     END

     -- return value
     RETURN @FirstDayOfTheWeekForMyWeek
END
 
A couple of example calls to this function are as follows:
 
SELECT dbo.GetStartOfWeek('1/1/07', 1) -- Result: 2006-12-31
SELECT dbo.GetStartOfWeek('1/1/07', 2) -- Result: 2007-01-01
 
Notice how the results will vary based on which day you use for the start of the week.
 
Here's another version that a colleague of mine wrote that also provides the same functionality as the example above. His version is shorter and easier to read and testing against 750,000+ records yields the same results on every record and the performance difference between the two appears to be neglible.
 
CREATE FUNCTION [dbo].[fn_GetDate]
(
     @InputDate DateTime,
     @Day INT -- (1=Sunday, 2=Monday, etc)
)
RETURNS DateTime
AS
BEGIN
     DECLARE @Offset INT

     SELECT @Offset = (@Day - DATEPART(dw, @InputDate))

     IF (@Offset > 0)
          SELECT @Offset = -7 + @Offset
 
     DECLARE @Date DateTime
 
     SELECT @Date= DATEADD(dd, @Offset, CONVERT(VARCHAR, @InputDate, 101))
 
     RETURN @Date
END
 
Enjoy.

Tuesday, October 9, 2007 @ 7:53 am,SQL Server,Matt Pavey

"When you need to analyze numerical data, percentiles are handy for understanding the distribution. Oracle has been providing analytical functions such as NTILE, PERCENTILE_CONT, and RANK since version 8, while SQL Server has been playing catch up. With SQL Server 2005, Microsoft finally added NTILE, RANK, DENSE_RANK, and ROW_NUMBER to SQL Server's T-SQL functions. Unfortunately, the SQL Server 2005 still does not provide PERCENTILE_CONT, and neither will SQL Server 2008. They do, however, feature the new SQL Server Common Table Expression (CTE), which you can use to calculate percentiles."

"This article presents a solution for calculating percentiles in SQL Server 2005 using SQL Server CTE. Based on a solution described in Joe Celko's SQL For Smarties, the idea is to calculate the percentile (percent rank) of each row; if one of the rows has the percentile value that you are looking for, you return it. Otherwise, you perform an interpolation between a value that is slightly more than the desired percentile and one that is slightly less."
 
 
I recently used this approach for some new functionality in one of my projects and it really saved alot of intensive and tedious queries to come up with the right value.

Thursday, September 20, 2007 @ 12:57 pm,SQL Server,Matt Pavey

I ran into a situation earlier today where I wanted to use a table-value function, which essentially is just a function that returns a table. But in my case I wanted to take that table and have it joined to another table to produce the desired results.
 
I had never really had to do something like that before and I quickly found out that you can't do a standard JOIN or subquery on a table-value function if you are also trying to pass it a value derived from the table you are joining on.
 
For example, this query will work:
 
select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(@RecordID) m on d.RecordID = m.RecordID
 
However, this query will not work:
 
select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(d.RecordID) m on d.RecordID = m.RecordID
 
I realize I could have accomplished what I wanted by eliminating the function all together and having a subquery to do this all, but the point wasn't to just get it working, it was to get it working and have this helper function (MyFunction) available to centralize some logic that is used in multiple places.
 
Luckily in SQL Server 2005 there is a CROSS APPLY clause that makes this trivial:
 
select            d.RecordID,
                    d.StudyID,
                    d.TrackingID
from             MyTable d
cross apply    dbo.MyFunction(d.RecordID) m
 
The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side if the table-valued-function returns rows.
 
In my particular example the query returned the data that I needed and I was able to keep my logic centralized.
 
These articles were helpful as I was testing and learning about the CROSS APPLY clause:
 
 
As with any SQL queries, you should test performance accordingly and determine if this particular solution is the best approach or if something else works better. I experimented with a couple other solutions; however, none of them performed any better and they didn't allow me to re-use them easily, which is why I decided the CROSS APPLY was worth using.

Saturday, September 15, 2007 @ 6:27 pm,SQL Server,Matt Pavey

Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Friday, September 14, 2007 @ 12:36 pm,SQL Server,Matt Pavey

If you don't have a 3rd party tool available to build a list of table definitions for you, such a field names, data-types, sizes, etc. you can query syscolumns, sysobjects, and systypes directly to get this information:
 
For example to get a list of all of the tables you could use:
 
select     o.name,
             'datecreated' = o.crdate
from       sysobjects o
where     o.xtype = 'U'
and        o.status >= 0
order by  o.name
 
Or to get the detailed table definition for a specific table you could use:
 
select       c.name,
               'type' = t.name,
               'default' = (select column_default
                             from information_schema.columns
                             where table_name=o.name
                             and column_name=c.name),
               'length' = case when t.name like '%char%' then c.prec else c.length end,
               'null' = c.isnullable,
               'identity' = sign(c.status & 128),
               'pk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name
                        where tc.constraint_type = 'PRIMARY KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name),
               'fk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name
                        where tc.constraint_type = 'FOREIGN KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name)
from         syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          o.name = 'MyTable'
order by    c.colorder
 
Besides the column name and data-type you can also get the default value, size, whether the field allows nulls, whether the field is an indentity value or not, whether the field is part of the primary key, and whether the field is part of a foreign key, etc.

Friday, September 14, 2007 @ 12:27 pm,SQL Server,Matt Pavey

Ever needed to figure out how many tables or stored procedures were going to be affected by some design change in one of your tables, like a field being renamed?
 
Aside from using a 3rd party tool, you can simply query various system tables to get this information.
 
For tables:
 
select       'TableName' = o.name,
               'ColumnName' = c.name,
               'DataType' = t.name
from        syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          c.name = 'MyFieldName'
order by    o.name
 
For stored procedures:
 
select     o.name,
             c.text
from       sysobjects o
join        syscomments c on o.id = c.id
where     o.xtype = 'P'
and        o.category = 0
and        c.text like '%MyFieldName%'
order by  o.name
 
It's easily customizable to fit your needs.
 
You can find more information on the sysobjects table here:
 

Friday, September 14, 2007 @ 11:52 am,SQL Server,Matt Pavey

So you've got a delimited list of values and you want to somehow pass those to SQL as a single parameter and use them in your query?
 
One style that would accomplish this behavior is to have your SQL statement built dynamically in one of your layers of code.
 
Another option would be to pass in the delimited list to a stored procedure and parse the string yourself with some string operations and a cursor and build a dynamic SQL statement and then use EXEC to get the results.
 
But I find myself typically wanting to use a more generic approach that has some level of reusability, hence a user defined function (UDF) to handle the split and keep the rest of the SQL statements very simple.
 
For example, let's say you have this SELECT statement:

select     ID,
             Name
from       MyTable
where     Name in ('Test1', 'Test2', 'Test3', 'Test4')
 
Nothing too exciting here. It simply returns the 4 records that we've asked for.
 
But what if we didn't know what records the user was going to ask for and we needed to make this statement more generic and parameterized.
 
Something like this would be nice; however, as you probably know, it's won't yield the results you need:
 
declare @List varchar(100)
 
set @List = 'Test1, Test2, Test3, Test4'
 
select    ID,
            Name
from      MyTable
where    Name in (@List)
 
Hence we introduce our user defined split function:
 
CREATE FUNCTION [dbo].[fnSplit]
(
     @List VARCHAR(8000),
     @Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
     ID INT IDENTITY(1,1),
     Value VARCHAR(100)
)
AS
BEGIN
     -- loop through the list
     WHILE (CHARINDEX(@Delimiter, @List) > 0)
     BEGIN
          -- add the value to the table
          INSERT INTO @Table
               (Value)
               SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1)))

          -- remove the value from the list
          Set @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + LEN(@Delimiter), LEN(@List))
     END

     -- insert remaining value from the list
     INSERT INTO @Table
          (Value)
          SELECT Value = LTRIM(RTRIM(@List))

     -- return
     RETURN
END

This function uses a simple while loop that just processes part of the delimited list at a time, removes the value, and continues processing the rest of the string until there is nothing left to process.

The final results end up being a simple table with records representing the delimited string in a tableized format.

For example:

declare @List          varchar(100)
declare @Delimiter   varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     *
from       dbo.fnSplit(@List, @Delimiter)

Let's modify our query from earlier to utilize our new split function and we'll see how it all ties together:

declare @List varchar(100)
declare @Delimiter varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     ID,
             Name
from       MyTable
where     Name in (select Value from dbo.fnSplit(@List, @Delimiter))

We now simply can pass in our delimited list and delimiter and utilize it in a simple SQL statement to get the results we needed.

Friday, September 14, 2007 @ 11:44 am,SQL Server,Matt Pavey

This is one of my favorite little tricks to use in SQL Server programming. I don't have to use it very often, but it's definitely a nice way to build a delimited string, considering the other options available.
 
 
Like I said, I've only had to use this in 2 or maybe 3 places, but it definitely saved some time and definitely kept the code simple and clean!
 
Basically the old approach was to return a set of records and through some layer of code build a delimited list... Or if you were a bit more savvy you might have kept it all at the database level and used some type of cursor... But with COALESCE you can create the same results in a single SELECT statement.
 
The article shows some good examples of how to utilize this.

Friday, September 14, 2007 @ 11:41 am,SQL Server,Matt Pavey

The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.
 
 
This is handy if you don't have access to any other tools for testing SQL queries, which in most cases is probably rare, but still, it's nice to have other options available just in case.

Friday, September 14, 2007 @ 11:35 am,SQL Server,Matt Pavey

Anyone who's written SQL statements has undoubtedly had to CONVERT values from one data-type to another. I do this quite regularly, specifically with converting a varchar value to a specific date format, or converting a date from one format to another.
 
By now I've got most of the different options memorized, but this MSDN reference always was a life saver in the middle of the night when I couldn't quite remember what I was after.
 
 
Enjoy!

Monday, September 10, 2007 @ 4:39 pm,SQL Server,Matt Pavey

Thought I’d share this article with everyone:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

I try to avoid temp tables whenever I can, but there are too many cases where there’s no better way to do something.

In the past I typically used #temp tables simply because that is the only thing I really knew how to use and because when I took over some previously written applications there were a lot of cases where they were used.

However, anytime I have to use a temp table now I typically use a table variable directly in the stored procedure.

Today was a great example of why only one of those would work. There was an old stored procedure that uses a #temp table and that same logic is unfortunately copied into several different places. Today I finally got fed up and created a user-defined-function (UDF) to centralize some of the logic. I found out very quickly that you can not use a #temp table in a UDF, which about made me pass out… But then I realized I could simply use a table variable which I prefer anyways.

Another nice thing about table variables is you don’t have to manually clean them up (e.g. drop table #temp).

The article explains different scenarios and recommended practices. Worth looking at if you ever do much with stored procedures or temp tables.

The opinions expressed on this website are my personal opinions
and do not represent my employer's or my clients' views in any way.