2010-02-15
Movies of wars
I recommend those two movies which may change some of your views about war
Battle for Haditha
http://www.imdb.com/title/tt0870211/
The Kingdom
http://www.imdb.com/title/tt0431197/
![[del.icio.us]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/delicious.png)
![[Digg]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/digg.png)
![[Facebook]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/facebook.png)
![[Google]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/google.png)
![[MySpace]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/myspace.png)
![[Technorati]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/technorati.png)
![[Twitter]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/twitter.png)
![[Windows Live]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/windowslive.png)
![[Yahoo!]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/yahoo.png)
![[Email]](http://my.textjourney.com/eric/wp-content/plugins/bookmarkify/email.png)
2009-12-18
Merry Christmas
Please accept with no obligation, implied or implicit, my best wishes for an environmentally conscious, socially responsible, low-stress, non-addictive, gender-neutral celebration of the winter solstice Holiday, practiced within the most enjoyable traditions of the religious persuasion of your choice, or secular practices of your choice, with respect for the religious/secular persuasion and/or traditions of others, or their choice not to practice religious or secular traditions at all. I also wish you a fiscally successful, personally fulfilling and medically uncomplicated recognition of the onset of the generally accepted calendar year 2010, but not without due respect for the calendars of choice of other cultures whose contributions to society have helped make America great. Not to imply that America is necessarily greater than any other country nor the only America in the Western Hemisphere . Also, this wish is made without regard to the race, creed, color, age, physical ability, religious faith or sexual preference of the wish.
2009-10-22
Jeffrey Chiang Will Be Receiving No New Offers Of Employment
Jeffrey Chiang Will Be Receiving No New Offers Of Employment
Posted by Bess Levin, Oct 22, 2009, 9:33am
So! Firms are starting to hire again, which is very exciting to those of you trying to improve your situation. Perhaps it’s been a while since a lot have gone through this process, and you’re a little rusty on the Do’s and Dont’s. Which why starting today we’ll be offering little pearls of accumulated wisdom picked up in the field. Tip one: don’t lie about having received an offer from one firm while you’re interviewing with another. Tip one-A: if you’re going to lie about said fake offer, impersonate someone and forge a little evidence: easy on the typos. Spelling Bank of America without ‘c’ is going to be a red flag. Jeffrey Chiang knows what we’re talking about.
Chiang apparently interviewed at Bank of America, where he was asked if he had any offers from other firms. Jeffrey claimed that he was in his second round of interviews with Morgan Stanley. An associate at BofA then contacted his friend at Morgan about Jeffrey’s prospects. The Morgan guy said that contrary to popular belief, JC had only had a phone interview, at which time he claimed to have gotten a full-out offer from BofA. As proof, JC provided a fabricated email allegedly from a recruiting woman at Bank of America, who would probably be surprised to be informed she’d offered Chiang a job (and that she didn’t know how to spell “America”). The Morgan people forwarded the faux letter of employment back to the people at Bank of America who were doing recon and from there it was forwarded to the entire free world.
Obviously, the lies here are not good form but what’s most upsetting is the lack of effort. There wasn’t an ounce of creativity or dancing in this scam (though we did appreciate the demand to be put up at the Four Seasons). In fact, it’s downright boring, as scams go. It doesn’t come close to a Vayner move, and yet Mr. Chiang has been relegated to the same status as one of the greats. (A list of firms JC will likely not be getting offers from, as gleaned from the outrage expressed by their employees, can be found below). Apparently, though, we now live in a world of diminished expectations where you don’t even need a video or a claim to bench press 500 lbs to rile people up or swear they’ll never offer you a job. Going forward, you’ve been warned.
_______________________________________________________________
From: Jeffrey Chiang
To: [Morgan Stanley]
Subject: FW: Bank of America Merrill Lynch Interviews
From: [Fake Bank of America ML Recruiter]
To: Jeffrey Chiang
Subject RE: Bank of America Merrill Lynch Interviews
Hi Jeff,
Everyone was very impressed with your interviews today. We are excited
to formally extend to you an offer to join Bank of Ameria [sic]
Merrill Lynch as an analyst next summer. You should be getting
documentation in the mail to sign very shortly. If you have any
further questions please feel free to email me. Again, congratulations
and we look forward to having you join us next year.
——————————
From: [Morgan Stanley]
To: [Bank of America ML]
Subject: FW: Bank of America Merrill Lynch Interviews
This is what Jeffrey sent Morgan Stanley to prove he received an offer
from your firm. Given you told me you dinged him, should I assume this
is fake? If so, that’s unbelievable and his school should be notified,
he shouldn’t get a job anywhere on Wall Street.
——————————
From: [Bank of America ML]
To: [Lehman Brothers], [UBS]
Subject: FW: Jeffrey Chiang
I don’t know if this guy has come up on your radar screens in terms of
analyst recruits, but you need to be warned about him. I should have
been tipped off by the fact that he ran a “5k marathon” on his resume.
I just figured something got lost in translation.
I interviewed him on campus, and while he was pretty weird/intense, he
seemed like somebody who would crank and potentially make for a good
analyst, so we waved him in for an office visit.
Things started going bad for him when I got a call from our HR
department about him during our Superday. In making his travel
arrangements with our travel agent, he had apparently made a big stink
about needing to stay at the Four Seasons and blow up on the travel
person. It was apparently bad enough that she went to the trouble to
inform our HR department.
Our Superday reviews on him were pretty mixed, nonetheless. He had
spent a summer at Gulfstar, so I did a bit of checking on him there,
and it became clear that they were also very unimpressed with the way
that he carried himself. So, we dinged him, but that is not where the
story ends.
He had told one of the associates in our office that he was in the
second round of interviews for MS’s Palo Alto office. Well, our
associate happened to mention this to his friend that works in the MS
Palo Alto office and the associate at MS said that Jeff had had only
had a phone interview but had indicated that he had an offer from
BAML. When the MS team asked him to send proof of his offer, he
manufactured the email below and forwarded to the MS team.
We have notified UT of this joker’s behavior, but needless to say,
this guy shouldn’t be able to get a job at McDonalds after a stunt
like this.
______________________________________________________
So far employees from the following firms have been read into the situation (i.e were forwarded the above); all but one were “shocked” by this “clown” who they believe should “blacklisted.” The exception was a representative of Jefferies, who said that JEF could use a guy like JC.
* Bank of America Merrill Lynch
* UBS
* Citi
* Goldman Sachs
* Morgan Stanley
* JPMorgan
* Credit Suisse
* Lazard
* Tiger Global
* Soros Fund Management
* Raymond James
* RBS Greenwich Capital
* Tudor Investment Corp
* Blackstone
* Calyon
* Blackrock
* CRT Capital Group
* Perry Capital
* Oppenheimer & Co.
* ESL
* Citadel
* BarCap
* Deutsche Bank
* SMH Capital
* Neuberger Berman
* Bridgewater Associates
JChiang Resume [PDF]
2009-10-8
The 7 phases of B-school application
I’m attempting to summarize what most of us have gone through or will be going through in our path to B-school. I was partially motivated by several other posts within the forum (which I thank for) and partially on self experience (though the characters in this story are fictional, etc., you know the drill). School names are used as examples and by no means I intend to mock any organization or person, this is just an attempt at summarizing, in a humorous way, this rocky personal journey.
i.Introduction
Let’s say you are 2 or 3 years out of college and the thought of an MBA starts lingering in your mind. Either you’ve heard some stories of former colleagues going for it and are curious about it or you think the name sounds cool.
You can talk to MBA alumni (if you have access to them) to start your research, or maybe to some friends. But this initial conversations can be biased (name 1 alumni who “officially” thinks his/her school sucked and you’ll get a bonus!) for all you know.
So you decide you need some “objective data” to continue your research and you go pick up the latest issue of US news/ B-week, or whichever one is available at newsstands. You browse through their pages and start wondering:
1st phase (the MBA honeymoon):
- Wait, wasn’t Kellogg a cereal brand?
- What’s with the GMAT scores? why 700? over 1000? that’s weird. What’s GMAT btw?
- Ah, finally, I know Yale, I know Harvard, I know Stanford, MIT and UCLA. But where’s Princeton? And Brown?
- I like International Business, so as per these rankings I should better be attending Thunderbird. But why are the starting salaries from there so much lower than from other schools?
- I loved Miami when I visited on spring break. Lemme see what their school’s like.
2nd phase (Delussional optimism):
- I’m a wise person, so GMAT shouldn’t be a problem for me. Maybe I’ll take one of these intensive 1-week courses and go for it! Why would anybody spend months studying? That doesn’t make any sense. I mean it’s high school level math and English for crying out loud. Heck, I can speak English, I’ve taken Calculus classes.
- I’m a clear admit at HBS, plus I’ll get a full scholarship. After all I’ll get a top GMAT, I do speak four languages and have made steady progress at work so far.
3rd phase (Depression while taming the beast):
- GMAT sucks. My friends no longer talk to me. My girlfriend broke up with me and spending 150k for an MBA doesn’t make much sense to me anymore (nor does it make sense to my family, my former friends nor my girlfriend). Do I really, really want to do this? Otherwise I could go back to having a life right now.
- Ok, so I’m headed for a 600 score, if I’m lucky. Let’s see what that would do for me. Hmm, I’d better score at least 650. Wait, 650 ain’t that bad! Oh boy, I’d kill for a 650.
- “So Johnny (an acquaintance of yours), how did your GMAT go?”
Johnny: “Oh man, I’m so depressed. I bombed my 7th attempt. I just can’t get past 550. I’m about giving up”
You: “Crap, Johnny, after all the effort you’ve put into this, I can’t believe what you are telling me. I mean, I’m still a zillion hours away from your study record to date. By the way, I’ll better be heading home and attack those SCs again!”
- (at 4am in the morning on a working day): I suck, I suck, I suck! I can’t believe the silly mistakes I’m making. Sigh, I wish I’d remember more about Statistics…
4th phase (post GMAT preliminary research)
- Ok, so I got a pretty decent GMAT. Now let me write sth and send my app right away so we can finally bring this “I’ll pretend I read your app.” game to an end. Let’s check the instructions.
1st question) What matters most to you an why? [3 to 5 pages]
Hmm. Maybe I’ll leave this one for tomorrow. Or let me brainstorm and write a shortlist:
1st shortlist (prior to any research):
a) Money.
b) Success.
c) Beer.
d) Getting my ticket stamped to land an IB job.
2nd shortlist (after some research):
a) Being mother Theresa.
b) Saving humanity.
c) Saving the environment.
d) “Changing the world”.
- I’ll apply to 147 schools. That way, I’d maximize my chances of getting a scholarship.
- What’s with the letter of recommendation? Should I tell my boss about my plans? It looks like the point of no return to me.
5th phase (applying, AKA the emotional roller-coaster)
[staring at essay#1 version # 84]: This sucks! I can’t believe how boring I sound. I should re-start from scratch!
- I should write about the snooker tournament I won when I was 16. That’d be original, plus I can spin it to show how I used my leadership, analytical and teamwork skills.
- Beh, I can apply in Round 2 as well.
- Crap! my recommenders haven’t even accessed the website yet and it’s only 2 days left! I’ll send them “friendly reminder #27″. No, wait, I sent #26 just 5 minutes ago. Maybe I’ll wait another half hour.
- Wait, was Kellogg’s deadline on the 5th? Or was that MIT? Maybe I should drop Wharton. I can’t make deadlines on the 3rd, 4th, 5th and 7th. OK, I’ll just drop Wharton from my list and have it as “fresh” backup for next year just in case.
- I wish I had applied to more schools in Round 1. Look at all these people getting interviews and admits!
6th phase (post application blues)
- Shoot, I won’t get in anywhere. I mean look at the profiles of applicants! I should retake GMAT. My 700 is not enough. I should aim for 790+.
- Crap! Yale dinged me without interview! Ohmigod! If they did it, ANYONE can do it! THEY COULD ALL DO IT!
- I have an idea! I’ll check which schools have rolling admissions and apply to those. I still have time!
- Suddenly University of Phoenix Online doesn’t sound that bad.
- Why? Why? Why didn’t apply to more backups? Why did I have to shake my interviewer’s hand so firmly? Why didn’t I coach my recommenders more thoroughly? I wonder what they’ve written. Probably nothing good. I wish I had submitted my app. a day earlier, that way I would have looked as a well organized person. I read that Kellogg dings all applicants above 28 years old who haven’t made directors positions. Wait, is that a typo on my MIT essays? That’s one school less, buddy. I’m soo doomed.
7th phase (endless joy)
- Hell yeah! I’ve made it! I’ve been admitted [dream school X] next year! I rule! I can’t wait to get recruited by [dream employer]. When is admitted students weekend?
- 2nd admit! I rule!
- Should I go to [School X] with a 7k scholarship or to [School Y] with a 25 k scholarship?
- Work? What’s work? Ah, right, that thing I’m supposed to be doing daily on weekdays from 9 to 5…
- I wonder whether spending this 150k makes sense after all…
- I’m so gonna get grilled at B-school! What if I mess up? I’d better start brushing up on some skills.
Hope you enjoy, and feel free to propose edits or add-ons. After all, we’re all together in this journey!
Cheers. L.
http://gmatclub.com/forum/the-7-phases-of-b-school-application-42452.html
2009-10-8
MBA Blog/Forum Links
2009-10-8
Reading List
Suggested Reading List
INVESTING
- Market Wizards - Jack Schwager
- The New Market Wizards - Jack Schwager
- The Intelligent Investor - Benjamin Graham
- Common Stocks and Uncommon Profits - Philip Fisher
- The Essays of Warren Buffett: Lessons for Corporate America - Warren Buffett
- Contrarian Investment Strategies - David Dreman
- Against the Gods - Peter Bernstein
- Behavioral Finance and the Impact of Investor Bias on Decision Making - Nick Greer
- A Random Walk Down Wall Street - Burton Malkiel
- Markets in Motion: A Financial Market History: 1900 to 2004 - Ned Davis
- Origins of the Crash: The Great Bubble and Its Undoing - Roger Lowenstein
- Fortune’s Formula: The Untold Story of the Scientific Betting System that Beat the Casinos and Wall Street - William Poundstone
- The Art of Short Selling - Kathryn Staley
- Bull’s Eye Investing - John Mauldin
- You can be a Stock Market Genius - Joel Greenblatt
- The Little Book that Beats the Market - Joel Greenblatt
- Fooled by Randomness - Nicholas Taleb
- Running Money - Andy Kessler
ECONOMICS
- Knowledge and the Wealth of Nations - David Warsh
- Global Capitalism - Jeffry Frieden
- Vienna and Chicago, Friends or Foes?: A Tale of Two Schools of Free Market Economics - Mark Skousen
ACCOUNTING
- Accounting for Growth - Terry Smith
- Reading Between the Lines of Company Accounts - Stephen Bloomfield
- Financial Shenanigans - Howard M. Schilit
MISCELLANEOUS
- Fischer Black and the Revolutionary Idea of Finance - Perry Mehrling
- My Life as a Quant: Reflections on Physics and Finance - Emanuel Derman
- Liar’s Poker - Michael Lewis
- Hedgehogging - Barton Biggs
- The Alchemy of Finance - George Soros
- When Genius Failed: The Rise and Fall of Long Term Capital Management - Roger Lowenstein
- The Smartest Guys in the Room: Bethany McLean & The Amazing Rise and Scandalous Fall of Enron - Peter Elkind
- The New New Thing - Michael Lewis
- Barbarians at the Gate: The Fall of RJR Nabisco - Bryan Burrough
- The World is Flat - Thomas Friedman
2009-10-5
MS 150 Ride
Thanks all my dear friends’ kind supports. Finally, MS 150 is over. I am not actually sure whether I should say I finish MS 150 or not, well it is NOT up to me. Here is the story, the first day San Antonio experienced big rain, well, but not that bad I guess for the dieing hard cyclists. So I hang on there and finished 75 miles ride with 17+ mph average speed in the rain. Appreciate our company’s kind sponsor and all the volunteers, so I can get into the tent and have a rest. But I have to admit LifeTime Fitness team’s BBQ smelled really good, especially after my long ride as you can imagine. At the second day, I woke up around 5:15AM and was shocked about the bad weather. The storm was unbelievable! Honestly, I kept questioning myself “Am I stupid or brave?” This question finally left to the MS 150 committee, the day 2 ride was canceled. I felt sorry for Fay’s coming at that early to pick me up. And I even was not able to see her under my building, which was probably half minutes walking distance away, just because the rain was too heavy and it already started flooding over there. Well, to everyone’s disappointment, the rest of the day was OK, just very humid.
OK, that is pretty much about my first MS 150 ride. Again, highly appreciate all my friends’ support. And to the readers of my blog, MS 150 is the largest organize National ride in U.S. You can check over here:
Here are some pictures.
2009-09-25
就我沒白吃
『就我沒白吃』—作者只寫了一頁紙，己將中國的整個「結構」寫出來，佩服！
今天是週末，我們高中同學要在天安酒店搞一次同學聚會。
自從畢業後，好多同學都混得有模有樣，我卻默默無聞，在一家工廠當製圖員，每月和
丈夫一起靠著不多的收入共同撐著這個家。我本不打算去，可禁不起同學們的一片盛情
，只好答應。
丈夫正在幫兒子復習功課，兒子就要上初中了，為了上一所好中學，這段時間丈夫沒少
操心，東奔西走，至今還沒著落呢。看了兒子一眼，我走出了家門。
天安酒店是高級酒店，我走進包房的時候，同學們都已到齊。還沒坐穩，一張張名片就
飛了過來，一看一個個不是總經理就是帶長的，就連以前成績總是甩尾的阿輝也當上了
派出所所長。
望著服務小姐端上眼花繚亂的菜肴，我真感歎自己孤陋寡聞，光這一桌就足以抵我三個
月的收入了。阿輝像宴席的主人一樣不停地招呼大家吃，不時地為這個斟酒、為那個夾
菜，嘴裏還說：”只管吃，算我的。”大夥也沒任何拘束，一 輪接一輪地交杯把盞、海
闊天空地閒聊。
酒足飯飽之後，天色已不早，此次聚會該結束了。可究竟誰埋單，我看大夥好像都沒有
要慷慨解囊的意思。這時候阿輝掏出手機，按了一串號碼，然後說：”小李，今晚所裏
掃黃抓到人沒有？哦！剛抓到———好！好！ 隨便送一個到天安酒店來給我埋單。”
說完，他得意地把手機放進了口袋，一旁的同學跟著哄笑起來。十五分鐘不到，一個中
年人就進來了，他看了帳單，不禁皺了皺眉頭，看來他身上的現鈔也不足。
他隨即也拿出手機，撥了一串號碼，說：”廖公嗎？我是馬校長呀！你兒子要轉學讀我
們學校的事，我今天就給你拍板定下來了……不過我今晚請朋友吃飯，你過來埋單好嗎
？在天安酒店203包廂……”
二十分鐘後，有人敲了敲包廂的門，門被打開了。當我見到戴著副高度近視眼鏡的丈夫
站在門口時，我暈倒了。
PS：
本故事榮獲2006年度最佳故事情節獎，年度最讓人心酸故事獎，年度最佳搞笑短文獎，
百姓評委會特別推薦獎。
2009-09-23
Temporary Tables vs. Table Variables in SQL Server
When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I’ll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:
- Normal tables are exactly that, physical tables defined in your database.
- Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
- Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
- Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It’s a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.
Creating indexes on SQL Server tables
Because both local and global temporary tables are physical tables within the tempdb database, indexes can be created on these tables to increase performance as needed. As with any index creation, this process can take time on larger tables. Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.
Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.
How do the internal workings of SQL Server perform differently between table variables and temporary tables?
The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.
This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.
Limits of temporary tables and table variables
Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables — large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).
Both temporary tables and table variables can be extremely useful tools in developers’ and administrators’ arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.
Local Temporary tables:
They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign. When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.
Open one session in Query Analyzer or SSMS (Management Studio) and create a temporary table as shown below.
CREATE TABLE #TEMP
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO
Upon successful execution of above command, MS SQL Server creates table in tempdb database. One cannot create another temporary table with the same name in the same session. It will give an error but table with the same name can be created from another session. To do this, open another session from SSMS or query analyzer and issue same command again. It will successfully create new temporary table for that session.
In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number. This is very common scenario when temporary table is defined in the stored procedure and procedure is getting executed by different users simultaneously. Since we have created temporary table with the same name from two different sessions, we should see two entries in tempdb database. From another session or any of the current session, issue following command. Output is displayed after select statement.
USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
GO
Table_Catalog Table_Name
————- ———-
tempdb #TEMP________0000000001F7
tempdb #TEMP________0000000001F9
Now create some data from the session in which temporary table (#temp) is created.
INSERT INTO #TEMP(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO #TEMP(COL1, COL2) VALUES(2,’Information’);
INSERT INTO #TEMP(COL1, COL2) VALUES(3,’systems’);
Selecting data from temporary table will give following results.
COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-27 19:39:56.727
2 Information 2007-03-27 19:39:56.727
3 systems 2007-03-27 19:39:56.727
This data is not visible from another session since we are using local temporary table. We can verify it by connecting to another session and querying the #temp table. Local temporary tables are dropped when session which created the table is ended, if one has not dropped it explicitly.
Also, please do note that if you are creating temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over (they can be explicitly dropped as well). Once the procedure execution is over, those temp tables will not be accessible from within that session. Example:
create proc test
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
end
go
exec test
select * from #temp
Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#temp’.
Global Temporary tables:
Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition. In contrast of local temporary tables, global temporary tables are visible across entire instance.
CREATE TABLE ##TEMP_GLOBAL
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO
Execute above statement to create global temporary table. You can verify it by checking the tempdb database. As global temporary tables are available across the instance, SQL Server doesn’t suffix it with the number. Following is the output of query ran against tempdb.
USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘##TEMP%’
GO
Table_Catalog Table_Name
————- ———-
tempdb ##TEMP_GLOBAL
There will be only single instance of global temporary table. Attempt of creating global temporary table with the same name from any other session will result into an error.
Create some data in one of the session where temporary table (##temp_global) is created.
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(2,’Information’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(3,’systems’);
Connect to other existing session or open new session. Execute following statement and you will notice that global temporary table is available along with the data from other session as well.
COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-28 09:52:34.310
2 Information 2007-03-28 09:52:34.310
3 systems 2007-03-28 09:52:34.310
Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it. This will help us in avoiding creation error when same connection from the connection pool is used by different processes which access temporary tables.
Global temporary tables can be used in data warehousing application where one session performs the ETL and populate the global temporary tables and other sessions read from the table, specific data and process it.
Features of Temp Tables
We’ll list out features that differentiate a Temp Table between either a Permanent Table or a Table variable. These pointers will be helpful to keep in mind when you consider Table Variables in our next post.
Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed. But don’t be too complacent – you’ll have to wait for the system to perform a clean up and therefore, it is highly recommended that you manually drop your temporary table.
Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK
Logging: There is less logging involved with temporary tables compared to permanent tables.
Transaction: When using a temporary table, a temporary table is an integral part of an outer transaction and therefore, ROLLBACKs must be supported by Logging
Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.
Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a Foreign Key Constraints
Statistics: SQL Server can create Statistics for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. Hence, with this in mind, be aware of the scope of Stored Procedure Recompiles.
Recompiles: There is scope for a large number of Stored Procedure Recompiles especially when you have DDL statements mixed anywhere within your Stored Procedure.
Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.
Features of Table Variables
Now that you’ve got a hang around working with a Table Variable, let me mention the main pointers that we need to keep in mind while working with. This will set the stage for differentiating between a Temp Table and Table Variable which I’ll illustrate in my next post.
Transactions: Table Variables are not bound to any transaction as they are just like any other variable
Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.
No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC Stored Procedure.
No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilations can come out like wild fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilations.
Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)
Table Variables And The TempDb: Okay, now I’ll touch upon one of the most common myths that exist among developers: that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory and therefore they’re the best option for efficient processing.
Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:
No Statistics: When it comes to Table Variables, the SQL Server optimizer does not create distribution statistics. Therefore, you run the risk of referring not-so-good query plans when the SQL Server optimizer selects after checking up with histograms. And if you consider this aspect with Tables Variables that contain huge amounts of data, we fall into serious I/O thrashing. Hence, as stated in the closing section of the last point, we have to have a thorough understanding of our scenario to choose a temporary object for the context.
A possible replacement for temp tables is a table variable.
In summary, following are the key points when temporary tables are involved.
- Temporary tables can be defined as local or global temporary tables.
- Local temporary tables are available to session in which they are created. If another session creates the table with the same name, it will be different copy of the table in tempdb database.
- Global temporary tables are available across the instance. Any user from any session can access it.
- It is best practice to drop the temporary table when related work is finished rather than relying on connection to end for the cleanup.
- Table variables can be used instead of temporary tables for performance reasons and when dealing with smaller sub-sets.
- When used in the procedure,function or trigger, its scope ends once execution is completed.
Limitations of Temporary Tables
Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. SQL Server has numerous problems with operations against temporary tables.
Using Temporary Tables Effectively
If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.
- Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
- When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.
- Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
- After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
- When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.
Conclusion
Generally, temporary tables should be avoided as much as possible. If you need to use them follow the steps above so that you have the minimum impact on server performance
If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.
To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance.
Source: http://dev.digi-corp.com/2009/05/temporary-tables-vs-table-variables-in-sql-server/
Loading ...
