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
2 as 0x02…
10 as 0xA and so on?
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), CONSTRAINT pk_myTable PRIMARY KEY CLUSTERED(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
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.