Generate GUID in SQL

[Ubaidullah]

Hi Bruce,

I am inserting a bunch of data into a SQL table using insert statements. What would be the best way to generate GUID values in SQL ?

A hack I am thinking of is to have a temp table with GUID values populated by a Clarion procedure and then update the inserted rows. But a SQL function to generate GUID along the lines of StringTheory would be good.

edit: I thought of using this for GUID fields inserted using SQL:
substring(cast(newid() as char(37)),1,16) 

Thoughts?

Regards,
Ubaidullah Nubar.

[Jane]

Here's an example of how I do it in SQL tables, Ubaidullah.  Table has a default value for the GUID that automatically uses a function to generate one when a new row is inserted.  That function relies on a view to make SQL random be non-deterministic.  (Google is my friend!)

Code: [Select]

-- jf 12/10/2018 - used by jfGuid function 
create view iFunction as 
select MyNewID = newid(), 
 MyDate = getdate(), 
 MyString = cast(newid() as varchar(36)), 
 MyChar  = char(floor(rand() *(122-97+1)+97)), 
 MyNum = cast((floor(rand() *(9) + 1)) as char(1)), 
 MySelector = floor(rand()*(2-1+1) + 1);


Code: [Select]

-- jf 12/10/2018 - make non-standard guid 
-- relies on iFunction view for non-deterministic random stuff 
-- EX version has parameters for length and case.  This one uses defaults 
 
create function jfGuid () 
returns varchar(100) 
as 
begin; 
declare @a int = 1,@len int = 16; 
declare @rv varchar(16)='',@selector int,@thisChar char(1); 
 while @a <= @len 
 begin; 
  set @a +=1; 
  select @selector = MySelector from ifunction 
  --print @selector; 
  if @selector = 1 
   begin; 
   select @thisChar = MyChar from iFunction 
   end; 
   else 
   begin; 
   select @thisChar = MyNum from iFunction 
   end; 
  set @rv += @thisChar; 
  --print 'rv = '+ @rv; 
 end; 
 
 select @rv = upper(@rv); 
 return @rv; 
end;


Code: [Select]

CREATE TABLE [dbo].[appLoginAudit](
[pk] [int] IDENTITY(1,1) NOT NULL,
[GUID] [char](16) NOT NULL,
[windowsName] [varchar](100) NULL,
[workstationName] [varchar](100) NULL,
[appName] [varchar](20) NULL,
[startupTime] [datetime] NULL,
[activeTimeBias] [int] NULL,
[ts] [float] NULL,
[sts] [float] NULL,
[dts] [float] NULL,
 CONSTRAINT [appLoginAuditGuid] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[appLoginAudit] ADD  DEFAULT ([dbo].[jfGuid]()) FOR [GUID]
GO

ALTER TABLE [dbo].[appLoginAudit] ADD  DEFAULT ([dbo].[jfUtcOffset]()) FOR [activeTimeBias]
GO

ALTER TABLE [dbo].[appLoginAudit] ADD  DEFAULT ((0)) FOR [ts]
GO

ALTER TABLE [dbo].[appLoginAudit] ADD  DEFAULT ((0)) FOR [sts]
GO

ALTER TABLE [dbo].[appLoginAudit] ADD  DEFAULT ((0)) FOR [dts]
GO


[edited to add remaining table defaults]

[bshields]

MySQL has a function called UUID() that will do this for you. Other SQLs probably have similar functions.

You can even add it to an insert trigger and it will prime itself for you. But from memory, Clarion PROP:ServerAutoInc can't handle this (or at least I haven't bothered to find out how to get it to work).

[Bruce]

>> MySQL has a function called UUID() that will do this for you. Other SQLs probably have similar functions.

true, but they make binary values, not alphanumeric values. This would be a bad thing.

What you want to do is populate the field with a random string - using (ideally) only upper case alphabetic chars, and numbers.
(ie, basically a 16 digit, base36 number.)

Also, while it can be set in a trigger, this is again not ideal (I'd go so far as to say "bad"). If it was in a trigger, it should ONLY populate the value if the field is BLANK. One of the goals of using GUIDs is to reduce the whole "server side assigning a value" problem, which makes parent-child forms so ugly.

Cheers
Bruce

[Bruce]

>> I am inserting a bunch of data into a SQL table using insert statements. What would be the best way to generate GUID values in SQL ?

the best way is not to generate them in SQL at all. Like all the other fields in the row, they should be generated by the client program.

Cheers
Bruce