Is NFR set?

Hi Friends,

After a long break of about 8 months, I am back again!! Many things changed recently, and I was quite busy adopting to the changes – the most challenging was, of course, adopting myself to new parenthood.

However, I am back again, with some new topics – topics that I have learnt or experienced during the long absence. I was working on a case very recently – my customer was trying to setup Transactional Replication, and while applying the snapshot, he was receiving an error message:

 "Cannot update identity column '<column_name>'"

We verified that <column_name> was a column in a table, and that it was an identity column. My first reaction was – is the NOT FOR REPLICATION (NFR) option set for this identity column? Well, in course of troubleshooting, the problem turned out something very different, but that’s not the intention behind this blog.

The SQL Server Books Online has a very detailed explanation about what NFR is. Rishi, from my team, has written a very informative blog explaining how, not setting NFR, can lead to potential data convergence issues.

My intention behind writing this blog is to help my readers identify which identity columns, constraints or triggers do not have the NFR option set. Once identified, these identity columns, constraints or triggers must be evaluated to check if any of these should be marked for NFR.

Which Identity Columns do not have the NFR option set? To check, execute the following code:

 SELECT OBJECT_NAME(object_id) as [Table Name], 
    name [Identity Column Name],
    CASE ColumnProperty(object_id, name, 'IsIDNotForRepl') 
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE 'Unknown'
    END [Is NFR]
FROM sys.columns
WHERE is_identity = 1 
    AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
    AND ColumnProperty(object_id, name, 'IsIDNotForRepl') = 0
    AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

Which Check Constraints do not have the NFR option set? To check, execute the following code:

 SELECT OBJECT_NAME(object_id) [Check Constraint Name],
    OBJECT_NAME(parent_object_id) [Table Name],
    CASE OBJECTPROPERTY(object_id, 'CnstIsNotRepl')
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE 'Unknown'
    END [Is NFR]
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsCheckCnst') = 1
    AND OBJECTPROPERTY(object_id, 'CnstIsNotRepl') = 0
    AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

Which Foreign Key Constraints do not have the NFR option set? To check, execute the following code:

 SELECT OBJECT_NAME(object_id) [Foreign Key Constraint Name],
    OBJECT_NAME(parent_object_id) [Table Name],
    CASE OBJECTPROPERTY(object_id, 'CnstIsNotRepl')
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE 'Unknown'
    END [Is NFR]
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsForeignKey') = 1
    AND OBJECTPROPERTY(object_id, 'CnstIsNotRepl') = 0
    AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

Which User Triggers do not have the NFR option set? To check, execute the following code:

 SELECT OBJECT_NAME(object_id) [Trigger Name],
    OBJECT_NAME(parent_object_id) [Table Name],
    CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerNotForRepl')
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE 'Unknown'
    END [Is NFR]
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsTrigger') = 1
    AND OBJECTPROPERTY(object_id, 'ExecIsTriggerNotForRepl') = 0
    AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

That’s easy, isn’t it? Well, easy or not, these scripts will sure save us the trouble of having to manually inspect each of the objects to check if they have the NFR option set.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.