November 2008 - Posts

In case you are good at BI and looking.... 

A UK based consultancy, with a fantastic reputation in the Business Intelligence market, specialising in providing Microsoft (SQL Server) based Data Warehousing, Performance Point and Business Intelligence Solutions, is looking to employ Business Intelligence SQL Server Architect. The role involves high-level design and development of SQL Server Data Warehouses and Business Intelligence Solutions, from the initial requirements gathering stage to developing and implementing the solution. Working as part of a team, you will have a strong client-facing presence, and get involved in project proposals and give strategic advice to clients. Candidates must have exceptional SQL Server experience, with dimensional modelling and data warehouse design skills using Ralph Kimball methodology. You should also have experience in ETL design using MS Integration Services ( SSIS ) and T-SQL. Experience of integrating Business Intelligence applications i.e. Analysis Services ( SSAS ) and Reporting Services ( SSRS ) is essential and Performance Point would also be highly desirable. This position is perfect for a MVP or a professional with Microsoft Gold Partner Consulting experience is highly desirable, especially any direct contact with Microsoft. You will be part of a company that has strong values, offering its employees the very best support and opportunities including the possibility to work from home. If you are interested in finding out more about this role or the possibility of others, please contact Lance Hamilton-Griffiths at Real Resourcing on 020 7758 7366 or forward your CV to I look forward to hearing from you.


Luckily this is fixed in 2005 (and I presume 2008) so only applies to SQL 2000 but for the multitudes of people still with 2000 this may help.

Consider the SQL below... 

create proc test_prints




      declare @i int

      set @i = 1


      while @i <= 50000



            print 'This is some output from my stored procedure'


            set @i = @i + 1





This runs in no time at all under Query Analyser, but - now, schedule the stored procedure and execute it under SQLAgent.

It runs, and runs, and runs - mmm (exactly); now, look at the amount of CPU SQLAgent is taking - 100% of one of the available cores.

No, not nice.

When logging - use a table rather than PRINT statements; its a general good practice to do that anyway.




When you can buy a 1TB Serial Attached SCSI (SAS) drive for just £142.22 ex VAT you just have to get 2 of them.

Ok, so its just 7200rpm but do I care? Not a jot; its for my server here with just myself using it.

Outside of me, and to my clients - this now opens the door for freeing up your 15Krpm/10Krpm expensive SAS disks for what they really should be used for - the database itself; any backups - well, I used to recommend using SATA because at the end of the day the backup is sequential in nature so 15Krpm/10Krpm/7.2Krpm doesn't really come in to it.

So, get yourself on ebay - buy some caddies and get some of these 1TB disks and use them for your backups before shunting them off to tape or remote NAS.



Below is the agenda for next weeks Thursday user group meeting at Microsoft offices @ TVP (Reading). These are great opportunities for meeting other SQL professionals and expanding your knowledge of the product and how to use it.

6pm – 6:30 Registration and networking + Tea/Coffee with biscuits.

Meet and greet.

6:30pm – 7 Round Table discussion - ALL

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, may be a problem at work.

7pm – 7:40 SQL Server 2008 - Data & Backup Compression
Beatrice Nicolini
Premier Field Engineer | SQL Server |Microsoft U.K.
Read my Blog.:

With the 2008 release, SQL Server makes a major advance in scalability for data warehousing. With data and backup compression, SQL Server 2008 reduces the size of tables and indexes by storing fixed-length data types in variable length storage format and allows saving disk media space for your SQL backups. This session will guide you through the new data and backup features of SQL 2008, and will show which options are available for different data warehousing scenarios.

7:40pm – 7:55 BREAK: Sarnies

More time to network and ask questions...

7:55pm – 8.15pm Open Nuggest Session
The Audience - anyone

Bring your 5 minute hint/tip/demo and present it. This worked well at the London User Group.
Please let me know in advance via email to
It's a great way of getting your name know and developing your career! There will be prizes for each person who presents.

8:15pm – 8.45pm Table Expressions and the Optimiser and some SQL 2008 optimisations for Optional Parameters
Tony Rogerson, SQL Server MVP

We will look at the theory behind Table Expressions (Derived Tables, Views, Table Valued Functions and Common Table BLOCKED EXPRESSION, we will delve into some traps and suprise you at what the optimiser is doing under the covers. To finish off we will look at some SQL Server 2000 introduced optimiser improvements that certainly if you are using dynamic SQL because of optional parameters will mean you can move away from dynamic SQL back to simple straighforward SQL.

To register: 


I'm happy to announce that the next Scottish SQL Server User Group will be hosted at Microsoft Edinburgh on the evening of the 13th November. Full details of the agenda and how to register can be found on the UK SQL Server User Group site here: theme of the meeting is "Jammin' with SQL Server 2008", so I'm looking forward to some freestyle SQL Server 2008 demos and discussions !

[Copy and Pasted from Colins blog:]

This has driven me mad for the last 15 minutes until I profiled it to confirm I was actually importing data into the right server and database.

On the Import/Export wizard in Management Studio on the mappings I selected DELETE existing data.

My destination table is as follows :

CREATE TABLE [dbo].[tmp](
     [chart_code] [varchar](20) NOT NULL,
     [horizontal_axis_min] [decimal](28, 5) NULL,
               PRIMARY KEY CLUSTERED (
                    [chart_code] ASC


ALTER TABLE [dbo].[tmp] ADD DEFAULT ((0)) FOR [horizontal_axis_min]

Notice that my horizontal_axis_min column is NULLable and I've got a DEFAULT constraint on the column for 0.

My source data contains a mixture of NULL and 0's for horizontal_axis_min, guess what - the NULL's in the source data get erradicated and the DEFAULT constraint applied!

This is not the normal behaviour with the INSERT statement, the NULL is kept....

insert tmp( chart_code, horizontal_axis_min ) values( 'test', null )
select *
from tmp

Looking at profiler (see below) the statement doing the insertion is using the bulk insert operator....

insert bulk [dbo].[tmp]([chart_code] varchar(20) collate Latin1_General_CI_AS,[horizontal_axis_min] decimal(28,5))with(TABLOCK,CHECK_CONSTRAINTS) 

I've not an answer for the problem as yet, except disable or drop the default constraints before you insert the data; be interesting to see what happens if I specified create the destination table - alas, I've not time at present.

Watch the trap!