Tag: T-SQL

  • 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.

  • HASHBYTES – A T-SQL Function

    Someone was asking if the HASHBYTES function was a good one to use in T-SQL as far as performance goes.. I wish I had a good reference for the function, but the best one I had on SQLServerCentral was this piece on using it to load a data warehouse. I also wrote an editorial on it not working with strings beyond 8k, which seems to be a bug, or a lack of resources devoted to ensuring string functions work with varchar(max).

    The HASHBYTES function returns a hash of an input string. A hash is essential a calculation based on the values of the input, and two inputs that are the same, ought to produce the same hash. One catch with this function is that you provide the algorithm used, which can be one of these:

    • MD2
    • MD4
    • MD5
    • SHA
    • SHA1

    Each of these produces different output, returning a varbinary(max) value. As an example, suppose I hash “Steve Jones”

    SELECT HASHBYTES('MD2', 'Steve Jones') 'MD2'
    UNION
    SELECT HASHBYTES('MD4', 'Steve Jones') 'MD4'
    UNION
    SELECT HASHBYTES('MD5', 'Steve Jones') 'MD5'
    UNION
    SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA'
    UNION
    SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA1'
    
    

    The results look like this:

    MD2

    ———————————————-

    0x27851A666BFCB4A35F971DD742CDA15F

    0x2E978DE4841B1F3651A8DF4B2D2CF5F5C624A76B

    0x75931813C7EAAEAB3CD1D8D621935903

    0x979AC597C05CA6DE3A88C31A456D1125

    As you can see, there’s a different hash for the same value using different algorithms. However if I were to compare the same string to itself, I can easily tell if something has changed. If the hashes aren’t the same, there’s a difference. I’m not sure this is a great use, but the more obvious use is that I can hash a password and then have the user enter their own version, hash it, and compare the results. In this way, the system never needs to know the value.

    Just make sure you use the same algorithm Winking smile