Short answer: stick with an Aggregate, don’t use Group By here, and only fall back to Advanced SQL if you truly hit limits of the Aggregate. The performance difference for this kind of lookup will be negligible if you configure the Aggregate correctly.

Let’s go through your points.

1. Aggregate vs Advanced SQL performance for this case

You have:

• Single table
• Filter on indexed column UserId
• Max Records = 1
• Need only one column (USERCSN)

Correctly configured, the Aggregate will generate a SELECT TOP 1 ... WHERE UserId = @UserId query that SQL Server will optimize very well. OutSystems aggregates are specifically optimized for this kind of usage and are generally preferred when they can express the query you need. Aggregates vs SQL > Performance Considerations for Data Fetching

Also, for “one record by indexed ID” scenarios, the best practice is explicitly to use Max. Records = 1 to avoid unnecessary data fetching. Aggregates Usage > Avoiding Unlimited Records

For such a simple query, the differences in TTFB between Aggregate and Advanced SQL will typically be in the noise compared to network latency and API stack overhead.

2. Using Group By just to reduce columns (pseudo‑DISTINCT)

Using Group By on a single attribute only to trim columns is not a good practice:

• It conceptually misrepresents what the query is doing (you’re not aggregating; you’re just selecting).
• It can force the database to perform grouping/sorting work that isn’t needed, especially if the engine decides it must sort/group to satisfy the GROUP BY. That’s extra overhead for no logical benefit in a “top 1 by indexed where” query.
• It makes your Aggregate harder to read and maintain.

If you need DISTINCT semantics for a counting or grouping scenario, then Group By + aggregate functions is usually fine and is actually recommended over DISTINCT. Outsystems Group by vs SQL Distinct
But using Group By purely as a hack to reduce output columns is an anti-pattern. In that case, if you truly need custom projection, Advanced SQL is a cleaner option than abusing Group By.

3. Can you exclude attributes from the Aggregate output?

Not in the way you’re thinking.

Key points:

• The Aggregate’s output structure is based on the entity, and you cannot selectively “turn off” attributes from the output in the editor.
• OutSystems does optimize attribute fetching and output, even if the structure shows all fields. The engine can trim attributes when they’re not used downstream, and this is one of the reasons Aggregates are recommended. Aggregates or Simple Queries
• In other words, seeing all attributes in the Aggregate result does not necessarily mean every column is materialized and transferred in a costly way if you never reference them.

So:

• You can’t say “only fetch USERCSN” explicitly in the Aggregate UI.
• But you can rely on OutSystems’ optimization to avoid paying a big penalty for unused attributes.
• Do not use Group By as a workaround for this.

If you absolutely require a strictly minimal projection for some reason (e.g., very wide table, proven performance issue, or you want a special index hint / rowlock behavior), then Advanced SQL is the right tool—but that’s the exception, not the rule. How are aggregates better that advance SQL?

4. REST APIs, TTFB, and best practice choice

For exposed REST APIs:

• Default rule: use Aggregates when they can express what you need.
  • They are easier to read, maintain, refactor, and they benefit from platform-level optimizations and automatic updates when entities change. Aggregates or Simple Queries
• Advanced SQL: use it when:
  • You need features Aggregates don’t support (complex joins, subqueries, CTEs, unions, etc.).
  • You have very complex queries where Aggregates become unreadable or fragmented across multiple queries.
  • You need DML (INSERT/UPDATE/DELETE), or specific DB-level constructs. Aggregates or Simple Queries

In your particular case:

• Indexed filter on UserId
• Max Records = 1
• Simple table lookup
• No complex logic

The recommended approach is:

1. Use an Aggregate.
2. Set Max Records = 1.
3. Filter by UserId as you are doing.
4. Do not add Group By.

This will keep your logic in the OutSystems “happy path” and deliver performance that is effectively equivalent to your handwritten SELECT TOP 1 in most real-world scenarios.

Summary answers to your questions

1. Performance difference Aggregate vs Advanced SQL here?
   For a single-table, indexed WHERE, Max Records = 1 lookup: typically no meaningful difference. Use an Aggregate.

2. Is using Group By on a single attribute to force single-column output bad practice?
   Yes, it’s a misuse of Group By and can add unnecessary overhead. Prefer a plain Aggregate or, if you really need custom projection, Advanced SQL.

3. Can you exclude specific attributes from Aggregate output without Group By?
   Not explicitly in the editor. The result structure always includes entity attributes, but the platform optimizes attribute fetching/output behind the scenes.

4. For REST APIs with tight TTFB goals, is Advanced SQL generally recommended?
   No. Aggregates are generally preferred for maintainability and built-in optimizations. Use Advanced SQL when aggregates cannot express your query or when you need special DB-specific behavior.

If you later profile and find this specific lookup is a genuine hotspot, you can swap that one Aggregate for Advanced SQL with negligible refactor cost—but I’d start with the Aggregate approach.