First, let's clarify a couple of things. When you say that you want to add a table from MS SQL Server Management Studio 2012, what you really want to do is add a table from MS SQL Server. The management studio is simply the user interface for interacting with the database itself.
Second, I am going on the assumption that this SQL Server database does not have ArcSDE loaded into it. If so, loading a table should be as simple as adding a standard table, and would not be a Query Layer.
ArcGIS is pretty picky when it comes to dealing with databases outside of the SDE environment. You have to make sure you've go the updated drivers for the MS SQL Server. You probably do as you are able to add the table. If you are able to get the data as a table inside ArcGIS, then you should be able to use it as part of a join or relate, regardless of what database it is stored in.
As you have surmised, a Unique ID or lack thereof, is the hangup. As you know, with any standalone table that is created through the ArcGIS environment, an Object ID is created. This is a unique ID that allows ArcGIS to identify any individual row. When you are bringing in a layer from a database outside the ArcGIS environment, you need to specify the field that will be used in place of the Object ID. Failure to specify a field like this means that you will be able to add a table, but won't be able to perform any options that require indexing of the rows. This includes any kind of Joins or Relates and other spatial operations.
In the image below, I am loading in a table. The first attribute listed is gid. As you can see, it shows that it is Not Nullable. That is a second criteria that is important, but not required at the outset. An error will be raised if NULL values are found, but not if the field allows NULL values. This is the field that I will choose as the Unique Identifier attribute.
Even though this layer is a spatial layer, the steps are the same for a non-spatial table as well.
Now, back to your problem, and a couple options for solutions. Your table doesn't have a Unique ID field. You need one. You have a couple of options.
- Add SQL Server's equivalent of an Autonumber field to your table. This method will create a Unique ID that will not change with respect to a particular row. Once an ID is assigned, it will persist, and never repeat or duplicate.
Create a View in SQL Server, based on your existing table, and add an Autonumber field there. This method will create a Unique ID similar to an ArcGIS Object ID. It should NOT be used as the basis for a join or relate, as the assignation of ID's to rows is solely based on the sort order changes. It is sufficient, however, for fulfilling the unique table ID requirement for a Query Layer. Here is some example code for that instance:
CREATE VIEW VIEW_NM
AS
SELECT Row_number() OVER(ORDER BY "EventName" DESC, "BoreholeID") AS tbl_id,
"EventName",
"Method",
"BoreholeID"
FROM yourtable
------ Edit -----
A concern was raised that using a Row_number() function would not yield an adequate unique ID, because it may return different features. In the case I provided, this is true, in that the Order By field is not unique. Therefore, you must be sure that you include a combination of fields in that function that will return a unique sort order, or values when converted to a number. These will ensure that a particular feature will return the same Object ID, regardless of what query is placed against it in ArcGIS. I am adding "BoreholdID" to the Order By section.
One issue that may crop up when working with a view, is the field type that ArcGIS will apply to this "tbl_id" field. It may see it as a field type it is not able to handle, therefore you may need to cast it to an Integer field type. This is how I did something similar with a view in PostgreSQL:
SELECT (int4(Row_number() OVER(ORDER BY "EventName" DESC)) AS tbl_id
The syntax for SQL Server might be slightly different. It might be int instead of int4.
- Another way to create this unique ID would be to use the Make Query Table tool. This tool allows you to select a table, view or create a custom SQL query using database tables, and display them in ArcGIS either as a Layer or a Stand-Alone Table. One of the parameters in the tool is how to create the
Key field. It again is done by selecting one or more fields found in the query layer, that will be used to dynamically create the ObjectID. This is much the same as the Row_number() function, as they are both creating a unique identifier based on data in the query result. One is simply doing it on the database side, the other is handling it within ArcGIS. One potential upside to this method is the easy ability to incorporate it as part of a python script. An example is shown at the bottom of the help document.
As a rule of thumb, you want to have a unique identifier attribute for every table, whether spatial or attribute, that you create. Most of the databases work more efficiently on tables where this attribute exists, and is designated as a primary key, or similar. It allows for indexes to be created, and in many cases, editing and selection functionality is restricted when a unique ID is not present.
Anyway, give this a shot and see if it works. As I mentioned, ArcGIS can be very particular when adding query layers. Some other issues you may into are with field types that translate differently from how they are defined in the database, to how ArcGIS interprets them.
This help document can help you ensure that you cast data types in your table to ones that will be recognized by ArcGIS. SQL Server data types supported in ArcGIS
