Problems with Floats as keys.

originally posted 2015-01-20

1	originally posted 2015-01-20

Working with a product that uses floats as database keys :^o
This has two solutions to generate float keys.

--ONE WAY
SELECT [OneWay] = convert(float(53),getdate()) + ABS(CHECKSUM(NewId())) % 9999999 + ((ABS(CHECKSUM(NewId())) % 999999) * 0.10) + ((ABS(CHECKSUM(NewId())) % 999999) * 0.010)

declare @wholePart int
select @wholePart = case when [Seed] is not null then [Seed] else 1 end from (select [Seed] = convert(int,max([ctrAttachmentId])+1) from [tblAttachments]) as x
select [WholePart] = @wholePart

--ANOTHER WAY
create function GetId(@wholePart int, @seed uniqueidentifier)
returns float
as
begin
  return convert(float(52),convert(nvarchar(20),@wholePart) + '.' + substring(convert(varchar(50),ABS(checksum(@seed))),1,9));
end

select [AnotherWay] = [dbo].[GetId](@wholePart, newid()), * from [tblAttachments]

--ONE WAY

SELECT [OneWay] = convert(float(53),getdate()) + ABS(CHECKSUM(NewId())) % 9999999 + ((ABS(CHECKSUM(NewId())) % 999999) * 0.10) + ((ABS(CHECKSUM(NewId())) % 999999) * 0.010)

declare @wholePart int

select @wholePart = case when [Seed] is not null then [Seed] else 1 end from (select [Seed] = convert(int,max([ctrAttachmentId])+1) from [tblAttachments]) as x

select [WholePart] = @wholePart

--ANOTHER WAY

create function GetId(@wholePart int, @seed uniqueidentifier)

returns float

begin

return convert(float(52),convert(nvarchar(20),@wholePart) + '.' + substring(convert(varchar(50),ABS(checksum(@seed))),1,9));

end

select [AnotherWay] = [dbo].[GetId](@wholePart, newid()), * from [tblAttachments]