GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...