GoGreen PC TuneUp™
Learn More

Insta-Install™
this is how we code sign our software and ssl encrypt our websites from our affiliate partner SSL.com

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: 116
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.