Transaction Strategies

A great reference series for use when selecting the appropriate transaction strategy for your next development: Mark Richard's Transaction Strategies.

Oracle <-> SQL Server Equivalents

A growing list of comparitive SQL functions for transition from Oracle to SQL Server:

Replacing Null Values
Oracle: NVL(field1, 'THIS IS NULL')
SQL Server: ISNULL(field1, 'THIS IS NULL')

Rownum
Oracle: rownum
SQL Server: row_number() over(ORDER BY field1)

Replace Characters in String
Oracle: REPLACE(field1, ';', ',')
SQL Server: REPLACE(field1, ';', ',')

Replace New Line Characters in String (CHR vs. CHAR)
Oracle: REPLACE(REPLACE(REPLACE(field1, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')
SQL Server: REPLACE(REPLACE(REPLACE(field1, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')

ToChar for DD/MM/YYYY HH24:MM:SS
Oracle: TO_CHAR(datefield1,'dd/mm/yyyy hh24:mm:ss')
SQL Server: convert(varchar,datefield1,103) + ' ' + convert(varchar,datefield1,108)

Outer Join
Oracle: table1.id =+ table2.id
SQL Server: table1.id =* table2.id

'IN' List
Oracle: IN list limited to 1000 expressions
SQL Server: 18,000 didn't hit any limits!

Decode
Oracle: Decode(field1,'Yes','Y','N')
SQL Server: CASE field1 WHEN 'Yes' THEN 'Y' ELSE 'N' END AS "Field 1"

Database Transaction Isolation

The isolation property is the most often relaxed ACID property because the DBMS must acquire locks on data which may result in a loss of concurrency. Relaxation of isolation can cause difficult-to-find bugs, but higher isolation levels increase the possibility of deadlocks.

The isolation levels defined by the ANSI/ ISO SQL standard are:

Serializable

• Specifies that all transactions occur in a completely isolated fashion
• May execute two or more transactions at the same time only if the illusion of serial execution can be maintained
• Requires that range locks are acquired when a query uses a ranged WHERE clause

Repeatable Read

• All data records retrieved by a SELECT statement cannot be changed, however, if the SELECT statement contains any ranged WHERE clauses, phantom reads may occur

Read Committed

• Data records retrieved by a query are not prevented from modification by some other transaction. Non-repeatable reads may occur, meaning data retrieved in a SELECT statement may be modified by some other transaction when it commits

Read Uncommited

• Dirty reads are allowed therefore one transaction may see uncommitted changes made by some other transaction

See: Isolation

db4o

Summary

• useful alternative to MySQL for small and embedded environments
• overcomes object-relational impedance mismatch problems
• object primary key is its OID (Object Identifier) which is auto-generated
• uses Query by Example (QBE) to select by comparison against a prototype object
• use of SODA (Simple Object Database Access) and native queries for efficiency
• copes with object refactoring e.g. adding/removing fields, primitive type conversion, class rename
• data is typically stored in files which can be encrypted using eXtended Tiny Encryption Algorithm (XTEA) or 3rd party hook-ins
• duplicate objects can be stored in the same table
• data is unaccessible from unsupported languages e.g. non Java or .NET code

Key Classes

• ObjectContainer - interface to db4o database
• ObjectSet - query resultset, list of items
• Db4o - factory class for database engine utilities
• ExtObjectContainer - volatile experimental methods

Key Methods

• Db4o.openFile(String fileName) - open the database
• objectContainer.set(Object obj) - INSERT the object into the database
• objectContainer.get(Object obj) - SELECT the matching objects from the database
• objectContainer.delete(Object obj) - DELETE the object from the database
• objectContainer.commit() - commit the transaction
• objectSet.next() - iterate through the result set
• objectContainer.ext().getID(Object obj) - get the OID for the object
• objectContainer.ext().getByID(long ID) - get the object by the ID
• Db4o.configure().objectClass("com.gth.org.uk.Person").rename("com.org.uk.Individual") - refactor class name
• Db4o.configure().activationDepth(int depth) - change the default activation depth to avoid infinite recursion

Alternative OODBMS

• InterSystems Cache
• Versant
• NeoDatis ODB

Source: The busy Java developer's guide to db4o

Column-Oriented Databases

Relational databases are being outperformed by databases that flip tables 90 degrees to store data vertically in table columns rather than in successive rows. By putting similar data together, column-oriented databases minimize the time to read the disk, which can add up when executing large-scale calculations such as those typically done in a data warehouse.

Relational database pioneer says technology is obsolete
Google BigTable