Create SQL Server trigger before insert/update
From the discussion in comments, I've restated your requirements:


  When X_ID = 2, then A,B,C,D should be unique. When X_ID &lt;> 2, then no
  uniqueness is necessary.


You can use a filtered index in SQL Server (ie, an index with a WHERE clause).

CREATE TABLE #MyTable (X_ID INT, Col_A VARCHAR(10),Col_B VARCHAR(10),Col_C VARCHAR(10),Col_D VARCHAR(10))

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) WHERE X_ID = 2;

INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
--non-unique values for x_id = 1
VALUES (1,'A','A','A','A'),
       (1,'A','A','A','A'),
       (1,'A','A','A','A'),
--unique values for x_id = 2
       (2,'A','A','A','A'),
       (2,'A','B','A','A'),
       (2,'A','B','C','A'),
       (2,'A','B','C','D')

--non-unique value for x_id = 2
INSERT INTO #MyTable (X_ID, Col_A,Col_B,Col_C,Col_D)
VALUES (2,'A','A','A','A')


The last insert will give you an error for violating uniqueness:
    Msg 2601, Level 14, State 1, Line 17
    Cannot insert duplicate key row in object 'dbo.#MyTable' with unique index 'MyUniqueIndex'. The duplicate key value is (A, A, A, A).

Depending on whether the columns are NULLable or not, and whether you want NULL to be included in the uniqueness check you may need your CREATE INDEX statement to include checks for NULL. In the above example, SQL Server will treat NULL as a discrete value (which is different than the way Oracle handles NULL). You may instead want to use something like this:

CREATE UNIQUE INDEX MyUniqueIndex ON #MyTable (Col_A,Col_B,Col_C,Col_D) 
    WHERE X_ID = 2
    AND Col_A IS NOT NULL
    AND Col_B IS NOT NULL
    AND Col_C IS NOT NULL
    AND Col_D IS NOT NULL;