There are certain things that you can outright be fired for - such as stealing, I believe. I think that showing up drunk for work might also be one of them?
There are more, and they are immediate. And nearly always lead to a lawsuit, because it is only for grave errors in judgement. i.e. theft, violence, endangering co-workers, fraud, misrepresentations to the board and such. So when this is triggered it is never a clean break. Lawsuits can come from both sides as the reason for the firing is grave and the argument doesn't end when a person was kicked out.
AFAIK showing up drunk isn't enough but if you fuck up (I think that's the legal term) and say you drop the production DB while drunk that you can be fired immediately.
To give an example of a sysadmin from a job long ago:
Turning up drunk to a meeting is enough to start disciplinary procedures -- HR handle this, and work out how to proceed. (On [1], I think they would have been proceeding through the "Misconduct" section, second chance etc.)
Rotating the backup tapes, then carrying said tapes about 1km to the other building to put in the safe, was part of the job, but he was watched while he did it.
Stopping at a bar along the way was not fine.
Being oblivious to the point that a manager was able to take the bag containing the tapes from the bar back to the office was not fine.
Coming back to the office (drunk) and not realizing what had happened was not fine.
This was gross misconduct, and led to immediate dismissal.
IANAL: In the US, doing something destructive [at work] while intoxicated can be "gross negligence", which is criminal if I remember correctly. Example... you show up to work drunk, get in a forklift, and drive through a wall causing damage.