November 2009 - Posts

You are registered for tonights London UG, if you are not coming then please unregister now so I don't send you a bill for £20! The agenda tonight is cracking - so much going on, so much in fact we have to start early tonight - don't worry though, just get there when you can make it.

This session is not available remotely - sorry, you need to come.

Please check the full and modified agenda here:

In summary we've got.... The usual Round table - bring your problem discussion

First ever LiveLock discussion which will figure on "All tables should have a clustered index" - please do some research before tonight and bring some "for the statement" and "against the statment" - not sure how it will go, but hey - should be a bit of fun :)

Dave Ballantyne will do a SQL nugget for us

Martin Cairney will be doing a session on "An introduction to the Power of Policies"

BakBone will be doing a product demonstration on their tools

Siddharth Mehta will be doing a session on "Microsoft Business Intelligence Project Boosters"

Tony (assuming I can stop coughing by tonight) will be going through some of the Internals for the beginner

A great line up - we've still room so tell your friends, again if you aren't coming please tell me.

Remember we start the meeting at 5.30pm.

Location: Microsoft London, Cardinal Place, 100 Victoria Street, London, SW1E 5JL

Many thanks

Tony Rogerson
SQL Server MVP

Table Variables are not Transactional, that is to say BEGIN TRAN....ROLLBACK has no effect on them; we can use that to our advantage.

Have you ever been in the situation where you use a log file within your application to log progress, unfortunetly because your process is in a transaction you lose what has happened, well - not anymore - just use a table variable.... 

create table my_log_file (

    msg varchar(1024)


declare @t table ( msg varchar(1024) )

begin tran

--  some processing

--  ...

--  ...

--  logging as we go....

insert my_log_file ( msg ) values( '1' )
insert my_log_file ( msg ) values( '2' )
insert my_log_file ( msg ) values( '3' )
insert my_log_file ( msg ) values( '4' )
insert @t ( msg ) select msg from my_log_file


select * from my_log_file
select * from @t

This Thursday 26th is the London SQL User Group and also the Edinburgh UG; in London we have Tony Rogerson doing a session on Internals Basics aimed at people who aren't experts but will likely benefit as a reminder for those who are, we have a Nugget from David Ballantyne composed from some of his blog posts on ranking problems and udf problems, we also have Siddharth Mehta who will talk about Microsoft Business Intelligence Project Boosters. The Edinburgh group sees Martin Bell talk through some of the items in the Newsgroups as well as a ton of SQL nuggets.

We are also starting our LiveLock discussions, ever watched Argumental? Well, same principal - we have a specific topic, the first one will be "Every table must have a clustered index" and we have a group who argue for and group against, we summarise by pulling it back to a best practice advice to end the stalemate - should be fun!

If you don't want these email blasts then just reply to me with the word remove on the subject.

To register please visit
Make sure you reserve your place sooner because this session will be popular.Come and socialise and learn from your peers; these physical meetings are great places to expand your network, get answers and find out how other people are using SQL Server and what is going on. This is the last meeting this year for London and for those who can we should do drinks after the meeting to continue the SQL chat in an even more informal environment.If you want to twitter please make sure you use the tag #uksqlug so it is shown on the site.Agenda

17:30 - 18:00 Meet & Greet

Meet up and socialise with your friends, meet new people, find out what other people are doing with SQL Server.

18:00 - 18:15 Round Table discussion and Nuggets

Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have - perhaps a work problem, general guidance etc..

18:15 - 18:30 LiveLock: Every table should have a Clustered IndexTwo sides of the audience, one will argue FOR the statement and one will argue AGAINST.18:30 - 18:45 Dave Ballantyne SQL Nugget from his blog posts...
( with an offshoot to

18:45 - 19:15 SQL Server Internals for the Beginner
Tony Rogerson, SQL Server MVP
Tony will cover SQL Server memory, Database Structure (how they are composed) including why the transaction log is so important and how to fix the amount of space it uses if it grows too big, we will also cover some best practice for instance maintanence plans and finally we will cover Index internals and fragmentation and what causes it.

This really will be from beginners in, but don't be put off - I will try and go as deep as people want and time permits.
Its a good open session so if you want anything specific covering then let me know in advance.19:15 - 19:35 Break with Pizza

19:35 - 20:15 TBC

To be confirmed shortly
20:15 - 21:00 Microsoft Business Intelligence Project Boosters
Siddharth Mehta

BIDS and SSMS are not always sufficient for any MS BI Project. Right from the documentation that might be required just for compliance to tools that are able to fill up the gaps that SSMS or BIDS leave behind, a lot of accessories are required for a project for a leveraged development and delivery.
MS BI Project Booster is a kind of kit that consists of a collection of tool & utilities (freewares) and douments and/or documenting methods, that is good to have installed or available in advance, that can help any MS BI Team to tackle issues or cater requirements in a speedier manner at various stages of a project development life cycle. It's a resource kit, and can be used at various stages of the project and by the use of the same, I believe that a project can have the benefit of an Agile BI Development from an implementation perspective.
My Blog:
About me:

Many thanks

Tony Rogerson
SQL Server MVP


SQL Server 2008 SP1 Cumulative Update (CU5) link:

Details on how they have fully fixed the OPTION( RECOMPILE ) Parameter Embedding Optimisation :

What is Parameter Embedding Optimisation, well its basically you can do this...

WHERE email = coalesce( @email, email )
      AND name = coalesce( @name, name )

Basically, if @email is NULL then the optimisers optimises out that clause in the query thus giving better access to index choices and negating the need for dynamic SQL.

A very cool feature that came in with RTM, was disabled in CU4 because of the bug and fixed and renabled in CU5.


Andrew Fryer has done a number of blog posts on Virtualisation for DBA's for SQL Server - very good and informative.