SQL Server: Native client vs. ODBC?

What is the difference between SQL Server Native Client connection and ODBC connection? What are the pros and cons of these tw开发者_JS百科o?

Huh? ODBC is officially dead? Someone might want to let Microsoft know that:

Microsoft is Aligning with ODBC for Native Relational Data Access

From the above link:

ODBC is the de-facto industry standard for native relational data access...

and

The commercial release of Microsoft SQL Server, codename 'Denali' will be the last release to support OLE DB.

and finally,

"We encourage you to adopt ODBC in the development of your new and future versions of your application. You don’t need to change your existing applications using OLE DB, as they will continue to be supported on Denali throughout its lifecycle. While this gives you a large window of opportunity for changing your applications before the deprecation goes into effect, you may want to consider migrating those applications to ODBC as a part of your future roadmap. Microsoft is fully committed to making this transition as smooth and easy as possible.""

(emphasis added)

ODBC is useful for times when the underlying database might change but you don't want your code to (assuming the SQL stays the same across technologies). You could connect to an Oracle database one day and switch out to a SQL server database the next. The disadvantage is that you don't get the optimizations that having specific drivers affords you. The SQL Server Native client driver has been proven to be much faster than just using a standard ODBC driver.

What is the difference between SQL Server Native Client connection and ODBC connection?

ODBC is a standardized API.

ODBC drivers are shared libraries that use native protocols (like SQL Server shared memory, or SQL Server TCP/IP) to implement the ODBC interface.

In other words, ODBC is an abstraction that enables code to work against multiple database technologies.

It's similar to Java's JDBC, or Python's DB-API, or GO's database/sql, except ODBC drivers use C functions. Also, they are more frequently installed at a system level.

ODBC has the usual pros and cons of any abstraction.

• Pros: Makes code more flexible/portable.

• Cons: Adds performance overhead and has fewer features.

It sounds like you know that you will use SQL Server and will always use SQL Server.

In that case, I'd use a native client library if it's available.

SQL Server Native Client is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver for Windows.

SNAC 11 is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver for Windows. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. A separate SQL ODBC-only driver is available for Linux.

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