All the features you think are lacking are in fact supported by SQL Server.
You just need to learn to write proper SQL instead of missing the Access
quirks that aren't there. Access is a fine application development tool but
as a database it's just a toy. I'm glad SQL Server looks nothing like it.