24

What is the difference between SQL Server Native Client connection and ODBC connection? What are the pros and cons of these two?

juur
  • 5,633
  • 10
  • 32
  • 36
  • For people just trying to pick an ODBC driver rather than debate ODBC vs OLEDB, see here: https://stackoverflow.com/a/54206996/5405967 – MarredCheese Dec 13 '19 at 21:46

4 Answers4

19

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)

nerraga
  • 614
  • 7
  • 17
William Powell
  • 191
  • 1
  • 3
  • 7
    They're bringing OLEDB back: https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/ – serpah Oct 06 '17 at 17:54
5

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.

Josh
  • 10,352
  • 12
  • 58
  • 109
  • 1
    -1. FACTUALLY wrong. ODBC is dead. all yousay is ... what you do with OLEDB these days, NOT with ODBC. – TomTom Mar 17 '11 at 20:01
  • You're right. My bad on mixing these up. Sorry for the confusion. – Josh Mar 23 '11 at 14:54
  • "The SQL Server Native client driver has been proven to be much faster than just using a standard ODBC driver." Really? I was just testing out before our switch. When I make the conn like Driver={SQL Server}; it is slightly faster for large numbers (more than 20X) of calling the same proc than Driver={SQL Server Native Client 10.0}; – Brian White May 01 '12 at 17:02
  • @TomTom err… proof? [Acc. to Wikipedia](https://en.wikipedia.org/wiki/Open_Database_Connectivity#Version_history) at the time when your comment was written, latest version was just 2 years old. And a year ago started development of the next version on Github. – Hi-Angel Mar 04 '17 at 15:12
3

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.

Paul Draper
  • 78,542
  • 46
  • 206
  • 285
-1

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/

user3200692
  • 77
  • 4