Category Archives: TSQL Tuesday

This month’s T-SQL Tuesday is hosted by Tomaz Kastrun – his call is to write about how we’ve used ChatGPT, and what are ethical issues, if any, that we have encountered while using it.

I am a relatively new user. I know most people are like me, but some of us are newer than others at this 🙂 I use the free version of 3.5. I read a lot of posts and tried to understand more of how it works, but I have learned that nothing beats actual experience using it. I learned that it is really good with narrative, and one podcast I listened to recommended using it to train in doing interviews. I haven’t interviewed for a job in 6 years now. I am still not interviewing since I like where I work, but I know it is a skill that gets rusty without practice. So I decided to try it out this way.

I started by introducing myself and asking it to role play an interview for a Database Engineer role, with me. Below is the response I got.

I was looking for it to ROLE PLAY an interviewer, but it put out an entire interview for me, giving me no chance to respond. What is more, some of those lines seem very similar to *my own lines* from many years ago – I’ve used them somewhere, don’t recall where – in a blog post, or in a podcast interview. It was me, most of that. I found this rather creepy. But I continued to ask it to work with me.

I got really hopeful now. I told it what our roles are and it has clearly told me in return that it understood, so what would the response be?

Ugh. Same thing again. I didn’t get a chance to say a word, not a word. It did the entire interview by itself, this sorta thing doesn’t help much at all.

Again, no luck. Clearly reprimands don’t work very well. So I decided to give it one final shot, and it worked!

So there it was! From this point on, it was fun. Not all questions or answers were ‘correct’ or up to snuff technically, but that wasn’t the point of the exercise. The point was to get some basic practice like you would talking to a person. To get it to do this – the issue was finding the right word to grab and understand what I need. Word, not sentences, like humans would. The word, in this case, was ‘question’. After I asked to ask me a question, it was on a roll, going on and on with questions.

Ethical Issues

I think this was too basic a test to find any serious issues, but I had one concern.

In the initial round, when it spit out full interviews, a lot of verbiage was mine, but I am myself not sure where it found it. No attribution was given. I am also not sure if this verbiage would appear for someone else, like is it reusing something I said for someone else? Not a big deal, it is only an interview practice, but still, would be nice to know. More importantly, how did it know it was me? Via my email?

I asked about where it found the technical content , and the answer was what one would expect.

To conclude, I found an amazing quote in a book I read recently, for school – ‘A word is known by the company it keeps’. I think this is a phrase that is very applicable to ChatGPT. Find the right words to use with it, and limit your expectations on what you get – I don’t expect a high degree of SQL Server expertise, I did expect a conversational interview with basic answers, which can be helpful to practice, and it lived up to that.

Thank you Tomaz, for hosting.

This month’s T-SQL Tuesday is hosted by Deborah Melkin(b|t), and she has an interesting topic. She wants us to write a rant on a scenario we encountered at a client that has the common ‘it depends’ stance go right out of the window..or in other words, something that is so bad that there is no way it should be sustained practice, ever.
Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins. We would get an excel spreadsheet with *hundreds* of names, and what kind of server/database/level of access was needed by the person. We then had to get on the server and painfully create these logins with the right access. We had to set up the password to be changed to something else on first login and set up the login itself to expire in a week. This was an entire day, sometimes two days’ worth of work to set up. And they *paid* us to do that! We tried some degree of automation, like writing some code to read the excel sheet and automatically create these logins. But, it didn’t work too well – because there would be some access needed that wasn’t the norm before, or some new database, or something or the other. Surely, if you want to outsource your work and care that much about security – there could be better ways? Some of the conversations/reasoning we had with this client, trying to explain *why* this approach was so problematic even if they were crazy enough to pay us to do it – as below.
1 Outsourcing work to a place with so much turnover can cause a ton of problems. No one person knows or is responsible for what came before them.
2 Having this many SQL Server logins is also a huge problem – there is no guarantee that people aren’t sharing their passwords (we actually caught several people doing this).
3 Having two third-party companies (one was us and the other was the contracting company) – with you the customer in the middle is in itself problematic. Ultimately your data and its security is your problem, not either of these company’s.
4 The ‘it depends’ clause does not belong at all in a scenario like this. Invest in paying people well and they will take good care of your data. Operate on principle of least privilege and your data will be more secure. Don’t pay someone to do trashy work with the line assuming that they will do it for money.
Needless to say I was really happy moving on from doing this sort of work. And am extra careful when I hear of companies outsourcing their stuff for cheap – you can run into all sorts of ridiculous scenarios like we did here. Thanks for reading.

I am glad to be contributing to the 150th blog party started by Adam Machanic and has helped so many get our blogs going. This month’s T-SQL Tuesday is hosted by a dear friend Ken Fisher – Ken wants us to write on our first technical job.

The year was 1987. I was an engineering dropout who had learned some COBOL, and some DBASE at a tech school liked the latter better, and was looking for a job. It wasn’t an easy time to be a techie. Most tech jobs expected engineering degrees, it was much much later that non-engineers would be able to make their way into tech jobs. The transition from paper-based systems into the digital world was threatening to a lot of people. There were union-led strikes at banks and various government-run organizations with people objecting to the ‘new computers’ taking away their jobs. I made a few bucks teaching some COBOL on the side but was desperate otherwise to find a gig. In those days newspaper ads for jobs were very common. One ad caught my attention – a ‘Data Systems Manager’ role, at a textile company. I got my resume together and highlighted my experience with DBase and an accounts payable project that I had done learning it. Included some reports, printed on ribbon-based dot matrix printers, line by line – I can still hear them run in my head :). Two days later, I got a call for an interview. The interviewer was a 65-year-old man, an expat from the US, and getting ready to retire. He was the only person they could find who could ask me any tech questions 🙂 He asked me a few easy ones, and then asked – why DBase over COBOL? I like the GUI and ease with which I could play with data, I said. He was impressed. I got the job and started.

The first few months were fun, writing DBASE programs for their Accounts Payable, Accounts Receivable, and Payroll. After that, the pollution at the place got to me. They stored clothing, and there was a lot of dust. The building was old and there was plenty of mold. Two years later, I got really ill with severe allergies and lung congestion. I had to quit that job, learn more FoxPro and other software and move on to other stuff. I learned the hard way to never compromise on health for any job. It is like driving without time for gas. It never works.

That gig taught me about my passion for working with data though, and that carried me a long way. Thanks, Ken, for hosting.

This months’ T-SQL Tuesday blog party is hosted by Rie Merrit (t|b) as part of Azure Community Group lead. Her call is to pick one or two things that work for running a user group and blog on it.

I am co lead at two user groups – the Triangle SQL Server User Group and Data Platform WIT virtual group. I will write here on two topics – finding and keeping a consistent audience, which I have learned from Triangle SQL Server User Group, and finding what works to keep the group going – which I learned from Data Platform WIT group.

Lessons from Triangle SSUG: Finding and Keeping a consistent audience
The Triangle SQL Server User Group is primarily led by Kevin Feasel(b|t). The rest of us on the board are me, Tracy Boggiano(b|t), Mike Chrestenson, and Rick Pack(t). When I joined the team they were already going strong with 3 meetings a month – each themed around DBA/Advanced DBA and Data Science respectively. Each meeting had a different location and sponsor and their own audience, with some overlap between DBA and Advanced DBA. When Covid hit and in-person meets went away, we brainstormed on what our strategy would be, to keep the show going. We had the following challenges.

1 It was difficult to find speakers for the data science group. There were few people and the community around it was a bit scattered.
2 Networking with virtual meets was limited and that was a big value people got out of our in-person meetings.
3 We had to make virtual meetings attractive enough to keep the audience we already had.

The challenges with keeping a physical location and finding sponsors were no longer relevant, thankfully – although that might be back soon. But we made the following changes to how we operated.

1 We expanded the focus of the data science group to include BI topics. This made it easier to seek speakers from among the BI #sqlfamily community. It has been much less of a challenge and going well.
Lesson: Broaden your topics if you are not getting enough people.

2 Kevin created a bi-weekly chat show called Shop Talk – we get on the air and talk about various tech topics and address any issues the audience wants to talk about. The show has had a dedicated audience and has been going really well. Granted, I will readily admit that Kevin is the star of the show because of his ability to speak with ease on any data topic and offer expert-level advice for free – but my take is that even if you don’t have a Kevin, and you don’t want a regular show – you can try the occasional online chat and invite the audience to weigh in. People like engagement and like to listen to topics that interest them.
Lesson: Try something different, like chat shows, every now and then if not regularly.

3 We did a few day-long virtual events – we had some attendance but not a lot. It wasn’t worth the effort and we don’t do it anymore.
Lesson: Don’t waste time doing things that do not gain audience traction.

4 For regular ug meets – we work hard on finding diverse topics and speakers. We look at social media for talks that speakers have, and also on listings like the one on Azure Community groups and ask the speaker if they would like to talk for us. We keep a consistent time and day on which we have the meets – no compromises there. This helps the audience to plan their availability easily. These things have really paid off. Our average user group attendance is around 20 people and some talks have up to 40 people tune in – several from various parts of the world, not just ours.
Lesson: Find diverse topics and keep consistent timings so that audience knows when to tune in.

Lessons from DPWIT: Finding what works
I joined the DPWIT group last year when PASS dissolved and Kathi Kellenberger was looking for a co-lead. We are two years old as of this March. The primary goal of the group was and it continues to be to empower women in tech by highlighting what they do – via speaking or other means. For six months we continued to host tech talks as it had been in the past. We suffered a low audience – mostly because there was an abundance of tech talks, several were already recorded and on youtube. Then we decided to put on day-long tech events. This was a huge amount of effort and still didn’t get a lot of traction. Then, Kathi and I did some interviews with the women who were going to do pre-cons at PASS community summit. These interviews were spontaneous, a lot of fun, and to our surprise got significant viewership as well. So, this year, my new co-lead Leslie Andrews and I decided to stick with doing interviews with tech women – mostly those who were not very famous in the community and needed high lighting. We have done two so far and it has been going really well. We also continue to write the monthly newsletter, keeping up with what Kathi used to do in this regard.
Lesson: Experiment and find what works well to get a decent audience. There is a huge range of options. You may fail a few times before you succeed.

Last, but not least – make sure what you are doing as a user group lead energizes you. If not feel free to give it up. When I gave up running SQL Saturdays , which I ran for 12 years in a row at Louisville – I felt really low and thought would miss doing it a lot. I do miss doing it – but lots of other things have taken that place. It was right for a certain time, but no more. Life goes on. Stay energized with what motivates you, and stay connected to the community in ways that work for you!! Thank you, Rie, for hosting.

I am trying to get started on blogging again this year…T-SQL Tuesday came in handy with a great topic. This month’s host is Andy Yun(t |b). Andy’s challenge for us is to write about some notion ‘something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something. ‘

There are many things on which I’ve changed my opinion in my journey as a data professional. For this blog post, I picked a common one – where business logic should reside. During my years as a production DBA – there was (and probably is) a hard-held belief that databases are meant for CRUD operations only and business logic belongs in the application/middle tier. This belief has its place – DBAs don’t like debugging code that is application-specific, or be tasked with why data looks a certain way or what code caused it. Business logic can also be very complex and get deeply embedded in one place (the database). I have believed in this and fought for this at most places I’ve worked as a DBA.

Over time though, my opinion has changed. The fact that DBAs have to learn some degree of programming to survive today’s world has a lot to do with it. Also, I started to understand the nature of business logic better. I learned that a lot of things like foreign key constraints, not null constraints, triggers actually have to do with business logic and how the business defines its data. Databases usually outlive applications and less work is necessary when there is a move to a new application layer. It is much faster to make changes on the database side and roll it out compared to most application changes. It also offers single-point control of business logic, as opposed to it spread all over the place in applications.

And last but not least – learning business logic makes a person an important asset to the business. Most DBAs complain about being ‘just firefighters’…if we know business logic and have control over that, that makes us more than that – it makes us an important asset, in addition to helping us learn more programming as part of that process.

I do realize that there are cons to putting business logic in the database too. It can be very complex in some cases. The business may not have enough people to maintain it. It is a single point of control, therefore a single point of failure too if one looks at it that way. But now I believe there are more advantages to it than disadvantages. Thank you, Andy, for hosting.

I decided to resume tech blogging after a long break and this tsql-tuesday came in handy. This month’s blog part is hosted by John McCormack (B|T). He would like us to blog about handy scripts.

I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.

My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.

SELECT q.object_id,object_name(q.object_id),q.query_id,max_duration, avg_duration, max_rowcount,
   p.plan_id,i.start_time,i.end_time
FROM sys.query_store_runtime_stats AS a
JOIN sys.query_store_runtime_stats_interval i
ON I.runtime_stats_interval_id = a.runtime_stats_interval_id
JOIN sys.query_store_plan p on p.plan_id = a.plan_id
JOIN sys.query_store_query q on p.query_id = q.query_id
WHERE q.object_id = object_id(‘dbo.myproc’)
order by i.start_time DESC

My next favorite one is one I use to find a plan based on text in the query.

SELECT c.plan_id, cast(c.query_plan as xml) , c.last_execution_time
FROM sys.query_store_plan C INNER JOIN sys.query_store_query B
ON C.query_id = b.query_id
INNER JOIN sys.query_store_query_text A ON
B.query_text_id = A.query_text_id
WHERE A.query_sql_text like ‘tablea’

The last one is duration of specific queries over time.

SELECT TOP 100 avg_duration/1000000.0 avg_dur_sec
FROM

sys.query_store_runtime_stats WHERE plan_id = 4962438
order by runtime_stats_id DESC

If you are reading this and not using query store yet – you must. Consider signing up for Erin Stellato’s precon too at the upcoming past community summit. It may be a good use of your time and money.

I am hosting 2019’s last T-SQL Tuesday of the year. I chose to ask people to write on gifts they had received – by way of community, relationships, or even tech features. A rather loose definition , as the one and only Aaron Bertrand calls it 🙂

I have a lot to be thankful for this year –

1 I took a strong liking to the graph data table feature in SQL Server and actually planned a book around it with another very popular author and mentor-friend. But the feature didn’t quite take off as I thought it would. But in the process, I relearned a lot of graph theory that I had learned in school, and also quite a bit about graph databases and how they work. Overall, it was a gift, although it didn’t quite pan out the way I thought it would.
2 I got more comfortable into my new role as dev/architect, which was a huge step away from DBA roles I had worked in for 20+ years. It has taken me a good two years to get comfortable with using GIT, working with devops as practiced at work, relearning TSQL and all that is needed in my job. It has been tough and very rewarding in the process. I consider the gig a gift, and have nothing but gratitude for the people I work with – in particular I would like to single out my colleague and dear friend from Spain – Efraim Sharon, our DBA team lead Mark Wilkinson, data science team lead Kevin Feasel and fellow dbas Andrew Pruski and Derek Fitzpatrick-Jolley. It is impossible to find a better group of people to work with.
3 I got back into speaking in a small way – i did two talks, one for the local user group and one for the PASS WIT chapter. Eugene Meidinger was my speaking mentor – he helped me with many tips and tricks, in addition to reviewing my slide set and giving valuable feedback. I consider it a true gift to have Eugene as my mentor-friend.
4 I was suspected of a serious illness and declared clear after many tests. It renewed my gratitude for the fragile gift that is life, and a renewed effort to appreciate every day I get and live it to the fullest.
5 I enjoyed hiking and exploring national parks in North Carolina and the Colorado mountains with my dear sister.
6 I attended my PASS Summit #16 and renewed many friendships, as well as made new ones. It remains to me the one place I go to recharge and refocus my professional life every year.

These are a few of my gifts for this year. I will end this post with the following quote and I believe this is important:
“When it comes to life the critical thing is whether you take things for granted or take them with gratitude.” ― G.K. Chesterton

This month’s T-SQL Tuesday is organized by Mohammed Darab(t|b). Mohammed has a really interesting topic – things you would say to yourself if you were 20. I am well past 20, and this is something I think about often. There are many,many things I wish I’d known earlier, that would have significantly changed my life for the better. But, life is intended to be just that – a series of obstacles and lessons we learn as we go along. If we knew everything to start with, there would be no obstacles and that’s not how it is intended to be. But, I would absolutely put down things I’ve learned the hard way that younger people or even older people with dissimilar challenges can learn and understand from. Below are a few of my answers, the answers that age taught me, and things I’ve learned, as a woman, a person of color, an immigrant and a woman in tech. Below are some things I’d have done totally differently.

1 Pick my battles. We live in a conflicted world. There is no way we are going to end up fixing every single thing we see that is wrong. I’d be annoyed or get turned off by a number of things starting with people violating traffic rules to the bro culture at work.It didn’t make my life any better to get involved in all of them. I’d say, now, to pick one or two that really matter, and understand that a lot of life is about just getting along.

2 Watch my back at work. I was given to being very open, and very candid about a lot of things, and often times went into battles without anyone looking out for me. Work situations are complicated, and people are in it mainly to make a living, not fight social causes. Now, I speak up on issues where I am assured of some sort of support. And if I am in unethical or immoral situations I just leave as quickly as I can.

3 Try less to belong, more to be who I am. As a somewhat lonely child I desperately wanted to belong. I’d often times hang out among people who were not like me in any way, or ponder too deeply on why some people are cliquish and others more accepting..on and on. I still sometimes have those feelings. Truth is that you can’t belong among people who are too different than you, and you can do absolutely nothing about other people having their cliques. Being who I am has attracted way more positive energy and like minded people.

4 Networking is about helping people. God, this lesson has taken a few decades. I am not extroverted, and for many years I had no clue what people said ‘networking’ was. I thought it was about brown nosing, going to golf, or visiting bars after hours..on and on. My volunteer work in the SQL community has led me to believe that quality networking is nothing more than honest, open availability to help people, and listen. I cannot even articulate how many doors opened for me after I learned this.

5 Nothing helps your career more than consistent efforts in any direction. As techies there are a billion different ways our paths can go, and most of us constantly worry about if we are doing this or that, compared to those other people who are. I worried a lot about not traveling and speaking as much as most friends do, not being an MVP, or a rockstar blogger..any number of such things. Truth is that consistent efforts in any one or two of those things pay rich rewards and we can’t be doing all of it. Spreading too thin is in fact a serious issue with many people. I’ve limited my efforts to things I enjoy – blogging, occasional speaking, writing, and volunteering wherever I can. That is how much I can do with my limited spare time. And the rewards it yields have been good enough.

6 Set small goals – I was given to setting very big, ambitious goals – like one blog post every week, getting through all certifications, and so on. Big goals are a form of self sabotage, albeit subtle. Few people get to achieve them, and somehow we all feel setting smaller goals is a lowly thing to do. It isn’t. Set small, measurable, easy goals, and document progress. It is true of every single thing – diet, exercise, tech learning.

7 Take vacations seriously – I spent a few decades of my youth working. I was a die hard workaholic, and the need to impress people was very strong. I canceled a vacation with my now late mom because of a reorganization at work. I never got to vacation with her again. We work to live, not the other way around. Vacations matter. Spending time and energy with loved ones is why we work.

8 Maintain community – I can’t really claim this is entirely something that I learned with age. Partly yes. As someone living many thousand miles away I learned the hard way that bonding between people, even people in your own family, needs effort and maintenance. Taking people for granted means you can’t guarantee the responses or regard you need will be available when you need it. It is the same with professional ties and with friends. It is partly why I make it a point to go to the PASS Summit every single year, when I have choices of other conferences. Meeting people in person is important. Saying hello, even once a year, and checking in on them matters and makes a difference. And no, just saying hi online is not quite the same thing. Take time for the people in your life. Invest in relationships. It is important.

9 All good things come to an end – This year marks my 16th year at PASS Summit. For the initial 12 years – I have nothing to show by way of photographs or memories. Some of those people are no longer around. I also don’t have any pictures of many people who were friends with me at earlier gigs, or neighbors or any number of things. These things matter. Nowadays I make it a point to capture those memories. It won’t be too long before many of us are old enough – those will be the only things that remain to remind us of good times.

10 Forgive and let go – Last but not the least. Don’t dwell on disappointments and resentments too much. I spent a LOT of emotional energy on this. One of my favorite quotes attributed to the Buddha is that holding a hot coal hurts the hand the holds it. I’ve basically learned that there are two kinds of people – those who are not worth it, and those who make mistakes and come around. The former are just that, let go of them when you learn it. The latter, forgive, include, accept. We are all human and mistakes happen. When you see that the person has a clear heart, and if he/she does, let go of the small stuff. The others, just not the right people for you, that is all.

I have learned all these lessons the hard way. But I have learned them. And it has made my life better. Thank you Mohammed for hosting!!