Good day,
Is it possible to change the tempdb my current session is using? I have a very heavy query that is meant for HD usage. Ideally, I'd like the query to be ran using a tempdb we have specifically for such heavy things. (Main issue is the query creates a very large temp table)
I'd like something along the lines of:
use tempdb <tempdbname>
<query>
use tempdb <normaltempdb>
If this is at all possible, even if by other means, please let me know. Right now, the only way I know of to do this is to bind a user to a different tempdb, and then have HD login using that user, instead of the normal user.
Thanks in advance, ziv.
if your main concern is impact to tempdb and other users, you could consider creating multiple default tempdbs of the same size and structure. Add these to the default group and sessions are assigned to a tempdb on connection thus lessening the risk of one large query impacting the whole dataserver
You could also consider the use of a login trigger for specific logins and check the program name which is connecting to decide upon which tempdb to use (e.g. Business Objects could go to a much larger DSS tempdb or similar).
There is no way to change your session tempdb in-flight that I'm aware of though as tempdb bindings are set on connection.