It’s another T-SQL Tuesday! This month, it’s hosted by the one and only Deepthi Goguri (b | t). This month, Deepthi has two question for us:
Now that we understand a little more how dynamic SQL works, let’s see how it helped me solve the problem.
What is OPENROWSET?
OPENROWSET is a functionality that allows you to access data sources outside your current server. This could be reading from an Excel file or calling another SQL Server instance. You’re able to treat that other data source as record set, or derived table, and work with the rows returned as you would a local table. One reason you may want to do this is that you need to use a stored procedures to query data from other servers and bring the data together, effectively creating an ELT (Extract – Load – Transform) process without having to use SSIS or Azure Data Factory (ADF).
Continue reading “OPENROWSET, Dynamic SQL & Error Handling”I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an interesting problem that had me puzzled. I found a bunch of different blog posts that pointed to me to the right direction but required a little extra work to find the solution.
There are several concepts that are at play here, so I’ll try to break this out so we can put the pieces together. The first once is centered around dynamic SQL. There are two parts of this I want to make sure we understand first – how it fits into sessions and how it gets executed.
Continue reading “Dynamic SQL: Sessions and Execution”Happy T-SQL Tuesday! Kevin Kline (t | b) is our host this month. You can read his full invite here, but here’s his request for us:
Tell us the story of how attending an IT conference or event resulted in an amazing career or life opportunity.
This seemed like an easy thing for me to write about when I first sat down. It should be simple enough. But it feels a lot harder because it’s not been a straight line or a single event.
When I think about conferences or events, the one that everything seems to revolve around for PASS Summit, with SQL Saturdays running close behind. I have been to some virtual events but of the in-person conferences, I’ve only made it to those and they seem to stand out to me. But is there really one that was a game changer?
Looking back at attending conferences, I think about what are obvious moment that many would think of something they could pinpoint. An obvious one could be winning Speaker Idol at PASS Summit 2019. I know – we’re all tired of me mentioning this, including me. But I worked really hard on that presentation and to be honest, it gave me a needed distraction from some personal stuff going on at the time. Plus it’s a concrete moment that seemed like it should be a game changer at the time. But did it translate to changing the speaking game for me? Let’s see, I got at least one SQL Saturday rejection after winning and before the pandemic hit. Almost 4 months later, everything else in-person was being canceled and my presentations were all moved to virtual. My session for this upcoming Summit is still going to be pre-recorded\virtual as part of a Learning Path (my in-person abstracts were not selected), which means I have to wait longer to present at Summit in person. So it didn’t feel like an avalanche of speaking opportunities came with the title; it felt like “business as usual”. Do not misunderstand me – I’m so grateful for all of the presentations I have been selected to do and I really do love presenting. But in this context, I’m not sure I can claim winning Speaker Idol as a career or life changing event.
But on the other hand, I became engaged in what I was doing by attending Summit, SQL Saturdays, and the other virtual events that have popped up over the years. I learned how to be better at my job. I learned how to share what I was learning with my coworkers so we could see how we could do better. By blogging and speaking at SQL Saturdays and other events, I started getting involved in the community and have expanded my involvement when I’ve gotten the chance. I’ve been able to be a mentor and moderator at virtual events like New Stars of Data, which I know have been game changers for so many others. All of these activities together helped in the interview process for new jobs because my future employer knew who I was and what I had to offer before I entered the door – physical or virtual. It was all the little things that added up to be the game changer in my career and not a single event.
On a personal note, the connections that I have made to different people have meant the most. Whether it was a SQL Saturday or PASS Summit, I was able to connect to other members in the #sqlfamily community. I would meet someone at one event and get to know them better at the next one. And yes, I even married someone I met through this community. There are still some people that I’ve only really gotten to know virtually over the past couple of years. In some ways, it’s been harder to create these connections virtually, yet many have still been able to happen in spite of and because of it. But if I have questions, I know I can reach out to with questions or get feedback on something I’m working on. But more importantly, so many of these people I can now call “friends” and I hope they know they can say the same about me. And that means more to me than anything else. Which, by itself, is a true game changer.
But can I really claim any of these connections as stemming from a single event? Connections take time to develop so I don’t feel as if I can.
I guess if I did have to focus on one, it would be PASS Summit 2016. This was the conference where I started making the connections to people in the community and understood how to make the most of these events in terms of what I needed to bring back to my day job. It’s where I found my passion for this community, which led to everything else that I’m doing. I’ve written about this before and I still feel the same way. One of the things I’m looking forward to by attending Summit this year in person is that I can get back to what made this such an important conference to me to begin with – focusing on learning the things I need to learn and re-connecting with this community where I feel at home.
Thanks to Kevin for hosting this month! Looking forward to reading everyone’s contributions!
It’s time to do the round up of this month’s T-SQL Tuesday entries. It was great to see so many people responding, including at least one new participant. I think there are a lot of kindred spirits here, as in we’ve all felt each other’s pain. (I know that I personally can relate to way too many of these things.) And I truly enjoyed reading everyone’s post.
So let’s go through the run down:
Continue reading “T-SQL Tuesday #152 – Round up”Hi there, T-SQL Tuesday community – long time poster, first time hosting…
That’s right, I’m hosting T-SQL Tuesday this month and I’m super excited about it!
So, what shall we talk about?
Continue reading “T-SQL Tuesday #152 – Invitation”I had the opportunity to do a presentation at work last week about SQL Server 2022. I titled it “Features in SQL Server 2022 We’re Looking Forward To”. I threw together a list and ran it by some coworkers first. Of the list of approximately 20 items, only 4 were deemed low priority so it would be OK if we didn’t talk about. I only had 30 minutes so I couldn’t even do a single demo. I think I had more links to various pages and blog posts than bullet points about what the features were.
But it definitely help me solidify my thoughts about SQL Server 2022 – this is a major release that requires a lot of attention.
Continue reading “Initial Thoughts about SQL Server 2022”It’s another T-SQL Tuesday! This month, Mala Mahadevan (t | b) is hosting. Her question for us this month is:
What are the T-SQL coding rules that are appropriate for where you work and what you do?
When I think of what my coding standards are, I tend to think of it as how would I review scripts for a pull request (PR). I think my past coworkers can attest that I can get quite picky when I look at code. So if I were your reviewer, what sort of things would I comment on?
Continue reading “T-SQL Tuesday #151 – Coding Standards”Welcome to May’s T-SQL Tuesday! Thanks to Kenneth Fisher (t|b) for hosting! This month, he’d like to hear about our first technical job. As he says: “I know most DBAs don’t start out working with databases so tell us how you did start.”
My first technical job actually came out of my first job out of college. I was an English and History major who wanted nothing to do with math or science. I was always pretty good with computers; I always credited that to having a computer in my room my senior year of high school – not because I was into computers but my dad was and he got tired of me kicking him off his computer to write papers for class. But I never wanted to take a computer science class or learn to program. (Cue laughter!)
Continue reading “T-SQL Tuesday #150 – My First Technical Job”Happy 2022! Let’s start the year off right by joining this month’s T-SQL Tuesday blog party.
Andy Yun (t | b) is this month’s host. Our challenge this month is:
…to think about something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something.
There’s a whole lot of things I could probably talk about, but I think we’ll stick to SQL Server for now.
I was always taught to clean up after myself so I thought dropping my local temp tables after creating them in a proc did that. I knew that once the session was dropped, the table no longer was there so SQL Server would eventually take care of it. But then you had the cases where you ran the same stored proc multiple times in the same session so you wanted to make sure the table was gone at the end of your procedure to prevent issues.
Pam Lahoud (t | b) has a great session called: TempDB, The Good, The Bad, The Ugly. She presented it at the EightKB conference but I happened to catch it at a NESQL meetup. I highly recommend that you watch it; it’s linked in the title. TempDB is one of my weaker areas and I learned so much by watching this.
My “a-ha!” moment was when she mentioned that you don’t need to drop temp tables that you created at the end of the stored procedure.
Wait, wha???
Yes! In SQL Server 2005, Microsoft added temp table caching as a way to solve metadata contention in tempdb. When you “drop” the table as part of a stored procedure, it doesn’t really get dropped at all. There is a copy of the table’s metadata – and its statistics – that gets left in the cache. So when you call the stored procedure again, the statistics are available for the procedure so SQL Server can use that to help create the execution plan.
There were two things about this that kind of blew my mind. First, the statistics of that temp table are reusable. If you set up a temp table and if it’s similar in scope each time you call the stored proc, you already have an execution plan cached that can be reused. Second, I’ve been doing this wrong since SQL Server 2005. That’s a really long time to not know that you don’t have a to drop the temp table at the end of the proc. You still have to make sure your stored procedure can work properly if you run it multiple times in the same session.
I know my overly simplified explanation doesn’t go into all of the details. Luckily, there are other people who can explain it much better than I can. A couple of resources if you’re interested in learning more:
There is a lot that goes into this and a lot of different caveats. It’s worthwhile to read up on these.
I can’t wait to read everyone else’s posts and unlearn something that I had thought was good practice.
Thanks for hosting, Andy!
Deb the DBA 