Differences Between Drivers for ODBC Drivers

Question

I was setting up the System DSN (64 bit) for my database in SQL server 2016 with Windows 10 64 bit pro. While I was asked to choose the driver to set up a data source, there are the following selections:

• ODBC Driver 13 for SQL Server
• SQL Server
• SQL Server Native Client 11.0
• SQL Server Native Client RDA 11.0

It seemed I can set up the data source with all of these drivers. Then which one should I choose in terms of speed and efficiency? What's the difference between them?

Thanks,

Jason

Answer 1

Microsoft ODBC Driver for SQL Server (MSODBCSQL)

This driver was announced in 2013 as the successor to SQL Server Native Client. It is installed with recent SQL Server versions (13 with 2016 and 2017). There are also standalone installers. Driver history for Microsoft SQL Server recommends this driver in preference to "SQL Server" and "SQL Server Native Client" for ODBC. Added features over "SQL Server Native Client": driver-aware connection pooling, connection resiliency, and asynchronous execution (polling). You can follow current development on the Microsoft ODBC Driver For SQL Server Team blog.

Driver={ODBC Driver XX for SQL Server} (XX for installed version. See System Requirements, Installation, and Driver Files.)

SQL Server Native Client (SQLNCLI)

Introduced with SQL Server 2005 and ships with SQL Server (also installable via sqlncli.msi from SQL Server feature packs). Added features over "SQL Server": Multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.

Driver={SQL Server Native Client} (SQL Server 2005)
Driver={SQL Server Native Client 10.0} (SQL Server 2008)
Driver={SQL Server Native Client 11.0} (SQL Server 2012 and later)

SQL Server Native Client RDA

Remote Data Access (RDA) is a SQL Server Compact feature which "lets an application access data from a remote SQL Server database table." I can't find much documentation, but it appears this driver was intended to support replication scenarios with Compact Edition.

SQL Server (SQLSRV32)

Included in Microsoft Data Access Components (MDAC) (now called Windows Data Access Components (WDAC)). This driver is available by default on Windows (since 98 and NT 4.0).

Driver={SQL Server}

Answer 2

ODBC Driver for SQL Server

ODBC is the primary native data access API for applications written in C,C++, PHP, Python and others for connecting to SQL Server.

It's widely used in data integration scenarios.

Also, it's preferred if you are developing application on Windows and deploying it to Linux .

SQL Server

The full name of SQL Server Driver is SQL Server ODBC Driver. It’s an old driver since sql 2000. You can use it to connect to SQL Server 2016, but you will not be able to access new features and functionality of SQL Server 2016

SQL Server Native Client

SQL Server Native Client is containing both the SQL OLE DB provider and SQL ODBC driver to support native connectivity to SQL Server and support all features of sql server 2016. It's the best in windows environment

SQL Server Native Client RDA

Remote data access (RDA) in Microsoft SQL Server Compact 3.5 lets an application access data from a remote SQL Server database table.

It can also store, read, and update that data in SQL Server Compact 3.5, and then update the original SQL Server table.

RDA will be removed in the future release, so avoid using it.

So choose sql driver based on the criteria above.

Answer 3

SQL Server Native client vs .NET Framework Data Provider for ODBC

It seems that for last version for SQL Server Native client, the Microsoft® SQL Server® 2012 Native Client also named as SQL Server native client 11.0. Doesn’t support new feature in SQL Server 2014 or later

Note that SNAC 11 does not support features released with SQL Server 2014 and SQL Server 2016 that were not available as part of SQL Server 2012, such as Transparent Network IP Resolution, Always Encrypted, Azure AD Authentication, Bulk Copy and Table Value Parameters.

https://blogs.msdn.microsoft.com/sqlreleaseservices/snac-lifecycle-explained/

You have to use Microsoft® ODBC Driver 11 or 13 for SQL Server to enjoy new feature in SQL Server 2014 or later