Aside from spinning up a SQL Server instance container, the free Azure SQL Database is another great tool for learning SQL. You can even use it for low-traffic or lightweight app. See the documentations for the limits. I will not be responsible for your usage.
That said, let’s provision the database.
Provision an Azure SQL Database
Go to you Azure Portal and search for Azure SQL Database
On the upper left-hand of the UI, click on Create and select SQL database (Free offer).
Configuring an Azure SQL Database is pretty much intutive. For the Server option, use an existing SQL Database Server or create a new one.
Click Review + create to finish the setup.
Connect from VS Code on a MacBook
Go to your Resource Group and find your Azure SQL Database. Or, you can simply search for Azure SQL Database in the search bar again and that will take you to your databases.
Copy the Server name.
Now, open your VS Code (install the mssql extension if you haven’t already). Why VS Code? That’s because Mirosoft will never port SSMS to macOS. That’s why.
Create new connection. Look for the plug icon with the ‘+’ sign next to it.
For the Input type, Browse Azure wouldn’t work for me even if I already took care of the networking setting. Let me know in the comment if you made it work. Using Parameters worked for me.
Paste the Server name. Don’t forget to tick the Trust server certificate. Use SQL login and input the sa user and password that you set when you provisioned your SQL Server.
That should be it. Your Azure SQL Database is now ready to use.
Alright, it’s the last one of the week, you can all breathe a sigh of relief, till next week, till hell begins again, unremitted, anyway, don’t let me rub off on you, in this video we’re going to learn some more about T-SQL, stuff around variables and date math, so that’ll be fun right, everyone likes fun, alright, down in the video description, you will find potentially one of the more important links that you will ever click on in your life, and that is the link to purchase this training for $100 off, there are also other links in there, which I think are equally valuable, depending on your goals and needs in life, where you can hire me for consulting and become a supporting member of this very YouTube channel, you can also ask me office hours questions, which I will answer every Tuesday, faithfully.
I used to answer them faithfully every Monday, but then I cheated on Monday with Tuesday, and now, I don’t know, I’m stuck with Tuesday, Monday dumped me, I don’t know, the whole sordid thing, might have to, I don’t know, I don’t know what to do here, it’s a sordid love triangle, anyway, and if you perhaps want to do, just do me a solid in life, you can of course like, subscribe, and tell a friend, in the video description there’s also links if you want to…
Have free SQL Server performance monitoring, you can do that, from me, it’s my gift to you, just for existing, and using SQL Server, that’s all, that’s it, the only bar for entry, totally free, open source, no weird sign up, phone home stuff, I don’t want to know more about you, or anything like that, just a bunch of T-SQL collectors, running on a schedule, collecting all the important things that you would ever want to know about your SQL Servers, wait stats, blocking, deadlocks…
Bad queries, CPU, memory, disk, you name it, it’s all in there, doesn’t get better than that, especially for that price, alright, anyway, let’s talk about this stuff here, let’s do the damn thing, so, one thing that I want to talk about, and this is a good start to things, is a pattern that I see in a lot of stored procedures, that I wish that I didn’t, and that is…
We have, for simplicity’s sake, we have one parameter in here, and it is nullable, right, by default, it is nullable, so you don’t have to pass anything in here, you’re not going to get an error, it’s like, SQL Server expects a value here, right, so, what a lot of people will end up doing, is, if the date comes in as null, they have a safeguard on it, and the safeguard, I mean, it couldn’t be anything, but, we’re going to use 2013-1201 for our safeguard, and we’re going to look at the side effects and repercussions of such a bit of code.
So we’ve got query plans turned on, and if we run this, and let’s say that a null gets passed in the first time around, and this is going to run, and run, and run, run so far away, I don’t know, something like that, and we look at the execution plan, we didn’t do too well, right?
SQL Server… SQL Server guessed that we were going to get one row, we got 1, 5, 2, 6, 9, 9, 7, we got 1.5 million rows back, we did 1.5 million key lookups, and we didn’t get a very, I mean, this isn’t the worst of it, because, like, we only go about 600 milliseconds in here, so that’s not, like, terrible, but, you know, our sort didn’t get enough memory, ba-ba-ba-ba-ba, we’re all sad, we’re all having a bad time.
And what’s funny… is that if we go into this portion of the query plan, this is in the properties tab, because we got an actual execution plan, we can see the compile and the runtime value here, and notice that this query was compiled with a cardinality estimate for null, however, it was run with a cardinality, well, not with a cardinality, it was run with the requirement to return everything.
Everything greater than 2013-1201, which is quite a discrepancy in rows, isn’t it? Sure is. Sure is.
So just replacing or overwriting a null with a value in the context of, well, in this context, it is a formal parameter, does not really get you what you want, I don’t think, because you still compiled with a cardinality estimate for null.
That was what your plan was compiled with, despite what it was run with. And if we look at the histogram for the, what do you call it, that we created, the index, we have this one thing here, and we’re basically getting this estimate for the, ah, PowerShell, go away.
I don’t know why. There’s too many button combinations these days. There’s too many hotkeys. It’s getting too damn hot. We basically got this estimate, all right? So that’s not a very good time for us, all right? We’re not enjoying ourselves.
We are not having a good time. Another problem that I see quite often is a little bit more like this, where someone will have, this is an example of someone passing in an integer value that gets added to a time.
So what that usually ends up looking like is two declines. The first one is, you know, we start with the integer value, which is the date of the time when the date of the end date is this one, right?
And the second one is, the second one is the start date, which is the time when the end date is this one. And the third one is the time when the end date is this one, right? And, gee, I hope I didn’t hit a weird button there. That jumped in a strange way that frightened me. I was like, oh, what did you do?
But if we run this store procedure with the local variables in place, and we run these three representative executions of the stored procedure, note the 1, 10, and 5 here, and we will get the same bad cardinality estimate for all of them, right? And this feels like a parameter sniffing thing, and like normally it would be a parameter sniffing thing if there were a parameter, but there’s no parameter within the perimeter, there is just a local variable, so we’re getting the density vector guess, we are not getting a compiled parameter, a sniffed parameter value guess, and that becomes especially incorrect, well I mean they’re all incorrect, right? We got like SQL Server is guessing 8, 6, 9, 7, 0, 8, 0 for all of these, even though we get back far less, so the estimated and actual rows for this are way off because we used these declared variables and we added some time to them.
So, let’s skip over, that doesn’t actually run. What you’re much better off doing, for these cases, is just using the expression itself in the WHERE clause, this is identical to what we had those local, the part that we had those local variables playing in the earlier bit of code, but now when we use the parameters here, SQL Server gets not only a stable guess, but a guess that is, ah, wait a minute, I did that wrong.
What I should have noted, before running those, was this being the old version, and this being the new version, right? So this is the one where we have the local variables, this is the one where we have the expressions embedded in the WHERE clause, and if we come back and look, this one gets the same bad treatment, with the bad cardinality estimate, but this one gets a much more appropriate cardinality estimate because we did not use local variables.
variables we put the expression directly in our where clause. So that is what we want to do and that is what you want to do when you are writing your store procedures. All right it’s all for me. It’s Thursday. It’s the last video of the week which means it’s a long weekend for everyone and I will see you next Tuesday with Office Hours. All right thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Giles Edwards-Alexander does an experiment to see if decomposing a
large function helps reduce token costs, suggesting that is may now be
possible to measure the economic benefit of refactoring
The robot ban will sweep up robot vacuum cleaners too, FCC media relations director Katie Gorscak confirms to The Verge.
It's not coming for your existing Roomba, and companies can keep importing and selling already approved ones. But the government claims that future foreign robots pose a national security risk, and even robovac companies are suddenly being asked to commit to US manufacturing.
Log in
