Select * Syndrome

NoSomething that I have seen lately over and over again and even ran into this morning is a practice that I would say is a pretty bad habit in SQL….the dreaded Select * syndrome

This method is heavily used for adhoc querying and I’ve seen it used in some troubleshooting scenarios but in my case I don’t have room for it in a production environment embedded in functions, procedures, or views.

To me it is a wasteful tactic in bringing back what is needed; it can produce unwanted scans or look-ups when in some cases all that is needed is index tuning. I’m a big fan of bringing back what you need instead of bringing back a tremendous amount of data. One can also make an argument for all the unused overhead it can produce.

I cannot begin to tell you the many times of deploying something out and then to find out the schema has changed and the select * in a view that was left in place years ago is my culprit from years of past coding that has been done.

For example; one that I have seen within the past couple of months is a view:

Select *

From table 1

Union All

Select *

From table2

This was being used quite frequently and is just asking for trouble and poor performance. There will always be excuses as to why it wasn’t done differently but in the end you will go back in and clean it up so it is best to think the process you are working on through in the beginning instead of the end.

Can I ever use it?

Sure….I’ve seen it used in If Exists clauses many times over and from my research and what I know SQL does not count this in the execution plans; if you leverage SQL the correct way it is more than powerful to handle what you need.

Tools to fight the good fight………

My source control is TFS and I like the newest version as you can set up controls that if a Select * is found it will break the build in dev forcing it to be resolved

If you haven’t already downloaded the free version check out the SQL Plan Explorer provided by SQL Sentry. Execute the tasks different ways with the select * and with pulling back designated columns and review the execution plan; you will be surprised at the outcome, and if you are old school that is fine too – analyze it in SSMS and see what you find.

Where Do I Start – The Journey?

journey

I’ve recently been approached by numerous people at work, on boards, forums, twitter, etc. regarding different ways to learn and improve one’s skill set.

The Forums

I find myself on forums more so than normal perusing through questions, providing feedback where applicable and so on. Some of the ones I traffic often are:

  1. SQL Server Central
  1. The SQL Brit’s Forum

I think forums are a great avenue to see what others are sharing on topics that I might have gone through, are going through, or will go through.

Bloggers

I enjoy reading/following bloggers of who I consider to be at “the top” of the SQL game. I will not list them all out here but if you look to the right you under DBA Blogs you will find additional links of blogs I follow. Over my decade in dealing with SQL I have tried to model and pick up some of the knowledge that they share on a regular basis. Take time to hit some of their sites and remember if you do reach out to one of them I always say be respectful; a lot of their time is providing free knowledge to the community which is what helps make the community grow and be better; be sure to thank them for all they do.

Email Subscriptions

My days normally consist of several hundred emails daily but I try to take a certain part of my day and read the emails I subscribe to; I really enjoy MSSQLTips, SWUGG, the live burn feeds I receive from the bloggers I follow, and several more. Getting involved and subscribing to some of the top tier SQL sites provides another avenue for learning opportunities, networking, etc.

Books

Really depends on what SQL Server versions you are running right now. A lot of people have been asking me about 2008 R2 and for that I liked the DBA Cookbook; felt it had some good stuff in there. Red Gate puts out a lot of good stuff along with the SQL Deep Dive editions.

Training Sites

Some ones that I’ve used or recommended in the past are:

  • Pluralsight – I’m a big Paul Randal and Glenn Berry fan so when SQLSkills.com joined it was a no brainer for me.
  • SQL Course
  • PASS (take advantage of the local chapters and virtual chapters)

Closing

I read an article this week by SQL Brit (John Sansom) regarding “Overconfidence – How it Almost Cost Me and What You Can Do to Avoid It”. After reading it I felt like it was one of the better ones I’ve read in the past month. It put things into perspective in a sense that while I’m very confident in my abilities you can never become “lax”. I am a believer in learning everyday and I will continue to hone in on my skillset; at the same time it is imperative that we do not just “assume” or “take for granted” the regular routines we do on a daily basis.

These are some of the things (not all) I like to dig into on a regular basis and I’m sure you have your own; I’d love to hear what others utilize if you have time drop me a line and I’ll share it out on the post so other community members who pass by might be able to gain something from it.

24 Hours of PASS

SQL Training……..to a SQL DBA I’m always up for any training to learn new techniques, new features, or new processes. You throw in the word FREE and how can you help not be excited about what PASS has to offer.

PASS has recently released their lineup for the free 24 hr SQL training with some stellar presenters and some great topics. To check out the sessions and register for any of the online classes you want you can click below:

24 hours of PASS SQL Training

Enjoy!