Storing hex equivalent in identity column in sql

Just as the title says, if I have a table has a struct like this

create table myTable
ID int identity(1,1) not null,
col1 varchar(20) not null,
col2 varchar(20) not null
constraint pk_myTable primary key (ID)

If I insert some value in this table it would automatically take value in col ID. I wanna know is a way I could get SQL Server to store hex equivalent in the column. Say, for 1, it should store 0x01 2 as 0x0210 as 0xA and so on?

  • Writing to SQL Server using MessageQueue from web application
  • SQL Server 2000: how to save an Image variable to a file in the file system from a stored procedure
  • Begin…End blocks in SQL Server: Is this code right?
  • How to detect a SQL Server database's read only status using tsql
  • Read XML from SQL Server using OleDbDataReader
  • Include monthly counts including months where data doesn't exist
  • I don’t wanna know any tricks or something, I know I can create this this col as varchar and then create a procedure for insert, or create a trigger for insert, that would do the required transformation and would produce the desired result, that’s not a big issue.

    But what I wanna know is there a inbuilt function/procedure/trigger that would help me achieve what I am trying to achieve? Not necessarily in SQL Server 2008, but may be in 2012, does there exists anything like this?

    One Solution collect form web for “Storing hex equivalent in identity column in sql”

    I agree with Joe Stefanelli and Habo above that you don’t need do do this. That said the technique is usefull in other scenarios.

    Define your table like this (note the computed column)

    CREATE TABLE mytable( 
    ID int identity(1,1) not null, 
    col1 varchar(20) not null, 
    col2 varchar(20) not null,
    ComputedHexColumn AS CONVERT(VARBINARY(8), ID),

    Then populate it

    INSERT INTO mytable(col1,col2) 
    SELECT 'col1 - 1','col2 - 1'
    UNION SELECT 'col1 - 2','col2 - 2'
    UNION SELECT 'col1 - 3','col2 - 3'
    UNION SELECT 'col1 - 4','col2 - 4'

    then anytime you select from the table it will include the hex value

    SELECT * FROM mytable

    image showing results complete with computed column

    In cases where the computed value was much more expensive to calculate then you can mark the column as PERSISTED. The database will then physically store the value and update it when appropriate.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.