MSSQL vs. SQLSRV: What’s the Difference? (Part 2)


In my last post, I promised a comparison of the APIs for the MSSQL and SQLSRV extensions of PHP, so here it is. I constructed the table below assuming some familiarity with the MSSQL extension. I’m aiming to provide a high-level comparison that you might use if you were considering moving to the SQLSRV extension, but I think there is also some interesting information if you are just curious about the differences. (For a detailed comparison, I will point you to the MSSQL API documentation and the SQLSRV API documentation.) In cases where a short note wasn’t enough (and there were several of these), I’ve provided relevant links to topics in the SQLSRV documentation.

As I constructed the table below, a few major differences between the APIs stood out for me. I think they are worth noting before looking at the function-to-function comparison:

  • The sqlsrv API offers the sqlsrv_prepare and sqlsrv_execute functions for executing a query multiple times with different parameter values. This potentially has performance benefits when performing a query repeatedly in a loop. For one-off queries, the sqlsrv_query function is recommended. For more information, see Comparing Execution Functions. (Note that any place in the table below where I suggest sqlsrv_query can be used, the combination of sqlsrv_prepare/sqlsrv_execute could also be used.)
  • The sqlsrv API has no special functions for executing stored procedures (in constrast to the mssql_init, mssql_bind, and mssql_execute functions in the mssql API). A stored procedure is executed by defining a SQL string like this:

     $sql = “{call StoredProcedureName(?, ?)}”;

The question marks are place holders for stored procedure parameters (if there are any). This query is then executed like any other query (with sqlsrv_query or sqlsrv_prepare/sqlsrv_execute). For more information and examples, see How to: Retrieve Output Parameters, How to: Retrieve Input/Output Parameters, and sqlsrv_next_result.

  • While the mssql API offers the mssql_connect and mssql_pconnect functions for establishing connections and “persistent” connections respectively , the sqlsrv API relies on ODBC connection pooling to make sure the overhead of creating a new connection is kept to a minimum. Connection pooling is on by default. For more information, see Connection Pooling.
  • The sqlsrv API uses scrollable cursors to allow random access to the rows of a result set. (For more information, see Specifying a Cursor Type and Selecting Rows.) The mssql API pulls an entire result set into memory to allow random access to the rows of a result set. Each of these implementations has benefits and drawbacks depending on the needs of your application. (Maybe a deep dive into this topic would be a good topic for another blog post…what do you think?)

If you have used both API’s (or if you’ve just read the comparison), I’d be interested in which API you like better and why. Do you think one is more elegant than the other? Is one more practical than the other? I think this could be an interesting discussion.

 

MSSQL and SQLSRV Function Comparison

 

MSSQL Function

SQLSRV Equivalent Function(s)

Notes

mssql_bind

sqlsrv_query

With the sqlsrv API, stored procedure parameters are defined in an array that is passed to sqlsrv_query or sqlsrv_prepare. For more information and examples, see How to: Retrieve Output Parameters, How to: Retrieve Input/Output Parameters, and sqlsrv_next_result.

 mssql_close

 sqlsrv_close

These functions are equivalent, but note that calling sqlsrv_close returns a connection to a connection pool. For more information, see Connection Pooling.

 mssql_connect

  sqlsrv_connect

These functions are equivalent, but note that sqlsrv_connect attempts to use a pooled connection before it creates a new one. For more information, see Connection Pooling.

 mssql_data_seek

 sqlsrv_fetch

or

sqlsrv_fetch_array

The sqlsrv API provides forward-only reading of result sets by default. However, in v1.1 of the driver, you can specify and retrieve any row of a result set with sqlsrv_fetch or sqlsrv_fetch_array. For more information, see Specifying a Cursor Type and Selecting Rows.

 mssql_execute

 sqlsrv_query

There are no special functions for executing stored procedures in the sqlsrv API. Stored procedures are executed with the sqlsrv_query function. For more information and examples, see How to: Retrieve Output Parameters, How to: Retrieve Input/Output Parameters, and sqlsrv_next_result.

 mssql_fetch_array

 sqlsrv_fetch_array

These functions are equivalent.

 mssql_fetch_assoc

 sqlsrv_fetch_array

The sqlsrv_fetch_array function returns a row with both numeric and associative indexes by default. You can retrieve only an associative array by using the SQLSRV_FETCH_ASSOC constant in the function call.

 mssql_fetch_batch

No equivalent function.

While the sqlsrv API does not have a function equivalent of mssql_fetch_batch, the number of rows in a result set can obtained using sqlsrv_num_rows. As you process rows, you can track the number of remaining rows.

 mssql_fetch_field

 sqlsrv_field_metadata

These functions both return metadata about a result set, although they each return slightly different metadata. See mssql_fetch_field and  for sqlsrv_field_metadata details.

 mssql_fetch_object

 sqlsrv_fetch_object

These functions are equivalent.

 mssql_fetch_row

 sqlsrv_fetch_array

These functions are equivalent, but note that the sqlsrv_fetch_array function returns an array with both numeric and associative indexes by default. You can retrieve only a numeric array by using the SQLSRV_FETCH_NUMERIC constant in the function call.

 mssql_field_length

No equivalent function.

Note that for some field types, the maximum length is returned by the sqlsrv_field_metadata function.

 mssql_field_name

 sqlsrv_field_metadata

In addition to other information, the field name is retuned by the sqlsrv_field_metadata function.

 mssql_field_seek

No equivalent function.

When using the sqlsrv_get_field function, fields must be accessed in order. The sqlsrv API does not provide a way to access fields randomly.

 mssql_field_type

 sqlsrv_field_metadata

In addition to other information, the field name is retuned by the sqlsrv_field_metadata function.

 mssql_free_result

 sqlsrv_cancel

These functions are equivalent.

 mssql_free_statement

 sqlsrv_free_stmt

These functions both free resources associated with a statement, but note that the sqlsrv_free_stmt does this for any statement (not only statements associated with stored procedures).

 mssql_get_last_message

 sqlsrv_errors

These functions both return error information about the last operation performed, but the sqlsrv_errors function returns information in an array (error code and error message).

 mssql_guid_string

No equivalent function.

While the sqlsrv API does not provide a function for converting GUIDs to strings, you can convert GUIDS to string-compatible types on the server with the CONVERT function.

 mssql_init

No equivalent function.

There is no special function for initializing stored procedures in the sqlsrv API. Stored procedures are executed with the sqlsrv_query function. For more information and examples, see How to: Retrieve Output Parameters, How to: Retrieve Input/Output Parameters, and sqlsrv_next_result.

 mssql_min_error_severity

No equivalent function.

The sqlsrv API does not provide functionality for filtering errors based on severity. For information on handling errors with the sqlsrv API, see How to: Configure Error and Warning Handling and How to: Handle Errors and Warnings.

 mssql_min_message_severity

No equivalent function.

The sqlsrv API does not provide functionality for filtering errors based on severity. For information on handling errors with the sqlsrv API, see How to: Configure Error and Warning Handling and How to: Handle Errors and Warnings.

 mssql_next_result

 sqlsrv_next_result

These functions are equivalent.

 mssql_num_fields

 sqlsrv_num_fields

These functions are equivalent.

 mssql_num_rows

 sqlsrv_num_rows

These functions are equivalent.

 mssql_pconnect

 sqlsrv_connect

The default behavior of the sqlsrv_connect function is to open a pooled connection if one is available. When a script ends or when sqlsrv_close is called, the connection is returned to the connection pool. For more information, see Connection Pooling.

 mssql_query

 sqlsrv_query

These functions are equivalent.

 mssql_result

 sqlsrv_fetch/sqlsrv_get_field

The combination of the sqlsrv_fetch and sqlsrv_get_field functions are equivalent to the mssql_result function. Calling sqlsrv_fetch makes a row of data available for reading and sqlsrv_get_field reads fields in the current row.

 mssql_rows_affected

 sqlsrv_rows_affected

These functions are equivalent.

 mssql_select_db

No equivalent function.

To select a database with the sqlsrv API, use sqlsrv_query to execute the following query: USE databaseName.

That’s all for today folks…thanks!

-Brian

Share this on Twitter

Comments (21)

  1. Anonymous says:

    SQLSRV’s prepared statement API (passing in bound variables byref at the creation of the prepared statement instead of at execution) is not only piss poor design, makes SQLSRV all but useless to me given that it makes it inordinately complex and fragile to write PDO-style wrappers for a SQLSRV-driver for Doctrine

  2. Daniel-

    Can you narrow the problem? Is it passing variables by reference? Or is it that you want to pass the variables at execution? (or both?)

    BTW, it looks like the SQL Server Driver for PHP team is considering a PDO driver: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/4614212e-9193-46d5-babc-5517bd494553

    Thanks.

    -Brian

  3. Anonymous says:

    My problem is both I guess you could say.

    The whole goal of a prepared statement is my ability to send my query, sans values, to the DB so it can spool up and start compiling the query and prepping indexes while I gather the parameter values. I then loop over the gathered parameter values, feeding a new set per transaction to have the DB re-use the query it’s already compiled and analyzed.

    So on one hand, being forced to push in my parameters at query creation is stupid because in a normal code flow, I haven’t even gathered my parameters yet (granted I know what they will be).

    Secondly, using variables by reference forces me to pollute my methods with ByRef variables that ad an extra layer of ambiguity to my debugging.

    And finally it almost all but destroys the ability to easily pass the prepared statement resource between methods as you can’t just pass the resource, you have to pass the parameters ByRef array along with it.

    While I can work around these problems, it’s unnecessary complexity being introduced into my application that I don’t need or want to spend my time on, and given the mssql and PDO_mssql drivers still function I will continue to use them until Microsoft at least tries to follow common PHP conventions (e.g. the avoidance of passing variables by reference, and prepared statement behavior).

    If the SQLSRV team is serious about developing a PDO driver then they have answered all of my frustrations. We can chalk the sqlsrv driver up as  a learning experience if they do finish the PDO driver.

    Then all I’ll have to yell at them about is poor Linux server support 😛

  4. Anonymous says:

    Hi Brian, I hope you can help me. I`m developing one project with sqlsrv but I have one problem when I want to run a stored procedure with OUTPUT parameter guid format. I have predefined format of const EMPTY_GUID = '00000000-0000-0000-0000-000000000000'; Everythin works fine, but retrieved GIUD from the database is broken with question mark in last character like "4C532F26-126E-4ADD-84F0-B7059E5E032?" I checked all setting in PHP, APACHE, MS SQL and everything seems fine. Charset, collation, everything is the same on all sides. DO you have any idea what could be wrong please? thank you!

  5. Milan-

    Without looking at your code, I can't really say what the problem might be. I think the best thing to do in this case is to start a thread in the forum (social.msdn.microsoft.com/…/threads) that includes the code that is causing the issue. (I'm assuming you are using the sqlsrv driver and not the mssql driver.) Once I can reproduce the problem, we can figure out how to fix it.

    Thanks.

    -Brian

  6. PSchmidt says:

    Brian,

    Thanks for the great summary of the APIs.  I have a couple of suggestions for improvement.  If you use to use: mssql_query followed by mssql_fetch_array, you can't just use sqlsrv_query and sqlsrv_fetch_array functions as they are not quite equivalent.  The fetch will fail unless you change the Scrollable to static.  The query also has different returns if there are zero rows.  mssql returns a true instead of a resource.

    Paul

  7. Thanks, Paul. That looks like useful information that I should add to the post…I'll confirm and add it!

    Cheers,

    Brian

  8. PSchmidt says:

    Brian,

    What I said may have been incorrect.  I mentioned the wrong function!  It is num_rows, not fetch_array that caused the problem.  Here is mssql code and how to rewrite it to work with sqlsrv when using num_rows:

    $res = mssql_query($sql, $conn);

    $num = mssql_num_rows($res);

    Should be rewritten as:

    $res = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));

    $num = sqlsrv_num_rows($res);

    Paul

  9. Anonymous says:

    hello.

    thank for this article. meybe you forget to compare with mysql?

  10. @faca5 – You are right! I will right that post soon…thanks.

  11. Anonymous says:

    Hello.  SQLSrv for PHP has worked well for me and I must say I like it.

    But, there's this one doubt that is puzzling me: when we pass a parametrized query (with the “?” marker) to sqlsrv_query what does it really do?

    Does it simply insert the values at the markers and passes a plain (non- parametrized) query to SQL Server?

    Or does it prepare and execute the SQL? In theory, this should not be the most efficient way, because it's a one-time query. Right?

    I'd very much like to take this out of my mind. Thanks.

  12. Anonymous says:

    I have a problem when I run php.exe

    Php startup: sqlsrv: unable to initialize module

    Module compiled with module API=20060613

    PHP compiled with module API=20090626

    These options need to match

    I used

    PHP Version 5.3.1

    php.ini

    extension=php_sqlsrv_52_ts_vc6.dll

    extension=php_pdo_sqlsrv_52_ts_vc6.dll

    anyone who can help me to solve this problem?

    thanks

  13. Anonymous says:

    Hello, Pure.

    I think I can help. Actually, the answer lies in the output of php.

    The binary versions need to match. Change your PHP.ini to:

    extension=php_sqlsrv_52_ts_vc9.dll

    extension=php_pdo_sqlsrv_52_ts_vc9.dll

    Notice VC9 instead of VC6.

    This should work.

  14. Anonymous says:

    Just wanted to point out that mssql_num_rows and sqlsrv_num_rows are NOT equivalent. You cannot get the number of rows from a forward cursor (the default) when using sqlsrv_next_result. The two behaviors are not even close to the same. You may want to point that out. If you call a stored procedure with multiple result sets, plan on doing a lot of recoding.

  15. Anonymous says:

    mssql_query and sqlsrv_query are not equivalent: the first needs just a single argument (T-SQL command) the latter needs the resource parameter and the T-SQL command (sqlsrv_query( resource $conn, string $tsql [, array $params [, array $options]])).

  16. Anonymous says:

    Parabéns Brian pelo post.

    Gostaria de saber como faço para executar uma procedure usando sqlsrv no php.

    aguardo.

  17. Anonymous says:

    This is a bit late, but one issue I've noticed is that I could use T-SQL variables with mssql, but I can't seem to with sqlsrv. This is a simplistic example, but the following used to work, but doesn't with sqlsrv:

    DECLARE @variable INT = 10

    SELECT @variable variable_name

    Specifically, I have a number of examples in an application and would love to be able to port it over. Any suggestions would be very helpful.

    Thanks,

    Neil

  18. Anonymous says:

    It turns out, I'm completely wrong. This seems to be absolutely fine.

  19. Noe says:

    sqlsrv_num_rows no funciona cuando el $sql es un procedimiento almacenado