Why does git think my .sql file is a binary file?

From version 2.18, git has an option working-tree-encoding precisely for these reasons. See gitattributes docs.
[Make sure your git version (and all who'll use the repo) is at least as recent as 2.18]

Find out the encoding of the sql file eg with file

If (say) its utf-16 without bom on windows machine then add to your gitattributes file

*.sql text working-tree-encoding=UTF-16LE eol=CRLF

If utf-16 little endinan (with bom) make it

*.sql text working-tree-encoding=UTF-16 eol=CRLF

Using the accepted answer from the linked question and a few other comments I came up with this as a solution to the issue, which is working and runs on Win10

$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
Get-ChildItem -Recurse *.sql | foreach {
    $MyPath = $_.FullName;
    $Contents = Get-Content $MyPath
    [System.IO.File]::WriteAllLines($MyPath, $Contents, $Utf8NoBomEncoding)
}

Here is a quick workaround that worked for me, using SSMS 2012. Under tools => options => environment => international settings, if you change the language from "English" to "Same as Microsoft Windows" (it may prompt you to restart SSMS for the changes to take effect), it will not use UTF-16 as the default encoding for new files anymore- all new files I create have Codepage 1252 (file => advanced save options) now, which is an 8 bit encoding scheme and seems to have no problems with Git Diff

For those struggling with this issue in SSMS for 2008 R2 (yes, still!), you can set the default encoding as follows:

• Locate directory C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Locations may vary. This is the directory used by the default installation on Windows 7 64-bit.

• In this location, add (or edit) empty SQL file SQLFile.sql.

This is used as a template for new .SQL files. Save it using the encoding you require (in my case, Windows-1252 with Windows line endings). The arrow to the right of the 'Save' button gives you a choice of encodings.

You need to co-ordinate encodings with your development team to avoid git and SSMS hassle.

I was exporting SQL commands from my database using the Microsoft SQL Server Management Studio 19.1 Generate Scripts wizard and committing the generated .sql file to Github.

By default, the wizard writes the .sql file in Unicode Text. This caused Github to think that the file was a binary format.

The simple fix in my case was to change the exported format in the wizard to ANSI Text instead of Unicode Text in the Save as selection. Now Github is able to recognize the file as plaintext and offer diffs on the content.

No PowerShell commands needed, no post-batch file to run. Just export the text as ANSI and Github will be happy.

The way to resolve this issue is to force the file to use 8-bit encoding. You could run this PowerShell script to change the encoding of all .SQL files in the current directory and its subdirectories.

Get-ChildItem -Recurse *.sql | foreach {
  $FileName = $_.FullName;
  [System.Io.File]::ReadAllText($FileName) | Out-File -FilePath $FileName -Encoding UTF8;
}

I’ve edit .sql file in GitHub and it showed the message

We’ve detected the file encoding as UTF-16LE. When you commit changes we will transcode it to UTF-8.

After commit the file in GitHub was shown correctly, but PR compare with previous version still showed BIN(just because previous version had wrong encoding)