While reading through about tuning SQL queries, I just read somewhere that 'Always use table alias and prefix all column names using the aliases when you're exceeding one table.'

So how exactly does table alias names affect performance? Or Will they really affect?

The does not affect performance in almost any practical or meaurable way whatsoever (italics added on edit). That's, it might give a barely (whether it all) measurable delay to question compilation. Once put together (and re-used) it doesn't have affect

It will remove ambiguity and it is needed in some instances eg schema bound sights

The SQL parsing engine (that reads all queries before performing them, and uses the data to cache the put together queries later on for faster execution) may be the only factor that compares the aliases, and uses it to assist remove ambiguities in symbol searches. The machine would already produce symbols, as with every other compilable statement in almost any other language, when it is being parsed just before execution-storage.

Almost not whatsoever, the performance impact is minimal, but you will have a far better time reading through the query. It is simply for your benefit.

The performance impact is allocating a couple of kb of memory to keep alias names, etc. within the SQL Server program itself. In comparison towards the relaxation from the procedures required to execute your query, this really is next to nothing.