I'll check messages below this one, but a suggestion right here - since you don't want to be creating views and want to use a single query which you will adjust (if I understand it correctly), don't put that query onto SQL Server, keep it in the MAP file.
Here is a sequence I just followed, starting from scratch.
1. Open 9, create a new blank project.
2. Import a new drawing from a SHP file (drawing: states, table: states table). The table has an MFD_ID field with a BTREE index on it, which makes the data editable.
3. Normalize geometry of all objects in the drawing using the Transform pane (Normalize transform template). This is currently necessary prior to exporting data to SQL Server.
4. Connect to a SQL Server database.
5. Copy and paste the drawing table (states table) from MAP file to SQL Server. This creates a new table (dbo.states table) and a virtual drawing on the geom field on that table (dbo.states table.geom). The table uses MFD_ID field as a primary key - this is important for inserting new records, the values in that field cannot be NULL.
6. Create a new query in the MAP file (not on SQL Server database):
SELECT * FROM [SQL Server LocalDB]::[dbo.states Table.Geom]
WHERE [mfd_id] > 10; -- arbitrary filter criteria
Run the query, observe that it works and that the resulting table is editable.
7. Create a new drawing based on the query (right-click the query, select Create - New Drawing, verify the geom field, click OK). Open the drawing, observe that it shows data.
8. Alt-click one of the objects in the drawing based on the query. This shows object field values in the Record pane. Edit one of the values, click Update Record. The changes propagate to the SQL Server database, the record corresponding to the object is changed.
9. Ctrl-click one of the objects in the drawing based on the query. This selects it. Invoke Edit - Delete to delete the object, confirm deletion. The changes propagate to the SQL Server database, the record corresponding to the object is deleted.
10. Invoke View - Mode - Create Point to start inserting points. Click somewhere in the map window to add a point. The system opens the Record pane for the proposed new object. Fill in the value for the MFD_ID field - must not be NULL and must be unique, I used the value that would pass the filter in the query. Click Add Record. The changes propagate to the SQL Server database, there is a new record added. Unlike with changes and deletions, the map window does not show the new record yet, because it does not watch for additions (long story why, but there are reasons for it). Invoke View - Refresh to refresh data manually. Once you do this, the new record should show in the map window as well.
Hope this helps.