InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:20:43 GMT20http://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]peterhe (12/17/2010)[/b]In programing, window functions like ROW_NUMBER() is a real help.[/quote]AMEN!Wed, 08 Jun 2011 10:38:38 GMThardwaremisterhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]dmoldovan (1/3/2011)[/b][hr]I've forgot about the "default trace"...[/quote]Definitely!Mon, 03 Jan 2011 13:07:17 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI've forgot about the "default trace"...Mon, 03 Jan 2011 12:29:19 GMTdmoldovanhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxService Broker, Extended Events, Event Notifications and the audit feature seem to be underappreciated. I do not know many people using them.Mon, 03 Jan 2011 11:46:16 GMTdmoldovanhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Nakul Vachhrajani (12/31/2010)[/b][hr]Hello, Andy!In the new year, I have spun off a new blog series based on the discussion that members of the community have been having in response to your editorial.The blog series starts off from Thursday, January 06, 2011, and the parent article link will be: [url=http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx]http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx[/url]As I publish the various child articles, I will be updating the parent article with the respective links.I hope that you and members of the community find the series a useful and favourable outcome of the editorial.[/quote]Ummm... why blog about what's already been written? Shoot... why blog at all? Why not publish your articles on this site? You already have the potential of 1.3 million readers and, I have to tell you, I typically don't look at blogs especially for articles that require so much personal opinion. ;-)Fri, 31 Dec 2010 17:30:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxHello, Andy!In the new year, I have spun off a new blog series based on the discussion that members of the community have been having in response to your editorial.The blog series starts off from Thursday, January 06, 2011, and the parent article link will be: [url=http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx]http://beyondrelational.com/blogs/nakul/archive/2011/01/06/underappreciated-features-of-microsoft-sql-server.aspx[/url]As I publish the various child articles, I will be updating the parent article with the respective links.I hope that you and members of the community find the series a useful and favourable outcome of the editorial.Fri, 31 Dec 2010 12:45:32 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]SQLkiwi (12/22/2010)[/b][hr]It seems like you are still quite emotional about it, yes :-P[/quote]You're probably right, Paul.Wed, 22 Dec 2010 05:56:42 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Gianluca Sartori (12/22/2010)[/b][hr]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.[/quote]It seems like you are still quite emotional about it, yes :-P[quote]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.[/quote]For sure, nested loops are usually a great choice where the inputs are small (though the tables may be large) and the inner side can seek on an index. This is not news, and neither is it particularly difficult to find examples of hash or merge join that are performance disasters.[quote]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.[/quote]More often than not, the rewritten syntax has subtle semantic differences from the original query, so you're actually asking a different question, although the results may be the same on a given data set. (Also, bear in mind that TOP is not a relational operator).That said, it is true that the optimizer does not explore every equivalent pattern - just the ones that are efficient to implement, of benefit to a broad range of queries, and frequently seen 'in the wild'.Wed, 22 Dec 2010 02:28:36 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]SQLkiwi (12/21/2010)[/b][hr][quote][b]Gianluca Sartori (12/21/2010)[/b][hr][quote][b]MelS-512196 (12/17/2010)[/b][hr]CROSS APPLY -- try it you'll like it! :-D[/quote]... if you like Nested Loops! :-PIt'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.[/quote]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[/quote]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.Wed, 22 Dec 2010 01:33:44 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Gianluca Sartori (12/21/2010)[/b][hr][quote][b]MelS-512196 (12/17/2010)[/b][hr]CROSS APPLY -- try it you'll like it! :-D[/quote]... if you like Nested Loops! :-PIt'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.[/quote]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.PaulTue, 21 Dec 2010 23:32:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI 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!!!Tue, 21 Dec 2010 10:29:45 GMTDale Turleyhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]paul.knibbs (12/21/2010)[/b][hr][quote][b]ben.mcintyre (12/20/2010)[/b][hr](some innocuous queries hanging, not being able to copy the tabular result set in any sensible way ...)[/quote]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.[/quote]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.Tue, 21 Dec 2010 06:11:33 GMTben.mcintyrehttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]MelS-512196 (12/17/2010)[/b][hr]CROSS APPLY -- try it you'll like it! :-D[/quote]... if you like Nested Loops! :-PIt'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.Tue, 21 Dec 2010 03:02:24 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]ben.mcintyre (12/20/2010)[/b][hr](some innocuous queries hanging, not being able to copy the tabular result set in any sensible way ...)[/quote]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.Tue, 21 Dec 2010 01:25:43 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Jedak (12/17/2010)[/b][hr][quote][b]ben.mcintyre (12/17/2010)[/b][hr]Now if only I could get SQL2005 to run with the old tools ....Ben[/quote]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.[/quote]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 cleanupSSMS: 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' :-(Mon, 20 Dec 2010 22:18:46 GMTben.mcintyrehttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]James Stover (12/20/2010)[/b][hr]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.[/quote]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.Mon, 20 Dec 2010 21:01:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxOne 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.Mon, 20 Dec 2010 15:49:58 GMTJames Stoverhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxCTEs. 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.Mon, 20 Dec 2010 09:55:48 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxFor 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...Mon, 20 Dec 2010 03:28:15 GMTadlanhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]pef (12/17/2010)[/b][hr]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.[/quote]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.Fri, 17 Dec 2010 19:26:05 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Craig Farrell (12/17/2010)[/b][hr]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. :-P[/quote]Nicely said, and it's certainly one of my favorite features ;)Fri, 17 Dec 2010 18:30:44 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI 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. :-PFri, 17 Dec 2010 18:21:59 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote]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?[/quote]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.Fri, 17 Dec 2010 18:15:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxA 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.Fri, 17 Dec 2010 18:14:01 GMTJon Russellhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxSQLCLRFri, 17 Dec 2010 17:37:29 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI 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.Fri, 17 Dec 2010 17:00:34 GMTpefhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]steven.malone (12/17/2010)[/b][hr]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.[/quote]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..CEWIIFri, 17 Dec 2010 13:34:25 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxThe 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.Fri, 17 Dec 2010 12:06:25 GMTrbartram-847800http://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxFor me, the most underappreciated feature is the integration with Visual Studio. I cannot imagine doing complex cubes or SSIS packages without it.Fri, 17 Dec 2010 11:54:28 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI 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 :-DFri, 17 Dec 2010 11:46:03 GMTjohn.campbell-1020429http://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxAnother 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!Fri, 17 Dec 2010 11:07:41 GMTaaron merteshttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxThanks Wayne. I missed it.Fri, 17 Dec 2010 10:31:25 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI would have to put my vote on (in this order)[li]Resource Governor[/li][li]Filtered Indexes[/li]Fri, 17 Dec 2010 10:18:46 GMTthirsthttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxI think there are a bunch of things that are under appreciated.Resource GovernorCTEService BrokerEvent NotificationsXML (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.Fri, 17 Dec 2010 09:31:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]Charles Kincaid (12/17/2010)[/b][hr]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.[/quote]FYI: In [url=http://msdn.microsoft.com/en-us/library/ms175972.aspx][u]BOL - WITH common_table_expression (Transact SQL)[/u][/url], in the first paragraph:[quote]This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.[/quote]Fri, 17 Dec 2010 09:27:07 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]sognibene (12/17/2010)[/b][hr]Passing a table valued parameter to a table valued function. [b]OH YEAH!!![/b][/quote]I agree! We had this in Oracle PL/SQL 15 years ago!Fri, 17 Dec 2010 09:17:50 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxSome 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.Fri, 17 Dec 2010 09:04:19 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspx[quote][b]ben.mcintyre (12/17/2010)[/b][hr]Now if only I could get SQL2005 to run with the old tools ....Ben[/quote]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.Fri, 17 Dec 2010 09:02:01 GMTJedakhttp://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxCROSS APPLY -- try it you'll like it! :-DFri, 17 Dec 2010 08:52:29 GMTMelS-512196http://www.sqlservercentral.com/Forums/Topic1036317-263-1.aspxBeing able to use SQLCMD mode in the SSMS I think is a great feature. I tend to write a lot of stand alone scripts, so being able to use sqlcmd variables to span batches and as table names in a From clause is very useful. It has allowed me to write much more reusable code, and a lot less dynamic sql.Fri, 17 Dec 2010 08:28:14 GMTziller