Sql server - will there be a problem if I try to insert data into a table via concurrent processes?

Deadlock is a different concept. In a simple for deadlock occurs if

- user A updates row 11 in table 1
- user B updates row 22 in table 2
- user B tries to update row 11 in table 1. Now it has to wait because the row is locked by user A
- user A tries to update row 22 in table 2. Now it has to wait because the row is locked by user B

The above is a deadlock. Both users are waiting for a resource from someone else and neither is willingly releasing it's own resources.

What comes to the question itself. If you insert into a table you should have no problems even if multiple user insert into the same table at the same time. This is a common scenario in databases.

However, some problems you might encounter
- operations slow down because of lock waits. This will happen even if the rows are not duplicates. For example index locks may cause waits
- transaction log handling becomes the bottleneck if not on a fast device
- memory handling slows operations if too big portion of the memory contains dirty pages without being able to flush them on the disk
- and so on...

Most of the potential problems are related to the server side resources. So ensuring that the resources (memory, CPU, disk speed...) are adequate should tackle most questions. If the operations are mainly updates, be careful with the amount of indexes, do not over index if not really needed.

Perhaps the biggest question comes down to the length of individual transactions. Transaction must enforce data integrity but keeping them as short as possible helps. This is something that should be taken into consideration when creating the client side. For example if a single transaction contains multiple inserts, do not execute them separately from the client side. Instead, create a stored procedure which takes an array as a parameter and insert all the rows during a single call.