Tag: T-SQL

  • Time Zones

    Visual Studio Lightswitch
    Designed to make developers’ lives easier. Shouldn’t we do that in T-SQL?

    I ran across this article on time zones, and was surprised that it was such a complex topic. It seemed to me that there should be an easy conversion function built into SQL Server that would do this:

    DECLARE @dt datetime
    SELECT @dt = GETDATE()
    
    SELECT CONVERT( DATETIMEOFFSET, @dt, 'CST')

    and I’d get my current time in Central Standard Time (CST).

    It’s 2012. We’ve learned that time zones are a part of applications. SQL Server is built for work with web applications, which usually span time zones. Developers can code around this, but don’t we want to give them tools to save time? Isn’t that the whole idea behind Lightswitch, ORMs, and most of the new languages in the world? To make developers more productive?

    CONVERT still has crazy numeric codes for conversion. Wouldn’t it make more sense to do this:

    DECLARE @dt datetime
    SELECT @dt = GETDATE()
    
    SELECT CONVERT( datetime, @dt, 'dd.mm.yy')

    than this?

    DECLARE @dt datetime
    SELECT @dt = GETDATE()
    
    SELECT CONVERT( datetime, @dt, 104)

    Time zone abbreviations are well known. Shouldn’t they be built into SQL Server?

    Sometimes I wonder if we are looking too much into things we can add to an application rather than the things that we can improve. Especially those things we use constantly.

    Steve Jones


    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Time Zones

    Why don’t we have a function like the one in this article to convert from time zone to time zone? Seems like something SQL Server should have added awhile ago.

  • Decryption and CASTing

    In my last encryption post I showed how to encrypt and decrypt data with a symmetric key. However there was a piece of the explanation I left out. If you look at that post, suppose that you ran this query after you’d encrypted the data:

    -- decrypt the data
    select 
      id
    ,firstname
    ,lastname
    ,title
    ,Salary = DecryptByKey(EnryptedSalary)
    ,EnryptedSalary
     from Employees
    go

    The results wouldn’t be what you’d expect:

    decrypt

    The binary data is returned, which isn’t rendered correctly. The salary column is the decryption, and the EncryptedSalary is the encrypted data. Note they are different.

    This stumped me for awhile when I was playing with encryption and I checked the dercryptbykey page thoroughly before I realized that the return type was varbinary and needed to be CAST.

    If I cast this back to nvarchar, I get the data:

    select 
      id
    , title
    , Salary = cast(DecryptByKey(EnryptedSalary) as nvarchar) 
    , EnryptedSalary
     from Employees

    decrypt2

    In my example, I CAST to nvarchar, and then to numeric, mostly for clean coding. This is numeric data. Can I cast directly?

    select 
      id
    , title
    , Salary = cast(DecryptByKey(EnryptedSalary) as numeric(10,4))
    , EnryptedSalary
     from Employees
    go

    No. I get an error.

    Msg 8115, Level 16, State 6, Line 1

    Arithmetic overflow error converting varbinary to data type numeric.

    This isn’t a valid CAST, so I need to double up the CASTs as shown in the original post.

  • Using a Symmetric Key

    In my Encryption Primer talk, I do demo on symmetric key use, and wanted to document it here. Encryption is a serious subject, and please do your research and education, as well as testing, before you implement it.

    This post will look at some simple encryption and decryption using symmetric keys. I showed how to create a symmetric key before, so I won’t talk about that here, but I’ll just show the code.

    Let’s set up a test table. In this case, I’m showing salary in a table, which isn’t something you want to do, but you might have this in an existing application: data you need to encrypt, but it’s stored unencrypted.

    -- create a table 
    create table Employees ( 
     id int identity(1,1)
     , firstname varchar(200)
     ,lastname varchar(200)
     ,title varchar(200)
     ,salary numeric(10, 4) );
    go
    insert Employees 
    values
      ('Steve','Jones','CEO', 5000)
     , ('Delaney','Jones','Manure Shoveler', 10)
     , ('Kendall','Jones','Window Washer', 5)
    ;
    go 

    Here I have three employees and salaries. Now I want to encrypt the salary. However I cannot just encrypt this value. Encryption creates a binary representation of the data, and that won’t fit in a varchar field. So I need to add a column.

    alter table Employees 
     add EnryptedSalary varbinary(max);
    go 

    Now I have a placeholder, so let’s create a key and then update the new binary column with the encrypted value.

    -- create a symmetric key
    create symmetric key MySalaryProtector
     WITH ALGORITHM=AES_256
     , IDENTITY_VALUE = 'Salary Protection Key'
     , Key_SOURCE = N'Keep this phrase a secr#t' 
     ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword'
    ;
    go 
    -- open the key 
    open symmetric key MySalaryProtector
     decryption by password='Us#aStrongP2ssword'
    ;
    
    -- encrypt the data 
    update Employees 
     set EnryptedSalary = ENCRYPTBYKEY(key_guid('MySalaryProtector'),cast(salary as nvarchar))
    ; 
    go 
    -- remove the old data 
    update employees
     set salary = 0
    ; 
    go 

    Note that I open the key, which is needed. I can close it at the end, or it will close when my session ends. I don’t close it here as I usually run this demo in the course of one session.

    The encryption takes place with the ENCRYPTBYKEY function, which requires the GUID of the key. Why the GUID and not the name I don’t know, but it seems like a PIA, halfway implementation. In any case, the KEY_GUID function is used as the first parameter.

    The number needs to be cast as a character, so I do that first, and then it’s the next parameter in the function. If I look at the data, it looks like this:

    select id , firstname , lastname , title , salary , EnryptedSalary
     from Employees; go 

    encryptsymm

    If you use the same identity_value and key_source, you should get the same encryption.

    To decrypt it, I do this:

    -- decrypt the data, with the casting  select id  , firstname  , lastname  , title  , Salary = cast(cast(DecryptByKey(EnryptedSalary) as nvarchar) as numeric(10,4))  , EnryptedSalary  from Employees go 

    The encrypted value has a header that tells it what key is used, so as long as it’s open, this works.