Voice of the DBA

Tag: T-SQL

  • Basic Computed Columns

    I ran into an issue recently with a computed column, which I’ve rarely used, so I investigated them and wrote this short piece in computed columns, mostly as a reminder for me.

    What’s a computed column?

    You can read the BOL definition, but basically it is a column whose value is based on the values of other columns in a table. By having SQL Server perform the calculation, you prevent errors in the logic, or more likely, get around the chance that some application might forget to perform an update.

    Let’s say I have this simple orderdetail table.

    CREATE TABLE OrderDetail
( OrderID INT
, ProductID INT
, Qty INT
, Price NUMERIC(10, 2)
, LineTotal NUMERIC(10, 2)
)

    If I want to add a simple order to this table, I can do this:

    INSERT dbo.OrderDetail
        ( OrderID ,
          ProductID ,
          Qty ,
          Price ,
          LineTotal
        )
VALUES  ( 1 , -- OrderID - int
          23 , -- ProductID - int
          10 , -- Qty - int
          8.50 , -- Price - numeric
          85.00  -- LineTotal - numeric
        )

    Note that in this case I have to perform the arithmetic of having the line total equal to the price multiplied by the quantity. The math is:

    Linetotal = Qty * Price

    85.00 = 10 * 8.50

    However if I were to make a mistake in the arithmetic and insert $84 instead, or even $8.50, the SQL Server would not catch this.

    Instead, I could use a trigger to calculate this value, but triggers seem to have other overhead and force me to maintain them. Instead I could use a computed column, I can save space by not persisting these columns, or I can persist them and index them if needed.

    In my simple example, I could change my table to be this:

    DROP TABLE dbo.OrderDetail
go

CREATE TABLE OrderDetail
( OrderID INT
, ProductID INT
, Qty INT
, Price NUMERIC(10, 2)
, LineTotal AS (Qty * Price)
)
GO
INSERT dbo.OrderDetail
        ( OrderID ,
          ProductID ,
          Qty ,
          Price
        )
VALUES  ( 1 , -- OrderID - int
          23 , -- ProductID - int
          10 , -- Qty - int
          8.50 -- Price - numeric
        )
go

    The LineTotal value is not stored in the table, which isn’t a big issue in this case. In fact, it means that I am saving a few bytes per row, which could translate into quite a few rows for this table (meaning less pages, less I/O, more chance of this remaining in the buffer pool, etc). The tradeoff is the computation is performed for this result set for every query. Which is better? It depends Winking smile

    Notice that I don’t have a value for my computed column in the insert statement, but the results from this version are the same as the previous one:

    computecol

    In fact, if I try the first insert above, which has the LineTotal in it, I get this error:

    Msg 271, Level 16, State 1, Line 1

    The column "LineTotal" cannot be modified because it is either a computed column or is the result of a UNION operator.

    That’s a simple look at computed columns. Are they useful? I’ve never really worried about them, allowing either a stored procedure or the application to handle any logic like this. In general I dislike storing computations, even defined ones like this, but that’s me.

    Use them if they fit your environment.

  • Two Types of Tail Log Backups

    In a recent thread I noted that a tail log backup is essentially a regular log backup, but made with the intention of restoring the database because something is wrong with your data file. Gail Shaw (blog | @SQLIntheWild) pointed out that that’s not quite true. There are two parameters that you need to add to the BACKUP LOG command. Thanks to Gail for the correction, and here’s a little more data.

    There are three options you have with a tail log backup are:

    • WITH NORECOVERY
    • WITH CONTINUE_AFTER_ERROR
    • WITH NO_TRUNCATE

    I covered the third one in doing some practice backing up of the tail log. The second one is noted in Books Online as one that you should use in the event that the database is offline and inaccessible. That will allow you to recover the last log backup (hopefully).

    The first one is recommended as the one you use when the database is going to be restored and you want the end of the log.

    As I mentioned in my previous post, this is a core DBA skill. It is what will allow you to recover a database with zero data loss.

  • The IF Statement in a T-SQL Query

    I’ve seen quite a few posts from people asking how to do something like this:

    SELECT 
  a.ID
, IF a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
FROM MyTable a

    Of course, that doesn’t work in T-SQL, and you’ll get something like this:

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword ‘IF’.

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword ‘THEN’.

    There’s not IIF, no IF( x, then y, else z) construct. There is an IF … ELSE statement, but it’s use in code flows as a control statement such as

    DECLARE @i CHAR(1)
SELECT @i = mychar FROM MyTable

IF @i = 'A'
  SELECT 'Success'
ELSE 
  SELECT 'Fail'

    Instead we have a CASE statement, which is designed to give you multiple choices. In the example above, I’d write:

    SELECT 
  a.ID
, CASE WHEN a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
  END
FROM MyTable a

    I can even add multiple “WHEN” clauses if I want:

    SELECT 
  a.ID
, CASE 
    WHEN a.MyChar = 'A' THEN 'Success'
    WHEN a.MyChar = 'B' THEN 'Close'
    WHEN a.MyChar = 'C' THEN 'Far'
    ELSE 'Fail'
  END
FROM MyTable a

    Let your developers know that when they are looking for an inline IF type of logical statement, T-SQL gives them CASE instead.

  • The difference between REVOKE and DENY

    There was confusion recently with a poster that was moving permissions around and asked why I said they should revoke permissions and not deny them. I decided this was worth a post to explain.

    If I GRANT SELECT (or UPDATE/INSERT/DELETE) permissions to a user, then they can use those permissions to view data in a table. If I REVOKE the permissions, it’s the same as if the user never had them. They would need to be GRANTed permissions again to see the data.

    However, if I DENY them the ability to see data, then that’s different. They can’t see the data, but a subsequent GRANT will not allow them to see the data because the DENY will still be in effect.

    It’s a more permanent change, and should be used when you need to be sure that someone cannot see data, not when you are looking to remove permissions. To undo a GRANT, use REVOKE.