Skip to content

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

T-SQL Tuesday

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.

Continue reading →

Giving back to the SQL Community in 2015 (T-SQL Tuesday #61)

T-SQL TuesdayWayne Sheffield (@DBAWayne) is hosting T-SQL Tuesday #61, the last one of the year, and he wants to know how you plan on giving back to the SQL community in 2015.

My short answer is to continue to do everything I did in 2014 – and some more.

In 2014 we restarted our local PASS chapter, SQL Server User Group Norway, and in 2015 I will continue to be a board member and help organize meetings. It has been great to see our chapter grow this year. We have more members, but more importantly, we are slowly becoming a community. Members have volunteered to help out with events, they want to start speaking, and it’s great to see former coworkers catch up with each other at meetings.

We also organized the first SQLSaturday in Norway in 2014, and we have already started planning our next SQLSaturday. (We’re aiming for August 29th, so save the date!) It was a lot of hard work organizing the event this year, but it was such an amazing learning experience that I can’t wait to start working on the next event. I’m so happy that we can give the Norwegian SQL community a day of high-quality free training!

Volunteering is fun and I’ve become slightly addicted to it. I’m planning to be a helper at SQLBits XIV and I have also told the SQLRally Nordic organizers that I’m happy to volunteer if needed. The same goes for all the other PASS events. I hope I can help out again at PASS Summit 2015 and 24 Hours of PASS, and I also want to join the Program Committee and learn from that.

Next year I want to challenge myself to speak and blog more. I love to do both, but they terrify me because they push me out of my comfort zone. There will always be someone who knows more than me, who has more experience than me, who has presented something before me, but I don’t want that to stop me. I wouldn’t be where I am today if others hadn’t shared their knowledge, and hopefully I will be able to help someone by sharing my knowledge. Also, I can’t think of a better way to learn something than to figure out how to teach it to others, so I’m really just being selfish.

And lastly, I will be hosting a T-SQL Tuesday next year! I thought it was fun to share the news in a T-SQL Tuesday post about giving back to the SQL community :)

Something New Learned (T-SQL Tuesday #60)

T-SQL TuesdayT-SQL Tuesday #60 is hosted by Chris Yates (@YatesSQL) and is about sharing something new you’ve learned recently.

For the past five years I’ve worked as a data warehouse and business intelligence developer. My job is all about providing high-quality data, visualizations, and make sure end users have the right data and reports to do their jobs. I don’t have to worry about performance tuning down to milliseconds, I don’t need to know anything about hardware, and administrators take care of backups and security. I don’t have to or need to know any of these things to do my job, and that is exactly why I want to learn about them.

I’ve spent five years learning and there is still so much more to learn about business intelligence. However, this year I’ve started attending sessions on topics I know close to nothing about, and it really is making me a better developer.

At SQLSaturday #337 Oregon I attended a session by Argenis Fernandez on Securing Your SQL Server Instance Without Changing Any Code. In theory it’s not relevant for me at all, and I have to admit that I hadn’t even heard about some of the things presented before that session. But do you know what I had heard about? Vendor software that requires SA accounts to run. And what did I learn? Rename the SA account, create a new account named SA, and voila! The vendor software can now use the “SA account” that you’re in total control of. It might be 101 material for many, but for me it was something new and useful.

At Redgate SQL in the City I attended another security session by Bob Pusateri, this time about Passive Security for Hostile Environments. Again, something that in theory was not really relevant for me, but it was probably the session I learned the most from! I knew about triggers, auditing and extended events, but it was the first time I saw a demo of event notifications. It was really useful to see the features compared and learn more about when they should and shouldn’t be used.

While I still feel like I have so much more to learn, I also realized that I actually know quite a lot that I can teach to others. And so do you! Share something you’ve learned recently, something you’ve known for ages, or something you think “everyone knows”. There’s always someone out there like me who will learn something from you :)

SQL Server Heroes (T-SQL Tuesday #59)

T-SQL TuesdayWhat day is it? It’s T-SQL Tuesday again! T-SQL Tuesday #59 is hosted by Tracy McKibben (@RealSQLGuy). Did you know that October 14th is also Ada Lovelace Day? I didn’t know that until I read about this month’s T-SQL Tuesday topic: your heroes. Who do I admire, who inspires me, who do I strive to be like?

Volunteers. The thousands of people who spend their free time organizing events, writing blogs and helping strangers. I remember watching the PASS Summit 2013 introduction video where they counted the volunteer hours and they just kept counting and counting and counting… and counting. 500000+ volunteer hours! Five hundred thousand plus hours! I was shocked. That was the first time I realized that PASS and the SQL Server community was more than just a technical conference.

Thank you, volunteers. All of you. Whether you’re a PASS board of directors member, a blogger, a SQLSaturday room attendant, a superstar speaker or a user group administrator, you’re my heroes in the SQL Server community. Without you, there would be no events, no free training, no community. Thank you for inspiring me to be a volunteer as well.

#SQLFamily – Pay It Forward (T-SQL Tuesday #57)

T-SQL TuesdayT-SQL Tuesday #57 is hosted by Jeffrey Verheul (@DevJef) and the topic is #SQLFamily. This is a topic close to my heart and even while writing this I’m all excited to read other stories. I can go on for ages about #SQLFamily, but I’ve decided to focus on one thing: paying it forward.

One year ago I had never heard about #SQLFamily and I didn’t even know the SQL community existed. I knew people wrote blogs and forum posts, I knew some taught classes and others published books, but I was your average employee: I did my job, learned what I had to learn to do my job well, and searched online to find solutions and better ways to do things.

Then I went to PASS Summit 2013 and the experience turned my life upside-down.

What happened? People welcomed me with open arms. They invited me to join them at #SQLKaraoke, introduced me to their friends, told me stories about SQLSaturdays and taught me about the community. They didn’t tell me about #SQLFamily, they were #SQLFamily. Even before I went back home to Norway I decided that I would do whatever I could to bring the SQL community to Norway.

This is an excerpt from the blog post I wrote after PASS Summit 2013: “I hope to attend and volunteer at a SQLSaturday, maybe even help organize one in Oslo. I hope to share my new knowledge with my co-workers and help improve our solutions at work. I hope to learn even more and get to know my new connections better. Maybe I’ll even make my own presentation and be a first-time speaker some day!”

In the ten months since I wrote that, I’ve become a board member of SQL Server User Group Norway, I’ve volunteered at SQLSaturday Copenhagen, I’ve done a SQLHangout video, I’m organizing the first SQLSaturday in Norway, and for the first time I’ll speak in another country at SQLSaturday Portugal. I’ve met so many wonderful people and I’m looking forward to getting to know even more. It’s hard to believe it all started when #SQLFamily members spent a couple of minutes saying “hi” to me. So little, yet so much! The last ten months have been a whirlwind, and I’ve enjoyed every single moment of it.

I’m so thankful for everything I’ve experienced this year. Paying it forward and helping my local community has given me something meaningful to do every day, and because I have so many amazing memories I’m now even more determined to keep paying it forward, and to give back. I’ve experienced first-hand how something someone might not even think of, like a friendly smile and a “join us”, can mean to someone else.

If I can pay it forward to just one first-timer at PASS Summit 2014, to welcome someone to the #SQLFamily… it will be worth the whole trip.

Pay it forward. You can change someone’s life :)