UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Unique Identifier Field, SQL Server 2008 R2    
 
   
youngb
post Feb 14 2018, 12:29 PM
Post#1



Posts: 531
Joined: 2-September 03
From: Galway, Ireland


Hi,

is it possible to have a text field that defaults to a unique identifier but can be updated/change later,
I know this is an unusual one but I have a good reason for needing it, I want to prevent duplicate updates to a log table for one update type but allow in some other updates types,
so the default unique identifier is there for the updates types that I don't need to check, and then I can update this field in the update that I want to prevent duplicate values of a combination of 5 fields,
why I am doing it this way is the log table of over 800,000 records is updated in many different parts of a large application and I am not sure where the duplicate updates are coming from as they are infrequent but enough to cause an inventory problem with the application, so my thinking is to use a unique identifier than combines the 5 fields to prevent the duplication, but what makes it a little more tricky is there are some updates where this will be acceptable so that is where I need the default.
Hope this is clear,
Thanks in advance for any help full suggestions.

Brian
Go to the top of the page
 
MadPiet
post Feb 14 2018, 10:11 PM
Post#2



Posts: 2,419
Joined: 27-February 09



Check out this article... maybe it will help. An IDENTITY column is an Integer/number and not text... that said, the article shows how to toggle IDENTITY_INSERT on and off.

https://docs.microsoft.com/en-us/SQL/t-SQL/...rt-transact-SQL
This post has been edited by MadPiet: Feb 14 2018, 10:55 PM
Go to the top of the page
 
youngb
post Feb 16 2018, 06:54 AM
Post#3



Posts: 531
Joined: 2-September 03
From: Galway, Ireland


Hi,

thanks for the reply, I have use Identity Insert before but
as you say identity column is always numeric so I can't use that,
but I have look at the field of type uniqueidentifier with a default of (NewID()) and then custom populate when I need to,
except I will have to be very careful as it has a very rigid format.
but I think that will work.
Thanks again for the suggestion

Brian
Go to the top of the page
 
PhilS
post Feb 16 2018, 08:32 AM
Post#4



Posts: 456
Joined: 26-May 15
From: The middle of Germany


You can use the NewId() function as a default for a varchar-type column as well, as you originally asked. This will use more storage space than an unique identifier, but it is easier to handle in Access/VBA if required.

If the whole requirement results from an (infrequent) bug in your application, I would rather suggest to hunt down and fix the bug instead of changing the data structure to accommodate the effects of the bug.


--------------------
New Access 2019 feature annouced: Modern Charts
Go to the top of the page
 
youngb
post Feb 20 2018, 07:18 AM
Post#5



Posts: 531
Joined: 2-September 03
From: Galway, Ireland


Hi,

Thanks for the feed back, I found an alternative approach by creating an alternative table of unique values,
to run a check before I update with low performance impact, but long term take your point it is a work around and the ideal thing is to find and fix the bug,
but it is not easy one as by this stage it has grown to a large application with about 32 users and the problem is very infrequent: 1 in every 5,000 records so although I will have to pin it down as some stage I need to put a preventive measure in place as I have not pinned down over the last two years, but I will eventually get it.

Regards
Brian
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st May 2018 - 12:23 AM