SQL Server Performance Office Hours Episode 36
Questions:
* Back in the days of yore, when you worked for somebody else, did you ever become despondent with your work (or SQL). If so, how did you re-motivate yourself?
* Why have I never heard you suggest ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT?
* For a table-valued function, which would *generally* be the better return type: `RETURNS @tbldata TABLE(ID INT IDENTITY(1,1), col VARCHAR(MAX))` or `RETURNS @tbldata TABLE(ID INT NOT NULL IDENTITY(1,1), col VARCHAR(MAX), PRIMARY KEY CLUSTERED (ID) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY=ON))`
* if you had to do work in another database what would it be?
* what dou you think heaven purgatory and hell are?
To ask your questions, head over here.
Video Summary
In this video, I, Erik Darling from Darling Data, hosted a lively office hours session where I answered five community-submitted questions and shared my insights on various SQL Server topics. Whether it was re-motivating yourself in the face of work challenges or choosing between different table variable configurations for functions, you got direct access to my expertise. I also took the opportunity to discuss some of my personal preferences, like working with DuckDB, a database platform that has really caught my attention due to its innovative approach and impressive features. If you have any questions or want more detailed advice on SQL Server topics, feel free to ask in the comments or through the provided link.
Full Transcript
Erik Darling here, Darling Data. And of course, it is a day of the week that begins the work week, no matter what your language settings are, which means it’s time for office hours, where I answer five community submitted questions, and you get five Erik Darling submitted answers. It’s amazing. What a fine transaction that is. If you want to ask me a question, the link to do that is down in the video description. There are many other helpful links in the video description as well. You can hire me for consulting, you can buy my training, you can support this channel with money, and if you don’t feel like doing any of that stuff, but you still like the content, well, liking, subscribing, and telling a friend that this channel is the best, well, it’s a pretty good way to help me out a little bit. The only thing left on my calendar for the year, past Data Community Summit, Seattle, Washington, Washington, November 17th to 21st, where it’ll be two days of T-SQL pre-cons with me and Kendra Little, and a bunch of other days of other stuff. So you should come, and you should come to my pre-cons, and I don’t know, maybe I’ll dress like a pilgrim or something. Anyway, let’s do the office hours thing, because that’s what we’re here to do.
All right. Here we go. Back in the days of yore, when you worked for somebody else, did you ever become despondent with your work or SQL? If so, how did you re-motivate yourself? Well, I am in a weird place in life, because I love what I do. And you know what they say? When you love what you do, you’ll never take a day off in your life. You know, of course, there would be times when I would get frustrated with something I was working on, or, you know, maybe someone I was working with. I don’t mean like within a company. I mean like, you know, like externally. But, you know, I’ve always found it pretty easy to keep going and find something new and interesting that catches my eye or imagination with SQL Server. So I consider myself very fortunate in that regard. If you are having that problem, I would maybe suggest seeing if there is something else maybe that you could focus on a little bit. Maybe just, you know, change a pace, change a mindset, change a point of view is what you need. Other than that, I don’t know.
You know, there are certainly downsides to working for someone else, like always having to build something that someone else thought of. But, you know, there’s downsides to working for yourself too. Like, sometimes you have no idea what to build. So it’s all, the grass is not always greener. But, I don’t know. Really, I don’t know what you’re doing with SQL or with your life in general.
So, I can’t really give you more specific advice. But, you know, I consider myself fortunate that I have never really had that big of an issue. All right. Next question here. Why have I never heard you suggest alter database current set read committed snapshot on with no wait? Because it’s a stupid waste of time. Just use with rollback immediate and you’ll actually get what you want to happen.
No wait doesn’t really help that much. All right. For a table valued function, which would generally be better? Be the better return type. Returns table data, table ID identity, call varchar max.
Or returns table data, table ID not null identity, call varchar max. Primary key clustered with optimized for sequential key. Why the hell would you turn on optimized for sequential key for a table variable?
Inside of a table valued function. Son, put the database down. Optimized for sequential key does not help you with that.
And, I mean, I’m sorry. I’m hyper focused on how silly that is. So, other than that, you know, the primary key obviously can be useful in some cases if you have equality predicates or certain join types to the table variable.
Without knowing more about how you’re using it, I can’t tell you if the primary key would be helpful for anything. Of course, even indexes on table variables do not get any statistical information. So, and have all the same limitations within a multi-statement table valued function.
It’s been discussed many times on the channel. So, I don’t really think that either one is going to solve a giant problem for you. But if I had to opt for one, it would probably be the one with the clustered primary key on it.
Because it doesn’t really hurt you in any way. And at least for some types of queries, it would be less painful or less annoying. It would still be equally as painful, just maybe less annoying.
All right. Next question. If you had to do work in another database, what would it be? I don’t know. It would still be work.
I get what you’re asking. If I had to work with another database platform, what database would I want to work with? Well, I’ve worked in a few other database platforms. And I got to tell you, I wasn’t that impressed.
You know, there are a couple of nice things that SQL Server doesn’t have in some places. And there was some stuff that I was like, wow, I really wish SQL Server had, I really wish other database had this SQL Server thing.
But I think the database that I am most enamored with currently is DuckDB. I think they are doing absolutely fantastic things. I think the way that they have narrowcast the purpose of their database is fantastic.
I love their focused efforts. And I love the stuff that they’re doing to extend SQL and their database. They can do all sorts of fun stuff.
Like they can read like Postgres and MySQL database files. They have a CSV import that is so simple and so good at what it does that the first time I ever tried it, it made me emotional. It was like, wow, you just did that.
I was like, I got a little misty looking at it. So if I had to do work in another database, it would definitely be DuckDB. I just don’t know how to make money with DuckDB. So that’s another thing.
All right. This is apparently going to be a quick one. All right. What do you think heaven, purgatory, and hell are? Well, I am not the most spiritually scholarly person in the world.
So I don’t know that I have the best point of view on that. I can tell you that for me, heaven would be smoking cigarettes in a French graveyard. Purgatory would probably be being on a team’s call for eternity or however long purgatory lasts.
If I recall correctly, there are some rules and limits around how long you spend in purgatory depending on your situation. And, well, of course, hell would be waiting for a squat rack for all eternity. I think that would be my, those would be my leanings there.
All right. I think that I’ve answered all those questions sufficiently. This is a really short one.
Anyway, I hope you enjoyed yourselves. I hope you learned something. Thank you for watching. And I will see you, well, definitely in the next office hours, but certainly in tomorrow’s video as well. So we have that to look forward to.
So perhaps we are not in heaven, purgatory or hell unless one or more of those is recording YouTube videos, in which case I am in whatever one that is. All right. Thank you for watching.
Thank you.
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.