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!

45 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. Excellent pieces. Keep posting such kind of information on your blog. I really impressed by your blog.
    Vee Eee Technologies

    ReplyDelete
  3. It was so nice article. I was really satisfied by seeing this article. SQL server dba Online Training Bangalore

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

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

    Tableau Training in Hyderabad

    ReplyDelete
  6. I am Alecia Maldonado used every single spell worker on the internet, spent untold amounts of money and discovered they are all fakes...i was the fool though; doing the same thing over and over again and expecting different results. In the end, I decided that I wanted a tarot reading to know what my future held for me; I contacted a woman who lives locally to me and she told me about a man named (Dr Mandaker); he just got he just got his website (holyaraminta.com), has another job for income, has no set prices, makes no false promises and refuses to help anyone that cannot be helped and even helps
    for free sometimes, he will give you proof before taking money. He is a wonderful man and he was the only person who actually gave me real results. I really hope he doesn't mind me advertising his contact on the internet but I'm sure any help/ extra work will benefit him.contact him as witchhealing@outlook.com He travel sometimes.i cant give out his number cos he told me he don’t want to be disturbed by many people across the world..he said his email is okay and he’ will replied to any emails asap,love marriage,finance, job promotion ,lottery Voodoo,poker voodoo,golf Voodoo,Law & Court case Spells,money voodoo,weigh loss voodoo,any sicknesses voodoo,Trouble in marriage,HIV AIDS,Barrenness(need a child),goodLuck voodoo,it's all he does Hope this helps everyone that is in a desperate situation as I once was; I know how it feels to hold onto something and never have a chance to move on because of the false promises and then to feel trapped in wanting something
    more!

    ReplyDelete
  7. 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
  8. 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
  9. thanks for sharing nice blog if you like to know more about duck creek visit it https://duckcreektraining.com/

    ReplyDelete
  10. 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
  11. Your style is very unique in comparison to other folks I have read stuff from. I appreciate you for posting when you have the opportunity, Guess I will just bookmark this page. Feel free to visit my website; 온라인카지노

    ReplyDelete
  12. 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
  13. 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
  14. Your article is really addictive. Keep posting. keep sharing the knowledge. I love to read your articles. Thank you for sharing this article with us. This article will make a good reference for me. Thanks a lot. It is appreciated.
    english short english stories

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

    ReplyDelete
  16. Great bloog here! Also your website loads up very fast! What web host are you using? Can I get your affiliate link to your host? I wih my website loaded up as quickly as yours
    경마
    온라인경마

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

    ReplyDelete
  18. Remarkable! Its truly remarkable piece of writing, I have got much clear
    idea about from this paragraph. 토토사이트

    ReplyDelete
  19. Nice to meet you! I found your blog on msn. You're so smart. I will add it to my favorites and read more of your posts whenever I have time. 안전토토사이트

    ReplyDelete
  20. Business Law Assignment Help management subjects require a lot of concentration and subject knowledge to do the assignment work neatly and accurately. This is why we have the subject experts and professionals who are highly experienced and know the business law subjects. You have the freedom of choosing the business assignment help provider on your own or based on your needs.

    ReplyDelete
  21. 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
  22. Keeping the full excitement to carry on your education as you can ensure your future as much as you can. That’s why I do not leave the passion of doing a continuous reading at all and meet many blogs. Among the bouquet of many blogs, your created post impresses me a lot. So, you do not have to lose your passion for making the creative solution. It would be better that you should go through Civil Law Assignment Help service to ease out your academic instructed work.

    ReplyDelete
  23. Hi! This is my first visit to your blog! We are a team of volunteers and new initiatives in the same niche. Blog gave us useful information to work. You have done an amazing job! 메이저토토사이트 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.

    ReplyDelete
  24. There will be best course in Hyderabad, duck creek policy program,duck creek can earn a salary 130000 per annum.it is useful to students career in the field you have interest visit my website link.thanks youhttps://duckcreektraining.com/

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

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

    ReplyDelete
  27. House of Avi designs and manufactures genuine leather hand-crafted Juttis. If you want to buy hand stitched jutti for women or have to buy leather jutti for women online, then you can definitely check out our online store. Our products go beyond style statements. You can choose from a wide range from traditional to punjabi or party wear to daily wear. Therefore, you can also buy punjabi jutti online at very reasonable and affordable pricing.

    buy t shirts for women with quotes
    get stylish jutti for women
    buy hand stitched jutti for women

    ReplyDelete
  28. 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
  29. افضل محامي في جدة لمختلف القضايا والاستشارات القضائية والشرعية والتركات وتحصيل الديون في جدة ، يسعى المكتب الى تقديم افضل الخدمات القانونية بالسعودية

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

    ReplyDelete
  31. 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