Voice of the DBA

Tag: T-SQL Tuesday

  • #TSQL2sday #137 Invite Using Notebooks Every Day

    tsqltuesdayIt’s time for the T-SQL Tuesday blog party, and I’m hosting this month. I don’t host too often, but since I had some schedule mix ups, I’ll handle this month. Read the invitation below and then write your post for next Tuesday, Apr 13.

    If you want to host a T-SQL Tuesday, let me know. You can ping me on Twitter.

    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.

    Publish your post sometimes on 13 Apr, UTC time.

    The Rules

    Publish your post sometime on 13 Apr 2021, UTC time.

    Leave a comment or traceback on this post.

    Use the T-SQL Tuesday logo at the top of this post in your post and link that back to this invitation.

    Spread the word.

  • T-SQL Tuesday #136–The Datatype Blog

    tsqltuesdayIt’s that time of the month again, and this time it’s an interesting topic. The invitation is from Iceland, where Brent Ozar has relocated for the foreseeable future. I’m slightly jealous, and wish I could go visit. I enjoy winter, and the pictures he’s posted look amazing. Definitely a bucket list trip for me.

    However, this month, he’s asking about data types. Are there some you love or hate, and I’ve got a thought on this. In case you wonder, there is a list, broken into types. Apparently MS went into a “categorize everything” frenzy in the docs, which is OK, but I often don’t intuit the way they’ve broken things down. I wish they kept a long list on a page somewhere that was easy to find.

    Naming Confusion

    It has been deprecated, but the timestamp type is still around. It’s not in the list, but it is mentioned as a synonym for rowversion. This is a unique binary number in each database, which is often used to detect changes in a row. If you have two people editing a row, and a change updates a rowversion column, then each can detect if that value is different from the original one. Handy in terms of client side conflict resolution, which can prevent last-writer-wins scenarios for applications.

    I haven’t seen it used lately, but in the 90s and early 2000s, I often saw code that checked this before letting a user make an update in some data entry application. However, this was often a “timestamp” column, which was constantly confusing to me as a DBA or developer. I kept thinking I’d get some sort of datetime stamp in there, rather than a binary value.

    This shouldn’t be a problem in the future, as timestamp isn’t really doc’d, though timestamp can be found on Google searches. 

    The other reason I dislike this type is that we can’t change it to rowversion. An ALTER TABLE … ALTER COLUMN doesn’t work.

  • T-SQL Tuesday #135 – Awesome Tools

    tsql2sday150x150It’s blog party week for T-SQL Tuesday, and I think this is a good choice for a topic. The host this month is Mikey Bronowski, and his invitation is on tools. I work for a tools vendor, and I’ve used a lot of them in my life, so I want to share what I think in 2021. I’ll also say that Mikey has a good list in his invitation of what he uses. I especially like his use of PoSh things and Greenshot.

    I’ve going to tackle this in a couple ways as I really have two parts of my job here, so I’ll look at tech tools and then productivity tools.

    Technical Tools

    I work for Redgate Software, so I use the Redgate Toolbelt a lot, as well as some of the Deploy tools. Specifically for me, I use these every week:

    • SQL Prompt – can’t live without it, and struggle to write t-sql when this isn’t working.
    • SQL Source Control / SQL Change Automation – I use these regularly, and while I tend to prefer SQL Source Control for many tasks, I love migrations. Slowly, I am moving to Flyway for some things, as I think this is our future.
    • SQL Clone / SQL Provision – I copy and mask databases regularly. This makes creating test copies quick and easy.

    Outside of Redgate, I really like VS Code for lots of editing, whether C#, Python, or PowerShell. I’ve been trying to use Azure Data Studio more, but I just prefer VS Code. No real good reason here. I have Sublime Text as well, which I use for text/html/markdown, but I think I am slowly going to move to VS Code for more, and I might not install Sublime on my next machine.

    I still live in SSMS most of the time, rarely in VS, but sometimes. However, as I do more DevOps stuff, I also have a few other major tools that I use every day and continue to learn about:

    1. git – I do a lot of command line stuff, and continue to get better, even with the VSCode client. I used GitHub and AzDO for repos, but lots of basic git stuff is important for my work
    2. Azure DevOps – I used other tools, but my goto, and the one I used for SQLMemorial.org, is Azure DevOps
    3. Chocolatey – package manager and install tool for Windows. This let me set up a machine in an hour or so, as opposed to a day.
    4. Docker – I use containers more and more rather than installing software.

    I may use other items, like tsqlt, but these are the big tools that help me get code written.

    Productivity Tools

    More of my job is writing related. I essentially run a newspaper at SQLServerCentral, and I produce a lot of content. As a result, I have some tools that make that easier, and help me coordinate with others.

    Here’s a list with a few comments:

    1. Evernote – I subscribe and use this to keep random notes for editorials and work things. I drop notes in here from meetings. I also have various items for my personal life, like logging maintenance on vehicles, license plates, recipes for cooking, and more. Worth my $$$. There are other good note apps, but I’ve been living on Evernote for 6-7 years, so no one has made a good case for my to switch
    2. DropBox/OneDrive – I sync stuff between machines and my phone. I’ve used DropBox for years, but their changes on mobile, which don’t make files stored and available, have me considering a fulltime move to OneDrive. A sync tool is important. Pick any one, but use it.
    3. Password Safe – Everyone needs a password manager. I’ve had this one since its release in 2002. free, secure, works across platforms, I see no reason to change. Use something here.
    4. Greenshot – I’ve tried various screen capture tools, but this one is lightweight, stable, and easily installed.
    5. Live Writer – I use the open source project for blogging. A drive sync tools keeps drafts on all machines.
    6. Camtasia – While there are lots of ways to capture screen video, Camtasia just works for me. Not the best, but simple enough and stable enough for building presentations and other recordings of my screen. I can record something quickly, and then edit a video or animated gif in minutes.

    While I certainly use Office (Excel, Word, PowerPoint), those are less of my day than these tools. I usually outline and build some stuff in Evernote before it goes into PPT. I do a little spreadsheet work, and some Power BI, but I’m really productive with these tools.

    The last tool I lightly mention is Spotify. I love music, and I appreciate having it in the background sometimes. My wife prefers Pandora, but whatever helps get you in a good mood, use it.

    My advice, try a few things, learn how one works well, and get better at it.

  • T-SQL Tuesday #134–Taking a Break

    I’m a day late for T-SQL Tuesday this month, literally because I was taking a break. Yesterday I took a holiday to go snowboarding with a friend. The weekend was a break from life, as I twisted my back Thursday night and spent most of the time since Friday in bed, healing over the weekend.

    This month, the invitation is from James McGillivray, and the topic is “Give Me a Break”, which might be appropriate as we have left 2020 behind, but not the pandemic. While I had a break, I’ll give you a few things that help me unwind.

    My Breaks from Work

    I’ve been stuck at some somewhat, but not completely, during the pandemic. The lockdowns in Colorado were shorter, and easier, so I haven’t had the challenges of others. Plus, I live on a horse ranch, so I’m not as restricted as many others.

    I am used to traveling, however, and despite the space we have, I felt somewhat trapped last year. I haven’t been used to spending more than 3-4 weeks in my office without a trip, so there was a different adjustment from many others. Some of my breaks:

    • horseback riding with my wife. We can ride off the property and through some nearby fields for a 2-3 mile trip
    • guitar – I spent more time learning some songs and sitting outside. I’ve even taken some breaks during the day when I need them.
    • language – I picked up Duolingo, and started relearning Japanese with my oldest. He studies on his own, but we text to practice.
    • cooking – my household went from

    Vacation

    For vacations, I have been trying to do more of this over the years, and more with my wife. My children are adults, so my wife has joined me for a number of trips in the last few years, some dedicated to vacation, some just extending work trips.

    The bucket list vacation for me is a big tour of Japan. I’ve never been, and for no good reason.

    Last year we were planning on Machu Piccu in the summer, which got cancelled. That’s still on the list, as is some sort of trip to Africa, likely Kenya/Tanzania. Hiking Mt. Kilmanjaro is on my list.

    As for the best vacation, it has to be with family. We’ve had a number, but I think our 12-13 day camping trip up through Yellowstone National Park and Glacier National Park was epic.