Community developer blogs

Simons SQL BLog

Recent Posts

• Cambridge usergroup meeting on Monday 6th - SQL 2008

  Posted: 30 Jun 2009 at 13:19 by simonsabin

  Don't forget if you are in the Cambridge area on Monday (6th July 2009) we are doing another usergroup meeting. This one is going to be on SQL Server 2008 features. We are holding this at the head quarters of Redgate software who are kindly sponsoring the event. To register go to I look forward to seeing you there. -

• Don't destory your certificate if you dabble with SQL 2008 Encryption

  Posted: 30 Jun 2009 at 13:11 by simonsabin

  Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty one. If you have enabled TDE on a database and encrypted some data then remove the TDE and backup, you may not be able to restore that backup if you don't have the certificate. I am guessing that this is to do with the encryption when you turn TDE on being a background task, I expect the same is true in reverse, i.e. pages stay

• How to stop that annoying beep on your server

  Posted: 30 Jun 2009 at 09:45 by simonsabin

  Many years ago I was working on a project that had some large batch processing in it. To do this processing we had some dedicated servers, one of which sat under the desk next to me. Apart from the noise of the fan soudning like we had actually bought a jet fighter the most annoying thing about this processing was that it resulted caused the servers beep to continually sound. Something in the code caused it to fire. Boy it was annoying.

• Hyperlinks in custom reports

  Posted: 29 Jun 2009 at 07:31 by simonsabin

  One feature introduced in SQL Server 2005 is reporting in management studio. You have a set of standard reports that allow you to see in a nice report format output from dmvs and the default system background trace. Whats neat is that you can write your own custom reports.

• Cambridge user group correction

  Posted: 23 Jun 2009 at 09:48 by simonsabin

  I incorrectly said in my last blog post that the usergroup was on the 6th June. That would be going some, whilst SQL is good it doesn't yet do time...(read more)

• Cambridge SQL Server Usegroup - SQL 2008

  Posted: 22 Jun 2009 at 23:46 by simonsabin

  I've just updated the agenda for the Cambridge usergroup meeting on the 6th July.[This is now the correct date SS 23/6/08] If you plan to attend make sure you register We are holding the meeting at Redgates offices ( which are very nice) The topic for the evening is all things SQL Server 2008. We will be trying live meeting again to view go to https://www.livemeeting.com/cc/usergroups/join?id=HWJRB6&role=attend&pw=R7R4N%217pq This will be open just before the meeting starts.

• Exporting XML data from SSIS - nugget

  Posted: 22 Jun 2009 at 23:10 by simonsabin

  James Rowland-Jones asked me this evening how to export XML data from SSIS. I've done this before but always used the Script component and I wondered if there was another way. I tried to think of a component that saves data to a file. Oddly there isn't a control flow task that does that. The nearest I came to was the "export column" transform, this is a hardly known transform.

• Do you live at WC1X 8TG

  Posted: 20 Jun 2009 at 20:48 by simonsabin

  If you came to the usergroup meeting and completed the survey today and said your postcode was WC1X 8TG can you please ocntact me so we can discuss what nugget/session you would be inetersted in doing, as you forgot to put your name on the survey. You can use the contact line on the right hand side of this blog. Cheers Simon -

• Usergroup meeting on Performance tuning and replaying profiler

  Posted: 18 Jun 2009 at 18:13 by simonsabin

  The live meeting on performance tuning and profiler has started. To attend go to https://www.livemeeting.com/cc/usergroups/join?id=JK8HND&role=attend&pw=Wbg%5CM4%3FfR Hope to see you there...(read more)

• Intellisense and objects created on the fly

  Posted: 18 Jun 2009 at 09:03 by simonsabin

  I had an email from one the people that attends the usergroup I help run in London. She was suprised that when she had a create table statement intellisense is able to understand that this table is being created and provide intellisense on that table. This is just like any other object in your script, i.e. if you declare a variable then intellisense can provide that variable in a list for you.

• When a query plan goes wrong

  Posted: 17 Jun 2009 at 14:22 by simonsabin

  I'm working on a whitepaper on when query plans go wrong, and I'm amazed at the number of scenarios that they can go wrong. A recent one I just came across was related to SSIS. If you use the table or view option for OLEDB sources you can get really bad plans. Piers did some digging and found that to get the meta data for the query it issues a set rowcount 1. He found this resulted in a plan being cached that when used without the set rowcount was awful for performance.

• When does 1+1 = 3

  Posted: 01 Jun 2009 at 15:31 by simonsabin

  There are sometimes when you are looking at somethin and the only possibly solution you can come up with is that there is a bug. Imagine you have a calculation that is showing 1+1= 3, everything about everything you've ever learnt says there it just can't be so. Thats what James (@jrowlandjones) faced the other day when loading in millions of rows of data. His data sets where for multually exclusive ranegs of index keys and yet he was getting a deadlock.

• Whats the most important thing when performance tuning?

  Posted: 31 May 2009 at 11:45 by simonsabin

  Paul Randal has a great survey this week. You have to decide on the ONE thing that is important when performance tuning. It would have been good to have a second question to ask what your job role is. I would expect a correlation between job role and the answer. I won't say what I decided, I was torn. I think indexing is essential as you can index out of many poor written queries but then again application code is also important.

• Myth : I don't need point in time recovery so I don't need the transaction log

  Posted: 27 May 2009 at 22:38 by simonsabin

  Wrong, wrong, wrong. This post was in response to a post in the forums "How do I delete the log file?" The transaction log for a SQL Server database is not a log like the ones many applications produce that is just history of the stuff you've done in the application, say for debugging. Whilst it is a record of the changes you make to your data it is a critical element in how data is kept consistent in your database database, i.e.

• When a decimal isn't a decimal - beware

  Posted: 26 May 2009 at 13:07 by simonsabin

  To say the type system in SQL is lax is an not quite correct, its actually lax, in consistent and very annoying. The most common feature I come across is that of integers and decimals. If I declare a variable as decimal(10,4) and assign it the value 10/4 you could expect a number of things to happen 1. The code doesn't run because you are casting an integer to a decimal 2. The code runs and stores 2.5 in the variable 3.

• SQL Server 2008 R2 - Register for the CTP

  Posted: 23 May 2009 at 08:31 by simonsabin

  If you like to be on the bleeding edge and want to try out the R2 release of SQL Server 2008 during the CTP cycle make sure you register here Spaces are limited so get in quick -

• UPDATE and DELETE TOP and ORDER BY : Part2

  Posted: 22 May 2009 at 17:01 by simonsabin

  I earlier posted about using derived tables with UPDATE and DELETE to be able to update or delete the TOP n rows (). In many large scale systems archiving is essential and so being able to efficiently archive data is essential, but more often than not your selectivity of what to archive is not based on the primary key. Whats nice is that, if you have an index that supports this, you can still use the same behaviour as demonstrated in the other article to efficiently

• DELETE TOP x rows avoiding a table scan

  Posted: 22 May 2009 at 13:00 by simonsabin

  The simplest way to delete the top X rows is use to use a derived table. delete t1 from (select top (10000) * from t1 order by a) t1 This is one of the tricks I teach in my Advanced TSQL for Performance and Scalabilty course The SQLCAT team posted a solution to deleteing the TOP x rows from a table using a view . The view isn't needed you can just use a derived table as above. You can do similar things with update, update t1 set b

• SQL Server 2008 May edition of books online is out

  Posted: 22 May 2009 at 08:44 by simonsabin

  As the title says the May 2009 edition of SQL 2008 books online is now available for download. -

• Trevor Dwyer has started blogging

  Posted: 21 May 2009 at 15:15 by simonsabin

  Trevor Dwyer an ex SQL Server MVP has started blogging on His mind is full of all sorts of amazing stuff so I'm looking forward to seeing what he is going to blog about. -