Actually, for really hot loads, it can have significant impact. If you look at the Anatomy of a Record you'll see that columns follow the record header, fixed column first followed by variable length ones. So whenever a column is accessed, the record header will have to be accessed first and this access is almost always an L2 cache miss. Any subsequent access within the same cache line (64 bytes) will be an L2 cache hit almost 100% of the times. Given that the CPU cycle difference between an L2 cache miss and a hit is about 2 orders of magnitude, you get a pretty big performance boost if you arrange your frequently accessed columns near the record header. The end-to-end performance boost will not be anywhere 2 orders of magnitude, but for certain OLTP loads it can add up to 5-10% overall. For analytic loads, the cost of IO overwhelms everything else and you probably won't be able to measure any difference.
This logic gets applied to every index individually, but on indexes you have to consider that the order of declaring the index is the actual order of the key so you don't have much room for change.