Mar 222016

This post took me a little longer to put together than it should have, but seeing as how I now have a two week old son at home, time is a little harder to come by these days! My T-SQL Tuesday topic ended up attracting 4 excellent responses, which I’m very happy to recap.

The first post was from Rob Farley (@rob_farley). Rob has some great insights into text search, but not full-text search. (That’s perfectly fine, I left the topic open on purpose and never intended for it to be limited to full-text search.) He tells us all about how collation can affect text searches and can greatly influence your results. Collation matters, folks! If you don’t believe me, try Rob’s example and you’ll see that he’s not joking. Rob finishes up talking about how indexes and collation can affect columns included in the GROUP BY clause, and includes a great tidbit on how collation can necessitate transformation of text when calculating hash values.

Next was Kenneth Fisher (@sqlstudent144). He walks us through the query in the header of his blog, which does a bit of text manipulation to create a hidden subtitle. I’ll admit I’ve never tried running that query myself, but the results sure are nifty. Good work, Sir!

Anders Pedersen (@arrowdriverolled his own full-text search back in the days before SQL Server supported it. Sounds simple and effective, and if it worked for the business, that’s all that matters, right?

Last, but certainly not least, is Jon Morisi. Jon tells us about his experiences with full-text search in terms of migration, and how recent developments in Azure sound most interesting to him. He is hopeful for the future.

So that’s T-SQL Tuesday for this month. Thanks to Rob, Kenneth, Anders, and Jon for posting, and to everyone else for reading our work. Thank you to Adam Machanic for dreaming up the idea for this a mere 76 months ago. If you’d like a complete listing of all 76 (and counting!) T-SQL Tuesday topics, Steve Jones has been kind enough to compile one.

See you next month!

Feb 292016

I’m so happy to once again be hosting T-SQL Tuesday. If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic, and it’s been going on for over 6 years now! Basically the host selects a topic, defines the rules, and then everyone else blogs about it. Once everyone’s done, I’ll summarize each of the submitted posts here on my site.

The Topic

This month, I’d like to talk about text, particularly searching and processing it. Many systems contain large amounts of text in one way or another. Often, that text ends up being stored in a database, and SQL Server has offered Full-Text Search for quite a while now to handle such usage cases. But that’s only a small part of the story.

If you’re using SQL Server Full-Text Search, I’d love to hear from you. But I’d also love to hear from anyone using any other kind of text searching or processing methods. Maybe your organization previously used SQL Server Full-Text Search but you’ve since moved to a different application. Maybe you have a tale of success or woe from a previous job. Maybe you don’t let any of your text search operations touch a relational database with a 10-foot pole. Whatever your story is, I hope you’ll please consider sharing it with us all on Tuesday, March 8.

The Rules

There’s only a few rules for T-SQL Tuesday:

  • Your post must be published between 00:00 GMT Tuesday March 8 2016 and 00:00 GMT Wednesday March 9 2016.
  • Your post must contain the T-SQL Tuesday logo (see above) at the top and the image must link back to this blog post.
  • Trackbacks should work, but if they don’t, please put a link to your post in the comments section so I (and everyone else) can see your contribution!

There’s also a few optional you can do that might help:

  • Include “T-SQL Tuesday #76” in your blog post’s title.
  • Tweet about your post using the #tsql2sday hashtag
  • Contact Adam Machanic and tell him you’d like to host a T-SQL Tuesday from your blog.

And that’s all there is to it! I’m looking forward to seeing what everyone writes about!

Jul 142015

I’m so happy to be able to contribute to this month’s T-SQL Tuesday! Andy Yun picked an excellent topic: default settings. Defaults exist for a reason: in the absence of a user’s preference, they represent the option that the application’s author(s) believe will generally work the best for the greatest number of people.

All that being said, if you’re reading this blog, you probably aren’t the average computer user, and you probably aren’t a fan of all the default settings your applications choose. I’ve got plenty of defaults for different applications that I despise and do my best to change as quickly as possible. Here’s a few that really grind my gears:

Windows File Extensions

I like to see the full names of my files, including the extension (the “.”, typically followed by 3 or 4 characters after the file name).  Windows tries to be helpful and by default doesn’t show the extension if it recognizes the file type. For example, the Microsoft Word document “Letter to Grandma.docx” would be shown as simply “Letter to Grandma”. This may be fine for many, but I’m not a fan.

No file extensions. Boooo.

File extensions. Much better!

To enable their display in Windows 7 (yes, that’s what I use at home) from any Windows Explorer window, go to “Organize” > “Folder and Search Options”, then the “View” tab. Here you’ll find a list of checkboxes, one of them is called “Hide extensions for known file types”. Uncheck this box and you’ll be able to see the extensions for all files, not just the ones Windows can identify.

Hidden Files

Not only do I like to see file extensions, I also like to see all my files. Windows allows files to be marked as hidden, which means they still exist on disk, just they are not displayed in Windows Explorer. While this can be useful to keep prying eyes away from files, it is not a security feature in any way, shape, or form.

To enable the display of hidden files, go back to the very same window we found the file extension option in (“Organize” > “Folder and Search Options” > “View” tab). The list of checkboxes has a section for “Hidden files and folders”, and its options are “Don’t show hidden files, folders, or drives” or “Show hidden files, folders, or drives.” Now you’ll be able to see everything.

Line Numbers in SQL Server Management Studio

This is primarily a SQL Server blog, after all, so I had better include something SQL-specific. SSMS has plenty of default options that are worth changing, but one of the first ones I take care of is adding line numbers. I like to be able to quickly see what line I’m on by glancing at the left margin of the query window. I understand why the default doesn’t include them, but being a programmer at heart, I like to see them. They comfort me.

To add line numbers in SSMS, go to Tools, then Options. Under “Text Editor”, select “All Languages”, then check the box to display line numbers.

(click to enlarge)

So there you have them, arguably my top 3 favorite defaults to change. I hope you found this helpful, and thank you Andy for the excellent topic!

May 132014

This month’s T-SQL Tuesday (the 54th!) is brought to us by Boris Hristov (@BorisHristov), and he’s asking us to write about interviews and hiring. I think many of us have at least one tale of an interview or interaction with a recruiter that’s gone good, bad, or ugly. I know I’ve got plenty of them, two of which I’ve shared before.

This time, I have a story of an interview that went well, but with an epilogue that completely threw me for a loop.

A while back I was contacted by “Shirley” (not their real name), a DBA at a company that had an opening for a Senior SQL Server DBA. They were wondering if I would be interested in applying, and sent a job description that was fairly vague, but with the potential to be something interesting. While perfectly happy in my current position, it never hurts to see what other opportunities are out there. I submitted my resume and was quickly granted an interview.

At the interview I talked to a few different people and got a much better idea of what they were looking for than the job description provided. I also spoke with Shirley and of course got that tour of the office where I saw the kitchen and the room with the Xbox and foosball table. After the tour, I did the standard wrap-up with human resources where they ask things like “tell me about a time where you had to work with others towards a common goal” or “what would you say is your biggest flaw?” They asked what my current salary was. They did not ask what my salary expectations were, but that’s ok because I probably wouldn’t have told them anyway. I tend to answer that question with a statement like “an increase based on my experience and the value I and my skills would bring to your company.” That was pretty much the end of the interview. My opinion at this point was that I wasn’t particularly impressed by the position, but the right amount of money might persuade me (but probably not).

Fast forward a few days, and I got a call extending me an offer. As is my standard practice, I never accept an offer immediately. I always give myself at least one night to think about it and confer with my wife. I considered this offer to be a non-starter: the position didn’t excite me and the pay wasn’t compelling. They offered me more than my current salary, but only a tiny bit more (less than 1%). In some circumstances I might have taken it, after all there’s way more to a job than the pay. But this place totally wasn’t worth it.

Having slept on it and talked it over with Michelle, I called their HR manager back and declined the offer. I thanked them for their time, their consideration, and for extending the offer to me, but said I am happier staying where I am at this point. HR was equally polite, thanked me for getting back to them, and wished me well. I’m sure in the back of their mind they weren’t quite so happy because it’s their job to fill positions and now they had to find someone else to offer it to, but (in my mind) a HR professional will never let that show. A job offer is just that, an offer, and the person it is extended to is under no obligation to accept.

That was that. Or so I thought. A while later I got an email from Shirley. All it contained was the following:

“So you did not what [sic] to negotiate with us but simply use it as leverage to get a salary bump? Good luck in the future.”


First of all, the “salary bump” comment was totally unfounded, but even if that were the case, it’s none of Shirley’s business.

Second (and more importantly), as an interviewer, I would never ever contact a candidate for any reason, especially in a negative tone like that. I consider communication with candidates outside of the interview room to be completely within the domain of human resources.

I wanted so badly to be the bigger person and just brush it off, but a few hours later I was still taken aback by tone of that email. I forwarded it back to Shirley’s HR and told them I was very sorry Shirley felt the need to send that to me, as their remarks did a rather poor job of representing the company. I added that if that’s the kind of person Shirley is, then I’m very glad I won’t be working with them. HR replied and thanked me for bringing it to their attention. The end.

Why not negotiate?

As wrong as Shirley’s email to me was, they did make a valid point. I certainly could have negotiated and probably would have gotten closer to my desired salary. But I wasn’t interested enough in the job to do so. If you’re not interested in taking a job at any price, there’s nothing to negotiate. Not to mention if you ask for $x and they got the approval to raise the offer to that amount, it would be really unprofessional to say no at that point.

Final Thoughts

As an interviewer, whether individually or as part of a team, you are the face of your employer. In addition to vetting candidates and trying to determine if they would be successful in a position, it’s your job to portray your workplace in the best possible light. If you can’t do that, you probably shouldn’t be taking part in interviews.

Mar 112014

T-SQL Tuesday LogoThis post is part of T-SQL Tuesday #52, which is being hosted this month by Michael J. Swart (@MJSwart). Michael is asking us to argue against a popular opinion, and I’m more than happy to do so, as this is a belief that I’ve kept to myself for quite a while.

SQL Server’s row compression feature can be an amazing tool. Not only is it lightweight on CPU usage, especially when compared to page compression, but it can save a significant amount of disk space as well. Your data also remains compressed while in the buffer pool, meaning more rows can be stored in memory, reducing the need to make slower requests to disk. On top of all that, some queries (especially those involving index scans) can see dramatic performance improvements.

In fact, row compression is so good that Microsoft’s whitepaper actually states “If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, all data should be row-compressed.”

Yes, row compression is a wonderful thing, and the databases I maintain frequently benefit from its use.

But I hate it.

Why? Because all too often, features designed to help make things easier also make people lazy.

By far, the biggest issue that row compression addresses is poor data typing, the use of a data type that isn’t appropriate for the values at hand. For example, if a column is only going to store the values 1 through 5, an integer data type is not necessary. A tinyint data type would be just as effective, and would consume only one quarter of the space. However if you are unable to change the data type, perhaps because the database is part of an application written by a third party, row compression can be a big help.

Row compression allows the storage engine to treat fixed-width data types as if they were variable-width. This means the disk space that isn’t needed to store a value in a fixed-width column and would typically go unused can be put to work. The savings can be tremendous, and SQL Server’s data compression features are completely transparent to end users or applications – all the magic happens behind the scenes, which is why a third party application would be none the wiser.

But what if you are able to change those data types, and just don’t feel the need to do so anymore? Data compression gives you most of the advantages of proper data typing, but all you have to do to get them is flip a switch – no pesky forethought necessary. And that’s why it’s terrible. Because for every person out there who designs their databases and data types with care, there are many more who aren’t interested, don’t care, or don’t even realize it’s an option. Features like row compression that mask these issues aren’t going to interest anyone in solving them the right way.

So while row compression is a wonderful tool and can do amazing things when used properly, don’t forget it’s also an enabler.