Part of the value in the ongoing evolution of the T-SQL language is that we are moving further and further towards being declarative rather than prescriptive ie: we are able to tell SQL Server what we want, rather than how to do it. Over time, that raises more and more possibilities for the optimizer to work with us to achieve a better outcome.
For example, note the following query against the AdventureWorksDW database:
2011-09-11
With SQL Server, there often seems to be an over-emphasis on the items that provide bullet points for the marketing brochure. Yet, small but useful changes to the product can make a more profound impact on DBAs/developers than the new items that are highlighted by those bullet points.
One of the things I loved about Reporting Services in SQL Server 2008 R2 is the amount of focus they provided on ease of use. In particular, the addition of data bars, lookup functions, pagination control, rendering targets in expressions, domain scope, etc. made a huge difference to anyone that really needs to build reports.
2011-09-11
We end up staying in Sydney several times per year, either for training or mentoring/consulting work. When we do, one of the hotels that we really like is the Westin. I would find it hard though, to list all the reasons why I like it. (There are some things I don’t like about it too but that’s a topic for another day).
But one of the things that has always surprised me, each and every time I eat breakfast there, is how sharp the knives are. Clearly, someone must be assigned to sharpen them, or they replace them very regularly (unlikely). Alternately, one of the staff members might have been passionate about it and it was an individual thing but we stayed about 160 nights in hotels last year and I can tell you that most hotels (big or small) don’t bother doing this.
2011-09-08
I really liked the addition of the WITH EXECUTE AS clause when defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:
CREATE PROC SomeSchema.SomeProc
WITH EXECUTE AS USER = ‘Fred’
AS
…
I’d also used the EXEC AS clause to temporarily change my execution context during testing. For example:
EXEC AS USER = ‘Fred’;
-- Try some code here while running as Fred
2011-08-13
One thing I love about T-SQL is that every day I seem to find another way to use it that wasn’t immediately obvious to me. I was reading the SQL Server Denali documentation and noticed that in the definition of the EXEC command that it said you could execute a stored procedure or scalar function using EXEC.
While I’d obviously used it to call a stored procedure, it had never dawned on me that you could use EXEC to call a scalar UDF, so I had to try it. Sure enough, it’s obviously worked for a long time. Here’s an example of a function and calling it as I would have in the past:
2011-08-13
I’ve been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:
But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.
2011-08-11
Ola has updated his index optimization scripts again. The update relates to logging to a table.
You’ll find the scripts here: http://ola.hallengren.com/
2011-07-19
When using the Usage Based Optimization wizard (or the Aggregation Design Wizard), on the screen that asks you to enter counts. There is a button that says “Count” but after using it, you might still be confronted by the message ““Estimated Count or Partition Count must be specified for cube objects that require counting”. It’s not overly helpful, given you have just asked it to count them itself.
If you see this, look through the list of dimensions and find any that have red squiggles under them. Then open each and find the attributes that also have squiggles and update those counts. Make sure that all squiggles disappear.
2011-07-06
I spent last week on campus in Redmond with the SQL Server Analysis Services Maestro program. It was great to have a chance to focus on SSAS for a week. As part of that, I did quite a bit of reading as I had quite a bit of travelling time. Ironically, I re-read a few books.
The first was Marco Russo, Alberto Ferrari and Chris Webb’s book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. I’ve often told BI classes that I’ve been teaching that this is a really good book and highly recommended. Re-reading it confirmed that. I thoroughly enjoyed reading it again.
2011-06-21
Another book that I re-read while travelling last week was Fast Track to MDX. I still think that it’s the best book that I’ve seen for introducing the core concepts of MDX. My colleague Mark Whitehorn, along with Mosha Pasumansky and Robert Zare do an amazing job of building MDX knowledge throughout the book.
I had dinner with Mark in London a few years back and I was pestering him to update this book. The biggest limitation of the book is that it was written for SQL Server 2000 Analysis Services, yet it still provides an excellent introduction to MDX. The knowledge from the three authors and Mark’s entertaining writing style make this still a “must-have” book for anyone learning MDX.
2011-06-21
