巧用UUID生成随机数或随机字符串

--前3条数据

use Test
go

select top 3 R1 = RAND(),R2 = RAND()
From sys.objects


--随机生成区间

--Declare @RandMin int ,@RandMax int

--select @RandMin = -100 ,@RandMax = 100

--select top 100 RandValue = ABS(CHECKSUM(NewID()))%(1+@RandMax - @RandMin) + @RandMin
--From sys.objects

 

Declare @RandMin datetime ,@RandMax datetime
select @RandMin = '20100101' ,
  @RandMax = '20101231'
select top 100 RandValue = Dateadd(Hour,ABS(CHECKSUM(NewID()))%(1+DateDiff(Hour,@RandMax,@RandMin)),@RandMin)
From sys.objects

 

--select * from ::fn_helpcollations()  --查看数据库排序规则


go

Create View dbo.V_NewID
as
 select RE = CONVERT(char(36),NEWID())
go

Create Function dbo.FN_RandStr(
 @StrLen int
)returns nvarchar(max)
as
Begin
 IF @StrLen < 0
 return (null)
 Declare @re nvarchar(max),@len int
 Select @re = re,
  @len = 36
 From dbo.V_NewID
 While @len < @Strlen
 Begin
  Select @re = @re +re,
    @len = 36 + @len
  From dbo.V_NewID
 End
 Set @re = Left(@re,@StrLen);
 ;With
 SN as 
 (
  Select Top 11
    RowID = Row_Number() Over(Order by Object_id) - 1
  From Sys.objects
 ),
 CH as
 (
  Select 
   ch = Case RowID when 10 then '-' else Convert(char(1),RowID) end,
   Chv =(
    Select Chv = char(ABS(CheckSum(re))%26+97)
    From V_NewID
   )
  From SN
 )
 Select @re = Replace(@re,ch,chv)
 From CH
 return(@re)
 
End

go

Select Top 100 RandValue = dbo.FN_RandStr(50)
From sys.objects o1, sys.objects o2
go