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: 552
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,547
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: 552
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: 516
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.

Go to the top of the page
 
youngb
post Feb 20 2018, 07:18 AM
Post#5



Posts: 552
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    17th November 2018 - 04:02 AM