Tag: database

Categories
Database MSSQL MySQL

How to change the default file encoding in Sql Server Management Studio

Recently I was working on SQL code and data migration engagement for a major Financial institiuation. I was leading the team of SQL developers on this proposition. We have been provided thousands of SQL scripts and hundreds of Stored Procedures along with similar amount of user-defined SQL database functions to convert them in new supporting format. Developers were converting the SQL codes, saving it and committing these to the version-control system GIT.  During Database code analysis and review (which follows immediately after development) to reduce the number of “code smells” that creep into your database builds, the Governance team noticed something wrong with the file encoding.

So, What went wrong?

The file that is used to create a new query window has ANSI encoding but when they save the file it was in UTF-16. Unicode characters were broken 🙁

It didn’t took much time to figure out the issue and resolve, but I decided to write the article explaining steps to resolve.

The default encoding for SQL Server Management Studio sql files is UTF-16, more specifically, either Western European (Windows) - Codepage 1252 or Unicode - Codepage 1200. These encodings play havoc with a git diff as these encoding appear as binary files.

Advanced Save Options

The preferred encoding is Unicode (UTF-8 with signature) - Codepage 65001

How to solve the File encoding in SSMS?

We can change the default file encoding in order to be the one we want in the first place. What I have done was change from ANSI encoding to UTF-8.

  • From within SSMS, open the sql template file named SQLFile.sql, by default in one of these locations: –
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\
    • %ProgramFiles%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
    • %ProgramFiles(x86)%\Microsoft SQL Server\[Sql Version]\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

My path to the file is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql where the 140 stands for the SSMS v17 (in my case, right know I’m using the v17.5).

Save File with Encoding
  • Resave using correct encoding:
    • File => Save As
    • Click the arrow next to the Save button
    • Choose the relevant encoding: Unicode (UTF-8 with asignature) - Codepage 65001
Advanced Save Options

All new query windows will default to UTF-8 files

'Coz sharing is caring
Categories
Database MySQL PHP

MySQL vs. MySQLi

MySQL is a relational database management system (or RDBMS) – meaning that it is a database management system based on the relational model. This RDMS runs as its own server and provides multi-user access to multiple databases at once. The source code of MySQL is available under the terms set forth in the GNU General Public License as well as a plethora of proprietary agreements. Members of the MySQL community have created many different branches of the RDMS – the most popular of which are Drizzle and MariaDB. As well as being the prototype of several branches, most free software projects that must have a full featured database management system (or DMS) use MySQL.

MySQLi Extension (or simply known as MySQL Improved or MySQLi) is a relational database driver that is used mainly in the PHP programming language. It provides an interface to the already founded MySQL databases. It is quite literally an improved version of its predecessor, MySQL; which was simply a means to manage databases over servers.

MySQL can be found in many web applications as the database component of a solution bundle (or LAMP) software stack. Its use can be seen widely in such popular web sites as Flickr, FaceBook, Wikipedia, Google, Nokia, and YouTube. Each one of these websites use MySQL for storage and the logging of user data. The code is comprised of the C and C++ languages and uses many different system platforms –including Linux, Mac OS X, and Microsoft Windows.

The MySQLi extension comes equipped with many benefits that compliment as well as improve those that were provided by its predecessor, MySQL. There are a few that are more prominent than others. These features that are meant to enhance the functionality of the MySQL (as well as provide an update to the database manager as a whole) are an object oriented interface, support for statements that have been previously prepared, support for a variety of statements, support for any kind of transaction that takes place, an enhanced level of debugging support, and an enhanced level of server support that is already embedded in the infrastructure of the database.

As an RDBMS, it is not required that MySQL be shipped with GUI tools in order to administer the databases or manage the data therein. It is possible for users to use a command line tool or download MySQL Frontends from a variety of parties that have the software necessary and web applications to manage the databases, build the databases, and work with the data records.

Summary:

1. MySQL is an RDBMS that runs as a server and provides multi-user access to multiple databases; MySQLi is an extension of MySQL.

2. MySQL does not need GUI tools in order to administer databases or manage the data therein; MySQLi builds upon the features of MySQL and include object oriented interface, support for previously prepared statements, and enhanced embedded server support.

'Coz sharing is caring