Spray foam insulation is usually sprayed up between the rafters effectively insulating the attic space itself.  Other forms of insulation (batt, blown in cellulose, etc.) are usually installed between the ceiling joists which insulates the room below, but the attic space remains uninsulated.