Developing and debugging stored procedures, dynamic SQL, and related code.
34 associated posts33 primary posts
It's the perfect time of year in Las Vegas when we can chill outside by the fire, enjoy a glass of wine, and go through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=esMYx_mbfqo
My cruise ship took a detour to Honduras to avoid the hurricane. Let's hang out on the beach and go through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=aD7FnCRzJL8
For this week's Query Exercise, your mission had two parts:
Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn't belong
Write a more high-quality query to identify exactly which rows we needed to handle
Kon'nichiwa! My vacation tour continues in Osaka, Japan, a city famous for its street food, nightlife, and host and hostess clubs. I'm usually the kinda guy who does classy dinners at 5PM and then hits the sack by 9PM at the latest, so you might wonder how I got here. The answer is that Yves planned this trip out, hahaha, so here we are. As long as we're here, let's go through your top-voted questions from https://pollgab.com/room/brento.
The last Office Hours of 2023 featured some oddball questions from https://pollgab.com/room/brento. Not bad, just ... odd.
https://youtu.be/prkpWssHsaE
It's back to lighter fog-friendly clothes as the ship pulls into San Francisco and I answer your questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=ZT5hr9JZz7k
This week, a lot of the questions you posted at https://pollgab.com/room/brento just need pointers to resources at other places. Welcome to the Read This, Watch This series of answers.
Don't Blame Anthony: Our developers prefer writing TSQL queries directly in their .NET code rather than writing sprocs and calling them. What are the pros / cons to this approach?
When you write dynamic SQL, start like this:
[crayon-6a6ecdeae2c82504667066/]
Right after the SELECT (or INSERT or UPDATE or whatever), immediately put a comment - using /*, of course, because you're not a terrible person.
That way, when you're looking at the plan cache or monitoring tools, you can see what generated the dynamic SQL, and where you need to go if you need to performance tune or fix it.
Some of the questions y'all post at https://pollgab.com/room/brento are open-and-shut cases. I was going to do a one-word-answers edition, but I think this is even better: gifs.
GI Joe DBA: Thoughts about IT professionals Unionizing? IT professionals are in demand. Because of our demand, should we use this leverage to unionize, raise the bar, force better health benefits, retirement benefits, etc for ALL.. US Citizens? Ever discuss this with colleagues?
Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot.
This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about good best practices after you've written the first one - things like how to catch errors, how to pass in multiple values, how to debug without the debugger, and more.
Before Fjöruborðið opens for lunch, I answer a few of your questions from https://pollgab.com/room/brento including:
00:00 Introductions
00:29 Peter: What is your opinion about the SQL generated by Entity Framework?
02:00 Peter: When would you switch from EF to stored procedures?
02:45 Always Learning DBA: What are free resources to help me transition into an architect or data scientist role?
04:43 How is the auditing and monitoring different for Azure SQL DB?
06:33 Recap
When you're working with T-SQL, you'll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.
What SET NCOUNT ON does is prevent the "1 row affected" messages from being returned for every operation.
A perfect storm of unusual problems caused a client to ask, "What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it - like numbers or dates?"
It actually works pretty well in most situations, believe it or not.
During the quarantines, I'm looking for new ways to help you level up for free.
I decided to start live-streaming whenever I'm working on things I can share with you publicly. I wanted to add a new check to sp_Blitz, so I figured I'd do it live on camera while you watch, and talk you through my thought process.
Over and over again, I run into applications that don't parameterize their queries, and as a result: Compilations/sec is nearly as high as Batch Requests/sec CPU is high due to all those compilations Plans rarely last for more than a few hours in the cache Monitoring tools that track resource-intensive queries are useless because every…
A client asked a great architecture question that deserved its own blog post:
Should we use more "stored procedures" for select, insert, update, delete or should that just be sent as queries from the application?
If you ask a junior database administrator where to put something, she'll probably say "in the database" because that's the thing she has control over. That's fair - DBAs love having control over stuff. However, let's step back a little.
When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here's a simple example: [crayon-6a6ecdeae3960130923475/] The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all…
Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both.
For performance reasons, we decide to build dynamic SQL:
[crayon-6a6ecdeae3c43995333914/]
When we run it, it works, and we can see the query on the Messages tab in SSMS:
For this month's T-SQL Tuesday, He of Perfect Hair, Bert Wagner asked, "What code have you written that you would hate to live without?"
There are a lot of pieces of code I frequently need to reuse when working on the First Responder Kit, like string splitting, XML PATH-ing, and converting milliseconds to some human consumable elements.
It Used To Be
That when you wrote scripts that had to work across different versions of SQL Server, you were pretty safe.
Microsoft would release a new version, or a Service Pack that had something new in it, and it wouldn't start showing up in older versions.
