Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control.

However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table. 
USE AuditDB; 
GO 
 
CREATE TABLE dbo.ProcedureChanges 
( 
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    EventType    NVARCHAR(100), 
    EventDDL     NVARCHAR(MAX), 
    DatabaseName NVARCHAR(255), 
    SchemaName   NVARCHAR(255), 
    ObjectName   NVARCHAR(255), 
    HostName     NVARCHAR(255), 
    IPAddress    VARCHAR(32), 
    ProgramName  NVARCHAR(255), 
    LoginName    NVARCHAR(255) 
);  
Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB; 
GO 
 
INSERT AuditDB.dbo.ProcedureChanges 
( 
    EventType, 
    EventDDL, 
    DatabaseName, 
    SchemaName, 
    ObjectName 
) 
SELECT 
    N'Initial control', 
    OBJECT_DEFINITION([object_id]), 
    DB_NAME(), 
    OBJECT_SCHEMA_NAME([object_id]), 
    OBJECT_NAME([object_id]) 
FROM 
    sys.procedures; 
Create DDL trigger to capture changes
USE ProductDB; 
GO 
 
CREATE TRIGGER CaptureStoredProcedureChanges 
    ON DATABASE 
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,  
    ALTER_SCHEMA, RENAME 
AS 
BEGIN 
    SET NOCOUNT ON; 
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32); 
 
    SELECT @ip = client_net_address 
        FROM sys.dm_exec_connections 
        WHERE session_id = @@SPID; 
 
    INSERT AuditDB.dbo.ProcedureChanges 
    ( 
        EventType, 
        EventDDL, 
        SchemaName, 
        ObjectName, 
        DatabaseName, 
        HostName, 
        IPAddress, 
        ProgramName, 
        LoginName 
    ) 
    SELECT 
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),  
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), 
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),  
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'), 
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME(); 
END 
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.

I have seen that sometimes, a few developers create a multi-valued column to store more than one value in a comma-separated manner (like 1,3,4) and then, they read the individual values by splitting using comma.

However, due to such design, they can't add a foreign key constraint like below.

• ALTER TABLE <Table-name> 
• ADD CONSTRAINT <FK-Name> FOREIGN KEY (<col-name>) REFERENCES <Lookup Table-name>(<Lookup col-name>); 

PS - Personally, I am not a fan of such design and I would recommend having a mapping table in such cases; however, at times, mostly on the existing system, you don't have the choice to rewrite or change the design and hence finding a quick fix is the only option.
 
To illustrate the problem and solution, let's take an example of two tables - Employee and Country - as below.
    CREATE TABLE Country ( 
       Id INT NOT NULL PRIMARY KEY, 
       Name varchar(100) NOT NULL, 
       Code varchar(50) NULL 
    ); 
      
    CREATE TABLE Employee ( 
       Id INT NOT NULL PRIMARY KEY, 
       HomeCountryId INT NOT NULL, 
       VisitedCountryIds varchar(200) NULL, 
       Constraint FK_Employee_Country FOREIGN KEY (HomeCountryId) REFERENCES Country(Id) 
    ); 

Let's assume the country id as 1, 2 till 249 (As per the latest data available during the time of writing the post).
 
As you can see there is FK constraint on the HomeCountryId, hence only valid Country Id (from 1-249) can be entered; however, in the field VisitedCountryIds, there is no check and any id (like 250, 251, etc.) can also be added even if it doesn't exist in the country table. Well, this can lead to the data integrity issue.
 
So how we can make sure that users can only enter valid country ids (from 1-249) in the VisitedCountryIds column?
 
The fix is two-fold as following.
 
Create the function in the SQL Server as below.
    CREATE FUNCTION [dbo].[svf_CheckCountryIds](@CountryIds nvarchar(200)) 
    RETURNS bit AS 
    BEGIN 
    declare @valid bit 
    declare @rowsInserted INT 
    declare @addedCountryIds table([CountryId] nvarchar(200)) 
     
    insert into @addedCountryIds 
    select value from STRING_SPLIT(@CountryIds, ',') 
    set @rowsInserted = @@rowcount 
     
    if (@rowsInserted = (select count(a.CountryId) from @addedCountryIds a join [Country] b on a.CountryId = b.Id)) 
    begin 
    set @valid = 1 
    end 
    else 
    begin 
    set @valid = 0 
    end 
     
    RETURN @valid 
    END 

As you can see in the above function, we are passing the column data that is in the comma concatenated form and then they are split using STRING_SPLIT function and stored in the addedCountryIds table variable. Also, the inserted row count is stored in the rowsInserted variable.
 
Later, the values on addedCountryIds arejoined with Country table and if the count is matching, i.e., if all the passed country id is present in the Country table, true/1 is returned else false/0 is returned.
 
Create the FK with check constraint on the VisitedCountryIds as follows,
    ALTER TABLE Employee 
    ADD CONSTRAINT [FK_Employee_VisitedCountryIds] CHECK ([dbo].[svf_CheckCountryIds]([VisitedCountryIds]) = 1) 

As you can see constraint FK_Employee_VisitedCountryIds is created on VisitedCountryIds with condition that function svf_CheckCountryIds should return value as 1/true.
Now when you enter any country id other than 1 to 249, for example, if you enter VisitedCountryIds as '103,236,250', an error will be thrown as follows as id 250 is not the part of the country id list.
 
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "FK_Employee_VisitedCountryIds". The conflict occurred in database "TestDb", table "dbo.Employee", column 'VisitedCountryIds'.
The statement has been terminated.
 
However, if you enter VisitedCountryIds as '103,236,249', it will be successfully inserted because all the ids are part of the country list.
 
I hope you found this post useful in handling the foreign keys in multivalued columns. Looking forward to your comments.

Sometimes, we get data that needs to be unique but we can also get NULL records. Existing ways to achieve uniqueness don’t allow NULLs (Primary Key) or allow a maximum of one NULL (Unique Constraint).
 
Let’s take an example to understand this better. Suppose, you have an Employee table with fields like Id, FirstName, MiddleName, LastName, Email, etc. As per the requirement, the email needs to be unique (if supplied), however, users are also allowed to register without entering their email and hence in the database Email is a nullable field.
 
So, how would you achieve the Email uniqueness having more than one NULL?

    CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].Employee WHERE Email IS NOT NULL 

The above code will ensure that the Email doesn’t have duplicate data, however, it may store more than one NULL.

Here we will explain how to find the Indian financial year and financial quarter in a particular date with an example in SQL Server.  I have used the CASE statement and DATEPART() function to achieve this requirement.

DATEPART() in SQL Server
The DATEPART() function returns a specified part of a date, like – year, month, day, hour, minute, etc.

CASE Statement in SQL Server
CASE is the extension of IF ... ELSE statement. So, once a condition is true, it will stop reading & return the result. If no conditions are true, it returns the value in the ELSE block.
 
A) Find the FINANCIAL YEAR from date
 Write the below-given SQL code to find the financial year from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEWHENDATEPART(QUARTER,@FilterDate)= 1 THENYEAR(@FilterDate)ELSEYEAR(@FilterDate)+ 1 ENDAS[FINANCIAL_YEAR] 
    ** Note - @FilterDate - The date to be find the financial year

Output
Following is the result of the SQL query for financial year,

 
B) Find the FINANCIAL QUARTER from date
 
Write the below SQL code to find the financial Quarter from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEDATEPART(QUARTER, @FilterDate)WHEN 1 THEN'Q4'WHEN 2 THEN'Q1'WHEN 3 THEN'Q2'WHEN 4 THEN'Q3'ENDAS[FINANCIAL_QUARTER] 
    ** Note - @FilterDate - The date to be find the financial Quarter

Output
Following is the result of the SQL query for the financial quarter,