105

Every SQL Server connection string I ever see looks something like this:

Data Source=MyLocalSqlServerInstance;Initial Catalog=My Nifty Database;
    Integrated Security=SSPI;

Do I need the Initial Catalog setting? (Apparently not, since the app I'm working on appears to work without it.)

Well, then, what's it for?

Improve this question
1
  • 36
    Hmmm, I just discovered that my app put all my created tables in master. Oops. I may have just learned the answer to my question.
    – Ryan Lundy
    Commented Dec 22, 2009 at 23:12

3 Answers 3

Reset to default
64

If the user name that is in the connection string has access to more then one database you have to specify the database you want the connection string to connect to. If your user has only one database available then you are correct that it doesn't matter. But it is good practice to put this in your connection string.

Improve this answer
3
  • 13
    Not quite true. The login may not have permissions on the default database. So, you need to change the database context on connection
    – gbn
    Commented Dec 23, 2009 at 7:30
  • This is required for the Entity Framework if you specify using the -ConnectionStringName parameter, so definitely good practice but also at times required!
    – James G
    Commented Jan 1, 2015 at 7:25
  • Can you please reword this answer? I still don't understand after reading through twice.
    – thatWiseGuy
    Commented Jan 29, 2019 at 23:33
36

This is the initial database of the data source when you connect.

Edited for clarity:

If you have multiple databases in your SQL Server instance and you don't want to use the default database, you need some way to specify which one you are going to use.

Improve this answer
3
  • 1
    The first part is correct. The second part is not correct. When you create the account it is assigned a default database which will be used if initial catalog is not specified. This generally defaults to master (for some unknown reason).
    – GrayWizardx
    Commented Dec 22, 2009 at 23:09
  • When I say "by default" I simply mean when you are not qualifying the database in your object names. In any case, I have clarified my answer.
    – Andy West
    Commented Dec 22, 2009 at 23:19
  • I like Andy's clarification, in fact his comment is helpful; doesn't it imply that if I do qualify every database in my object names, then the Initial Catalog is not as important / irrelevant?
    – Nate Anderson
    Commented Oct 22, 2020 at 19:44
17

Setting an Initial Catalog allows you to set the database that queries run on that connection will use by default. If you do not set this for a connection to a server in which multiple databases are present, in many cases you will be required to have a USE statement in every query in order to explicitly declare which database you are trying to run the query on. The Initial Catalog setting is a good way of explicitly declaring a default database.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.