Erland Sommarskog

If you want to trully know how thing works I suggest great blog: http://sommarskog.se/error_handling/Part1.html http://sommarskog.se/error_handling/Part2.html http://sommarskog.se/error_handling/Part3.html More than 100 pages of technical knowledge. And presentation: http://www.sommarskog.se/present/WhenThingsGoWrong%20(SQLSat%20706).zi…

Another reason to write your own technical content, even if a similar question/answer does exist on SO, especially in examples where you experiment to put together examples that show/prove a point, is that it is practise for yourself. I intend to get some of my own content out there (it may never happen, the intention has existed for quite some time!) and if I ever find time to do it some of the things I might include are expansions of things I've given as SO answers, expanding on details that might have been too much in the SO/SE context and potentially caused confusion or sliding off into tangents that would be too far off-topic from the starting point. I might not ever point people at this writing, the main point of it will be my own practise (of technical matters, and just the act or writing and organising information). If it comes to be something that feels useful then it might be the start of an online portfolio that I selectively send people to, or feed back into SO/SE (i.e. giving an answer with bits extracted from something I've already written with a “for some related tangents you might find this fuller article useful” link, as I already do for other people's articles like the very useful tomes of MS SQL knowledge at https://www.sommarskog.se/ that I've referenced on DBA.SE before now).

For SQL Server there’s a very nice article on making query plans more predictable here: http://www.sommarskog.se/query-plan-mysteries.html

Yeah, parameter sniffing is also a concern and a thing you have to manage on the SQL Server side - it's led to probably the most venerable article on the topic in that community - http://www.sommarskog.se/query-plan-mysteries.html

I made an app that generated reports. The queries ran quickly when I tested it directly on the database but when I ran my app it would hang on a particular query. Sometimes. I started making some small seemingly insignificant changes to my code and suddenly my app generated all the reports quickly. All was well until the next month when I had to generate the same reports and my app failed on the same query. I solved it by reverting the small insignificant change I made the previous month. WTF. I looked into it and there's a wealth of information on the topic[0]. Apparently, how my code passed the query to the database differed to how I tested my query directly on the database which cause the database to compile the query differently and use different execution plans. My most recent fix was to add another index which the database suggested. Let's hope it works next month. [0] http://www.sommarskog.se/query-plan-mysteries.html

I disagree with this article. SQL is an decent (though crusty) language when used specifically for the purposes of interacting with relational databases. The fact that most database vendors have hacked mutually incompatible proprietary extensions on-top of it for doing other tasks doesn't make it a good choice for them. Why would you use SQL over a language that has been specifically designed for code reuse, integration and interacting with multiple systems (rather than just the database)? > Myth #1: Stored procedures can't be version controlled Of course they can, but unlike normal code which runs/builds directly from the file-system, a sproc tends to be written directly on the database server (since you won't get all those fancy refactoring/debugging tools unless you do so). Copying it to the fs before committing your changes is a step that people often forget, or one that requires yet more tools. > Myth #2: Managing the impact of changes in the database is hard > Myth #3: Database tools lack modern IDE features His examples of basic refactoring tools hardly compares to something like ReSharper. > Myth #5: Code in the database can’t be properly encapsulated and reused, you need an object-oriented language for that They can be encapsulated, but it's a pain. Even just getting data from one stored procedure to another can involve hackery like opening a connection to the database from inside the calling procedure. See this link: http://www.sommarskog.se/share_data.html -…