myBlog myBlog Home

Back to Blog MTNCOMP


SQL database random of rand() uniqueness review

Published: Tuesday, January 15, 2019 written by Drew Jackson
View Count: 71
Keywords: sql 2017, random, rand(), uniqueness, t-sql



back in 2013, 2014, had to come up with some randomness testing, and this little tidbit had to be used to discover uniqueness, then re-explored in 2017 and 2018.
 
just a little database test on random random seeding for those of you who want to explore how many times does an insert take into a database using a random number generator with an increment seed does it yield uniqueness.
 
i have to accept the fact that those reading this have a sql 2014 / 2017 install and smss (management studio installed), and have dbo access / sa access to your local installation.
 
more to come...

create database test1 
use test1 
 
-- use master
-- drop database test1
 
create table v (
row_id integer identity(1,1),
text1 nvarchar(4000),
text2 nvarchar(4000),
float1 float,
float2 float,
currency1 money,
currency2 money,
integer1 integer,
integer2 integer,
datetime1 datetime default getdate(),
datetime2 datetime default getdate()
)
 
-- drop table v
 
create procedure proc_v as
--alter procedure proc_v as 
declare @loop integer
select @loop = 1
while @loop < 100000
begin
--insert into v (float1) select rand()
--select round((rand() * 1 / 3) * 100000,0)
insert into v (float1) select round((rand() * @loop / 3) * 1000000,0)
select @loop = @loop + 1
end

proc_v

-- delete  from v
select * from v
select float1, count(*) from v GROUP BY float1 ORDER BY 2 DESC
 
set rowcount 50
set rowcount 0

-----------------------
 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[v](
[row_id] [int] IDENTITY(1,1) NOT NULL,
[float1] [float] NULL,
[float2] [float] NULL,
[text1] [nvarchar](4000) NULL,
[text2] [nvarchar](4000) NULL
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[row_id] [int] IDENTITY(1,1) NOT NULL,
[text1] [varchar](25) NULL,
[text2] [varchar](255) NULL,
[memo1] [image] NULL,
[memo2] [image] NULL,
[dollar1] [money] NULL,
[dollar2] [money] NULL,
[float1] [float] NULL,
[float2] [float] NULL,
[integer1] [int] NULL,
[integer2] [int] NULL,
[datetime1] [datetime] NULL,
[datetime2] [datetime] NULL,
[datetime3] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p] as 
 create procedure p as  
declare @l as integer
select @l = 0
while @l < 10000
begin
insert into v (float1, float2) select rand(), rand()
select @l = @l + 1
end

select * from v

p



if you found this article helpful, consider contributing $10, 20 or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2019 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.