Speak up!

So the holidays have come and gone, and that old familiar “back to work” phrase is making it’s rounds in my head. It’s time to pump up the ‘ol Blog with some new topics.

Here’s the deal…

We have written several articles now, so you hopefully have an idea of the type of content we can provide. We’d like to start a feedback loop going here. It would be most helpful to hear from you about the topics you’d like to see on this blog. As well, all other comments are welcome to any of the posts we provide. In other words, WE WANNA HEAR FROM YOU!

So, please post comment to any of our posts, or if you prefer a quieter approach, send an e-mail. The address is (please forgive our obscuring the address, but we really aren’t into spam): sqlcoach(then that little squiggly at sign thingy that you see in all e-mail addresses)gmail.com.

Thanks in advance to all those who help us out with your comments!

Quick Tip: Deleting all Data from a Table... FAST!!

There are times when you need to “blank out” a table. That is to say, remove all data from the table, but leave the table to be repopulated.

Many people opt for:

DELETE FROM MYTABLE

This certainly works. However, if you have a lot of records (and especially if there are a lot of indexes as well) this approach can be time consuming.

The reason is simple. SQL Server tracks all changes made in a database so they can be rolled back (Using Begin Tran/Rollback/Commit commands). Also, SQL Server is tracking these commands for disaster recovery. If the server goes down later, these changes are stored in a way that SQL Server can reconstruct the database when it boots back up and recovers. So, for every row you delete, SQL Server is writing to a log about that delete action. As we all know, writing data takes some time.

A quicker option is to use the following:

TRUNCATE MYTABLE

This is basically instantaneous. SQL Server merely logs the fact that a truncate happened. So, if you delete two million records, only one log entry is made. SQL Server then simply changes some internal pointers, and *poof* it’s done. BE VERY AWARE THAT YOU CANNOT ROLLBACK A TRUNCATE STATEMENT. That is the price you pay for speed!!

One final note, if you have an Identity field in your table (otherwise known as an auto-increment field from those of you coming from an Access background) then Truncate will reset your starting number to 1 (or whatever you defined as your starting number). This can cause some problems in some apps that depend on the field continuing to increment. If you need the field to continue to increment, there is a technique for that. If your interested in that technique, just drop us a comment and we’ll post it for you.

Search Stored Procedures with SysComments - The Inner Workings of Your Database Revealed!

Hello and welcome back to SQL Coach!!

Two days ago I shared some information with you about the SysObjects table. Tonight I want to talk about SysComments.

In case you missed the Tuesday evening post, SysComments is one of SQL Server's System Tables. Do not attempt to DELETE, INSERT or UPDATE the table in this table. That is SQL Server's job and we need to leave it up to SQL Server to decide what to update and when.

Have you ever been in the situation where you needed to create a stored procedure to accomplish something that you know you have already written or at least saw a stored procedure that performed a task similiar to what you need to accomplish?

And have you ever been frustrated because no matter how hard you tried to remember you simply could not recall which stored procedure that code is in?

If you only have a handful of simple stored procedures it would be a simple task to open each and perform a search for some part of the code that you remember.

But, wouldn't it be so much simpler if you could simply write a query and have SQL Server do the work for you and return a listing of any stored procedures that are good candidates?

I am about to show you how to accomplish this... are you ready? Great, let's get started!

Remember that SysComments stores the actual text (code) for your stored procedures and functions. The table also contains an ID field that maps back to the id field in SysObjects.

The column in SysComments that stores the text of your stored procedures and functions is called simply enough text.

Here is a very simple example of how to search for all stored procedures that contain an Insert operation:

SELECT so.name
FROM dbo.sysobjects so
INNER JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE so.xtype = 'P'
AND sc.text LIKE '%INSERT%'


Now, one thing you may notice is if you have a stored procedure with a lot of text and several INSERT statements spread down throughout the code the above query will return that stored procedure's name more than once.

The reason for this is the text column in the SysComments table can only hold 8000 characters. If the text in the procedure is longer than 8000 characters SQL Server needs to create additional rows. In each new row, the text column will contain up 8,000 characters (or however many characters are left) of text until finally all text in the procedure is accounted for.

No worries though, there is a simple solution to the names being returned multiple times, simply add a DISTINCT to your SELECT statement so it looks like this:

SELECT DISTINCT so.name
FROM dbo.sysobjects so
INNER JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE so.xtype = 'P'
AND sc.text LIKE '%INSERT%'


Now, you have the ability to search the text (code and comments) of your stored procedures. And, believe me, this can be a real timesaver when your database grows to more than a dozen or so of long procedures. Beyond the time savings searching like is fun. Make SQL Server do the work for you!

Let me share one more trick with you before I end this post.

You do not even need to link back to the SysObjects table as long as you do not care what type of objects are returned by your search.

For example, if you want all stored procedures, scalar functions, etc returned that contain the word "password" you can accomplish this with the following statement:

SELECT DISTINCT OBJECT_NAME(id)
FROM dbo.SysComments
WHERE text LIKE '%password%'

Here is how it works: SQL Server provides the OBJECT_NAME function. It accepts an ID value (from the sysobjects table) and returns the name. The SysComments table contains an ID column that links back to the ID column in the SysObjects table.

Okay, that's it for this post. Remember to come back often and bring your friends!

The Power of SysObjects - The Inner Workings of Your Database Revealed!

Hello and welcome to SQL Coach!!

Tonight I want to share some information with you that I find extremely useful in my daily work.

As you may already know... inside the Tables section (in your Object Explorer window) of your database SQL Server contains what are called System Tables.

SQL Server uses these System Tables to store information describing your database objects and statistics about the use of your objects.

I feel the need to stop right here and say DO NOT INSERT, UPDATE or DELETE any data in the System Tables. Like I said, these are for SQL Server to maintain, not you (or me).

However, there is no problem in SELECTing the data contained in these tables. =)

The tables I want you to learn about are called SysObjects and SysComments.

There are many columns in each table and they can be intimidating for a beginner. So, let's do something easy to show you their "bark is worse than their bite."

First a super quick summary of these tables is in order.

SysObjects stores basic information about all objects inside your database. It's useful for you to know because it tells us the name of each object and the type of the object.

SysComments stores the actual text (code) for your stored procedures and functions. It contains an ID field that maps back to the id field in SysObjects.

Now, let's do some quick queries to help you find your way around these two.

SELECT name
FROM dbo.sysobjects

Executing the statement above will list all objects associated with your database. Probably not that useful but maybe kind of interesting if you want an overall feel for the size of database you are working in.

Now, we'll make use of the xtype field found inside SysObjects:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'

Executing the statement above will list all User tables inside your database.

Let's have a look at the Procedures inside your database:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'P'

Executing the statement above will list all Procedures inside your database (if you want to look at your functions use 'FN' for scalar functions and 'TF' for table functions).

Now, I bet you have a good idea how the Object Explorer window knows what to show, huh?

That's it for now... join us later this week and we'll take a good look at the SysComments table.

Quick Tip: Left vs. Inner Joins

I have run into a few people now who don’t fully understand the differences between a left join and an inner join. In these cases, the people simply used Left Joins all the time to “cover their bases”. If this is you, please read on…

As far as performance goes, a left join is MUCH LESS EFFICIENT than an inner join. Therefore, it is definitely worth your time to investigate them and understand them. I am not going to re-invent the wheel here as there are about 1.2 ga-billion articles on this topic, and Books Online even has some pretty decent info. I just want to emphasize that this is a topic worth the time to learn. The query it could save may be your own!

Implicit Data Conversion: The “Hidden” Bottleneck

Have you ever written a query that just seems to take forever? You have looked it over time and time again, and it seems to be perfect! It’s straightforward, has an appropriate index, and still takes 5 – 10 seconds to come back.

Well, there can be a number of causes. Today, we are going to discuss “Implicit Conversion.” I ran into this again today helping a colleague out. I have seen this literally bring a system to its knees by causing deadlocks during high load. In short, we can blame this for the fall of the Roman Empire.

Consider the following stored procedure:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @WidgetID

That looks innocent enough, right? However, a statement like this has the potential to make demon and children cry if that table has several hundred thousand rows in it.

Here’s the deal. Have you ever seen a system where in some tables an ID is stored as a Character string, but in others it’s stored as an Integer? Sure, we all have. Developers and even DBAs get confused and write a procedure with the wrong data type all the time in that scenario. Say for example that our table actually has WidgetID definted as an Int, but the developer was confused and wrote the procedure as if the WidgetID column was a VarChar(20).

Now, every time SQL Server has to look for a Widget, it has to convert @WidgetID from a VarChar to an Int. This is an implicit conversion of data type. Internally, SQL Server uses a convert function to do this. When this happens, SQL Server cannot use an index effectively because any time it has to hand something to a function, it cannot be certain of the result of the function. Therefore, it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

The fix? Simple! Strangle the person who started mixing data types in the system. Once they are dead, alter your procedure to convert the value first:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Declare @intWidgetID Int
Set @intWidgetID = Convert (Int, @WidgetID)

Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @intWidgetID

…and you will now retire a millionaire because you single-handedly rescued the world.

Building a Weekend Table - SQL Dates and Times Series

Hello and welcome (back) to the SQL Coach blog!

Tonight I am going to show you how to build a table populated with the dates of all weekend days within a certain date range.

In this project we will be covering:

• The SmallDateTime SQL Server data type
• How to create a Variable Temporary Table in SQL Server
• How to loop through a date range in SQL Server T-SQL
• How to use DatePart to determine if a date refers to a Saturday or Sunday

Open a new query window and copy and paste the following code into it...

-- Get rid of Row(s) Inserted tracking and messaging
SET NOCOUNT ON

-- Create a temporary variable table
DECLARE @WeekendDays TABLE
(
dDate SmallDateTime,
sType VarChar(3)
)

-- Define the date range we will be using
DECLARE @dDate SmallDateTime,
@dEndDate SmallDateTime

SET @dDate = '19900101'
SET @dEndDate = '20251231'

-- Populate the temporary table with dates
-- of all Saturdays and Sundays that fall
-- within the date range
WHILE @dDate < @dEndDate
BEGIN
If DATEPART(WEEKDAY, @dDate) = 1
INSERT INTO @WeekendDays Values(@dDate, 'SUN')

Else If DATEPART(WEEKDAY, @dDate) = 7
INSERT INTO @WeekendDays Values(@dDate, 'SAT')

SET @dDate = DATEADD(DAY, 1, @dDate)
END

This code will create and populate a temporary variable table holding the dates of all Saturdays and Sundays from January 1, 1990 through December 31, 2025.

For the remainder of this post I will refer to this code as the base code.

Now, let's have some fun and check out the data in this table! ;)

Suppose you want to know how many Saturdays are in December 2007.

You can find out this information by adding the following query below the base code and executing it (it being all code, the whole enchilada):

SELECT COUNT(*) AS NUM_WEEKEND_DAYS_IN_DEC_2007
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Excellent! Now we know December 2007 has 10 weekend days.

Now, let's see the dates of these weekend days.

You can find the dates by adding the following query below the base code and executing it:

SELECT dDate
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

You should see 10 rows that begin with this data...

2007-12-01 00:00:00
2007-12-02 00:00:00

Now, let's get rid of the time data because we really do not need it here.

SELECT Convert(Char(8), dDate, 112)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Now, the data will look like this...

20071201
20071202

Now, let's Americanize this format.


SELECT Cast(Month(dDate) as VarChar)
+ '/' + Cast(Day(dDate) as VarChar)
+ '/' + Cast(Year(dDate) as VarChar)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

As you see, the data now looks like this...

12/1/2007
12/2/2007


I hope you now have a much better understanding of how to work with Dates in Sql Server. And I imagine you have some ideas you just can't wait to try out!

I apologize for the lack of indenting in the code. I actually wrote the code using indenting to make it easier to follow but once I published this post the formatting was removed. Ah well, these things happen. I think the code is clear enough you can easily follow it.

Remember to check back soon for more great SQL information. And please help us to spread the word about our blog. Tell your friends. Tell your co-workers. Heck, tell your family and even your pets!

Thanks!