Train Wreck The last time I watched a high school band nearly fall apart mid-performance was when the relatively new, certainly nervous band director started the piece off much faster than what the students could play. I felt bad for the students, the director, and the audience. I cringed watching …
I almost ordered parts for a circuit that would have destroyed itself the instant I powered it on. Instead, an LLM caught the critical flaw in my design, saving me time and money. While I regularly see value in using LLMs in my coding projects, this is the first time …
Until recently, my family's 90,000+ photos have been hidden away in the depths of my gaming PC's hard drives. Many of the more recent photos were also scattered across our individual iCloud accounts, making them hard to find and access. A failing backup drive on my PC (thanks S …
After setting up dozens of Internet of Things (IoT) smart home devices, I started to wonder: how hard could it be to build one from scratch? I needed a project to learn on, so I decided to create something fun: a device that alerts my neighbors when my kids go …
Watch this video on YouTube I like using ChatGPT. But I don't like the $20/month price tag for using OpenAI's app, especially given the API costs fractions of a cent. So what did I do? I created my own ChatGPT-like app, of course! Talkie, a ChatGPT Clone Plenty of …
Earlier this year I presented at PyCon 2023 in Salt Lake City, UT on the topic of "Efficient Cross-Server Data Joins on Slow Networks with Python". You can watch the presentation on YouTube : Watch this presentation on PyCon's YouTube channel. Slides from the session can be found https://github.com …
Watch this week's video on YouTube How do you keep track of your daily work accomplishments? If you are like me, you wait until the end of the week and then dread having to think back all the way to Monday to try and remember what you did that week …
Watch this week's video on YouTube I like solving the daily New York Times crossword on paper. However, logging in to download the PDF every day and printing it is a pain. In this post, I will share how I automated the whole process with curl and some bash scripting …
Watch this week's video on YouTube Recursive queries are fun to plan and write. They can be frustrating too depending on the complexity of the problem you are trying to solve. This post shows one solution for finding all records that are related, either directly or via intermediate records, using …
Watch this week's video on YouTube Imagine you need to join two tables of data and filter the results. Perhaps you also need to convert some of the values for display as well (eg. 0 => "No", 1 => "Yes"). Do you choose to perform all of this in your SQL query …
Watch this week's video on YouTube A Giant Heap Recently I had to filter out 1.2 billion records from a 3.5 billion row heap. Don't ask me why this 3.5 billion row table is stored as a heap. If the lack of a clustered index wasn't bad …
Watch this week's video on YouTube Hey everyone. Long time no chat. I wanted to write this quick post to let you know why there haven't been any videos in a while. If you are interested in technical content, skip this post and come back next week for a post …
Watch this week's video on YouTube This week, I want to share my process for analyzing Twitter. Specifically, I want to find who all of my friends follow on Twitter that I don't currently follow. Essentially, I want to build a Twitter follower recommendation engine. Let's start with the theory …
Watch this week's video on YouTube Pop quiz: What will be the output of the below three statements? DECLARE @ val_varchar varchar ( 3 ) = '100' ; DECLARE @ val_bigint bigint = 100 ; DECLARE @ val_tinyint tinyint = 100 ; PRINT ( 'varchar to varchar:' ); PRINT ( CAST ( @ val_varchar AS VARCHAR ( 2 ))); PRINT ( 'bigint to varchar:' ); PRINT ( CAST ( @ val_bigint AS VARCHAR …
Watch this week's video on YouTube A year ago I built an app to keep track of pickup volleyball game scores and payments. It works well, but after a year of regular use it's time to update it with some improvements. As part of the update process, I'm rewriting the …
Watch this week's video on YouTube At first I wasn't going to write this post. I thought it would be too simple. Then I watched two experienced SQL developers go through similar scenarios last week. Sometimes the simple things are worth revisiting, so... A SQL Server UPDATE Bug I received …
Watch this week's video on YouTube This week, I filmed my experience as a first time speaker at PASS Summit in Seattle. No blog post, just a video to relive the experience.
Watch this week's video on YouTube With the release of SQL Server 2019 imminent, I thought it'd be fun to rank which features I am most looking forward to in the new release. (Also, I needed a lighter blogging week since I'm busy finishing preparing for my two sessions at …
Watch this week's video on YouTube SQL Server has several ways to store queries for later executions. This makes developers happy because it allows them to follow DRY principles: D on't R epeat Y ourself. The more code you have, the more difficult it is to maintain. Centralizing frequently used …
Watch this week's video on YouTube A few months ago I was presenting for a user group when someone asked the following question: Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement? The room was pretty …
This post is a response to this month's T-SQL Tuesday #119 prompt by Alex Yates . T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month. This month's topic asks to write about what in your career that you have …
Watch this week's video on YouTube Last week we looked at how implicit conversions and datatype precedence can cause SQL Server to output unexpected results (if you aren't aware of how it handles these features). This week I want to share another example of when SQL Server's output may surprise …
Watch this week's video on YouTube A couple of weeks ago I decided to rebuild my recording studio by getting rid of my fabric backdrop and replacing it with a true wall instead. Doing this would allow me more flexibility when shooting, further improving my filming process efficiency . To determine …
Earlier this year I made a series of videos that were played before the start of several DBA Fundamentals presentations. These videos gave viewers something SQL related to watch while the online rooms were filling up with attendees before the start of each day's live presentation. Since these videos only …
This post is a response to this month's T-SQL Tuesday #118 prompt by Kevin Chant . T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month. This month's topic asks about our fantasy SQL Server feature. Watch this week's video …
Watch this week's video on YouTube Last week's post briefly mentioned that SQL server may not reuse a query plan from cache if there is a small difference in whitespace . In addition to differences in whitespace, SQL Server won't reuse query plans for a variety of reasons. Today's post will …
Watch this week's video on YouTube Part 1: Introduction to Execution Plans Part 2: Overview of Statistics Part 3: Reading an Execution Plan Part 4: Commonly Troublesome Operators Part 5: Troubleshooting Execution Plans Today I'm concluding my series on SQL Server execution plans by sharing the specific steps I take …
Watch this week's video on YouTube Part 1: Introduction to Execution Plans Part 2: Overview of Statistics Part 3: Reading an Execution Plan Part 4: Commonly Troublesome Operators Part 5: Troubleshooting Execution Plans When examining a query's execution plan, certain operators tend to crop up over and over again as …
Watch this week's video on YouTube Part 1: Introduction to Execution Plans Part 2: Overview of Statistics Part 3: Reading an Execution Plan Part 4: Commonly Troublesome Operators Part 5: Troubleshooting Execution Plans In the first part of this series I explained what an execution plan is and how to …
Watch this week's video on YouTube Part 1: Introduction to Execution Plans Part 2: Overview of Statistics Part 3: Reading an Execution Plan Part 4: Commonly Troublesome Operators Part 5: Troubleshooting Execution Plans Last week we looked at what execution plans are and how you can view them . This week …
Watch this week's video on YouTube Part 1: Introduction to Execution Plans Part 2: Overview of Statistics Part 3: Reading an Execution Plan Part 4: Commonly Troublesome Operators Part 5: Troubleshooting Execution Plans I nearly always use execution plans as the starting point for SQL query performance troubleshooting. In this …
Watch this week's video on YouTube In order to stay current in a technology you have to immerse yourself in community content. Documentation is good for knowing the specification of a language or tool and books or training courses are great for when you want to dive deep on a …
Watch this week's video on YouTube Today I want to share with you an interesting observation I made about SELECT expression execution order. I was working on writing a dynamic SQL query that would transform the following piece of JSON: { "data" :[[ "a" , "b" , "c" ],[ "d" , "e" , "f" ]]} Into a query …
Watch this week's video on YouTube Today I want to share my process for blogging and making weekly YouTube videos. I've been hesitant to write this post because of how much I am still refining the process. Since I started making videos two years ago, not a few weeks go …
Watch this week's video on YouTube This week I want to share something that surprised me about using SQL Server's SET IDENTITY_INSERT statement. I started with two tables with identity columns defined: CREATE TABLE dbo .[ User ] ( Id int identity , UserName varchar ( 40 ) ); CREATE TABLE dbo . StupidQuestions ( Id bigint identity , UserId …
Watch this week's video on YouTube A long time ago I built an application that captured user input. One feature of the application was to compare the user's input against a database of values. The app performed this text comparison as part of a SQL Server stored procedure, allowing me …
This past weekend I had a blast presenting Joker's Wild with Erin Stellato ( blog \| twitter ), Andy Mallon ( blog \| twitter ), and Drew Furgiuele ( blog \| twitter ). Watch this week's video on YouTube Table of contents: What is Joker's Wild? Watch this to witness Andy's amazing PowerPoint animation skills ( 0:00 ) Bert demos …
Watch this week's video on YouTube While I know I don't utilize most of the features available in SQL Server, I like to think I'm at least aware that those features exist. This week I found a blind-spot in my assumption however. Even though it shipped in SQL Server 2012 …
Watch this week's video on YouTube Over the past several week's I've been exploring ways to rewrite queries to improve execution performance . I learned a lot of these techniques over time from trial an error, attending presentations, reading blog posts, speaking to other dbas and developers, etc... but never knew …
Watch this week's video on YouTube SQL Server's cost-based query optimizer does a pretty good job of figuring out what order to filter your data to get fast query executions. It considers things like index coverage, data distribution, and much more to decide how to retrieve your query's data. However …
This post is a response to this month's T-SQL Tuesday #114 prompt by Matthew McGiffen . T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month. This month Matthew asks us to write about puzzles, so I decided to recreate …
Watch this week's video on YouTube SQL Server Spool operators are a mixed bag. On one hand, they can negatively impact performance when writing data to disk in tempdb. On the other hand, they allow filtered and transformed result sets to be temporarily staged, making it easier for that data …
Watch this week's video on YouTube When you need to filter query results on multiple values, you probably use an IN() statement or multiple predicates separated by ORs: WHERE Col1 IN ( 'A' , 'B' , 'C' ) or WHERE Col1 = 'A' OR Col1 = 'B' OR Col1 = 'C' While SQL Server will generate the …
Watch this week's video on YouTube Correlated subqueries provide an intuitive syntax for writing queries that return related data. However, they often perform poorly due to needing to execute once for every value they join on . The good news is that many correlated subqueries can be rewritten to use a …
Watch this week's video on YouTube There are many options available for improving the performance of a query: indexes, statistics, configuration settings, etc... However, not all environments allow you to use those features (eg. vendor databases), leaving query rewriting as the only option. This is the first post in a …
Watch this week's video on YouTube SQL Server needs to make sure data types match when performing operations that involve multiple pieces of data. When the data types do not match, SQL Server has to implicitly convert the data before performing any operations. While most of the time these implicit …
Watch this week's video on YouTube A while back I learned that it's possible to create temporary stored procedures in SQL Server . I never put that knowledge into practice however because I struggled to think of a good use case for when a temporary stored procedure would be preferable to …
Watch this week's video on YouTube It's important to be aware of columns that allow NULL values since SQL Server may handle NULLs differently than you might expect . Today I want to look at what things to consider when joining on columns containing NULL values. Natural, Composite, NULLable keys Let's …
Watch this week's video on YouTube When first learning SQL Server, I had the brilliant idea of defining every column on my table as VARCHAR(8000). After all, the VARCHAR datatype only consumes as much storage space as the string itself (plus two bytes) , so even if I defined the …
Watch this week's video on YouTube In a traditional gaps and islands problem, the goal is to identify groups of continuous data sequences (islands) and groups of data where the sequence is missing (gaps). While many people encounter gaps and islands problems when dealing with ranges of dates, and recently …