What is a schema?

You can think of schemas as logical collections of database objects. If your famililar with coding in other languages, a schema is almost like a namespace.

The default namespace in SQL Server is dbo which you've probably seen in the form <schema>.<tableName> for example dbo.OrderHeader.

A schema can contain:

• Tables
• Functions
• Views
• Stored procedures
• and other database objects

Organize your database objects

In it's most basic form a schema can help you group and organize your objects. When developing custom code for applications like Manhattan SCALE WMS, I use some of the following schemas to separate my code from the main application code in schema dbo:

• custom - for custom tables, stored procedures, views and functions.
• exitpoint - for the application exit point code (common event trigger in Manhattan SCALE WMS)
• report - for procedures called by the reporting system

This way of separating my code from the dbo schema, makes it really easy to find and filter just the custom code.

The list of objects will be less cluttered, and I rarely need to view or alter the standard code in the dbo-schema. 

Enhanced security

Database schemas can also help you set access restrictions. When external applications like SSRS-reporting or Crystal reports need to access data I only want to allow these applications read access. A reporting service should never have acces to write to the production database.

Giving your report application write access is a security risk and really bad practice 😱

How do I use the objects?

Lets say you have created a report schema with a stored procedure for fetching sales orders sold today:

report.sales_orders_today

To execute this procedure I would run the following statement:

EXEC report.sales_orders_today;

SET LOCK_TIMEOUT

The SET LOCK_TIMEOUT specifies how many milliseconds a statement should wait during a lock before returning an error.

Use the statement in your stored procedures right before the BEGIN TRANSACTION clause.

This example will wait for 5 seconds (5000 ms) before returning error in the event of a lock.

SET LOCK_TIMEOUT 5000
BEGIN TRAN
   -- Do your stuff
COMMIT TRAN

Deadlock priority 

The deadlock priority setting will let you control how important you code is in the event of a deadlock.

 The priority is low, medium and high.

When a deadlock occurs, SQL Server will rollback the cheapest transaction. 

The DEADLOCK PRIORITY is placed right before the BEGIN TRAN clause.

SET DEADLOCK PRIORITY LOW
BEGIN TRAN
   -- Do your stuff here
COMMIT TRAN

This snippet will be rollbacked if it's involved in a deadlock.

Transactions

A transaction is a block of SQL statements that usually modifies data. You can either commit or rollback a transaction. Transactions are useful when you want to make sure a chain of statements are completed together and if any of the statements fails, undo (rollback) all modifications performed.

For example; imagine when you want to change the order status on an order with data in two different tables

• Order Header
• Order Details

In this case you will only want to save (commit) your changes if all changes can be saved. If saving changes to one of the tables fails, you don't want to save anything and undo (rollback) everything.

This is where transactions comes to the rescue.

BEGIN TRANSACTION
   UPDATE OrderHeader
      SET Status = 'Completed'
   WHERE OrderId = 100

   UPDATE OrderDetails
      SET Status = 'Completed'
   WHERE OrderId = 100
COMMIT TRANSACTION

Stored Procedure to set Order status

Now lets embed the code to a separate stored procedure "spOrdersSetStatus". For the sake of best practice I will also introduce a TRY and CATCH block to handle any errors occured.

CREATE PROCEDURE spOrderSetStatus
  @OrderId INT,
  @Status NVARCHAR(25)
AS
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
         UPDATE OrderHeader
            SET Status = @Status
         WHERE OrderId = @OrderId

         UPDATE OrderDetails
            SET Status = @Status
         WHERE OrderId = @OrderId
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

The code will behave like this.

1. The code within the TRY block is run
2. BEGIN TRAN - A transaction is opened
3. If the code within the TRY block runs fine, COMMIT TRAN is done ( transaction closed) and the code ends.
4. If an error occurs within the TRY block, the compiler jumps to the CATCH block and continues.
5. ROLLBACK TRANSACTION is  run (transaction undo)
6. Code ends.

Reflections

The procedure above looks clean and solid and will work fine when called directly from an application.

However there are some major drawbacks if the procedure would be called from another stored procedure (nested stored procedure):

• No checking how many transactions are actually open. Closing more transactions than we've opened will result in error.
• No control if there is an active user transaction to commit, and in what state the transaction is in.

Nested Stored procedure

Now lets check what will happen if our stored procedure is called from another procedure.

Lets create another procedure to handle order completion:

CREATE PROCEDURE spOrderComplete
  @OrderId INT,
AS
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
         EXEC spOrderSetStatus 
            @OrderId = @OrderId,
            @Status = 'Complete';

        EXEC spOrderSendReport 
            @OrderId = @OrderId;

        EXEC spOrderClose 
            @OrderId = @OrderId;

      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

This code will 

1. BEGIN TRAN - Open transaction 1
   1. Run the spOrderSetStatus procedure
   2. Run the spOrderSendReport procedure
   3. Run the spOrderClose procedure
2. COMMIT TRAN - Close transaction 1

What will now happen if an error occurs in spOrderSetStatus?

1. BEGIN TRAN - Open transaction 1
   1. Run the spOrderSetStatus procedure
      1. BEGIN TRAN - Open transaction 2
         1. Code Runs
         2. Error Occurs
         3. All transactions are rolled back and we now have 0 open transactions.
2. Error spotted in the TRY block, code jumps to CATCH block.
3. ROLLBACK TRAN - Trying to rollback and close transactions but we have 0 open
4. ERROR throw due to no open transactions.

What we need is a way to keep count of open transactions before we close any transactions.

Local transactions

There is actually a kind of local transaction or savestate, that we could use. The syntax for opening a local transaction is SAVE TRANSACTION <yourTransactionName>. This could come really handy in our example.

We will also need a way to check if there already are any open transactions when deciding to use local or global transactions. The variable @@trancount is a perfect match and will give us the number of open transactions.

CREATE PROCEDURE spOrderSetStatus
  @OrderId INT,
  @Status NVARCHAR(25)
AS
BEGIN
    DECLARE @hasOpenTran BIT = 'False';

   BEGIN TRY
        IF @@trancount = 0
        BEGIN
            BEGIN TRANSACTION
            SET @hasOpenTran = 'True';
        END
        ELSE
            SAVE TRANSACTION spMyLocalTran

        UPDATE OrderHeader
            SET Status = @Status
        WHERE OrderId = @OrderId

        UPDATE OrderDetails
            SET Status = @Status
        WHERE OrderId = @OrderId

        IF @hasOpenTran = 'True'
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @hasOpenTran = 'True'
            ROLLBACK TRANSACTION
        ELSE
            IF XACT_STATE() <> -1
                ROLLBACK TRANSACTION spMyLocalTran
                
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

This procedure will now gracefully only close transactions opened locally by this stored procedure, and you will avoid any errors due to mismatch between opened and closed transactione.