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

Brian Swan - MSFT

10 Mar 2010 4:57 PM

Comments 18

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.

The sqlsrv API has only one function for handling errors, sqlsrv_errors, which returns error information about the last sqlsrv operation that was performed. This is in contrast to the mssql_get_last_message, mssql_min_error_severity, and mssql_min_message_severity functions offered in the mssql API.

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.