Is_System_Named

Is_System_Named

Issue in  Database Deployment in Agile Process 

When you migrate the DDL from one server to another server in agile development then the major problem you will face because of constraints name. As all comparison tool have same problem; it will give the differences if you have only constraints name different even for same constraint. That will suggest you to delete and recreate the constraint with the source name you have. So suppose if a primary key is getting created without specifying the name then system will give one unique name to the primary key. So while comparing two database will show you differences because the name of the primary key is different even though we have same primary key (on same table , same column). Then the tool will suggest you to delete this primary key and recreate with source primary key name. As this primary key is referring many foreign key you have to first delete all the foreign key then delete the primary key and recreate the primary key and then back create all the foreign keys.

is_not_trusted in SQL Server

How to know your all rows are validated against check Constrains?

    Suppose you have a table with huge records and in column EmailID has CHECK constraints, how you can make you sure that all data are validated through the check constraints. There is chance where a developer has made the check constraint disable and loaded the data then he has made the constraint enable. So that your table has invalid data even though you have check constraint on the table.

 Here quick way to look it sys.check_constraints table where we have column called is_not_trusted if it is 0 then you are in safe and all data are validated against CHECK constraints. If 1 then your data have issue.

 Let’s run through below test case:

-- drop table dbo.MasterTable

CREATE TABLE dbo.MasterTable (

      id INT

      ,NAME VARCHAR(100) CHECK (

            NAME IN (

                  'vikas'

                  ,'amit'

                  )

            )

      )

GO

INSERT INTO MasterTable

VALUES (

      12

      ,'vikas'

      )

/* This will throw the error as data is conflict with check constraints*/

INSERT INTO MasterTable

VALUES (

      18

      ,'invalid test data'

      ) – This will through error GO 100

/* Disable the constraints */

ALTER TABLE dbo.MasterTable NOCHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

GO

/* Now load the invalid data */

INSERT INTO MasterTable

VALUES (

      18

      ,'invalid test data'

      ) – This will through error GO 100

/* Enable the constraints */

ALTER TABLE dbo.MasterTable CHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

If you look the table you have junk data that’s breaking the CHECK constraints.

Now if you see the Meta data.

/* Have the list of the check constraints*/

     

SELECT is_not_trusted

      ,*

FROM sys.check_constraints

You will find the values 1 for is_not_trusted column.

 While enabling if you want to check all the existing data against CHECK constraints then go with WITH CHECK

ALTER TABLE dbo.MasterTable

      WITH CHECK CHECK CONSTRAINT CK__MasterTabl__name__6B9AB725

n   Using below query we can validate all the constraints against data.

 DBCC CHECKCONSTRAINTS  ('dbo.MasterTable ' ) with ALL_CONSTRAINTS