Friday, November 18, 2011

T-SQL Tuesday #024: The Roundup

T-SQL TuesdayThanx to everyone for their contributions to T-SQL Tuesday #024: Prox ‘n’ Funx.

The roundup is finally here… Well, shux, I’ve been so busy that I almost let it fall through the crax, but before weex and weex went by, I finally read through the stax of submissions. I certainly had a lot of kix reading them… you could hear shriex of delight coming out of my office window.

(Okay, I admit it… The roundup was late because I spent a lot of time looking up words that end with a -ks sound… It loox like you can count me among the ranx of freax ‘n’ geex ‘n’ punx ‘n’ jerx ‘n’ dorx ‘n’ quax who carry something a little too far… It stinx, doesn’t it?).

Methinx it was a success! The SQL Community rox!

But don’t take it from me… Read the submissions for yourself. Click on the linx below for some good reading.

declare @Subject varchar(100)='T-SQL Tuesday #024: Prox ''n'' Funx';

insert [Brad Schulz].Blog
select Subject='T-SQL Tuesday #024 Roundup'
,Content
from
(
select Content from [Rob Farley].Blog where Subject=@Subject
union all
select Content from [Noel McKinney].Blog where Subject=@Subject
union all
select Content from [Greg Lucas].Blog where Subject=@Subject
union all
select Content from [Michael J. Swart].Blog where Subject=@Subject
union all
select Content from [Kent Chenery].Blog where Subject=@Subject
union all
select Content from [Steve Wales].Blog where Subject=@Subject
union all
select Content from [Merrill Aldrich].Blog where Subject=@Subject
union all
select Content from [Aaron Bertrand].Blog where Subject=@Subject
union all
select Content from [Pinal Dave].Blog where Subject=@Subject
union all
select Content from [Rich Brown].Blog where Subject=@Subject
union all
select Content from [Bob Pusateri].Blog where Subject=@Subject
union all
select Content from [Kerry Tyler].Blog where Subject=@Subject
union all
select Content from [Jes Schultz Borland].Blog where Subject=@Subject
union all
select Content from [Thomas Rushton].Blog where Subject=@Subject
union all
select Content from [Jason Brimhall].Blog where Subject=@Subject
union all
select Content from [Nancy Hidy Wilson].Blog where Subject=@Subject
union all
select Content from [David Howard].Blog where Subject=@Subject
union all
select Content from [Brad Schulz].Blog where Subject=@Subject
) Contributions
Rob Farley contributes a wonderfully detailed post comparing inline functions and non-inline functions, filled with lots of examples and query plans. Rob categorically states that “if it’s not inline, it’s rubbish.” I couldn’t agree more, but then again, I’m always a sucker for any blog post that uses the word “rubbish”.

Noel McKinney talks about a situation concerning the abuse and overuse of stored procedures. Imagine a database that is completely devoid of data and yet consumes truckloads of disk space… because it houses 27,000 stored procedures! A good story to keep in the back of your mind in designing solutions.

Greg Lucas gives us a detailed explanation of a useful ExceptionHandler procedure that is designed to be used in the CATCH block of a TRY…CATCH construct, logging detailed information about the error in a table. It encapsulates and standardizes error handling so that the developer can save time in coding.

Michael J. Swart talks about some interesting numbers related to stored procedures. At first I thought it would be the usual numbers like “maximum parameters allowed” and “maximum nested levels”, but if you know Michael, then you know he wouldn’t only spout out dry statistics like that. By the time you finish his list, you’ll be struggling to catch your breath from laughing.

Kent Chenery joins T-SQL Tuesday for the first time, contributing a CLR routine for calculating Easter in any given year. That’s pretty cool… I never even thought about the fact that an algorithm existed for calculating Easter. Make sure you read the Wikipedia article he references… the detail behind the algorithm is fascinating.

Steve Wales is another first-time contributor to T-SQL Tuesday. He talks about the differences in coding procedures and functions between SQL Server and that other database created by that company in my neck of the woods, Oracle. He also provides a link to an earlier post of his that warns about the fact that you have to be aware of the compatibility mode when using built-in functions.

Merrill Aldrich offers his ode to composable solutions, illustrating how table-valued functions (and views) offer maximum composability and flexibility (and that stored procedures don’t offer that same flexibility). As a rabid fan of TVF’s, I couldn’t agree more.

Aaron Bertrand wrote a terrific post on the shortcomings of INFORMATION_SCHEMA views earlier this month, but he waited for T-SQL Tuesday to post his pièce de résistance, plunging the knife deeper in talking about INFORMATION_SCHEMA.ROUTINES (and syscomments and sp_helptext) and how they just don’t cut it in getting the full procedure definition.

Pinal Dave talks about the new CUME_DIST() analytic function in SQL2012, which provides a really cool easy-to-implement percentile-calculating capability. Pinal’s post is the first of a series of blog posts about the new SQL2012 analytic functions… make sure you add them to your must-read list.

Rich Brown tells us about the benefits of using stored procedures for Reporting Services datasets, abstracting away the data layer from the presentation layer. He also brings up an interesting idea of putting Reporting Services procedures into their own schema.

Bob Pusateri gives an overview of the pros and cons of both procedures and functions. Speaking of procedures, though, I have to also mention that I found Bob’s post of October 18th to be very interesting in terms of using a cool trick of using sp_executesql to create a view in another database besides the current one.

Kerry Tyler discusses scenarios (and some myths) regarding transactions being left open by procedures that produce errors or experience timeouts, concluding with a discussion on XACT_ABORT.

Jes Schultz Borland expresses her frustration about the fact that a Reporting Services dataset only recognizes the first result set from a procedure that produces multiple result sets. It is confusing, being that the term dataset has different meanings across platforms (in .NET the dataset class truly is a collection of datatables).

Thomas Rushton makes a “Hell yes” case in answer to the question, “Should I learn to use stored procedures?” And he follows up with a second contribution talking about the best practice in releasing stored procedure scripts (or scripts for any kind of object for that matter).

Jason Brimhall talks about functions… specifically functions that he is thankful for, functions he can’t live without, and functions that he had fun with in delving into SQL Server internals. I’m going to set aside some time to read more about those.

Nancy Hidy Wilson reminds us of the myriad of information we can glean from the good old SERVERPROPERTY() function… and she reminds us to check out DATABASEPROPERTY() and DATABASEPROPERTYEX() as well.

David Howard discussed bad plans resulting from parameter sniffing and gave a list of techniques to solve the problem.

And, finally, my contribution talked about a procedure that I use to deal with a client system that contains no stored procedures at all… it finds the original text of ad-hoc queries in a Forced Parameterization database.

Thanx again for your contributions!

30 comments:

  1. Well done! Looks like I've got some fun reading ahead. I always wait for the round-ups before starting :-)

    Thanks for hosting and thanks for the round up Brad!

    ReplyDelete
  2. Amazing Content Writing also Design of Full Deep Details Best Crossbow guidesNext time Recommended Sharing Articles

    ReplyDelete
  3. Thank you for sharing the article. The data that you provided in the blog is informative and effective.

    Tableau Training in Hyderabad

    ReplyDelete
  4. I actually learned your website many blogs. I wonder how you came up with this tips and thoughts on your brain. Can’t wait to hear your reply as I’m thinking to discuss more with you, Can you write my website blogs? If your have time please mail or call me. I hope you recognize me. Really you have many guts that's why all readers always learn your posts. Thankyou so much my dear friend and keep it up :) cpm homework help integrated 3 - help writing an essay for college - math homework help online - assignment writing help

    ReplyDelete
  5. ISO 27001 Certification is an Information Security Management System (ISMS) standard published in October 2005 by ISO. ISO 27001 Certification Provides a systematic approach to minimizing the risk of unauthorized access or loss of information and ensuring the effective use of protective measures for securing the information. The standard has provided a framework for organizations to manage their compliance with legal and other requirements and improve performance in managing information securely. Call @ +91 9962590571

    ReplyDelete
  6. thanks for sharing nice blog if you like to know more about duck creek visit it https://duckcreektraining.com/

    ReplyDelete
  7. You writers write an eye-catching article on the internet. Do you want to draft an eye catching assignment? If yes then essay typer from SourceEssay are here to help you.

    ReplyDelete
  8. I must say you have written a great article. The way you have described everything is phenomenal. If you have time, please visit my site. Feel free to visit my website; 카지노

    ReplyDelete
  9. I found your blog while I was webs writing to find the above information. Your writing has helped me a lot. I'll write a nice post by quoting your post. Feel free to visit my website; 토토

    ReplyDelete
  10. This piece of writing is in fact a nice one it assists new web viewers, who are wishing for blogging. 토토사이트

    ReplyDelete
  11. Greate article. Keep writing such kind of information on your page.
    Im really impressed by your blog. 바카라사이트

    ReplyDelete
  12. Roofing Tips Each season comes with problems unique to the weather patterns common during that time. Hot, humid months bring excessive sun damage and potential hurricane damage, and cold wintery months bring snow storms and potential for leaks caused by melting ice and snow. Therefore, it is important that you perform a thorough inspection of your shingles and other roofing materials each and every season. Facts About Spanish Clay Roof Tiles, When You Need Replacement Roof Tiles, How to Repair Roof Flashing, Leaking Roof: What You Can Do When the Roof is Leaking, Bury a Drain Pipe from Downspout Gutter to Dry Well, an Option to Deal with Water Run-Off from the Roof, How to Lay a New Roof, How to Identify Problems with Your Roofing System, The Other Solar Power: Skylights and How to Winterize Your Roof

    ReplyDelete
  13. Really nice information thanks for sharing.... For more details, Visit Top cbse schools in Hyderabad

    ReplyDelete
  14. Nice content, please checkout my website iPourit.in
    Or
    Visit www.Pourit.in

    ReplyDelete
  15. Using conservative, state-of-the-art dental procedures, Dental Square strives to restore, enhance, and maintain the natural beauty of your smile for a beautiful, long-lasting smile. If you are looking for the Best Dentist in Roorkee, then Dental Square is the appropriate solution for you because we provide the Best Dental Services in Roorkee.
    Best Dentist in Roorkee
    Root Canal Treatment in Roorkee

    ReplyDelete
  16. افضل محامي في جدة لمختلف القضايا والاستشارات القضائية والشرعية والتركات وتحصيل الديون في جدة ، يسعى المكتب الى تقديم افضل الخدمات القانونية بالسعودية

    ReplyDelete
  17. افضل محامي في جدة لمختلف الاستشارات والقضايا الصعبة والمرافعات والتركات وتحصيل الديون بجدة ، يسعى المكتب الى تقديم افضل الخدمات والحلول القانونية بالسعودية

    ReplyDelete
  18. Marketing Assignment Help Uk provides you with the assignment services because marketing assignment is the process of developing, planning, strategies, analysis, and implementation of programs. Our assignment helpers are highly experienced in their own field. They are highly efficient in the field. So hurry up and visit our website.

    ReplyDelete
  19. very informative and impressive article. Thanks for sharing with us
    cyfuture cloud

    ReplyDelete
  20. I got the information in your blog post is very informative to me. Thank you for sharing this blog post.
    SQL Server Training in Hyderabad

    ReplyDelete
  21. تقدم شركة تنظيف بالدمام جميع الخدمات المتنوعة شركة اركان الشامل لخدمات النظافة في الدمام
    تنظيف منازل بالدمام

    ReplyDelete

  22. I absolutely love the content that you share with us, It's easy to understand your article and it makes my work easier. Thank you for sharing your insights with us.
    Here is sharing CyberArk information may be its helpful to you.
    CyberArk Training

    ReplyDelete