Under Appreciated Features

Question

Under Appreciated Features

Resource Governor
Filtered Indexes

Jedak SSCertifiable Points: 5576 More actions · Answer 1

ben.mcintyre (12/17/2010)
Now if only I could get SQL2005 to run with the old tools ....
Ben

Query Analyzer still works. In fact, I use to all the time to develop TSQL items against sql server 2005 and 2008. It connects and executes queries. The object browser works as well. About the only thing I've found that doesn't work is scripting.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

Some of my older sub-queries that were using a GROUP BY with MAX() for things like "get the most recent address" or "get the primary phone number", I've replaced that with the newer RANK() OVER (PARTITION BY.. ORDER BY..) function. It's not always faster, but it is easier to read.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Cliff Jones SSChampion Points: 10547 More actions · Answer 3

sognibene (12/17/2010)
Passing a table valued parameter to a table valued function. OH YEAH!!!

I agree! We had this in Oracle PL/SQL 15 years ago!

WayneS SSC Guru Points: 95460 More actions · Answer 4

Charles Kincaid (12/17/2010)
I did not know that you could put a CTE in a view. I tried it and it worked. Seems reasonable given what a view actually is. I had not seen it stated that it was supported.

FYI: In BOL - WITH common_table_expression (Transact SQL), in the first paragraph:

This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I think there are a bunch of things that are under appreciated.

Resource Governor

CTE

Service Broker

Event Notifications

XML (for xml and xquery)

and finally DMOs.

I think people know of the DMOs, but they don't really use them. It is too easy to rely on old habits of querying sysprocesses or to continue to do things the way they were done in SQL 2000.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

thirst SSC Rookie Points: 28 More actions · Answer 6

thirst

SSC Rookie

Points: 28

December 17, 2010 at 10:18 am

#1264160

I would have to put my vote on (in this order)

Charles Kincaid SSChampion Points: 13593 More actions · Answer 7

Charles Kincaid

SSChampion

Points: 13593

December 17, 2010 at 10:31 am

#1264164

Thanks Wayne. I missed it.

ATBCharles Kincaid

aaron mertes SSC Enthusiast Points: 155 More actions · Answer 8

Another item that i find astounding is not relegated solely to SQL Server. It's the actual SQL language itself. I have worked with no other language that is as expressive and powerful, yet so condensed. You can solve most all database problems with less than 10 keywords, amazing!

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 9

I would say that Central Management Server is the best for me. I can right click on my central management server and choose new query. Once a new query window opens for all of my servers, pink ribbon at the bottom of the screen, I can then open my templates and double click on my "show all job step failures for last 3 days" template and execute the template and view any any job steps that have failed on all of my servers over the last 3 days.....

Checking all of the servers in less than 5 minutes 😀

Revenant SSC-Forever Points: 42467 More actions · Answer 10

For me, the most underappreciated feature is the integration with Visual Studio. I cannot imagine doing complex cubes or SSIS packages without it.

rbartram-847800 SSC Veteran Points: 224 More actions · Answer 11

The SQL dedicated admin connection, so your Management app does not lock up and hang when something goes wrong in SQL. So needed over the years.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

steven.malone (12/17/2010)
You mentioned "DTS lead to SSIS"
More like Microsoft threw DTS under the bus and brought out SSIS.
I think the most under appreciated feature was the ability to create system functions.
Clearly it was under appreciated since they yanked it.
It was really nice having a business rule in one place that could be called from any database without a three part name.

I don't know that I would say they supported user defined system functions, you kind of had to hack them in to make them system.. So yeah it was under appreciated.. I do agree that they worked good and were useful but not really supported.

In general I'd like to back the bus back over DTS just to be sure its dead.. However, with that said, DTS was supported in 2005, 2008, and 2008R2, as of now it is not supported in 2011. In almost every way SSIS is better and not many vendors support dual product tracks for the same kind of tool, so I guess it is hardly a surprise..

CEWII

pef SSC Veteran Points: 213 More actions · Answer 13

I just scanned through the replies and did not see any mention of TRY/CATCH, thinking back to the error handling inconsistencies before SS205 and the thousands of lines of "IF @@EROOR <> )" crap every SP was cursed with, this gets my vote.

pef

Paul White SSC Guru Points: 150467 More actions · Answer 14

SQLCLR

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jon Russell Hall of Fame Points: 3048 More actions · Answer 15

A couple of things come to mind, but one feature I have started to rely on this year was the Blocked Process Report. It takes a bit to get the hang of using it, but it is very useful and helps you zero in on the top blocking process.