SQL database random of rand() uniqueness review
by Mountain Computers Inc., Publication Date: Tuesday, January 15, 2019
View Count: 1311, Keywords: sql 2017, random, rand(), uniqueness, t-sql, Hashtags: #sql2017 #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 an Andrew Jackson 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.
© 2024 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.
