It's really two stations, a country station opened in 1906 (originally called Sydney Terminal) and an adjoining suburban station opened in 1926 (originally called Central Electric). Although it's not without interest, the suburban station is not of great architectural merit and, in any case, is probably taken for granted by most Sydneysiders.
The country station on the other hand, to me, is one of Sydney's great sandstone buildings, with its cedar doorways and ornate stained glass windows. There's more to it than just the station building too - there's the long ramp leading from Pitt Street, the sandstone colonnade in Eddy Avenue and the approach to the station from Railway Square (more sandstone!). I think it compares more than favourably with other similar station buildings.
I suppose it's used mainly for suburban and interurban travel these days. The growth in air and coach travel and the contraction of the country rail service have had a major impact on the number of passengers using the older station. At some times of the day, the country station (platforms 1-15) sees very little traffic, but at other times it can still get busy. It's a popular meeting spot too. It is one of Sydney's most recognisable landmarks.
I might also add that I posted some photographs of the country station (not the suburban one) a few months back in another thread:http://www.walkingmelbourne.com/forum/viewtopic.php?f=16&t=6396