Saturday, December 2, 2017

How tempDB works in MSSQL

Temp table is stored in tempdb until the connection is dropped (or in the case of a global temp tables when the last connection using it is dropped). You can also (and it is a good practice to do so) manually drop the table when you are finished using it with a drop table statement.
No, others cannot see your temp tables if they are local temp tables (They can see and use global temp tables) Multiple people can run commands which use the same temp table name but they will not be overlapping in a local temp table and so you can have a table named #test and so can 10,000 other users, but each one has its own structure and data.
You don't want to generally look up temp tables in tempdb. It is possible to check for existence, but that is the only time I have ever referenced tempdb directly. Simply use your temp table name. Example below of checking for existence
  IF OBJECT_ID('TempDB.dbo.#DuplicateAssignments') IS NOT NULL 
  BEGIN 
  DROP TABLE #DuplicateAssignments 
  END  
You name temp tables by prefacing the name with # (for local tables the ones you would use 999.9% of the time) and ## for global temp tables, then the rest of the name you want.

12 comments:

Atom Steels said...

Fertility is the natural capability to produce offspring. As a measure, fertility rate is the number of offspring born per mating pair, individual or population.Human fertility depends on factors of nutrition, sexual behavior, consanguinity, culture, instinct, endocrinology, timing, economics, way of life, and emotions.Greate thinks of a fertility center for humans.

Fertility Center in OMR

calfre143 cal said...

when to use Triggers?
please explain me.

SQL Server Training in Hyderabad

Hindi Video Songs& Comedy said...

Best SQL
https://pandaysanatansharma.blogspot.com/?m=1

narayana p said...

It's so nice article thank you for sharing a valuable content. SQL server dba Online Training Bangalore

Susmitha Bommepalli said...

Very nice information. I would like to appreciate you. Keep it up!
Best Data Science Online Training Institute In Hyderabad | Online Data Science Training
Data Science Online Training Institute In Hyderabad
Data science online training in hyderabad
Best data science training in hyderabad



Unknown said...

Best selenium online training institute `

supreet said...

Great post. Needed to write simple word that Thanks for suggestions. Keep it up! best sap simple finance online training institute in hyderabad

sujitha s said...

BIG DATA Technologies provides you with a state of the art software which combines modern GPU technology (Graphic Processing Units) with the best practices in today’s Big Data platforms, providing up to 100x faster insights from data.
Bigdata Training in Chennai OMR

suganya d.suganya said...

Testers can build, enhance, and maintain scripts to regression test their mobile applications. Hands-on instruction is provided for those who want to explore the power of using Appium. The course covers content from installation to execution and reporting . The focus is on the practical application of Appium to resolve common mobile automated testing challenges. This course focuses on getting started with Appium.
course/appiumtraininginsholinganallur/

sumant kumar said...

thank you for sharing useful post.
web programming tutorial
welookups

Coepd BA Trainings said...

We at COEPD provides finest Data Science and R-Language courses in Hyderabad. Your search to learn Data Science ends here at COEPD. Here, we are an established training institute who have trained more than 10,000 participants in all streams. We will help you to convert your passion to learn into an enriched learning process. We will accelerate your career in data science by mastering concepts of Data Management, Statistics, Machine Learning and Big Data.

https://www.coepd.com/AnalyticsTraining.html

Coepd BA Trainings said...

We at COEPD provides finest Data Science and R-Language courses in Hyderabad. Your search to learn Data Science ends here at COEPD. Here, we are an established training institute who have trained more than 10,000 participants in all streams. We will help you to convert your passion to learn into an enriched learning process. We will accelerate your career in data science by mastering concepts of Data Management, Statistics, Machine Learning and Big Data.

https://www.coepd.com/AnalyticsTraining.html