> does this just find the next 0 or non-0 in the file?
No. Read the fine article. SEEK_HOLE finds the next zone in the file for which storage has not yet been allocated.
For example, create a 100 MB file (using e.g. mmap()) and write to the first and last 1 KB of it, and close the file. You will find that many filesystems will not allocate 100 MB of disk space for the file; they will store the data that was written (which might require some padding) and simply note that there is a big chunk of "empty space" in the middle of the file.
When you read the file again, the filesystem will report the "empty space" as containing 0. Sometimes, you know that you can safely skip over these unallocated blocks as an optimisation when reading the file (such as in the case of 'cp'). The idea of SEEK_HOLE is to enable this.