Top 10 SQL Server functions every DBA should know by heart

I suck at syntax.   A lot.

Basic Selects and Joins I’ve got.  Insert, Update or Delete a row?   No problem. Beyond that I have to look it up more than I probably should. But, since I was last a SQL developer in 2001, I function just fine.  I am very familiar with pretty much all of the T-SQL keywords, what they do, etc.  But I just don’t use them often enough to commit them to memory.

If you are a DBA, there are many functions built into SQL Server that you MUST commit to memory.  If you don’t…they will creep in there and take up residence anyway.   Like Borg…they will assimilate parts of your brain. Just play along and all will be ok.

My top 10 list:

1. @@servername – I use ‘Select @@Servername’ literally every morning. I open SSMS, go to registered servers, Select my ‘Prod’ local group and run this in a query…just to see if all of my little Production server children respond.  Takes 5 seconds to make sure everyone is online that should be.

2. @@version – Depending on the client I may use this every day if their servers are everything from 2005 RTM to 2014 SP-whatever.  Currently, all of my servers are exactly the same.   Its a little weird.  But in a good way.

3. DBCC Opentran – log file filling up?  Processes blocked and timing out? See if some doofus (hopefully not you) left a transaction open and went to lunch.  If you are lucky you can kill it.   Unlucky and you find an undistributed replicated transaction.

4. Getdate() – Not sure why, but this winds up in probably 80% of the scripts I write.  Not surprisingly, it often brings along it’s little brother, DateDiff.

5. sp_Configure – If you don’t know this one, run it by itself against your test instance…you get back a ton of information about the instance.   Min/Max memory, Backup Compression, etc.  Basically a bunch of the stuff you see if you right-click the server instance and browse the GUI. And you can make changes here too.

6. DB_Name()/DB_Id() – When the view you are using give you the DBName and you need the ID, or vice versa…use this to flip it.  Go ahead:

Select DB_Name(dbid), * from sysprocesses

 

6.5 Object_Name/Object_ID – same as above but for tables, indexes, etc.

7. Set NoCount On – Don’t need (53 row(s) affected) in the messages pane? Use this at the top of your query

8. Set Statistics IO On/Off – want to see a bunch of info about scan counts, logical and physical reads, etc. from your query?  Use Set Statistics IO ON.   For duration info, use TIME ON.  Generally speaking, bigger is not better on this.

9. Min/Max/Count/Sum – “Help! I need a quick query to give us the most recent sale to each client in TX!”  MAX(DateSold) is your friend.  All of these count as aggregate functions, and you will need to understand GROUP BY as well.  Select Count(*) from Table….google up a better way 😉

10. Cast/Convert – Need to Concatenate an INT field into a string?  CAST it into a varchar():

Print 'Today is day ' + 1 + 'of the rest of your life'
--Conversion failed when converting the varchar value 'Today is day ' to data type int.

Print 'Today is day ' + cast(1 as varchar(1)) + ' of the rest of your life'
--Today is day 1 of the rest of your life

Convert is used when working with dates in one format and you need them in another.   Read the linked doc and save in your favorites

Honorable mention: sp_whoisactive by Adam Machanic (b|t).

This would be top 5 on the list if Microsoft had written it.   Step one in troubleshooting a performance issue, right after checking Task Manager to see if it really IS SQL Server taking up all the CPU, or some other program gone wild.

You, my dear rookie DBA need to get very comfortable with all of these.   Look them up, test them, and memorize.  Don’t just bookmark…memorize them.  Give your brain some muscle memory.

Did I leave out something really obvious?  Should it have been in my top 10?  Tell me about it in the comments!  Or harass me on Twitter 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs