Using a Numbers Table in SQL Server to insert test data (T-SQL Tuesday #65)

T-SQL Tuesday logo.

T-SQL Tuesday #65 is hosted by Mike Donnelly (@SQLMD). There is no specific topic to write about this month, Mike simply wants us to learn something new and then write a blog post to teach it to others. I want to share something that I only recently learned, something I wish I had known about years ago, something that became part of my toolbox as soon as I discovered it: the Numbers Table. It is so simple and solves so many problems that everyone should know about it 🤓

A Numbers Table (perhaps most known as a Tally Table, sometimes called an Auxiliary Table of Numbers and even referred to as the Swiss Army Knife of SQL Server) is a one-column helper table that contains the numbers 1, 2, 3, 4, 5 and so on all the way up to the-highest-number-you-could-possibly-need.

It can be used to replace slower loops and row-by-row operations with faster set-based operations, generate dates, split strings, find gaps in data sets, expand data sets, insert test data and probably hundreds of other things. There are so many great and detailed articles already published about this topic, so I will stick to the T-SQL Tuesday topic of “Teach Something New” and share the two most recent things I learned: different ways to create a numbers table, and different ways to quickly insert test data by using a numbers table.

Table Partitioning in SQL Server - The Basics

Cheat Sheet of Table Partitioning in SQL Server.

There are many benefits of partitioning large tables. You can speed up loading and archiving of data, you can perform maintenance operations on individual partitions instead of the whole table, and you may be able to improve query performance. However, implementing table partitioning is not a trivial task and you need a good understanding of how it works to implement and use it correctly.

Being a business intelligence and data warehouse developer, not a DBA, it took me a while to understand table partitioning. I had to read a lot, get plenty of hands-on experience and make some mistakes along the way. (The illustration to the left is my Table Partitioning Cheat Sheet.) This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.

Redgate SQL Prompt 6.5 Tab Coloring

Redgate SQL Prompt Logo.

Redgate recently released SQL Prompt 6.5. There are some nice features in this version, but my favorite is by far Tab Coloring! It is no longer an experimental feature, it has become a proper feature. I did a quick demo of Redgate SQL Prompt including Tab Coloring in my SQLBits session to show how you can save time and work more efficiently.

Presenting a webinar for the first time

Cathrine Wilhelmsen presenting a webinar for the first time.

Presenting online is a completely different experience than presenting in-person. When you present online you lose the interaction with the audience, you are unable to read body language or facial expressions to see if they are interested or bored out of their minds, and you get absolutely no feedback on whether or not they understand what you are trying to explain. You have to trust yourself, talk to a screen for an hour, and hope that the technology does not suddenly decide to throw a tantrum and stop working for no reason.

I recently presented a webinar for the first time. It went quite well, but there are many things I want to do better next time. Writing down what I learned will help me improve my own presentation skills and slide decks, but I also hope it can help other new speakers prepare for their first webinar.

Volunteering and Speaking at SQLBits XIV

SQLBits XIV Superhero Edition Logo.

SQLBits XIV Superhero Edition was my first SQLBits experience, and it was a truly amazing week! I want to start with a massive thank you to the organizers: Darren Green, Simon Sabin, James Rowland-Jones, Chris Testa-O’Neill, Allan Mitchell, Jonathan Allen and Annette Allen. You did one heck of a job to make this the best conference in the universe! 👏🏻 I also want to say thank you to all the sponsors, volunteers and speakers who made this a great event. And to all the attendees, I hope you had a blast and that I’ll see you again next year!