These are worth reading, kind of a read once – nothing too complicated. However they will be useful if you have the need for these

• [How to find out what procedure is doing a data modification]
  • Very simple, yet effective approach to determine what RPC call is altering your data. This is a read it once, you get the ah-ha why didn’t I think of that moment and you wont need to reference it again!
• [Temporary procedures : T-SQL]
  • We all know about temporary tables, but you can do that for stored procedures as well. Neat if you have a need for it
• [Conor vs. FOREIGN KEY join elimination]
  • Explains a bit of the history why a single column foreign key performs better than a multi-column FK. Its the old 80:20 rule at work, or probably more accurately 99.9:0.1 rule
• [SSRS -Report execution failed. Solution: SSPI=NTLM]
  • Seems downgrading to NTLM solves some integrated security issues if Kerberos is not playing nicely. Handy to know.
• [Take advantage of Table Variables NOT being transactional]
  • Ever needed to store state in a transaction that you didnt want to be rolled back? Think log tables! Read and learn
• [SQL SERVER – Fragmentation, Detection and how to resolve it]
  • Nice overview of fragmentation, and shows how to use DMV’s rather than the old school SQL 2000 DBCC approaches
• [CodePlex: SYS2 DMVs]
  • Hopefully this will serve as a good collection point – its very ‘young’ at the moment, but I would like it to live!
• [Locking improvements in SQL 2008 R2]
  • Handy to know about, unfortunately R2 has a price hike associated with it
• [Find the most expensive operations in Execution plans]
  • Handy way to take the XML plan and process it in a way to get a tabular list of the most expensive queries back. Very nice.

I suspect everyone who has dealt with scalar UDF functions in production environments are already VERY aware of the performance sucking capability they can have on your nice server. Here are some nice comments if you are not painfully aware:

• [Do Scalar UDFs give SQL Server a Bad Name?]
• [UDF Overhead – A simple example]

Gareth

Everyone should know now that there has been a massive amount of Microsoft activity. This article will cover the recent SQL activity, however the big one really is that the tipping point for 64 bit is in progress. This is a shame for me as I’m running 32bit OS, and I’m going to have to pave my machine – but that is the price developers are going to have to pay to play!

• [What's New in SQL Server 2008 R2 Editions]
  • Biggest first – Price increase! To say I’m disappointed is an understatement in this area. Specifically for developers  already have to support 2005 & 2008. Now they have to deal with R2, and persuade their customers to move to R2 if they want certain features.
  • MDS – Master Data Services – more on this one later. Pretty cool
  • Parallel Data Warehouse release
    • The DATAllegro purchase is now seeing the light as a Microsoft product!
    • Support of very large datasets (100Tb-1Pb). This is definitely something I would like to play with, but at $57,496 per processor…
  • Standard now has backup compression – but with the price tag…
• [SQL Server 2008 R2 November CTP – What’s New In Reporting Services?]
  • Report Builder 3.0 is available! Woohoo!
  • Enhanced sharepoint integration
  • Aggregates of aggregates
  • New Data Visualization Report Items
• [Using Filtered Statistics with Partitioned Tables]
  • Learn to appreciate filtered statistics!
• [MDS (Master Data Services) is x64 Only]
  • I totally get this, but this is the tipping point for me to repave my machines
  • With this requiring x64 and many new products requiring Windows 2008, Windows 7 or Vista it seems now is the time to move up.
• [MSSQL End of service dates for Service packs]
  • Support for SQL Server 2005 Service Pack 2 (SP2)  will end on January 12, 2010
  • Support for SQL Server 2008 RTM will end on April 13, 2010

More to come!

Gareth