UPDATED — Jul 3, 2015 — To verify database exists, per comments by Konstantinos Katsoridis. Thanks for finding the bug! In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica. The gap being Microsoft did not provide a DMO to return this information. The good news is the documentation for the upcoming release of SQL Server 2014 looks to include a DMO, but that doesn’t help those of us who are running SQL Server 2012. I’ve developed a function, dbo.fn_hadr_is_primary_replica, to provide you...
Continue reading...TSQL Code
An Alternative to SELECT COUNT(*) for Better Performance
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...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...
