SQL Server Performance

Default instance and named instance

Discussion in 'ALL SQL SERVER QUESTIONS' started by Antonio Benildus Muerling, Jun 19, 2012.

  1. Hi All,

    Please can someone tell me the difference between a default instance and named instance between the SQL server.
    Under what circumstances should I select named and on under what situation should I select default.
    When I open SQL server management studio, how would I know that this is installed on default and this is with named.
    Default instance and Server instance is it applicable to the databases or the server.
    Finally would there be a difference while connecting via the ODBC, towards the front end aplplication. while trying it (default and named)


  2. FrankKalis Moderator

  3. Shehap MVP, MCTS, MCITP SQL Server

    The main difference between default instance and named instances is that the first one is using the default name "MSSQLServer" and you can either us the connection string DNS_Name\MSSQLServer or DNS_Name

    But for named instance , you have basically to use the connection string DNS_Name\instance name unless you assigne TCP port 1433 (Default) for it then you can use DNS_Name as a connection string

    This applies for clustered and stand alone SQL server instances

    For ODBC connection ( Oldest versions in particular ), I don't recommend it at all for large mission DBs where intensive OLTP transactions are there particularly due to its poor powers as :

    · It is missing MARS (Multiple Active result sets) which help in having multiple result sets per one connection and so more scalable OLTP transactions (Fixed by Microsoft staring from SQL Server native client and onwards)

    · Bad Network IO performance in particular for huge data entity

    Therefore, I do recommend to use OLEDB provide or better .net SQL client data provide (.net framework provider)

Share This Page