July 22, 2014 at 2:27 am
Hugo Kornelis (7/21/2014)
Creating permanent tables in tempdb this way is tricky. They will behave "sort of" like permanent tables (they will not be removed when the batch ends or when you leave the scope), but they will not really be permanent in that they will be loist when the box restarts. (Or fails over in the case of a box that is part of a high availability setup).ChrisM@Work (7/21/2014)
Sreepathi1987 (7/21/2014)
Easy One. 🙂But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?
Yes, which makes tempdb useful as an experimental sandbox. If you forget to clear up your tables, SQL Server will do it for you.
Beware of putting too much trust in this sandbox. Especially for tuning, tempdb is not a good place to try. Various features of the optimzed are not used in tempdb! (I know because I have been bitten by this often enough that I now, finally, remember. Most of the time).
That's interesting, Hugo, and a little scary because I know a few folks here on ssc use tempdb specifically for this purpose. Do you have any documentation yet or is a little early?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2014 at 3:28 am
ChrisM@Work (7/22/2014)
Hugo Kornelis (7/21/2014)Especially for tuning, tempdb is not a good place to try. Various features of the optimzed are not used in tempdb! (I know because I have been bitten by this often enough that I now, finally, remember. Most of the time).
That's interesting, Hugo, and a little scary because I know a few folks here on ssc use tempdb specifically for this purpose. Do you have any documentation yet or is a little early?
I have no documentation. The reason I know this is from experience - things like trying to repro something in my tempdb, not being able to get it, posting in despair to the MVP mailing list, and getting a response from Paul White (who else, when it comes to this subject) pointing out that I should try it in a permanent database instead.
After I think the third time, it now finally sticks. 🙂
If there is any official documentation on this, and if I have ever seen it, I do not remember it. But I can assure you from personal experience that it is true.
July 22, 2014 at 6:18 am
I wanted to just mention something briefly:
When I run that scrip as-is, I get a CREATE TABLE permission denied in database 'tempdb'. However, when I change it to #MyTable, the command is allowed (which is how I have always created temporary tables in tempDB). This was in my dev environment where I have permissions to create tables in all of the other databases, so I thought it was worth mentioning!
July 22, 2014 at 7:18 am
This was removed by the editor as SPAM
July 22, 2014 at 10:21 am
twin.devil (7/21/2014)
Sreepathi1987 (7/21/2014)
Easy One. 🙂But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?
The answer is Yes.
Nice and easy question, but still tricky if you have never created a table in tempdb before. So Thanks for sharing Steve.
+1
|
|__+1
To author: thank you for post - very interesting one.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
July 22, 2014 at 5:06 pm
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
July 27, 2014 at 3:15 am
TomThomson (7/22/2014)
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.
I was about to say the same but read your reply, only login with admin rights can create non-temp tables in tembdb.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
August 11, 2014 at 12:09 pm
I was able to create but I don't see it in object Explorer
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply