Post reply

How to add Computed column with URL (Fixed string + another column value)

  • January 17, 2020 at 8:47 am

    #3716198

    Hi,

     

    I want to add a new column to the table.

    Eg

    Table A

    ID

    1

    2

    3

     

    Desire Output

     

    Table A

    ID         URL

    1            http://www.google.com/sessionid = 1 (i.e 'www.google.com/sessionid'+ = ID)

    2          http://www.google.com/sessionid = 2 (i.e 'www.google.com/sessionid'+ = ID)

    3        http://www.google.com/sessionid = 3 (i.e 'www.google.com/sessionid'+ = ID)

    Is it possible to add Computed column like this?

     

    Thanks

     

     

     

     

  • January 17, 2020 at 9:58 am

    #3716228

    Hi Sangeeth,

    Yes, that's possible, but you do want to consider whether that URL needs to be stored/generated in SQL Server where it is consuming resources on the SQL server, or if it could be generated in the client application(s) based on the queried session ID data.

    To learn how to add a computed column, see https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2016 - the examples on that page add a computed column based on arithmetic on another column, but your computed column needs to have a CAST on the integer column before adding it to the URL string instead. The below is an example of the value you want within the "AS (...)" brackets in the computed column declaration, assuming your ID is an an integer data type.

    'Your URL prefix' + CAST(<your integer column name> AS VARCHAR(11))

    If you would also like these computed column values to be stored on disk instead of calculated on query (in general this would result in slower INSERTs/UPDATEs, but faster SELECTs), after the "AS (...)", add the term "PERSISTED".

    If you need further help with this, it could pay to try to add this computed column, and let us know where you're having trouble so we can provide more helpful advice.

    Andrew

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply