Recent Articles:

Minion Reindex arrives this Friday!

The MidnightDBA team is announcing the release of a new, free index maintenance solution for SQL Server: Minion Reindex by MidnightDBA.  We designed Minion Reindex to solve all of the reindexing headaches we’ve experienced at dozens and dozens of client sites.

The Bottom Line

Your databases need index maintenance for optimal performance, and most shops don’t have a decent solution in place.  Minion Reindex is extremely simple to implement, and deeply configurable. Junior DBAs will love the ease of use, and expert data professionals will love the extensive settings and features available.

Read more on the Minion Reindex page:

  • Check out the full features list.
  • Get the download link there starting on Friday, October 24, 2014.
  • Join us for the Minion Reindex webinar Monday, October 27.
  • See documentation and tutorials as they come in!

You can ask questions and follow along with the discussion on Twitter using the #MinionReindex hashtag.

Stay tuned for the best thing index maintenance has ever seen…

 

PASS Summit 2014: Recommendations and Invitations (#sqlpass #Summit14)

PASS Summit 2014 Banner_200x200The first week of November, we’ll be at the PASS Summit inSeattle, WA.  This is the highlight of most SQL Server speakers’ years…a sort of educational, Christmassy nerdfest, with a feel of a class reunion mixed in. We have a lot of fun, and so I’d like to draw your attention to some of the better bits.

Taking part, if you can’t attend

The keynotes and luncheons are usually live broadcast, and you can order session recordings to cuddle up with at home. I suppose you can put on a kilt, go hit a karaoke bar with friends, drink too much, and contract the nerd flu, if it’ll make you feel more like you were at Summit. That’s what I’d do.

Some of the best stuff, if you’re there

Before the Summit starts:

During the Summit:

  • Get an invitation to High Tea 2014, our very fun and inappropriate party, if it turns out that’s your thing. (Requests and speaker abstracts due October 15!)
  • Hit a luncheon or two:  Women in Technology, and/or the Birds of a Feather.
  • Drop by the evening events. The Welcome Reception is usually worthwhile, and the Community Appreciation Party is at the quite awesome EMP Museum this year!
  • Check the After Hours page for the Photowalk, karaoke, Kilt Day, Table Top Game Night, and more.

Our Sessions

On Wednesday November 5 ( 3:00pm, room 602-604), Sean is presenting Performance Tuning Your Backups. This is quite possibly his best and most mind-blowing session. If you’re attending the Summit and you haven’t seen it, I really think you should.

On Thursday November 6 (1:30pm, room 401), Jen is presenting How to Interview a DBA: A Panel Debate, along with

This is a set of people with very strong opinions, so I fully expect it’s going to be a very interesting panel debate. Just the way we like it.

Happy days,
Jen McCown

http://www.MidnightDBA.com/Jen

Tip: Concatenate rows with XML, or COALESCE

Edit: I went and changed the first query without paying close enough attention. I’ve fixed the explanation and the query.

Concatenate rows with XMLMoonAndHEROnly

I still reference my Forgotten T-SQL Cheat Sheet, especially for the XML trick that turns a set of rows into a list. The T-SQL Cheat Sheet example uses a parent-child table combination, but what if we want to get the same effect from a single table? Here:

SELECT	DISTINCT Person ,
		Books = STUFF(( SELECT	',' + Book
				FROM	Books AS B2
				WHERE B2.Person = B1.Person	
				ORDER BY Book
				FOR XML	PATH('')
				), 1, 1, '')
FROM	Books AS B1;

 

This works for cases where you need several rows of comma delimited lists. So, data like this:

PERSON BOOK
Bob Book1
Bob Book2
Bob Book3
Tom Book7
Tom Book9

 

Could be easily run through the above query,to get a result like this:

PERSON BOOK
Bob Book1,Book2,Book3
Tom Book7,Book9

 

Concatenate rows with COALESCE

Now, a simpler case to concatenate rows is to just use COALESCE. It won’t give you multiple rows, but sometimes you just need a single, simple list:

    DECLARE	@Books VARCHAR(8000);
    SELECT @Books = COALESCE(@Books + ', ', '') + Book FROM Books;
    SELECT @Books as AllBooks;

If we used this on the same data set, we’d get this:

AllBooks
Book1,Book2,Book3,Book7,Book9

Just a quick reminder from your friendly neighborhood SQL-woman.
Happy days,
Jen
http://www.MidnightDBA.com/Jen

Release Date: Minion by MidnightDBA

Just 2 days 9 hours 5 minutes 31 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/