December 17, 2010 at 6:15 pm
I’ve got a couple in mind already that I’ll post as a comment, but I’d like to hear what you think first. What feature would you tell your fellow DBA’s to spend some time learning?
I'd have to say T-SQL itself. I know a whole lot of DBA's (system and otherwise) in the local area and I'm a bit amazed at what they don't know about T-SQL.
--Jeff Moden
December 17, 2010 at 6:21 pm
I know this is an article about built in features, but there's a corollary to it.
The online community for SQL Server has saved more Professional's buttocks than any other 'feature' out there, and I think we all take it for granted that the web is just chock full of information, analyzed, explained, and boiled down into consumable parts that we can apply directly.
I think, over time, we all assume that we can research just about anything we want on the web. Without the community though, there'd be nothing to research with. Many, many times though, it's taken for granted, or just forgotten. I've been just as guilty in the past, but without that community, I wouldn't know how to use 90% of the features in SQL Server to know if they were even useful, or that I should even look at them more than with a cursory glance.
So everyone, stand up, and give yourself a hand. We'll forget you again tomorrow. 😛
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 17, 2010 at 6:30 pm
Craig Farrell (12/17/2010)
I know this is an article about built in features, but there's a corollary to it.The online community for SQL Server has saved more Professional's buttocks than any other 'feature' out there, and I think we all take it for granted that the web is just chock full of information, analyzed, explained, and boiled down into consumable parts that we can apply directly.
I think, over time, we all assume that we can research just about anything we want on the web. Without the community though, there'd be nothing to research with. Many, many times though, it's taken for granted, or just forgotten. I've been just as guilty in the past, but without that community, I wouldn't know how to use 90% of the features in SQL Server to know if they were even useful, or that I should even look at them more than with a cursory glance.
So everyone, stand up, and give yourself a hand. We'll forget you again tomorrow. 😛
Nicely said, and it's certainly one of my favorite features 😉
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2010 at 7:26 pm
pef (12/17/2010)
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.
Man, what do you mean by "underappreciated"? If any of the guys on my team wrote a data moving proc, especially one used by SSIS, without this construct, I would have a serious one-on-one talk with him and let him know that if he did it next time, within say six months, we would not talk but he would get a letter of reprimand.
Underappreciated?
BTW, I am looking forward to THROW becoming mainstream.
December 20, 2010 at 3:28 am
For me, I would say the DMVs included in 2005 and later, including one I recently found very useful, sys.sql_dependencies. It allows you to find all the stored procs that refer to a particular column or table, and even lets you find select *'s that some idiot has left lying around...
December 20, 2010 at 9:55 am
CTEs. Output clause!!
And goodness, I can't believe no one has mentioned table-result User Defined Functions. What heaven.
When we get to SQL Server 2008, I know I will be terribly grateful to be able to define date-only and time-only fields.
Finally, while my love-hate relationship with SSIS is mostly HATE, I can say that I'm grateful to have something over nothing. I work with another agency who does not have something like SSIS with their database product and it makes things difficult.
December 20, 2010 at 3:49 pm
One thing I dearly miss from the Query Analyzer days is Ctrl-B. That combo would highlight the results pane bar and then you could move the pane up & down with the mouse. Now, you have to hover over the results pane bar and wait for the double arrows to appear. This is an irritant when you are working over a high-latency remote desktop session. Hover...wait 10 seconds...wait for double arrows...move mouse...wait 10 more seconds...hover again. Lather. Rinse. Repeat.
Being in the BI space, I really like being able to craft MDX queries directly in SSMS. I also like the ability to explore data using the Cube Browser - quick and effective.
James Stover, McDBA
December 20, 2010 at 9:01 pm
James Stover (12/20/2010)
One thing I dearly miss from the Query Analyzer days is Ctrl-B. That combo would highlight the results pane bar and then you could move the pane up & down with the mouse. Now, you have to hover over the results pane bar and wait for the double arrows to appear. This is an irritant when you are working over a high-latency remote desktop session. Hover...wait 10 seconds...wait for double arrows...move mouse...wait 10 more seconds...hover again. Lather. Rinse. Repeat.Being in the BI space, I really like being able to craft MDX queries directly in SSMS. I also like the ability to explore data using the Cube Browser - quick and effective.
I miss the functionality of the f4 key in query analyzer... nice, super quick way to find tables and column names even throughout the entire server.
--Jeff Moden
December 20, 2010 at 10:18 pm
Jedak (12/17/2010)
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 don't work is scripting.
I should qualify my earlier whines by specifying that it is specifically scripting that I am whining about.
Jeff and James can still go back to QA with SQL 2005 (and 2008 ?), and I often do, due to several particularly annoying bugs is SSMS (some innocuous queries hanging, not being able to copy the tabular result set in any sensible way ...), but in general there is not a lot of difference.
I am a developer more than a DBA and hence every single thing I do is scripted so it can be rolled out in a versioned update script.
EM: select any number of objects, right click, script, minor cleanup
SSMS: select one object at a time, script, remove vast reams (many pages) of formatting metadata (there is no option to turn this off), search for copy of and add the IF EXISTS DROP statement, cutting and pasting the object name which doesn't highlight cleanly now with a double click because the entire declaration is done as a string (which causes its own problems if, heaven forbid, you want to modify anything, since all quotes are now escaped), paste to destination. Repeat. Wipe sweat from brow.
or, use the clunky bulk scripter wizard which allows multiple objects but gets dependency orders all mixed up and results in so much confusion it's better to go one at a time.
Why do we go two steps forwards, one step backwards with these tools ? I could understand if it was temporary, since the product was completely rewritten, but it looks to me like someone seems to believe there's no problem here.
Sorry, this should be under 'most overappreciated feature' 🙁
December 21, 2010 at 1:25 am
ben.mcintyre (12/20/2010)
(some innocuous queries hanging, not being able to copy the tabular result set in any sensible way ...)
I'm curious--what would you class as a "sensible way"? I've often copied the tabular output of the SSMS query window into, say, a Word document to format it as a proper table, and it never seems to have much of a problem doing that.
December 21, 2010 at 3:02 am
MelS-512196 (12/17/2010)
CROSS APPLY -- try it you'll like it! 😀
... if you like Nested Loops! 😛
It's a great feature, but it often ends up with a Nested Loops implementation. It's one of the things that can kill a server if misused.
-- Gianluca Sartori
December 21, 2010 at 6:11 am
paul.knibbs (12/21/2010)
ben.mcintyre (12/20/2010)
(some innocuous queries hanging, not being able to copy the tabular result set in any sensible way ...)I'm curious--what would you class as a "sensible way"? I've often copied the tabular output of the SSMS query window into, say, a Word document to format it as a proper table, and it never seems to have much of a problem doing that.
OK, I'll have to partially retract that one. I meant, without column names, since with the default SSMS settings, you don't get column names.
However, in the interests of not looking like too much of an idiot, I just had a look through the 'Results To Grid' options under Tools > Options and there is an 'Include Column Headers when copying or saving the results' tick. Thanks for challenging that one, you just made my life a bit easier.
I have had problems with data type formatting as well, though - for example a BIT column comes over as a Boolean type (true/false). I seem to recall date issues as well. In essence, the results grid is a .NET grid, and it formats with .NET types, which may bear a slightly tenuous link to the native SQL data type you might much prefer.
It's been the niggling details which drive me wild. Options, MS folks, relevant, useful options. Oh so easy to provide, oh so hard to do without.
December 21, 2010 at 10:29 am
I haven't read all the posts but for me the two best and most used features are included columns and the ability to run a blocked processes trace in SQL Profiler.
And in 2008, debugging. Worst feature though has got to be intellisense!!!
December 21, 2010 at 11:32 pm
Gianluca Sartori (12/21/2010)
MelS-512196 (12/17/2010)
CROSS APPLY -- try it you'll like it! 😀... if you like Nested Loops! 😛
It's a great feature, but it often ends up with a Nested Loops implementation. It's one of the things that can kill a server if misused.
That's a bit of a sweeping statement Gianluca! :blink:
None of the physical join implementations are inherently superior to any other, and none will 'kill a server'. Used correctly, APPLY makes all sorts of cool things possible that cannot be achieved any other way. It is always possible to construct daft examples, of course.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 1:33 am
SQLkiwi (12/21/2010)
Gianluca Sartori (12/21/2010)
MelS-512196 (12/17/2010)
CROSS APPLY -- try it you'll like it! 😀... if you like Nested Loops! 😛
It's a great feature, but it often ends up with a Nested Loops implementation. It's one of the things that can kill a server if misused.
That's a bit of a sweeping statement Gianluca! :blink:
None of the physical join implementations are inherently superior to any other, and none will 'kill a server'. Used correctly, APPLY makes all sorts of cool things possible that cannot be achieved any other way. It is always possible to construct daft examples, of course.
Paul
Well, indeed my post was a bit abrupt...
I have to admit I am a bit biased: some weeks ago I had to rewrite a lot of queries that developers wrote with APPLY. After I explained them what it was and what could be achieved, they started to think that APPLY was the magic wand that could solve virtually any problem, as obviously it is not.
I'm not trying to teach you how it works, I'm sure you know better than me. However, when the optimizer is unable to convert it into a plain JOIN, APPLY is implemented with Nested Loops, that is generally evil on a big input. Moreover, when the above conversion fails, the optimizer also fails evaluating a better JOIN/filter order. Things that make the conversion fail are quite common (TOP and UNION, for instance), so you have to be careful.
When I code a statement, I also try different syntaxes to see which one performs better. It shouldn't make any difference, but it does.
-- Gianluca Sartori
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply