Underappreciated features of Microsoft SQL Server


SQLServerCentral (or “SSC” as we know it), ran a very good editorial last year on Friday, December 17, 2010. The editorial was a guest editorial by Andy Warren on “under-appreciated features of SQL Server” (http://www.sqlservercentral.com/articles/Editorial/71788/). Andy looks back at the evolution of Microsoft SQL Server over the past decade (that we just bid goodbye to) and the tons of features that have been introduced across the various releases. Some of these features are still with us, some have been deprecated; however the editorial is more targeted to identifying those features that held a lot of promise, but for one reason or the other failed to take off within the general SQL Server community.

If you follow the discussion that took place afterwards, a lot of interesting features were listed down. I have here a summary of the same:

SQL Server Management Studio/Profiler

  1. Object Explorer Details window (Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server, Object Explorer Details (Part II) – Underappreciated features of Microsoft SQL Server)
  2. Ability to filter in the object explorer
  3. Templates & Template Explorer
  4. Activity Monitor
  5. Blocked Process Report
  6. T-SQL Debugger
  7. Multi-server queries

T-SQL Enhancements

  1. Passing a table valued parameter to a table valued function
  2. Error handling – TRY/CATCH, The THROW command 
  3. Filtered Indexes
  4. Windowing functions & Partition functions (ROW_NUMBER())
  5. CTEs
  6. OUTPUT Clause
  7. PIVOT
  8. CROSS APPLY
  9. BCP & Bulk Insert

Administration Enhancements

  1. Dedicated Admin connection
  2. Catalog views & DMV
  3. SQLCMD mode in SSMS

Major Technological Enhancements

  1. SQL CLR
  2. Resource Governor
  3. Event notifications
  4. Tuning Advisor
  5. Data Collector
  6. Central Management Server
  7. Missing indexes in graphical plans
  8. XML tools
  9. Upgrade Advisor
  10. Connection Strings
  11. Service Broker

As I was following the discussion and compiling the list, it dawned upon me that this is an excellent opportunity to write a little bit on each of these features that we as a community might end up appreciating a bit more.

The major technological enhancements are such that they require a detailed study of their own. However, the rest are really very quick demos in order to get started and I am sure that they will definitely change your life at the work place.

I will thus spin off a series of small articles and ultimately link them all back to this parent article. Finally, as a closing post to the series, I will be also be pointing you towards some learning resources for rest of the items on the list.

Do send in your feedback on how you would use (or are using) each of these items.

Have a good day!

Be courteous. Drive responsibly.

Advertisements

2 thoughts on “Underappreciated features of Microsoft SQL Server

  1. Ulrich Drescher

    Thank you for the nice post. Good summary of exactly the features that are “invisible”. E.g. the filtering in object explorer I discovered accidentally a few months ago and I like it. The blocked process report I will search for and test it in the profiler.

    Like

    Reply
  2. Nakul Vachhrajani

    Thank-you, Ulrich. I will be writing on each of these features and link them up to this parent article. Hope you like the rest of the series. If you have any suggestions or would like to hear about some other feature also, do let me know.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s