Very interesting topic. I have wrestled with this in the past, and have ended up choosing the specialization path. A lot of it has to do with where one ends up working. In smaller shops there is usually no option but to wear multiple hats. In bigger organizations, where one is part of a larger team, it is usually easier - and even the only option - to specialize.

It also has a lot to do with how you want to advance your career. People who want to follow a management path will need to have knowledge on related fields to their area of expertise, so they can communicate effectively with other teams in the organization. For these people it is important to maintain high-level knowledge of many related areas and how they all fit together. On the other hand, people who are interested in a more technical path may find that specialization is the best option. As you have pointed out, the amount of technical knowledge, even within SQL Server, is becoming so vast these days, that it is becoming more common for someone to be a specialist in one area WITHIN SQL, such as BI.

One last aspect is innovation. It is difficult for people to be able to find new and better solutions to problems without deep knowledge in their area of expertise. Again, because of the vastness of information out there, specialization is key. Of course, someone with a broader view of things may be needed to guide or prod the specialist to innovate. And that's where being an effective manager comes to play.