Hell Here! Get Your Fresh Hell, Here!
Let’s face it — as far as developer tools go, SSMS is pretty bad.
Intellisense? The jokes write themselves.
Don’t get me wrong, SSMS is a good management tool, but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute — F5 — ?).
When you’re writing some simple queries, it’s fine.
When you’re writing code with lots of branching, dynamic SQL, temp tables, error handling, and so on… Notsomuch.
Here are some tips I’ve put together after a couple years of wrangling the Blitz scripts in the First Responder Kit.
Tip #1: Format Your Code
There’s no shortage of free and paid tools out there. This list from the Recently Legendary Aaron Bertrand on Stack Exchange has both.
- http://www.red-gate.com/products/sql-development/sql-prompt/
- http://www.apexsql.com/sql_tools_refactor.aspx
- http://www.dpriver.com/pp/sqlformat.htm
- http://www.cleancss.com/sql-beautify/
- http://www.sql-format.com/
- http://poorsql.com/
- http://www.freeformatter.com/sql-formatter.html
Normalization isn’t just for data. Normalized coding patterns help you keep things organized.
This really helps in those sticky situations where you have a BEGIN without an END, a missing or misplaced parenthesis, or some single-tick nightmare of nested, nested, nested dynamic SQL.
Tip #2: Always Be Debugging
If you’re writing dynamic SQL, always add a Debug mode. It doesn’t have to be anything fancy at first, just something like:
IF @Debug = 1 BEGIN PRINT @MySQLInjectionGift END;
If you find yourself writing blocks of dynamic SQL that don’t fit in a single PRINT output, Helper_Longprint by Yildirim Kocdag is awesome.
When you get further into things, you may also want to build in a no-execute mode so you’re not off running bad dynamic SQL blocks.
Tip #3: Keep Track Of Temp Tables
… And I guess table variables, too.
In a lot of the Blitz procs where we use temporary objects, I’ll set up a Debug block that will select data out of all the temp tables we’ve put data into.
Of course, if you have a lot of temp tables, it’s easy to get lost. When I set up debugging for those objects, I like to include the table name as a column, too.
Now you can see what did (or didn’t) end up in your temp tables.
Tip #4: Know Your Place
Don’t be afraid to use RAISERROR to mark off sections of your code. This also belongs in a Debug mode so you’re not needlessly returning noise, but can be super helpful for you to figure out things like which code path was hit and which section throws an error executing common code.
Why RAISERROR over PRINT? Nice message formatting, and you can use WITH NOWAIT to print messages immediately.
With PRINT, you may be stuck waiting for enough buffers to fill up to display a message.
Tip #5: Know Your NULLs
This is another one for dynamic SQL peoples. It’s so obvious, you’ll kick yourself if you haven’t already thought of it.
IF @MySQLInjectionGift IS NULL BEGIN YELL ABOUT IT BEING NULL END;
When I first started writing dynamic SQL, I can’t tell you how many times I got eaten alive by a NULL variable or value making my whole string NULL.
Which brings us to our last tip!
Tip #6: Know Your Variables
There are two ways you can do this. Either create a variable logging temp table and insert the variable name and value to it, or just use RAISERROR to print the contents to the console. In either case, this is a life saver for figuring out what values got assigned things and when.
If variables change assignment frequently, RAISERROR is a better option. If they get assigned once, the logging table is just fine.
Wishlist
While I wish SSMS, and SQL Server, had better ways of handling dynamic SQL, color coding parenthesis pairs, and matching BEGIN/END blocks better, those just aren’t built-in tools or features currently.
Many tools that are better for development aren’t necessarily better (or even good) for management. It’s a pain to switch back and forth if you need to do both regularly. Third party tools have filled many gaps, but I reckon there’s just some behavior that can’t be altered unless you get into the actual bits behind SSMS.
Maybe someday.
Thanks for reading!
55 Comments. Leave new
Why not use Visual Studio for development with source control and half automated deployments using DACPACs? As you say SSMS isn’t a development tool so why make it one?
You had me until DACPACs. Oof.
I have had a lot of success implementing continuous integration using dacpacs. There is a lot to look out for but overall with correct VS configuration more consistent approach to database deployments. Why do you not like them?
Either way you could just publish from VS if you have correct permissions. Not forcing to use DACPACs just saying they are there.
Marcin — they’re cumbersome, unintuitive, and don’t help with most of the stuff that I talked about in this post.
My goal here isn’t continuous integration, it’s just trying to figure out what’s going on when I hit F5 (hopefully!).
Thanks!
Point taken but since we are talking about development: Import DACPAC into visual studio where you don’t have to think what happens when you click F5. VS will validate code, highlight problems, it can even apply your custom code syntax highlighting and formatting standards. Such as “fail build if 4 part naming standard is detected” Then you can publish changes straight from VS or generate DACAPCK for deolyment. You can also target a specific version and edition to validate against.
Sometimes I’ll split up a stored procedure by wrapping subsections in:
BEGIN –Comment explaining this code block
–Actual Code
END
There are two benefits of this over a standard comment:
1. This approach makes it obvious which chunk of code the comment applies to.
2. This makes the subsections of your SQL collapsible.
I do this sometimes. Something else I do is add comments to my ENDs to help me keep track of large chunks when perusing the code. Of course ctrl + ] helps a ton, too.
IF @Debug = 1
BEGIN
do stuff for 100 lines
END — IF @Debug = 1
what ist ctrl + ] supposted to do? It does not work in my German SSMS
@Thomas: Ctrl+] Jumps between “syntax pairs” (e.g., Begin/End). See https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts?view=sql-server-2017
How to use it please elaborate more with example
You need to have code with a BEGIN/END block or similar. Put your cursor on the block of interest and press CONTROL+] and it will navigate to the corresponding BEGIN or END etc.
Is not SQL Server Operations Studio the tool meant for Database development?
Eduardo — no, it’s just SSMS for other platforms.
Eric, much thanks for the article. You stated that SSMS is not a good development platform. Could you give me a couple of pieces of software that are good?
Ian — no, I haven’t found one that has helped me with those problems.
try Toad (for Oracle), sorry!
Thank you Del, I will give it a go.
About dynamic SQL debugging, inspired a old blog post of yours, I wrote a wrapper for sp_executesql that takes care of tracking dynamic SQL code and performace.
Shameless plug here -> http://tsql.tech/tracking-dynamic-sql-performance-automatically-with-a-wrapper-on-sp_executesql-dynamic-sql-inception/
Very cool! Have you been using it much?
Thanks!
Yes, I had a DW application composed by a lot of (massive) stored procedures that used Dynamic SQL for every statement, of course it had issues (and of course the cached plans were unavailable for reasons).
It eased my life as fortunately the sp_executesql statements weren’t using parameters hence with a simple find/replace I’ve switched each call with my wrapper and got the data I needed.
I wanted to share it with the world 🙂
I’ll mention [Redacted] as a useful addin I use every day,
mostly for its “script this thing out to a new SSMS tab” function, which is mapped to F2 (too close to F5?),
and also the fact you can Format T-SQL with a click of a button.
[URL Redacted]
Psst – SQLer – if you’re going to promote a paid product, please disclose your affiliation when doing that.
To learn more about why:
https://en.wikipedia.org/wiki/Astroturfing
If you find yourself needing a 1,000 line stored procedure stop! Find someone who can write what you need in C# or Python, which is actually capable of being debugged and modified later !
Yes sir. I second this.
Rich — sure, all of the Blitz procs are totally open source. You’re welcome to get started on that.
Gotta give it to you Erik. When it comes to Blitz you have guys done fantastic work not only developing it but maintaining it (thank you and the team!) And I think that’s one of those things that by design has to be in 100% TSQL and not python or C#… however think about blitz in C# with a gui! 🙂
Marcin — we actually tried that a while back, but canned it. Brent knows better why than I do.
It’d run Blitz, and produce a PDF of the results. I think the issue was with keeping the script up to date, or fixing bugs.
Apps are hard.
Yeah, we had problems getting it into the Windows App Store – at the time, they wouldn’t allow anything that depended on database code, wouldn’t let us query databases, etc. We gave up on dealing with that.
It’s totally open source with the MIT license though – you’re welcome to build one yourself.
Generalizations like that are misleading. Use the best tool for the job at hand. If your 1,000 line stored procedure operates on large sets of data, then it will probably perform better than C# or Python while minimizing network traffic. If your procedure contains lots of logical branches and/or unavoidable RBAR, then of course SQL might not be the best choice.
I love sp_Blitzgoodness so much because it *is* only SQL and has no other dependencies. Face it, in a db, SQL *is* the lowest common denominator. I beg thee Ozarians: please keep it this way.
Pete – aww, thanks! Yeah, that was a big driver for us when we built it, too.
One practice I’ve found helpful in longer procedures is an @ErrorLoc variable that I SET right before every non-trivial statement. Then, in the CATCH block, I always include @ErrorLoc somewhere in the message. (I combine @ErrorLoc with a @Counter variable sometimes so that I can tell which iteration in a loop triggered the CATCH). ERROR_LINE() works ok in simpler procs, but in a complex proc that calls sub-queries and such, explicitly naming the statement/sub-proc about to run gives me high-confidence immediately as to which statement is generating the error.
But yes, it pollutes the code quite a bit, so it isn’t for every proc/script.
Every now and again a blog post makes me rethink (and improve) how I write & implement SQL code. This is one such blog. Appreciate the effort, Eric.
Thanks Andrew! Just checking — you read this post, right? 😉
Tip #4: Know Your Place
IF @debug = 1
BEGIN TRY;THROW 50000,”,1; END TRY BEGIN CATCH;DECLARE @ErrorMessage NVARCHAR(4000);
raiserror(‘hello I am here’,16,1)WITH NOWAIT;
END CATCH
But I have no idea how to smartly show the call stack
How can i made my SQL DB secure. i saw video online some hacker hack website by using SQL Injection?
Try starting here: https://groupby.org/conference-session-abstracts/sql-injection-attacks-is-your-data-secure/
Thank you very much
I’ve been doing this to debug dynamic SQL statements in SQL2016:
select * from
string_split(replace(REPLACE(REPLACE(@sql, CHAR(13), ‘^’), CHAR(10), ‘^’),’^^’,’|’),’|’)
It’s a simple solution to split the statement into multiple rows on a select to get over the 4000 char print limit (or nasty formatting when you do a select @sql) .
I’ve found the following usually works quite well (clicking on the XML result opens the full text in an XML document:
SELECT CAST(N” AS XML);
Oops – HTML fail!
SELECT CAST(N'<?q ‘ + @SqlText + N’?>’ AS XML);
Peter — yeah, unless you have any characters in there that XML doesn’t like: ampersands, quotes, equality than, less than, etc.
Check out my answer here for a workaround with XML.
I guess that’s a better approach, certainly simpler. I now remember seeing it before, but can’t remember why I stuck with the string-cat version.
I just did a quick test with single and double quotes, less-than and greater than signs and ampersands, and both approaches seem identical – not arguing, just wondering what else I’ve missed.
[…] Erik Darling has a few hints for debugging large stored procedures in SQL Server: […]
There’s a bug where RAISERROR WITH NOWAIT also doesn’t print the messages, just like PRINT. I’ve logged it into Connect and someone from the SQL Server team took it up. Not sure what the status is now. Here’s the bug on the new feedback site: https://feedback.azure.com/forums/908035-sql-server/suggestions/32900290-raiserror-with-nowait-doesn-t-always-print-directl
This is actually buffering by SSMS and not by SQL Server itself, after 500 messages.
“Many tools that are better for development aren’t necessarily better (or even good) for management. It’s a pain to switch back and forth if you need to do both regularly”
Just had a whiff of nostalgia, thinking back to Enterprise Manager and Query Analyzer…..before they were squished into the same product. 🙂
[…] Quick Tips For Debugging Large Stored Procedures Read all tips from Brent Ozar’s Team which help you cope with loooong Stored Proc. […]
I’m surprised that there was no mention of using the Transact-SQL debugger in this article. While it can be somewhat tricky to get working initially, I find it a considerable advance over inserting print statements into your code!
Tony — I did mention at the beginning of the article 😉
I’m really not a fan of using it in shared development environments, because it can cause nasty blocking.
Thanks!
Nice to see that I’m doing a lot of the suggestions here already. I have been writing progress to a table variable, then doing summary counts along the way so I can see where my rows got choked from 10,000 to 100 or perhaps didn’t update in a particular category. This only populates and gets returned when @debug = ‘y’ (went with a char(1) rather than bit for readability). That way I can see where my plans went awry. I also second Brian Boodman’s comment about the BEGIN/END blocks around code chunks, because I can shrink whole sections if I don’t want to have to keep looking at them/through them.
Forgot one, I’m also enjoying using a @unittest variable so that I can control what data is being processed in the sp, and I know what to expect of the output (compared to what actually happens). Working toward using unittests to check the output matches expected
Can you explain with scenario how Debug option used in Store procedure
Rakesh – that’s a neat idea for a blog post. I might write that up in the coming months. (Not quickly though – if you’re in a rush, your best bet is to ask on a Q&A site.)