Sometimes rapid code development doesn’t always produce the most efficient code. Take the age old line of code SELECT COUNT(*) FROM MyTable. Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan. Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.Table ‘FactProductInventory’. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Starting with SQL Server 2005, Microsoft introduced a...
Continue reading...TSQL Code
Merging SQL Server and Softball Just for Fun
With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball. Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have. I have been managing a slow-pitch softball team, the Sons of Pitches, for the past 5 years. Yes, I did say “slow-pitch”. My friends and I have already passed the peak of our physical ability, so we use it as an excuse to get together and have a little fun. As a DBA, I’m...
Continue reading...How to Use xp_dirtree to List All Files in a Folder – Part 2
In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder. The previous posting mostly talked about how the stored procedure worked and the output it generated. For this posting, I have expanded upon the script to parse all subdirectories and list all files in all subfolders from the starting point. Just for a quick review, xp_dirtree has three parameters: directory – This is the directory you pass when you call the stored procedure; for example ‘D:\Backup’. depth – This tells the stored procedure how many subfolder levels to display. ...
Continue reading...T-SQL Terminator
You may have noticed in my code examples, I always include a semicolon “;” at the end of my TSQL statements. If you’re not doing this, then you need to start now. Actually, if you’re not writing TSQL code as a DBA then you need to start now. Starting in SQL Server 2005, Microsoft introduced the TSQL terminator. This syntax is not required for most statements, but at some point in the future it will be required for all. You can read the details on MSDN. As with all new changes to SQL, you should be future-proofing your code. Some statements...
Continue reading...How to Use xp_delete_file to Purge Old Backup Files
Continuing on my recent trend of using undocumented stored procedures, this week I thought we can cover using xp_delete_file to purge old backup files from a folder. This is stored procedure used by the maintenance plans to clean up old backup files, but it makes for a handy purge tool when creating your own backup scripts. Let’s suppose we have a simple script (taken from a recent post on stackoverflow.com) to backup all databases on a server. The script will create a new database backup file of every database on the server each time it’s executed. However, the path may not...
Continue reading...
