assign unique id to point features

If you use that id for something like as a foreign key in relation to another table your whole database will get in big trouble if you have to move a point for some reason. Probably you then will have to keep the id even if it not describes the xy-coordinates any more.

As a unique key is often the best to have something not telling anything about the data, because most data might change.

Adding on from Nicklas answer and my comment.

I would say the most used convention and most recommended is just to use a auto-incrementing ID, eg start at 1 and just keep going. No logic and simple.

If you have a distributed system, or don't like auto-incrementing numbers, you could use a GUID. Most databases will handle creating this kind of ID for you. However they are a pain for a user to enter manually, for searching etc, so just keep that in mind.

The other option is to use some kind of hash of the data but I would not recommend this. It would mean that would you need to write an algorithm to do this for you, you can't always ensure uniqueness, they also tend to be a pain to enter for searching.

These are just my opinions, but from personal experience, trust me, never use business data in IDs.

Follow up question: Is it best to create and maintain the unique id in the DBMS (SQL Server) or in the GIS software (ArcGIS)?

I'd strongly suggest checking for uniqueness inside the DBMS. That's one of the many strengths of DBMSs. It also allows you to access your data with different GIS software that probably wouldn't be aware of the unique constraints.