MS SQL allows clients to use many databases from a single connection. To do this, you can use the dbname.user.syntax, or execute an explicit USE statement.

Firebird does not allow you to use different databases in the same SQL statement, but it does allow you to perform transactions spanning multiple databases.

There are many generic tools to enter SQL commands to your database, in both platforms. Note that for Firebird, you do not need to use GO to delimit T-SQLbatches; rather, you manage transactions explicitly. You can also use the default of commit-every-statement on both servers.

Variable handling is similar on both platforms. Variables must be declared before being used, specifying their types. However, bear in mind that variables names in Firebird need not be prefixed with a @ character, and they do need to be declared before the procedure or trigger body.

For example, compare the following code snippets.

/* MS-SQL */
CREATE PROCEDURE my_procedure
AS
DECLARE @my_variable int
SET @my_variable = 5

/* Firebird */
CREATE PROCEDURE my_procedure
AS 
DECLARE VARIABLE my_variable int;
BEGIN 
  my_variable = 5; 
END 

Under both database servers, parameters are considered normal variables, set to an initial value.

The standard statements which can be found in all databases are SELECT, INSERT, UPDATE and DELETE. Firebird and MS SQL support them, but there are some non-standard MS SQL extension to consider if they are being used.

The SELECT statement in Firebird does not allow the INTO clause to create a new table on the fly. Instead, it is used to bind a result into a variable.

/* MS SQL syntax to get field values into a variable. */
SELECT @my_state = state
FROM authors
WHERE auth_name = 'John'

/* Firebird syntax. */
SELECT state INTO :state /* --> note the ":" before the name */
FROM authors
WHERE auth_name = 'John'

In MS SQL 7 and above, the SELECT clause can take a TOP specifier to limit the number of rows returned. This feature is currently under development for the Firebird engine.

Both MS SQL and Firebird support the normal INSERT syntax and the INSERT..SELECT syntax.

Both MS SQL and Firebird support the normal UPDATE. MS SQL also supports a form of UPDATE in which a join is performed, and one side of the join is updated. Think of this as a WHERE on steroids. If this feature is absolutely required, it can be implemented using views.

Both MS SQL and Firebird support the normal DELETE. MS SQL also supports the TRUNCATE TABLE statement, which is a more efficient (but dangerous) form of DELETE.

/* MS SQL syntax to delete all records in my_table. */
TRUNCATE TABLE my_table /* ...or... */
DELETE FROM my_table 

/* Firebird syntax. */
DELETE FROM my_table 

Transactions are rarely used directly in Firebird when using DSQL. Named transactions are not supported in this case. Both syntaxes accept the WORK keyword for compatibility.

This should not present a problem in most situations, as MS SQL's explicit transaction control is usually in place because there no support for using exception handlers.

In general, most discussions about isolation level problems found in MS SQL environments are void when taken to a Firebird database server. Contention between readers and writers is minimal and is resolved by the multigeneration architecture.

MS SQL uses cursors mostly to iterate over query results to perform activities. Other than syntax, there is little difference in what can be accomplished in either database. Although there are many options for iterating backwards and forwards, in practice the only cursor used is the forward-only cursor.

/* MS SQL syntax. */
DECLARE my_cursor CURSOR
FOR SELECT au_lname FROM authors ORDER BY au_lname
  DECLARE @au_lname varchar(40)
  OPEN my_cursor
  FETCH NEXT FROM my_cursor INTO @au_lname
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    /* Do something interesting with @au_lname. */
    FETCH NEXT FROM my_cursor 
  END 
CLOSE my_cursor
DEALLOCATE my_cursor 

/* Firebird syntax. */
DECLARE VARIABLE au_lname VARCHAR(40);
  ... 
  FOR SELECT au_lname FROM authors 
  ORDER BY au_lname INTO :au_lname DO 
  BEGIN
    /* Do something interesting with au_lname. */
  END 

Note that MS SQL can place cursors in variables and pass them around; this cannot be performed in Firebird.