Java vs C++ vs C#: What Is The Best Programming Language?

From Akihabara in Tokyo how to choose a programming language?

Reading the SQL Server Execution Plan

We're having a pretty good weather in London: still sunny and about 18C. I took the bicycle to Hampstead Heath with the VAIO in the rucksack and started looking into SQL performance tuning, as you do.

Went through SQL Server 2008 Query Performance Tuning Distilled in Safari.

Here are a few reading notes...

What are the different types of joins?

• nested loop join: the most intuitive one. This is the sort of join you would write if you were to code it in C++: iterate over the smallest table first and for each row, look for a match in the other table. Efficient only if the first input is small, and the second is large and indexed.

• hash join: used if the largest input is not indexed. This is done in two steps:

step 1: builds a hash table with the smallest of both inputs. This hash table uses a hash function to associate a value in the joined column with an index to a bucket. Go through the whole input row by row and add each row to its appropriate bucket using the hash function.
step 2: go through the second input row by row. For each value in the joined column, work out the index to the bucket in the hash table using the hash function. If a row is present, then there is a match and the row is kept in the result set.

• merge join: used if an index exists on the join columns of both tables. The join columns are sorted in both tables using the indexes. Then comparing columns is relatively fast because it takes advantage of the ordering.
  

What is a RID Lookup?

• If a table does not have a clustered index, data pages are on the heap.
• If a table has a clustered index, they are inside the clustered index.

Non clustered indexes contain pointers to table rows: this pointer is either

• a RID (Row ID) if the table is on the heap
• or a clustered index key if the table has a clustered index.

A RID lookup takes place on a heap table (table without clustered index). In order to locate data using a non clustered index SQL Server uses the RID to locate the data row in the heap. A RID lookup is costly because it involves an extra page read (on top of the page read needed for the non clustered index). You wouldn't get this extra page read with a clustered index.

How to see the execution plan directly from the SQL profiler?
This is very handy! No need to try and re-run a slow query in SSMS. Simply track the event ShowPlan XML in SQL Profiler under the Performance group. When you run the trace you can see the actual execution plan of any statement. The plan is displayed in a graphical way as in SSMS.
Warning, not to be used in production! It slows down the performance of the database quite a lot.

More about indexes:
Index Analysis
Index Design Recommendations 

Other resources:
Checklist for analysing slow-running queries

Using XML in SQL Server

Investigating the possibilities around storing and retrieving native XML data in SQL Server...

• XML Support in SQL Server 2005
  
• XML Best Practices for Microsoft SQL Server 2005
• XQuery Against the xml Data Type
• XQuery Basics 
• Performance Optimizations for the XML Data Type in SQL Server 2005

... and looking into table partitioning

• Partitioned Tables and Indexes in SQL Server 2005
  
• Planning Guidelines for Partitioned Tables and Indexes
• Designing Partitions to Manage Subsets of Data

T-SQL vs PL-SQL

"Oracle PL-SQL is the same as Microsoft T-SQL. If you know one, you know the other"

Not really.

Here are a few differences, some of them can have a serious impact of the way you write sproc code.

Good:

• T-SQL: stored procedures return result sets very easily: all you have to do is write a select statement without any 'INTO'. PL-SQL: you have to select into a ref cursor and define this ref cursor as output parameter.

Bad:

• T-SQL: stored procedures do not rollback automatically if something fails. Until SQL Server 2005 you had to test @@ERROR after each and every statement and goto a handler to rollback. Ugly, tedious and error-prone. In 2005 you can use a TRY CATCH, which is much more elegant. However you still have to rollback explicitly
  PL-SQL: sprocs are atomic. Any error inside a sproc rolls everything back up to the point where the sproc was called.
• T-SQL: no %TYPE! You can't refer to the type of a column without repeating it.
• T-SQL: RAISERROR does not break the flow. It simply returns an error string or message but the sproc still returns normally. Unless you use it within a TRY block, in which case the flow is diverted to the CATCH block (for SQL 2005 and beyond). Depending on the severity level you specify, RAISERROR within a TRY block either
• returns an error number without breaking the flow
• jumps to the CATCH block
• breaks the current database connection (wow!) (provided you have sysadmin role)
• PL-SQL: raise_application_error throws an exception, exits the current sproc, rolls back till implicit savepoint at the beginning of the sproc...

Other differences:

• Oracle does not have a BOOLEAN column type, although you can define a BOOLEAN variable in PL/SQL. SQL Server has a BIT column type where values can be only 0 or 1.