The code I'm working on right now needs to insert a bunch of related records to import external data.
There's an extensive piece of code that uses CustCustomerService to insert data followed by a smaller piece of code that uses table records to insert and update the rest. I've inquired with my colleague as to why the two different approaches and the answer was that when he started he heard going through Services was better but the service didn't support all the changes he needed, hence the switch.
Researching this myself I see only information describing how to use a service and how different services differ. But no information comparing them to in language database access.
So. Why exactly would I use a service over just inserting and updating records directly?
EDIT: I have to clarify. With database access I mean something like:
smaServiceObjectTable sob;
sob.clear();
sob.Foo = Bar;
// ...
sob.insert();
The two main technical reasons (I can think of) are:
AX maintains the RecId number sequence, so you shouldn't ever perform inserts, and more importantly using the services for inserts/updates allows business logic to run by both you and others.
So imagine the user decides to buy a 3rd party ISV that interacts with customers whenever a record is updated/inserted...how would this happen with direct SQL?
What if the user later wanted to have some logic validate CreditLimits on customer accounts when anything was changed on the customer...how will they reliably know what methods change the customer with direct SQL?
The RecId's are often used as foreign keys, and there is the feature of surrogate keys as well. Direct SQL does not easily/reliably let one maintain those normalized relationships.
The RecId sequence is stored in a table but also cached, so it's not easy to just pull the next one unless you really know what you're doing.
Then to build off of what @FH-Inway was saying, what happens when a new developer comes into the picture. That dev will have to figure out whatever custom SQL was written before. The services are much more re-usable as well and force better practices.
Edit: To respond to your edits in your post, you're talking about interacting with tables in AX via X++ vs interacting with an entity service or something similar, and not direct SQL.
This is a scenario-dependent thing, I think, and it's difficult to write a comprehensive answer. The service approach allows a central interface, which can provide consistency and additional validation when you have multiple moving parts. You can just think of it like copying and pasting the same 10 lines of code or encapsulating it in a method for reuse. That way when you want to change the way those 10 lines function, you just modify the method.
So for some entities (Customer/Address/Vendor/etc) it makes sense to use the services if you can to ensure every other piece of business logic is hit.
There are many times where it makes perfect sense to just interact with the tables.
A good example I can give is if you were to create a new customer. If you use the service, you just provide the name, address, credit limit, etc and it will create it and run associated business logic. If you were to try and just insert a new record into CustTable, you may not remember to assign a PartyId, whereas using the service this is done automatically for you. There may also be other underlying tables that automatically get populated with data when you create a new customer that will be taken care of by the service.
Another example is creating a new item in InventTable. The underlying tables InventItemInventSetup, InventItemPurchSetup, and InventItemSalesSetup may need records that you could forget to create when doing InventTable.insert(). So this is why using the service internally can make more sense as well.