• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

Top 10 SQL Server functions every DBA should know by heart

February 17, 2017 by Kevin3NF 4 Comments

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

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: Accidental DBA, Apprentice, Beginner, EntryLevel, SQL

About Kevin3NF

Reader Interactions

Comments

  1. Greg says

    March 28, 2018 at 12:37 am

    Let me add a couple functions that I also think handy:
    – sp_who2 to get a quick view on what is going on on the server
    – set statistics time / IO to see execution results
    – and dbcc freeproccache / dropcleanbuffers- clearing the cache before watching any stats

    Reply
    • Kevin3NF says

      March 28, 2018 at 6:44 am

      I use Set Statistics all the time (see number 8 on my list). Sp_WhoIsActive for me is far superior to sp_who2. Freeproccache and dropcleanbuffers are great in Test/Dev…do not use in prod 🙂

      Reply

Trackbacks

  1. Functions To Know By Heart – Curated SQL says:
    February 21, 2017 at 7:05 am

    […] Kevin Hill has a top ten functions list for database administrators: […]

    Reply
  2. DBCC Opentran, simplified! says:
    April 3, 2017 at 8:01 am

    […] my Top 10 SQL Server Functions post awhile back, I listed DBCC OPENTRAN as one of the top 3, and for good […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • HealthCheck
  • Index
  • Install
  • IRL
  • Management
  • Migration
  • MySQL
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • Podcast
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • Upgrade
  • vendor code modify
  • video
  • vNext

Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy
 

Loading Comments...