Question: Consider that, I have the following values in a table.

| Name | Contact Number | Location |
| --- | --- | --- |
| ABC | 123 | L1 |
| XYZ | 456 | L2 |

Now, the following set of insert should work

```none
ABC, 123, L2
ABC, 456, L3
ABC, 789, L1
MNO, 567, L5

```

But, following set should not insert.

```none
ABC, 123, L1
XYZ, 456, L2

```

The concatenated string of columns of each row should be unique.

Can someone please help to build the SQL query?

Answer: 
Since you confirmed that you want the concatenation to be unique, a trivial unique constraint like this will not work:

```sql
alter table MyTable
add constraint MyUniqueConstraintName 
unique ([Name], [Contact Number], [Location]);

```

Because that would allow the following insert, contrary to your requirement:

```sql
insert T ([Name], [Contact Number], [Location])
values ('abc', 'de', 'f'),
       ('a', 'bc', 'def');

```

What we can do is add a computed column, and then force the computed column to be unique:

```sql
alter table T add 
   Concatenated as concat([Name], [Contact Number], [Location]),
   constraint ConcatenatedValueMustBeUnique unique (Concatenated);

```

However, one additional wrinkle is that you said that any of the columns can be `null`. This raises two awkward questions

1. Does `null` equal `null`, for the purposes of your requirement?
2. Does "" equal `null` for the purposes of your requirement?

Here's an example of each:

```sql
-- should this be allowed or disallowed?
insert T ([Name], [Contact Number], [Location])
values ('ab', null, 'cd'),
       ('ab', '', 'cd');

-- what about this? 
insert T ([Name], [Contact Number], [Location])
values ('ab', null, 'cd'),
       ('ab', null, 'cd');

```

In the strictest case (that is to say, the case of the most restrictive constraint), we would say "`null` equals `null`, and `null` also equals the empty string", which you could express as

```sql
alter table T add 
   Concatenated as concat
   (
      isnull([Name], ''),
      isnull([Contact Number], ''),
      isnull([Location], '')
   ),
   constraint UniqueConcat unique (Concatenated);

```

But that is actually equivalent to the original constraint I provided, because `concat` "eliminates" `null` (turns it into an empty string), so it's the same as `Concatenated as concat([Name], [Contact Number], [Location])`

If you require the condition "`null` equals `null` but `null` does not equal the empty string", you might replace the empty string in the `isnull` expressions above with something like `char(0)`.

But if you want to say "`null` does not equal `null`", things actually get rather tricky. So hopefully you don't want to say that!

If you want to do it as code rather than a constraint - I wouldn't recommend it, because what if someone writes data into the table using different code?
You can use the `EXCEPT` operator:

```
INSERT INTO dbo.MyTable (Name,[Contact Number],[Location])
SELECT Name,[Contact Number],[Location]
FROM (  VALUES  ('ABC','123','L1') 
        ) as xyz(Name,[Contact Number],[Location])
EXCEPT 
SELECT Name,[Contact Number],[Location]
FROM dbo.MyTable;

```