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.
Thursday, October 18, 2007
Thursday, October 11, 2007
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!
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!
Tuesday, October 9, 2007
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.
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.
Thursday, October 4, 2007
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!
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!
Subscribe to:
Posts (Atom)