FizzBuzz in SQL

Saw this article from Steve Jones about FizzBuzz in SQL. Figured I would try it.

Took me about 5 minutes to do. I did have a few small issues in my code at first though.

Here’s what I came up with:

declare @t table (
num varchar(10))

declare @i int

set @i = 1

while @i <= 100
insert into @t
select @i

set @i = @i+1

when convert(int,num)%3 = 0 and convert(int,num)%5 = 0 then 'FizzBuzz'
when convert(int,num)%3 = 0 then 'Fizz'
when convert(int,num)%5 = 0 then 'Buzz'
else num
from @t

The first time I did this, I made num an int because I wanted just numbers in my numbers table. This would have proven to be more difficult in the conversion of a varchar to an int. The easier way was to do the conversion on case statement.

Is this the best way to do this? No idea. BUT…it works.

SQL Saturday 440 – Pittsburgh

Once again it’s that time of year. The committee and I have been working on spreading the word, getting sponsors, getting attendees, organizing lunch, and so much more. It takes a lot of work and has a lot of moving pieces. This year is lining up to be the best yet. We have a ridiculous schedule this year and it’s going to be hard for me to decide which sessions I want to attend. Check it out here.

I highly recommend coming this year. Take a Saturday and invest it in yourself by learning something new and meeting some new people who have the same interests about SQL as you do.

Here is the info:

SQLSaturday is a full day FREE training event for SQL Server professionals and those wanting to learn more about SQL Server and network with the SQL Server community.  Pick from more than 30 different sessions and learn from the experts.

Don’t know anything about SQL? Back by popular demand, the “Wanna be a DBA” makes its return.  Explore the life of a DBA and see if it can be yours too. Date Ponta Grossa : October 3rd, 2015

Location: Pittsburgh Technical Institute – 1111 McKee Road, Oakdale, PA 15071.

Time: Check in starts at 7:30am. Sessions throughout the day

Cost: Admittance to this event is free, but we do charge a lunch fee of $10.00



Call for speakers: Call for speakers is still open. To submit a session go here -

Seating is limited so register today!

The Premier SQL Server training event in Western Pennsylvania

Join the Amazing Pre Con this year!

Brent Ozar, Jeremiah Peschka

Developer’s Guide to SQL Server Performance – Live!


Want to know more? Of course you do! Go here –

Still have questions? We can answer them. Mail us here –

Like twitter? We do too. Follow us here @SqlSaturdayPitt and use the hash tag #SQLSat440

T-SQL Tuesday – SQL Family and community

This month’s T-SQL Tuesday is being hosted by Jeffrey Verheul (B|T). Our topic of the month is SQL Family. Check out the host page here.

Here is our task:

“ How do you feel about SQL Family? Did they help you, or did you help someone in the SQL Family? ”

I feel that the SQL Family is a great online community that helps each other out wherever and whenever they can. I don’t like to use the term SQL Family though. I prefer to keep work and personal life separate so I tend not to use the term “SQL Family”. When talking about my job and why I like what I do, one of the things I always bring up is the great online community.

When I first started learning about databases, I lived on sites like SQL Server Central and MSSQL tips. I loved how that people had tons of questions and other people just answered them…ACTIVELY. What was even better was that people weren’t jerks about answering basic or n00b questions.

Then I was introduced to twitter.  I started using twitter when I was working in New Zealand. My boss at the time was encouraging us to use twitter but I never really thought it was useful. Then I met the #SQLHelp hashtag. That is when I really understood what it was all about. For me, it’s like an ongoing discussion in a huge room of DBAs, developers, and Sys Admins. Great use of twitter. Almost always a good discussion on there.

As for me helping someone, I am currently on the committee that puts together Pittsburgh’s SQL Saturday (did you sign up yet?!). For the last 3 years I have helped organize this event. It’s been a great experience working with some great people. We periodically meet to discuss the event. It takes quite a bit of work to pull these off. From organizing speakers and figuring out what is for lunch (no pizza!) to actually getting people to come. The reward comes the day of the event though. It’s great seeing the last couple months worth of work all come together.

Overall I think the community as a whole is the best part about being a SQL DBA. Most of the time every problem you’ve ever had has been seen by someone else and that person will 99% of the time be willing to help you out with it. And if there is a time where you might have a new problem, there will always be about 50 people on #SQLHelp or stackexchange to give you some ideas of what to check. Thanks SQL peoples!


T-SQL Tuesday – Assumptions

This is my first attempt at contributing to T-SQL2sDay. The topic of the month is Assumptions. 

Assignment – Write about a big assumption you encounter at work, one that people are uncomfortable talking about.

I work for a vendor and our application(s) don’t live on a single server but on many different servers. We have message boxes, SQL servers, application servers, and so on.

When we get a call/email about our applications being slow or timing out, we usually log into the server and try to take a look and see where the pain point is. A lot of the blame is usually put onto SQL or the application.

Here is where the assumption comes in.

As a database administrator working for a vendor who deals a lot with the support team, we assume that the hardware was specced out and properly configured. Many times during conference calls this unease topic comes up. Sometimes these conversations go something like:

“Your application isn’t working, it’s so slow. We keep getting timeouts”

“We might need to give it a bit more memory, my laptop has more resources than the database server”

It’s not a conversation a lot of people want to have but sometimes it really isn’t the application or it really isn’t SQL’s fault.

Many users of the system, the administrators, engineers, and executives all assume that all the magical hardware is the appropriate speed, size, etc. But is it really? Is your hardware adequate for what your application is trying to do?


SQL Saturday #250

I’m currently helping to plan Pittsburgh’s next SQL Saturday. It should be a good day with lots of good sessions by some new (see Steve Hood‘s post here) and some returning speakers.

I’m personally looking forward to the session on Statistical Semantic Search. It’s something I haven’t used before.

Here is the current brochure. If you haven’t signed up yet, what are you waiting for?!

Welcome to SQLSaturday #250 in Pittsburgh

SQLSaturday is a full day FREE  training event for SQL Server professionals and those wanting to learn more about SQL Server and network with the SQL Server community. Our event will be held Saturday September 14, 2013 at ITT Technical Institute, 5460 Campbells Run Road, Pittsburgh, PA 15205. Admittance to this event is free, but we charge a fee of $10.00 to help defer the cost of lunch, coffee/tea  and water which will be provided to attendees throughout the day. 

Pick from more than 30 different sessions and learn from the experts!! Check the schedule to take a peek at the sessions and the speakers. Some of our sponsors are interested in hiring. Don’t forget to stop by at the sponsors table during the event.
Please register soon as seating is limited, and let friends and colleages know about the event.

 New this year !! An entire track for “Wanna be DBAs”. Don’t know anything about SQL Server? This track is for you !! Explore the life of a DBA and see if it can be yours too. This is mainly focused towards students and novices. Once you complete your registration at . Register via eventbrite at  also( only if you want to attend the “Wanna Be a DBA” track).

Check “Track 0” under the schedule  for session details

Don’t forget to follow us on Twitter @SqlSaturdayPitt and join the conversation with the #SqlSat250 hashtag

PreConference Training (Optional and prepaid) – Friday Sept 13, 2013

Introduction to Performance Tuning in SQL Server 2012 presented by John Sterrett and Konstantin Melamud
Have you always wanted to dig into performance tuning but wasn’t sure where to start? During this all day pre-con you will be able to find out which SQL statements are causing your performance problems and you will be able to apply basic concepts to improve your general performance. You will also get all the free tools and scripts the pros use to to their systems.

Eventbrite - Introduction to Performance Tuning with SQL Server 2012

Tempdb Destroyer

One of my previous posts, I covered how to create a really big database with just garbage data in it. Recently, I was given a task to blow up tempdb. I thought about using a similar script and tried it out.

Of course it worked and it blew up TempDb but it also errored out. This wasn’t acceptable. I needed to find a way to blow up the tempdb with out it erroring out.

I tried a couple little things but ended up using the following script:

SET nocount ON
USE tempdb
IF OBJECT_ID('tempdb..#freespace') IS NOT NULL
DROP TABLE #freespace
IF OBJECT_ID('tempdb..#BigGuy1') IS NOT NULL
IF OBJECT_ID('tempdb..#BigGuy2') IS NOT NULL
IF OBJECT_ID('tempdb..#BigGuy3') IS NOT NULL
IF OBJECT_ID('tempdb..#BigGuy4') IS NOT NULL
IF OBJECT_ID('tempdb..#BigGuy5') IS NOT NULL
CREATE TABLE #BigGuy1 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy2 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy3 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy4 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy5 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #freespace
( drive VARCHAR(1),
[MB Free] INT)
@free INT
SELECT @drive = LEFT(physical_name, 1)
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
INSERT INTO #freespace
EXEC master..xp_fixeddrives
SELECT @free = [MB Free]
FROM #freespace
WHERE drive = @drive
while @free &gt; 1
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
DELETE #freespace
INSERT INTO #freespace
EXEC master..xp_fixeddrives
SELECT @free = [MB Free]
FROM #freespace
WHERE drive = @drive
print @free

This one uses a loop to check how much free space you have left. You can also set the threshold. For example, if you want to have 100MB free, you would just change @free > 1 to @free > 100.

With a word of caution, do NOT run this on your production system. We used this for a training session in a TEST environment.

The easy part about this one is that when you are finished expanding your tempdb, you can just restart your SQL services and it will reset the size.

Have fun!


#sqlsat171 – behind the scenes

This year was the first year Pittsburgh had a SQL Saturday. Luckily, I was able to find out about it a couple months beforehand and was able to get involved. I didn’t help out that much but I was still was able to lend a hand. It was the first time I was able to be part of this kind of event. In the past I have always been the one who went to the sessions but this time I got to see behind the scenes.

It started when I signed up (one of the first days!) and checked the “would you like to volunteer” checkbox during the registration process (which was very easy, btw). I then received a series of emails for in person meetings or phone conference meetings. I was able to attend a couple of the phone calls but didn’t really have much input to offer. The only in person meeting I was able to attend was a meeting after our local user group meeting. It was a great one to go to because it was the one where we picked out all the sessions. It was a long process but I think we all were satisfied with the outcome. We had a solid slammed packed day planned.

After that, I helped out a bit by contacting the former Chicago SQL user group president, Bill Lescher and asking for some words of wisdom. He had some good advice about getting sponsors. I also contacted about sponsoring the event (which they did!!!)

So then some more emails and then finally the day came. Volunteers had to be there at 6am so that meant I had to wake up at 5am to get ready and drive up to the north side of Pittsburgh. Of course I had to stop on the way and pick up some caffeine in Monster form.



My duties for the day were to be room proctor. I was in charge of getting the speakers what they needed, handing out and collecting evaluations, and making sure we stayed within the time limits. It was an easy job because I got to attend sessions at the same time.

So how were the sessions? GREAT! It was nice to spend a whole day listening to and learning about a technology that you find interesting.

I started off my day to a nice intro to Service Broker by Adam Belebczuk. Service broker can get a bit complicated if you have never used it before. I thought Adam did a great job cramming a ton of info into 1 hour. Great presentation skills as well (ZOOM IT!).

Next up was Dean Richards talking about VMware. It was another good introduction to VMWare. If you never used VMWare before then this was your course. It was a nice recap for me.

The final morning session was Mike Hiilwig‘s session of 7 SQL Agent jobs that I should be running. It was a very entertaining session that show some great methods for DB maintenance. Personally, I like to use powershell for monitoring errors over multiple servers but Mike’s ideas were also very good. I hope to see more sessions by him in the future.

LUNCH BREAK – The only take away I got from lunch was that college students don’t get dressed on weekends. The event was held at La Roche college. We didn’t eat until around 12:30. While I waited in line to get food with my other SQL Sat-ers, I noticed that every single college student I saw was still in their PJ’s or sweat pants. It was fall break though. ii-na.

After lunch I went to Justin Dearing‘s session on MongoDB. It was very cool to be exposed to noSQL. I’ve read about it before but have never seen it up close. It’s something I doubt I will ever use but now I feel I have a better understanding of it.

The next session was Michael John’s Top Development and Design Mistakes. It was a packed room for some antodotes of Michael’s experience in the field.

My final session of the day might have been my favorite. It was Sam Nasr’s 14 new SQl functions in 2012. It was exactly what the title says it was. We went over them 1 by 1. It was a great session that was more like a class. The whole group was engaged in the session. We actually had some time left over and explored these functions a bit more in depth.

After the sessions finished everybody filed back into the main hall for the giving out of the prizes and the final closing remarks.


After the day was over, some people headed to the bar for a couple SQL drinks. It was a nice way to finish off a great day. Overall, I enjoyed helping out (the little bit I did) and thought it was a great thing for the city of Pittsburgh. I hope we have more of these and the community is continues to support community training.

Thank you to everybody involved!!




Changing physical locations of all DBs

I got a request this week from an application engineer. The request was to move ALL physical database files from one physical drive to another physical drive on the same machine.

Hmmmm, I have never moved ALL .mdf files from one place to another at one time. One of my teammates told the application engineer that it may take some time. He want to detach/move/attach all the DBs.

I knew this wouldn’t work because:

1) it would take forever

2) how are you going to detach the master DB?

No Detach for YOU!

So my solution contained 3 parts:

  1. Change the location of all the files inside SQL
  2. shutdown and move the files
  3. Change SQL’s startup parameters for the master database

Easy as π.

I used some good old dynamic SQL to create an ALTER DATABASE script for each db (except the master)

--first, let's just take a look and see where our files are living
SELECT name, physical_name,
END AS FileType
FROM sys.master_Files
--Now let's create those ALTER DB statements
SELECT 'Alter DATABASE ' + + ' MODIFY FILE ( NAME = ' + + ', FILENAME = ' +
--here is where you want to put where you want to move your files
' ''c:\NewFolder\'+
right(physical_name, charindex('\',reverse(physical_name))-1 ) + ''' ) '
FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
 --remove this if you want both DB AND log file
 type = 0 
--and keep this part, dbid 1 is master
 and d.database_id <> 1
--Check again to see if the physical name is changed
select name, physical_name,
when type = 0 then 'DATA'
when type = 1 then 'LOG'
end as FileType
from sys.master_Files

After you run this, you should see this message a bunch of times:

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

Now you can shut down your SQL services and move the physical files to their new location.

Once you have all the files moved over (including the master) you’ll need to change the startup parameters.

To do this:

  1. open up the configuration manager
  2. Right click on your SQL instance –> properties
  3. Click on advanced –> startup parameters
  4. Change the location of the master .mdf
Change the location here
Once you have updated your startup parameters, you should be good to go. I want to stress “should” here because you have to make sure that your service account has access to the new location. If it doesn’t, SQL won’t start.
I tired this a couple times on my test machine and then in the production environment. I was hoping to “wow” the customer with the speed of completing this task, unfortunately their disks were really slow and moving the files took the majority of the time.

WTF is the DL on COTS EMR DBs?

Well it seems I have missed April and allllllmost May. I had a nice 2.5 month vacation from work but just this week started a new position in Pittsburgh. During my time off I monitored #sqlhelp, read some articles, and tried to keep up with the ever-changing world of databases. I also did a lot of relaxing, played too much skyrim, and traveled quite a bit.

The company I started working for is having a conference for their users this week so almost no one is in the office. It gave me a good chance to read through their documentation.

It’s very easy to get lost in documentation when you are new to a company. Especially when most companies these days use lots and lots of acronyms. Luckily, a company acronym dictionary was given to me in my intro packet. It came in handy while reading through documents and meeting with various people. It’s still easy to get lost in conversations involving simple things.

Do you use a lot of acronyms at your company? Do you document them? Love em/Hate em?