T-SQL Tuesday #139: The data world is hybrid

Invitation from Ben Weissman.

Our data estates are not just on premises anymore. And they haven’t all moved to the cloud either.

New offerings for a hybrid world like Azure Arc-enabled Data Services pop up but are far from being widely used in production environments.

It has become pretty evident to me, that we’ll be living in a hybrid world for a long time – potentially indefinitely. But what does that mean for our data strategy?

My Invitation

On this month’s #tsql2sday, I would therefore like to invite you all to share your hybrid and edge experiences!

Where are you on your journey?

Which challenges did you face?

How did you address them?

T-SQL Tuesday #138: Managing Technology Changes

Invitation and wrap-up from Andy Leonard.

One point I make (repeatedly) in my latest book – titled Building Custom Tasks for SQL Server Integration Services – is “software changes.” In fact, software changed on me between the completion of editing and the release of the book! I wrote about the changes in a post titled Building Custom Tasks for SSIS Second Edition Errata, Chapters 1-9. There’s a live stream video at the bottom of that post, as well.

Changing software inspired this month’s T-SQL Tuesday topic:

“How Do You Respond When Technology Changes Under You?”

T-SQL Tuesday #137: Using Notebooks Every Day

Invitation and wrap-up from Steve Jones.

Jupyter Notebooks

I first heard about Jupyter Notebooks years ago. At the time I was just getting started in Python and I thought these were a great way to share code online with others. However, the setup and administration was a pain, and I quickly gave up.

When I saw Microsoft add notebooks to Azure, I got slightly excited again, but once again, it was a bit of a pain to work with these. Too much administrative overhead, in my opinion.

That changed a bit with Azure Data Studio, which has seen notebooks get added, and become easier and more stable. If you haven’t tried notebooks, get ADS and give it a try today. I even wrote a short piece on using these.

Your Invitation

For this month’s T-SQL Tuesday, I want you to write about how you have used, or would like to use, a Jupyter notebook. This seemed to be exciting for many people at first, but I haven’t seen a lot of uptake from users in general. So I’m curious if you are using them.

A few resources for you:

There is even a way to run these from PowerShell.

So let me know how you are using notebooks, or your plans for the future.

T-SQL Tuesday #136 – Blog About Your Favorite Data Type (Or Least Favorite)

Invitation and wrap-up from Brent Ozar.

Your mission: write a blog post about your favorite data type, and schedule it for next Tuesday, March 9.

Mad about money? Feverish about float? Tell us what you use your favorite data type for, and what people need to know before they get started using it. Data types have so many interesting edge cases, like when Aaron Bertrand taught us to use lower case for our data types. (I still don’t do that, but for the record, I do feel guilty.)

T-SQL Tuesday #135 – The outstanding tools of the trade that make your job awesome

Invitation and write-up from Mikey Bronowski

TL;DR

Without tools, most of the work would be much harder to do or could not be done at all. Write a blog post about the most helpful and effective tools you use or know of.

If you do not own the blog – Tweet your contribution or post on LinkedIn with #tsql2sday and put the link back in the comments here.

Tools are great

Can you imagine your day without them? How do you make your coffee or slice the bread? How do you cook breakfast or dinner?

We are surrounded by tools, and there is a special class of them called “tools of the trade”. Those are needed to do your job whatever it is.

I would like you to write about the tools that help you at work, those that helped you the most or were the most effective. It can by anything really, does not have to be related to SQL at all. Here is the list of example, but you can go beyond that and share the tools you cannot live without. Are you…

  • writing code?
  • recording videos or streaming live sessions?
  • creating podcasts or taking pictures?
  • migrating loads of data or environments?
  • writing documentation?
  • maintaining the open-source projects?
  • managing the team?
  • travelling a lot (well, not currently)?
  • learning a new language?
  • renovating house?

Everyone has its own experience and workstyle. The goal of this post is for everyone to learn about a new tool, or starting to use one, so life gets easier a little bit.

T-SQL TUESDAY #134: – GIVE ME A BREAK!

Invitation and roundup from James McGillivray.

Breaks are critical for our mental health. Write a post about relaxation techniques, dream destinations, vacation plans or anything else relating to taking a break for your own mental health.

TAKING A BREAK

2020 was a tough year. And there’s no guarantee that 2021 will be better. Hopefully it is, or at the very least, hopefully our experience in surviving 2020 will allow us to be more prepared.

In the early months of the pandemic, I was burning myself out. South Africa was in a hard lockdown, and we were prohibited from doing many of the things I usually do outside of work. Sport, exercise (outside the house), socialising, and choir were all banned. Additionally, my wife is an Emergency Physician, and at a high-risk of becoming infected, so we isolated at home as much as we possibly could.

With all this time on my hands, and no sport on TV, I started working crazy hours to keep myself occupied. And even when I wasn’t working, I was spending time in front of a screen; watching TV, browsing social media, even having a chat with my parents or friends was happening in front of a screen.

Before long, I was feeling lethargic, listless, and honestly, very down. I was struggling to provide emotional support for my wife when she needed it most, and I realized I was burned out. I knew that I needed to change something… which I will write about next week (but here are some pictures to whet your appetite).

YOUR TURN

For T-SQL Tuesday #133, I’d like to know about your own views on vacations, escapes or other breaks. Whether it’s work, technology, or any other situation that you need to get away from. Plans, memories, or relaxing activities, I want them all!

You can choose any of the topics below, or any other variation on the theme.

  • What do you do to take a break when you’re stuck at home?
  • How do you switch off, and get away from being connected 24/7?
  • If money was no issue, what would be your bucket list vacation?
  • What vacation are you dreaming of taking once it is safe to do?
  • What is the best vacation you’ve ever had?

T-SQL Tuesday #133: What (Else) Have You Learned from Presenting?

Invitation and wrap-up from Lisa Griffin Bohm.

This month, I’d like those of you who have presented, or written a presentation, to share something technical THAT DID NOT RELATE to the topic of the presentation, that you’ve learned in writing or giving the presentation. This can include a work presentation, for those of you who haven’t spoken at an event! 

Why do I ask?  Well, if any of you have heard Allen White (b|t) speak on any topic, he will start his presentation speaking about how much PASS has contributed to his life and career. He will then talk about how every person has a story to share, and there will be a piece of that story that no one has ever heard before. We ALL have something to learn from each other. He will also tell you that you will learn more than you ever thought just by writing and presenting on ANY topic, as well as answering questions!  So, I’d like folks who have done this to share and encourage – especially the new folks who are on the fence about presenting. Feel free to go more in-depth with some of the technical details of your learning than I did!

I will share a couple of stories about my own experiences. My first presentation at our local PASS user group was a lightning talk about a bizarre performance hit we took from a foreign key with an ON DELETE CASCADE option. It was a rare scenario, so I figured most folks hadn’t run into this.

As I was researching foreign keys in general, I realized… hey!  Did you know there were other options for updating and deleting data tied to foreign key constraints? I don’t know that I’ve paid attention to that for the last… at least 10 years. I’ve never seen any of these options used in code I’ve maintained or identified as problematic.  You can specify default behaviors for both delete and update options – for example, if I update the value that the foreign key constraint points to, I can change all those foreign key values to be NULL. Or to update that foreign key value to the new value. Or… not do anything. There are ramifications for all of the choices, but I could definitely see how they might be useful in certain scenarios.

Also, people were interested enough in the topic to ask about indexing on some of the different fields, and asked me to expand this into a full presentation. I did so and presented at our next local SQL Saturday, including testing with the indexes that were recommended to me.

Learning summary:

  1. tables may show up in the execution plan even if they weren’t in the query AT ALL (look for constraints!)
  2. there are other options for coding foreign keys to handle updates/deletes to the primary key data that could be useful elsewhere
  3. Reinforcement: Indexes with low cardinality don’t always improve performance!

I also wrote a book on refactoring legacy T-SQL. I was talking about functions, and how SQL Server’s STATISTICS IO won’t show IO that a function performs. I remember thinking, “I CANNOT write a book in 2019 that talks about running a trace!”  So, I started using simple Extended Events to show the increased IO for functions that the native SQL Server STATISTICS IO sneakily avoids. I need to start making more use of Extended Events but this was a great .. kick in the pants to get going on those, as well as being able to code a very simple, more modern solution to share with readers on how to more accurately find IO stats of the SQL you’re running/tuning!

I could continue about buying a “lab” laptop, re-learning how to use Oracle Virtualbox, dealing with networking (not one of my strengths), using SQL Server core (sooo fast to install!), and all of those fun things. But, there will be future blog posts on those topics, and it’s time for others to share THEIR stories and experiences!  Thanks for reading!

T-SQL Tuesday #132: How Are You Coping with Pandemic?

Invitation and roundup from Taiob Ali.

This month we will share how each of us is coping with a global pandemic.

2020 is a unique year for all of us. We are living through a worldwide crisis that most of us have not seen or experienced. It has touched almost all aspects of our life. Depending on your location, social interaction has been reduced to almost none for many of us. Our home has become an office, school, day-care center, playground. Many of us lost our family members due to this pandemic. PASS Summit 2020, SQL Saturday events, monthly user group meetings are happening virtually. Most of us who were working in an office building are working remotely. Travel has become a thing of the past. Every individual’s challenges are different. I want all of you to share your experience (with whatever you are comfortable with), how you are dealing with these new challenges, and tips you have for others going through the same.

The enormous scale of the crisis and the impact it is having are naturally causing a lot of fear, uncertainty and anxiety across the globe. Add social isolation, disrupted work and family routines, cabin fever and economic instability, and it is understandable that our mental health is suffering. In a recent survey by the Kaiser Family Foundation, 45% of adults feel that worry and stress related to coronavirus has had a negative impact on their mental health.

https://www.kff.org/health-reform/report/kff-health-tracking-poll-early-april-2020/

These are few pointers (do not be limited by these) to help you get started.What new challenges were you facing at the beginning of this year?

  • What new challenges were you facing at the beginning of this year?
  • What are the things you had to adjust to deal with these new challenges?
  • What tips you have for others that helped you in coping with the changes during this pandemic?
  • What are the areas you could not find a solution to and that you wish someone can help you?
  • Challenges you noticed, among others, and how they dealt with?
  • Are there new opportunities opened up for you as things shifted to remote, digital?
  • Stories of kindness during this crisis.
  • Anything else you want to share?

We will focus on three areas to share our thoughts. For many of us, separating these three might not be possible, as each one depends on the other. You can write about one or all three areas. You are also welcome to share anything outside of these three areas.

  • Mental health
  • Physical health
  • Professional growth

T-SQL Tuesday #131: Data Analogies, or: Explain Databases Like I’m Five!

The current invitation is from Rob Volk.

Welcome to T-SQL Tuesday! This month we’ll explore analogies, or how would you explain database concepts to someone who’s not technologically savvy.

If you’re a data professional, you’ve probably been asked “So what exactly do you DO?”, and probably not found a great way to answer. Database theory and practice can be hard, and every professional struggled with their first computerized data concepts.

While attending and presenting sessions on SQL Server and other data topics, I’ve found analogies often explain a concept better than any computer science or mathematical description does. We’ve probably heard that “a clustered index is like a phone book”, and those old enough to remember phone books will agree, but it doesn’t really explain why that structure is useful. (hint: it requires sorting)

I recently found a Youtube video that describes how binary search works, and why sorting makes it work. As this is a foundational concept for most database indexing schemes, I think it does a great job explaining why it’s more efficient. What I like most about it is that it doesn’t demonstrate this on a computer, but uses cards with different values (even if they’re not physical cards).

Brent Ozar recently posted a video on statistics that DOES use actual playing cards. I’ve also seen Aaron Cutshall use cards in presentations on Set Theory.

For this month’s T-SQL Tuesday, I’d like you to write about your favorite analogies that help explain database concepts to people who aren’t database experts. I like the spirit of Reddit’s Explain Like I’m Five (ELI5) subreddit, where the explanations are meant for someone who has no previous assumptions about the topic.

ELI5 doesn’t preclude deeper examples. For example, I posted a long time ago one version of my “cursors are tweezers” analogy to explain why you shouldn’t use them. You can not only explain why something works, but also why something else DOESN’T work, and I welcome such examples if you have the space and time.

T-SQL Tuesday #130 – Automate Your Stress Away

Invitation and recap from Elizabeth Noble.

Life as a data professional is stressful. This year is even more stressful. We have so many responsibilities and so may demands coming at us every day. I’ve found over the years that I love the stress, but I also want to make my life and the lives of those around me easier, calmer, more peaceful. I can’t change everything about my job or what is expected of me. After a particularly stressful summer many years ago, I wanted to figure I could change in my day to day tasks. How could I make my life easier?

At the time, the largest hurdle I had was around deployments. Our deployments consisted of a collection of SQL scripts collected from individual user stories. Each script was executed separately as we didn’t have a good process for what to do if any particular script did fail. In addition, we created rollbacks for each script that was deployed. The process worked for us for quite some time until one day we deployed our first new application in years.

Eventually, we moved a handful of our databases to source control. That part was easy as you can use Visual Studio and SQL Server Data Tools (SSDT) to import a schema for an existing database. The next steps took quite a bit of communication between the teams. A few missteps later, we had 2 of our 10 or so databases deploying through Continuous Integration Continuous Delivery (CICD) pipeline. We still deploy once every two weeks, but our deployments our generally quicker and less tedious.

The time savings is nice. We have about 24-26 deployments a year, and we easily save at least an hour on average per deployment. That’s a full day a year! But the best part for me is the next day. I still check my email as soon as I wake up to ensure that aren’t any issues reported, but even if there are issues, they are usually quickly resolved and we go on about our day.

My invitation to you is I want to know what you have automated to make your life easier? This can be anything creating a SQL Server Agent job to automated running a daily report or using dbatools to manage your servers. I’m curious what challenges you’ve found at your job and what you’ve done to make things better. If you haven’t had a chance to automate some part of your job, what would you like to automate and what are your hurdles? If you’re interested in some help or advice, let us know. I love #SqlFamily, and I’d love to see what we can do to help out.