Using a Numbers Table in SQL Server to insert test data

T-SQL TuesdayThis month’s T-SQL Tuesday 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.

Continue Reading →

Table Partitioning in SQL Server – The Basics

SQL Server Table Partitioning Cheat SheetThere 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. Table Partitioning is an Enterprise Edition feature only.

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.) One of my favorite ways to learn something is to figure out how to explain it to others, so I recently did a webinar about table partitioning. I wanted to follow that up with focused blog posts that included answers to questions I received during the webinar. This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.

Continue Reading →

Attending SQLCruise Mediterranean 2015

SQLCruise Mediterranean 2015When I sat down to write this post I struggled to figure out how to start. SQL Server training? Yes! On a Cruise? Yes! Is that business or pleasure? Yes! Is it a technical conference or a vacation? Yes!

It shows just how unique the SQLCruise concept is. Instead of writing about what it is (you can read all about that on the SQLCruise website anyway), I want to focus on why I decided to attend SQLCruise.

Why? Because I want to use uppercase, bold and italic letters whenever I write about it :)
YAY, I’m attending SQLCruise Mediterranean 2015 and I’m SO EXCITED!

There are many reasons why I’m so excited. SQLCruise Mediterranean is a one-time event to celebrate the 10th (!) SQLCruise, and I’m so happy I get to be part of it. From a personal point of view, I get to fulfill my lifelong dream of going on a cruise, see and explore new cities around the Mediterranean, hang out with old friends and make new friends to share experiences and memories with. From a professional point of view, I get to expand my network, learn from some of the most knowledgeable people in the SQL Server community, discuss real challenges we face in projects, and maybe even improve my soft skills and get career advice.

I truly believe that attending SQLCruise is one of the best things I can do for myself this year to grow both personally and professionally. I’m investing in my future and I’m sure I will leave with more friends, knowledge and memories than I can imagine. I will for sure do my best to have fun and make it a truly amazing experience.

SQL on a Cruise? This is going to be the best vacation ever! :)

Become a Biml Hero Certified Expert

Biml Hero Certified Expert

Continuing my “2015 is such an exciting year for everyone using Biml” trend, Varigence recently announced the Biml Hero Certified Expert Program. This is an extensive certification program that will ensure that you have the skills and knowledge to implement advanced, automated Business Intelligence and Data Warehouse solutions using Biml, BimlScript and Mist.

…and it sure won’t be easy!

To become a Biml Hero, you will need to have at least one year experience using SQL Server Integration Services (SSIS) and Biml. If and when Varigence accepts you into the program, you must first attend an 8-hour advanced training class. Within six months of attending the training class you must pass a Biml Hero Certified Expert Test, and finally submit a complete production-quality Biml solution as well as a case study of a real-world Biml project you implemented. Varigence will review and provide feedback on the code sample and case study, and if they approve it… Congratulations, you will become a Biml Hero Certified Expert! Simple as that :)

Read all about the program in Varigence’s Biml Hero blog post and in the Biml Hero program guide.

The first Biml Hero training class will be held on April 29th in Copenhagen, Denmark during the Biml Scandinavian Tour 2015. Training classes will mainly take place in the US (Greenville, South Carolina) and Australia (Sydney), and online training options will be available in the future for those who are unable to travel.

Having the first training class in my region is a unique opportunity that I couldn’t miss! I will attend the training class in Copenhagen and then spend the next six months working on improving my Biml and BimlScript skills by teaching my coworkers and working with them on our Biml projects. (And I’m sure that by summer my coworkers will be teaching me lots of new tricks!)

It is truly exciting, and very terrifying, to be part of the first group of people who will attempt the Biml Hero certification. It sure won’t be easy and there is always that risk of failing, but I will do my best, work hard, and have lots of fun with this awesome technology.

Are you in? :)

Page 1 of 13 1 2 3 4 5 ...10 ...Last »