Oracle Scratchpad

January 24, 2013

Usage Stats

Filed under: SQL Server — Jonathan Lewis @ 7:00 pm GMT Jan 24,2013

The video of the online chat that I had with Grant Fritchey about statistics in SQL Server and Oracle is now online. It went pretty well – according to the stats 537 people attended, although the peak concurrency I noticed  was only 467 – of which there were still over 400 after an hour and a quarter.


January 22, 2013


Filed under: SQL Server — Jonathan Lewis @ 9:55 am GMT Jan 22,2013

Updated 22nd Jan
I’ve previously advertised the fact that the latest online discussion that Red Gate has arranged for me to have with Grant Fritchey will be on 23rd Jan and we will be talking about statistics. If you’ve listened in to any of these talks in the past you’ll realise that they are completely unscripted; what you get is a couple of guys in a (virtual) pub comparing and contrasting their favourite database engines and trying to learn a little bit about how the other technology works.


October 26, 2012

Running Total

Filed under: Oracle,SQL Server — Jonathan Lewis @ 7:02 pm BST Oct 26,2012

Here’s a question on OTN from a  SQL Server user that should prompt a few interesting ideas. Re-arranged and paraphrased it goes something like this:

In SQL Server I can write the following code:

SELECT @Counter = 0

UPDATE TempDB.dbo.TransactionDetail
SET @Counter = AccountRunningCount = @Counter + 1
FROM TempDB.dbo.TransactionDetail WITH (TABLOCKX)

What I want to do is more like this:

DECLARE @Total INT = 0
UPDATE StringOutput
set @Total = SumOfLength = @Total + ColLength

How do I do something similar in Oracle ?


October 8, 2012

Temporary Tables

Filed under: SQL Server — Jonathan Lewis @ 3:22 pm BST Oct 8,2012

After the success of the last Oracle/SQL Server discussion, James Murtagh of Red Gate has arranged another online discussion – this time about the different ways in which temporary tables are implemented and used. As before I’ll be doing the Oracle bit and Grant Fritchey will be doing the SQL Server bit.

Update: Now that the event is over, you can listen to the recording at this URL.

May 31, 2012

Clustered Indexes

Filed under: SQL Server — Jonathan Lewis @ 11:24 am BST May 31,2012

… which, for those in the know, means something to do with SQL Server. (The closest physical feature in Oracle  is the index-organized table, the closest sounding name for an Oracle feature is the index cluster – which, just to add to the confusion, must have a specific index that is called the cluster index).

Red Gate has arranged for an online debate between an Oracle specialist (me) and a SQL Server specialist (Grant Fritchey) to talk about the strengths and weaknesses of the two mechanisms, discuss why Oracle users seem to be biased towards one implementation and SQL Server users towards the other, and then see where the conversation takes us. This will be followed by a Q&A session.

If this sounds interesting – and I think it’s a great idea, you can learn an awful lot more from a discussion than you can by listening to monologue – you need to sign up early. The event will be held on 7th June at 16:00 BST.

Update: Now that the discussion is over, you can listen to the recording at this URL.

September 17, 2010

SQL Server 5

Filed under: SQL Server — Jonathan Lewis @ 6:24 pm BST Sep 17,2010

SimpleTalk have just published another of my SQL Server articles – which looks at the way that SQL Server stores data in “clustered indexes”, and the variation that appears depending on the pattern of data.

June 28, 2010

SQL Server 4

Filed under: SQL Server — Jonathan Lewis @ 6:06 pm BST Jun 28,2010

SimpleTalk have just published another of my SQL Server articles – which talks about storage methods and block dumping in SQL Server, and ends with the suggestion that the reason that SQL Server DBAs seem to be very keen on “clustered indexes” may be related to the fact that SQL Server doesn’t seem to handle free space management for heap tables very well.

In the same week: SimpleTalk interviews Tom Kyte in their “Geek of the Week” series.

June 18, 2010

I wish (2)

Filed under: Oracle,SQL Server,Wishlist — Jonathan Lewis @ 6:13 am BST Jun 18,2010

Continuing my little list of things in SQL Server that I’d like to see in Oracle:

June 1, 2010

I wish .. (1)

Filed under: Oracle,SQL Server,Wishlist — Jonathan Lewis @ 7:45 pm BST Jun 1,2010

I pointed out some time ago a few of the things in SQL Server that I would like to see in Oracle. Here’s a couple more:

May 28, 2010

SQL Server 3

Filed under: SQL Server — Jonathan Lewis @ 5:29 am BST May 28,2010

I haven’t had much spare time for writing this week – although I’ve got about 45 drafts of notes to publish – but Simpletalk have just published the “learning SQL Server” paper I sent them a couple of weeks ago, and it’s worth a link.

April 30, 2010

Back from MOW

Filed under: SQL Server — Jonathan Lewis @ 8:12 am BST Apr 30,2010

My trip back from Miracle Open World was extremely uneventful compared to the travail (and travel) of most of the foreign speakers who were there – 13 hours across five trains to get from Copenhagen to Amsterdam, and the worst delay was a train arriving 30 second late in Osnabruck.  Then the airports reopened before I had to switch to my “emergency exit” plan of taking a ferry from Hoek van Holland to Harwich and a train home.

The reason I mention the trip from MOW is just an introduction to SQL Server. Every year MOW starts with a surprise “infotainment” session on the evening before the main event is due to start, and this year the surprise for most people was a presentation about SQL Server; the surprise for me was that Mogens only told me 15 minutes before it was due to start that I was the one giving it.

March 4, 2010

SQL Server 2

Filed under: SQL Server — Jonathan Lewis @ 6:21 pm GMT Mar 4,2010

Following on from my posting about the presentation (about Enterprise databases) that I did at a Microsoft event on SQL Server 2008, I’ve just had an article on designing efficient SQL published on a website that’s strongly biased towards SQL Server developers – using an example of SQL written in the SQL Server dialect.

It’s worth a read. Don’t be put off by the change in dialect, the topic and technique are more significant than the language. (In fact it’s a write up of a presentation that I gave at the UKOUG Conference Series 2009 in Birmingham.)

February 4, 2010

SQL Server

Filed under: CBO,Execution plans,Hints,Infrastructure,Oracle,SQL Server,Statistics,Wishlist — Jonathan Lewis @ 7:07 pm GMT Feb 4,2010

A few days ago I did a presentation on SQL Server. This probably sounds a little strange given my status as an Oracle specialist – but the nice people at Microsoft asked me if I would contribute to one of their seminars so I downloaded and installed the 180 day free trial copy of the Enterprise version, then downloaded the “Books Online” manuals and started to play.

It was an interesting experience – and I think the audience (and organisers) found my presentation useful. The title was “What the Enterprise needs in an RDBMS” – and that’s something I do know about – and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.


Website Powered by