When would you want to use ESRI SDE (I think it is called Server but I forget the level - please feel free to edit) versus a spatially enabled database. What are the trade-offs on either side? What are the benefits on either side?
SDE [ArcSDE] can refer to at least two things: the organization of your data in the database (the SDE Schema) or a service listening for connections from clients (the SDE service). Generally they go hand in glove - the SDE service is bound to an SDE schema in a database.
In its "purest" (or perhaps dirtiest) state, SDE handles all of the spatial computations, and only stores data in your database as BLOBs and other native SQL types. Some database functions, like text or XML indexing, are used to improve performance, but generally the database doesn't "know" it is serving spatial data. There's just a bunch of tables and views and procedures, and they're full of data and functions.
With a spatially enabled database, the database IS aware that the data has a location. So, you can put location queries right into your SQL statements. Perhaps this is a good thing for you, it really depends on who is consuming your data. If your data consumers are fluent in SQL it's great! If your data consumers are fluent in ArcMap they could probably care less.
More recently we have been able to blend the two, by using SDE to translate to an underlying native spatial type. Furthermore, we can use "direct connect" to bypass the SDE service and just have the consumer application (ArcMap, ArcGIS server, etc) connect straight to the database. Personally I have had varying levels of success with direct connections.
Benefits to using ArcSDE:
Drawbacks to using SDE:
Benefits to a spatially enabled database:
Drawbacks to using a spatially enabled database:
I have more experience with plain SDE so there are likely more points for the spatially enabled database.
Hope this helps!
Here's my one line answer: Use SDE when you need multi-user access to your geospatial data.
Let's say you want multiple users to edit your data: use SDE. Let's say you want to serve data to and allow it to be edited over the web: use SDE. If you're a small shop, with one GIS guy, don't use SDE.
If you're the only person using your spatial data, SDE isn't for you. If you don't need multi-user editing, SDE isn't for you. You're better off using a file GeoDatabase.
As for trade-offs...SDE is not trivial to set up or manage. You have to use a RDBMS.
SDE is meant for larger organizations where one database is needed but several users need to access and update/edit data.
Nowadays most spatial dbs allows multiple spatial columns in one tables, while SDE sticks to one spatial column for one table. They also have spatial data integrated with their flexible and powerful data management tools, which SDE lacks, such as user sachems, data replication, SQL support and etc.
ESRI SDEBinary is the fast performer. If it comes to ST_GEOMETRY, SDE may not have the best performance.