Deleting Records from Large tables

Database maintenance is a necessary evil for a database administrator and deleting records is one of these necessary evils that we need to deal with. Deleting records can be tedious task especially in Very Large Databases (VLDB). This post will demonstrate different ways to handle deleting records from a database table.

Basic Delete

The easiest way to delete records from a database table is to use a DELETE FROM statement

DELETE FROM VLDB_Table WHERE ColumnName = 'Value';

As the number of records grow, the amount of time it takes to delete records from our table increases. While the records are being deleted, the table is locked and other processes will be blocked until the completion of the delete statement.

Truncate Table

The truncate statement is a much faster delete process, which removes all records from a table.

TRUNCATE TABLE VLDB_Table;

A truncate statement is not always an option on a table, if the table has a foreign key relationship with another table, the table is used in an indexed view or used in replication, the truncate option is not an option.

Batch Delete

With the limitation of the truncate statement and potential for blocking using the basic delete, that leaves us looking for an optimal solution for cleaning up records. Using a basic delete in conjunction with the TOP option and a loop, we can delete a records in a batch.

DECLARE @BatchSize int  = 1000;
 
WHILE EXISTS (SELECT 1 FROM VLDB_Table)
BEGIN
   DELETE TOP (@BatchSize) FROM VLDB_Table WHERE ColumnName = 'Value';
END

Another batch delete scrip that I have come across uses the ROWCOUNT as the factor for the loop.

DECLARE @BatchSize int  = 1000;
-- Delete the initial batch outside the loop, this is needed to set @@ROWCONT
DELETE TOP (@BatchSize) FROM VLDB_Table WHERE ColumnName = 'Value';
 
WHILE (@@ROWCOUNT > 0)
BEGIN
   DELETE TOP (@BatchSize) FROM VLDB_Table WHERE ColumnName = 'Value';
END

SQL Saturday Homecoming

Minnesota Sql Saturday #149

RA RA REE kick them in the knee

RA RA RASS kick them in the other knee

And the crowd goes wild!

OK OK OK, this is why I am a DBA not a cheerleader…

Homecoming is always a fun time and for Rick ( b | t ) and I it is no different. We started our journey a few months back with the grand vision of presenting at SQL Saturday #149. We went out on the road to SQL Saturday #159 Kansas City and SQL Saturday #161 East Iowa to share our presentations, and now its time for our homecoming. This coming Saturday, September 29th 2012 Rick and I will be presenting again at SQL Saturday #149 Minnesota.

Currently we are scheduled to present Freaky Fast Development Tips in Room: Rapson 45 at 8:30 AM. Then we follow that up with SSRS: Reporting on Reports in Room: Rapson 43 at 2:15 PM.

SQL Saturday #149 Schedule

So come on out and celebrate our homecoming with us!

Freaky Fast Database Development Tips

Join Rick and Dave on this most excellent adventure as they teach you how to get more work done in less time. Leave your checkbook at home, because this is all about maximum productivity with minimum budget. Leave with Visual Studio and SQL Server Management Studio shortcut keys, extensions, macros, and add-ins. We guarantee you will see something you haven’t seen before, or your money back. Co-Presenter: Rick Krueger (@DataOgre)

SSRS: Reporting on Reports

Business Intelligence gets a lot of press these days, but do you often wonder to yourself as you are writing a report, ‘Who is going to use this’? Learn about the rich information that SSRS is already capturing for you about your reports: who is running them and how often, how long do they take to execute, how much time is spent in data retrieval vs rendering, which parameters were passed in, and more… Co-Presenter: Rick Krueger (@DataOgre)

Another Success at SQL Saturday #161

East Iowa Sql Saturday #161

So my adventure continues…

One short week after a successful pair of sessions in Kansas City, Rick ( b | t ) and I found ourselves back on the road to East Iowa. This road trip was much shorter, and we were able to make (and find) the happy hour event at Back Pocket Brewing in Coralville, IA. If you happen to be in Coralville, I recommend the Double Bock or Slingshot both are pretty tasty. We met up with Ed Leighton-Dick ( b | t ) first, followed by Pat Phelan ( t ) and Eric Selje ( b | t ). Later Sheila Acker ( t ) and Larry Toothman ( t ) arrived. After a couple hours of great conversations, it was close to 8:00 PM and we all proceeded to the speakers dinner. Ed picked a great Italian restaurant called Monica’s. I was able to sit next to Karla Landrum ( b | t ) and Russell Fustino ( b | t ) and had great conversations about everything from SQL Server to family and friends.

One of my primary objectives in speaking at the SQL Saturdays is getting out and meeting new people in the SQL Family, and so far so good. But now it was time to get back to my hotel and review the session materials one more time…

Dave and a Hawkeye

The next morning we made our way to the SQL Saturday #161 event. One thing was very apparent, we were in Hawkeyes Territory. Now as a side note, I saw two kilts at the SQL Saturday #159 event in Kansas City, and now here in East Iowa I am seeing another kilt, maybe this is a new trend for all SQL Saturdays…

We presented Development Tips at 11:00 AM and Reporting on Reports at 3:45 PM. We received very positive feedback and had great dialog in both sessions. Thank you to all of you that came to the sessions. I was able to see Erin Stellato ( b | t ) present on statistics, she did an amazing job! Overall I thought the event was great, Ed and his team did an amazing job organizing the schedules and the event overall. After the event we stayed and mingled at the after party for about an hour before we made our way back home to Minnesota.

Minnesota Sql Saturday #149

The drive home to Minnesota was nice, all the stress and worries were gone and I could finally relax. As I reflected on the past two weeks, I was rather amazed of our accomplishments and realized that I am hooked on presenting at these sessions. Now I am looking forward to SQL Saturday #149 in our home town…

BTW, I just found out yesterday that both of our sessions were selected. Hope to see you there as my adventure continues…

Road to Success at SQL Saturday #159…

Kanas City Sql Saturday #159

So my adventure begins in May of 2012…

Rick Krueger ( b | t ) and I were talking about SQL Server and decided that it was about time that we became active members in the SQL Family. Our first attempt at bringing the SQL Family together was not as big of hit as we had hoped, but we found that we both had a passion and the drive to do more. This is when we decided that speaking at SQL Saturday #149 Minnesota was our next goal.

One day, in June, Rick says to me, “Dave, I think we should submit two abstracts to Iowa and Kansas City.” After thinking about it for about 3-4 seconds, I quickly agreed. I felt that this would be the perfect opportunity for us to meet some new SQL Server friends, and get speaking time. Plus if we were able to speak at Kansas City and Iowa events, we would surely get into the Minnesota event. So Rick and I submitted two abstracts to both SQL Saturday #159 Kansas City and SQL Saturday #161 East Iowa.

Now the thought for submitting two abstracts was to better our odds of getting a single spot. We didn’t anticipate both Kansas City and Iowa accepting both abstracts. After the rush of getting our abstracts selected wore off, we quickly realized our work was cut out for us. We had summer coming up, which in Minnesota is the nicest 2 months of the year, kids graduations, weddings, teaching, and family life to maintain. And now on top of all that, we had to prepare two full sessions.

I will say that it was a lot more work that I had ever imagined, but it was all worthwhile. We spent a lot of late nights working on slide decks, demos, and speaking scripts. We enlisted some great friends at the office to listen and give great feedback: Eric ( b | t ), Brandon ( b | t ), Bob ( t ), Rich and Scott thanks again! We made changes to our session as late as the drive from Minnesota down to Kansas City. But one of the last changes we made took our session from a 10 and cranked it up to 11.

The Friday before SQL Saturday #159, Rick, my wife, and I left for Kansas City. We made it to Kansas City just about 2:00 PM and attempted to meet up with the SQL Saturday #159 speakers on a BBQ crawl. This should have worked in theory; however I had some navigational difficulties, which prevented us from making the entire BBQ Crawl. However all was not lost, we made it to the final stop at the Woodyard BBQ joint. We met Jason Kassay ( b | t ), Chris Shaw ( b | t ), Andre DuBois ( t ), and Bill Fellows ( b | t ). Great group of guys, I am looking forward to hanging out with them again at future SQL events.

Dave and Rick at SQL Saturday #159

Later Friday night we went to the Screenland Theater for a speakers dinner. If you have never been to the Screenland theater, I would recommend it, lots of nostalgia and interesting architecture. It was at the dinner party where I was able to meet Meagan Longoria( t ), Chris Price ( t ), David Klee ( b | t ), Andy Galbraith ( b | t ), Steve Wake ( b | t ), and John Morehouse ( b | t ).
All these people were super nice and we had great conversations.

SQL Saturday #159 Kansas City was spectacular. The event organizers did an outstanding job! The Rooms were great, there were plenty of helpful people, the lunch was delicious, and the smoothie break was a nice touch. Over all it was a top notch event, kudos to the entire team!

And… Rick and I  successfully gave our first two sessions!!!

It wasn’t until the next day driving back to Minnesota that I realized what Rick and I were able to accomplish. If were to do it over, I would like to say I would do things different, but that’s just not my style.

I would like to thank both my wife Kristin, and Rick’s wife for putting up with us during this crazy adventure.

Iowa SQL Saturday #161

Next stop SQL Saturday #161 East Iowa…

BI Templates

Templates in BIDS or SSDT are a great time-saving feature for both SSIS and SSRS. They also promote consistency across all developed reports and integration services packages. Finding the correct path to store templates can be a challenge, which is what I hope this blog will illustrate. Below I listed a quick reference where to store your templates.

Templates Paths for SQL Server 2005

%Program Files%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2008 and 2008 R2

%Program Files%\ Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2012

%Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

SSIS = DataTransformationProject
SSRS = ReportProject

NOTE:

  • These paths are based on default installs, you may need to adjust according to your install location.
  • The %Program Files% will be either Program Files or Program Files (x86), depending on if you are using a 64bit architecture.
  • You will need to replace the [Template Type] with DataTransformationProject for SSIS and ReportProject for SSRS