[Feature] Make unit test fixture and expected SQL macros dispatchable

### Is this your first time submitting a feature request?

- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Adapter maintainers need a clean way to override warehouse-specific SQL generated by dbt unit test fixture and expected-row macros.

Today, macros such as `get_fixture_sql` and `get_expected_sql` are not dispatched adapter macros. That means third-party adapters that need small SQL syntax changes have to shadow/copy larger upstream macros instead of overriding only the warehouse-specific fragment.

Concrete example: SQL Server does not support `limit 0`, so `dbt-sqlserver` needs to generate `select top 0 ...` for empty unit-test fixtures or empty expected rows. Without dispatch, the adapter has to carry a copied version of the upstream macro, which creates an upgrade-sync risk.

Requested behavior:

- Make `get_fixture_sql` and `get_expected_sql` dispatchable, or
- Add smaller dispatch points around the warehouse-specific SQL fragments used for empty fixture / empty expected-row generation.

### Describe alternatives you've considered

The current workaround is to shadow/copy the upstream dbt unit-test macros inside the adapter and change only the SQL Server-specific syntax. That works, but it is fragile: future upstream changes to the unit-test macros will not be inherited automatically by the adapter.

Another alternative is to keep adding adapter-specific branches upstream, but that does not scale as well as dispatch because warehouses can differ in SQL syntax for empty relations, type casting, literals, and fixture construction.

### Who will this benefit?

This benefits adapter maintainers and users of third-party adapters.

For `dbt-sqlserver`, this would allow the adapter to replace `limit 0` with T-SQL-compatible `select top 0 ...` for unit tests without copying the full upstream macro. Other adapters with dialect-specific unit-test SQL requirements would also be able to customize only the relevant SQL generation.

### Are you interested in contributing this feature?

Yes, potentially. Guidance on the preferred dispatch boundary would be helpful.

### Anything else?

This came up while fixing SQL Server unit tests for empty fixtures / expected rows. The adapter only needs a small SQL syntax override, but the current macro structure requires a larger shadowed copy.