I am part of a weekly talk show we run at the TriPASS user group, called ‘Shop Talk’. Shop Talk was the brainchild of Kevin Feasel, our key user group lead..we meet on a bi weekly basis and discuss random tech topics related to sql server. Some of these are questions from our audience, and some are just ideas for discussion that one of us come up with. I am constantly amazed and grateful for how much I learn by being part of this show – from my co hosts and from the very intelligent audience we are blessed with. Last week, we discussed Brent Ozar’s blog post on ‘What SQL Server Feature Do You Wish Would Go Away?’. The recording of our discussion (this topic starts around 26:00) is here.
I am not blogging what we discussed in its entirety, the show speaks for itself there. I learned a few features, and use cases of SQL Server features that I never knew existed. Some of these are deprecated – so why is this important to blog about? It is, because if my resume says I’ve worked on this product for 20+ years – I better know what those features are as well. The topic of Brent’s post is a rather common interview question too – I’ve asked as an interviewer it once or twice myself, and I’ve been asked about it as an interviewee several times by several people. It helps to know more ways of answering it. Below are a few terms that I learned/re-learned as I had forgotten they existed, from the show.
Use cases for cursors
I did not consider cursors as among features that have to go away. Cursors are not ideal in a set based language but they absolutely have their use cases. But I heard of two use cases that I did not know of.
1 Leaky Bucket Algorithm
If we have a bucket into which water is poured in randomly. We have to get water in a fixed rate, to ensure that extra water gets out. This is possible by making a hole at the bottom of the bucket. It will ensure that water coming out is in a some fixed rate, and also if bucket will full we will stop pouring in it. So the idea is that the input rate can vary, but the output rate remains constant.
I found this cool blog post by Kevin Feasel again on not just the algorithm but a use case for it and why this use case demands cursors. Quoting from the post itself ‘ The problem is that we have not only a window, but also a floor and ceiling’ – this means using windowing functions is not possible. This is a genuine use case among others which makes it clear that cursors are needed although should be used sparingly.
2 Quirky Update
Remember the days before windowing functions? I do!! We had to do all kinds of workarounds to get running totals..there is no need for us to revisit that any more – but it helps to remember how things were done..the term ‘quirky update’ – using variables and cursors to do what windowing functions now do – came from this article by Robyn Page. Scrolling down to the code, we can see how she does it. I have used this, several times – and I recall how hard it was when you had to write SSRS reports with running totals with logic like this. This is not an argument to keep cursors, but not one to get rid of them either. We have other ways of accomplishing what Quirky Updates used to do, and that’s a good thing to remember, for sure.
Fibers Vs Threads
When Kevin asked me if I knew what fibers were – I drew a blank. I am not aware of any such term with SQL Server. Looks like this goes back all the way to Windows NT/SQL Server 6.5 days – I vaguely do remember ‘lightweight pooling’…’Fiber is multiple pieces of code set on a single thread to execute and controlled by an internal piece of code written in the SQL executable and not by Windows itself.’ This line from an old article made me laugh out loud ‘ Fibers, called lightweight pooling, should be turned on only if CPU is 100 percent and context switching is high.’ Needless to say in today’s world lightweight pooling is no longer necessary.
Numbered Procedures
I had no idea such a thing even existed..but apparently they do…and how it works is as below..you can group procedures by naming them with a <samename>;<incrementing number> as below.
CREATE PROCEDURE proc1;1
AS
BEGIN
SELECT 1
END
GO
CREATE PROCEDURE proc1;2
AS
BEGIN
SELECT 2
END
GO
CREATE PROCEDURE proc1;3
AS
BEGIN
SELECT 3
END
GODROP PROCEDURE proc1
The ONLY advantage of such a grouping is , apparently, the last line – you get to drop all of them with one call. I am not sure what was the motivation behind such a feature, but was glad to see it deprecated now.
Those are all the quaint/interesting things I learned during this show. Thanks for reading.