There are a few ways to do it depending on what you're going for. You can use the custom SQL feature, but it's not ideal when it comes to long, complex SQL statements. I've found it's more useful for small, simple SQL statements. If the SQL is super complex you can have your SQL developers put the code into Views on SQL Server. You'll probably want to create a new database for them, so the Views aren't on the production database. A View is basically a Table comprised of a SQL statement. They do not contain any data unless they're called (I think).
Views are what we use for 95% of our SQL Server data. Stored Procedures can be used as well. I tend to avoid those if possible, because of issues I've had connecting them to Tableau.
My recommendation would be to sit with your SQL devs and explain what you're trying to do given what Tableau is capable of.
Hope that helps.
I agree with Mark. As someone who is also fairly new to connecting directly to Clarity through Tableau, I'll try to build on what he said.
I've found that whether to use the actual tables or custom SQL statements depends on the job, but I generally use custom SQL. There are some limitations to what type of SQL commands Tableau can handle (for instance, temp tables don't work). You can get around this by getting creative and 'thinking like Tableau.' Sometimes you need to break a complex SQL query into 2 or 3 (or more) separate queries, and join them together. I generally try to minimize the number of custom SQL queries I use, just to make it more understandable / readable.
One thing I've found is that the really long and complex SQL queries are generally doing things that don't need to be done in SQL. Often, these SQL queries are doing lots of contortions to do aggregations and calculations that are very easy in Tableau. I would definitely recommend sitting down with a SQL programmer in front of Tableau and working through this. If possible, try to get a consistent contact. After the first project or two, once they understand how to best use Tableau and SQL together, things get a lot smoother.