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 “localhost\SQLEXPRESS”.
  
• 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=localhost\sqlexpress;Initial Catalog=MyNewTestDatabase;Integrated Security=True;Pooling=False
    
  • Through a file reference: Data Source=.\SQLEXPRESS;AttachDbFilename=C:\opensvn\development\ezweb\trunk\src\website\App_Data\ASPNETDB.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 http://www.aspfaq.com/sql2005/show.asp?id=3 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.