• Bad question. The answer depends on what privileges you are logged in with. Telling people the correct answer is that you can do this is encouraging them to assume that it is normal to log in with special prividleges all the time.

    If you have decent security, so that your login doesn't have create table in SA permissions, which it won't have unless you've done something special to provide that or you are logging in with SA privileges, you can't create non-temp tables in tempdb but can create temp tables in tempdb.

    Obviously if you are logged in with SA permissions you can create a non-temp table in tempdb. However, it should be pretty unusual to log in with SA privileges - it's something which when I was responsible for administration, support, and development of databases scattered aroun the world I did pretty rarely - normally I would login without SA privileges, instead I would log in as whatever non-SA-privileged user the app I was cirrently interested was run as. Even now that I'm just using SQL Server for my own personal stuff I don't log in with SA privileges, just as I don't log in to Windows as a sysadmin unless I really have to; and when I do I have to look the password up in my password safe, as I use it so rarely that I don't remember it - and I don't have SQL logins on my SQLS instances, so the only way I get to SQL Server with SA privileges is by logging in to windows as a sysadmin.

    I guess some people may grant create table permission in tempdb to all users; I reckon that too is poor pratice, and some of Hugo's earlier comments tell you why.

    Tom