While I haven't worked with the spatial data type in SQL Server, my guess is that you won't really see any difference in Tableau...for a couple of reasons. I think Tableau will probably just convert it (internally) to a decimal. The only place where you could get some boost is in running the query from SQL Server, but data types don't have that much impact on how fast a query runs. Sure, the size of the data types can affect the speed for returning the data, but I think it's highly unlikely that you'd see any difference using spatial vs decimal.
I could be wrong of course...
One more thing to note on this. The current beta version of Tableau supports SQL Server spatial data, so there could definitely be some value in using spatial data types in SQL. More info here: Coming Soon | Tableau Software