How to use SQL Server Express (where’s the UI?) – level 200

If you are used to working with SQL Server 2000, you may expect to find a tool similar to Enterprise Manager and Query Analyzer.  SQL Express is a free product, and it shows, but you get an awful lot for free. . . but you don’t get it all.  I think that’s perfectly fine.  I’d rather have something for free than nothing.

Here’s a few tips to get you started:

  • SQL Express installs as a local instance named “SQLEXPRESS”, so your connection string needs to include the instance name:  “.SQLEXPRESS” or “localhostSQLEXPRESS”.
  • SQL Server Configuration Manager isn’t the UI you want to add databases, tables, etc.
  • Use any of the Visual Studio Express products as your database UI.  They all have the database manager built in.

    • Use the Server Explorer window to add your database instance.  Then you can use it to add a new database and add tables to that database. 
    • If you have database create scripts, you can run them inside Visual Studio Express.  If you are used to hitting F5 in Query Analyzer, then you’ll want to map a shortcut key to the “Run Selection” command: Right click -> Run Selection.
    • You can create all your database objects here.
    • You can run and step through stored procedures for debugging.

  • You can also use osql.exe to manager your database.  This is useful when you want to automate database scripts using NAnt.
  • You have the option of how you want to connect to a SQL Express database:

    • Through the SqlClient provider: Data Source=localhostsqlexpress;Initial Catalog=MyNewTestDatabase;Integrated Security=True;Pooling=False
    • Through a file reference: Data Source=.SQLEXPRESS;AttachDbFilename=C:opensvndevelopmentezwebtrunksrcwebsiteApp_DataASPNETDB.MDF;Integrated Security=True;User Instance=True
    • If using ASP.NET, you already have a connection string you can use: LocalSqlServer:  Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True

See for more information.  There are other, non-Microsoft, tools that can be used to manager SQL Express as well.

UPDATE:  There is a November 2005 CTP of SQL Server 2005 Express Management Studio available.  This version is being linked to right off the SQL Server express download page.