Parameterization is a process where SQL Server slightly modifies a query in the parse phase: it replaces constant values by parameters. So if you submit — Query 1 SELECT COUNT(*) FROM   Sales.SalesOrderDetail WHERE  ProductID = 706; the parser will replace this by (and fool the optimizer into thinking you submitted): DECLARE @1 smallint = 706; SELECT COUNT(*) FROM   Sales.SalesOrderDetail WHERE  ProductID = @1; You can verify this very easily. If you go to the actual execution plan and hover over the left-most icon (the SELECT), a popup window will open that shows (a.o.) the query that was fed into the…

To me, it feels as if 2014 is a long time away. But it isn’t. Sinterklaas has already dropped off his presents and is probably already back in his castle in Spain. Christmas is almost upon us. And before we know it, we’ll be washing oliebollen down with Champagne. That also means that I need to get cracking on preparing my precon seminar on execution plans. What precon seminar you say? Oh right – I didn’t tell you yet. The good folks organizing SQL Saturday #269 in Exeter (UK) (on March 22) have decided to extend the event with a…

I had a great week last week. First at SQLRally Nordic, in Stockholm, where I presented a session on how improvements to the OVER clause can help you simplify queries in SQL Server 2012 enormously. And then I continued straight on into SQLRally Amsterdam, where I delivered a session on the performance implications of using user-defined functions in T-SQL. I understand that both events will make my slides and demo code downloadable from their website, but this may take a while. So those who do not want to wait can download the material from this blog post. Both SQLRally events…

This weekend, I will be presenting two sessions at Silicon Valley Code Camp, in Los Altos Hills, CA. On Saturday, I will have an early start – the first time slot of the day, at 9:45 AM, I will present on how T-SQL user-defined functions can easily wreck your performance – and how you can prevent that. On Sunday afternoon (1:15 PM), I will then present a session on the OVER clause, focusing on how both the SQLL Server 2005 version and the enhanced SQL Server 2012 syntax of this feature can help you solve common problems without having to…

It was an offer I could not resist. I was promised a free copy of one of the newest books from Red Gate Books, SQL Server Transaction Log Management (by Tony Davis and Gail Shaw), with the caveat that I should write a review after reading it. Mind you, not a commercial, “make sure we sell more copies” kind of review, but a review of my actual thoughts. Yes, I got explicit permission to be my usual brutally honest self. A total win/win for me! First, I get a free book – and free is always good, right? And second,…

It feels just like yesterday that I went to Munich for the very first German edition of SQL Saturday – and it was a great event. An agenda that was packed with three tracks of great sessions, and lots of fun with the organization, attendees, and other speakers. That was such a great time that I didn’t have to hesitate long before deciding that I wanted to repeat this event this year. Especially when I heard that it will be held in Rheinland, on July 13 – that is a distance I can travel by car! The only potential problem…

Enabling compression on your database can save you a lot of space – but when you have a lot of varchar(max) or nvarchar(max) data, you may find the savings to be limited. This is because only data stored on the data and index pages is compressed, and data for the (max) data types is generally stored on other, special-purpose pages – either text/image pages, or row overflow data pages. (See Understanding Pages and Extents in Books Online). This is from the SQL Server 2008R2 Books Online, but it is still valid in SQL Server 2012 – but apparently, this page…

As promised, here are the slide deck and demo code I used for my presentation at the Bleeding Edge 2012 conference in Laško, Slovenia. Okay, I promised to have them up by Tuesday or Wednesday at worst, and it is now Saturday – my apologies for the delay. Thanks again to all the attendees of my session. I hope you enjoyed it, and if you have any question then please don’t hesitate to get in touch with me. I had a great time in Slovenia, both during the event and in the after hours. Even if everything the tour guide…

As some of you might know, I have been to SQLRally Nordic 2012 in Copenhagen earlier this week. I was able to attend many interesting sessions, I had a great time catching up with old friends and meeting new people, and I was allowed to present a session myself. I understand that the PowerPoint slides and demo code I used in my session will be made available through the SQLRally website – but I don’t know how long it will take the probably very busy volunteers to do so. And I promised my attendees to make them available through my…

Scalar user-defined functions are bad for performance. I already showed that for T-SQL scalar user-defined functions without and with data access, and for most CLR scalar user-defined functions without data access, and in this blog post I will show that CLR scalar user-defined functions with data access fit into that picture. First attempt Sticking to my simplistic example of finding the triple of an integer value by reading it from a pre-populated lookup table and following the standard recommendations and templates that Microsoft publishes for CLR scalar table-valued functions, my first attempt for this function looked like this: [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read,    …