Managing SQL Server Compact Edition Databases with SQL Server Management Studio

  • Article

SQL Server Management Studio, included with SQL Server 2005, lets you create and manage SQL Server 2005 Compact Edition (SQL Server Compact Edition) databases. In this walkthrough, you will create a new SQL Server Compact Edition database, populate it with data, and query the data, all from within SQL Server Management Studio. You will also see how to compact and repair a database file.

Prerequisites

To perform this walkthrough, you must install the following:

  • SQL Server 2005
  • SQL Server Compact Edition Server Tools
    To install SQL Server Compact Edition Server Tools, run the following installation program:
    C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlce30setupen.msi
    For more information about installing the tools, see the "Install the SQL Server Compact Edition Server Components" section in Creating a Mobile Application with SQL Server Compact Edition.

Create a database and populate with data

  1. Open SQL Server Management Studio.

  2. When you are prompted to connect to a server, type (local) for the Server Name, and then click Connect.

  3. Open a new query window. Create a SQLMobile database and populate the database with data.

    USE Master;
GO
DROP Database SQLMobile;
GO
CREATE DATABASE SQLMobile;
GO
USE SQLMobile;
GO
CREATE TABLE MembershipData (MemberId INTEGER IDENTITY (1,1) CONSTRAINT pkMemberId PRIMARY KEY, MemberName NVarChar (50));
CREATE TABLE FlightData (MemberID INTEGER FOREIGN KEY REFERENCES MembershipData(MemberID), Destination NVarChar (50), FlightStatus NVarChar(50), ArrivalDate DATETIME, FlownMiles INTEGER);
INSERT INTO MembershipData (MemberName) VALUES ('Mr Don Hall');
INSERT INTO MembershipData (MemberName) VALUES ('Mr Jon Morris');
INSERT INTO MembershipData (MemberName) VALUES ('Ms TiAnna Jones');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (1, 'Seattle', 'Flight Delayed 1 hour', '8/25/00', '20000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (2, 'London', 'Flight on time', '9/12/00', '15000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (3, 'Sydney', 'Flight Gate Closing', '11/5/00', '30000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (1, 'Tokyo', 'Delayed Fog', '5/25/00', '25000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (2, 'Minneapolis', 'Flight on time', '5/1/00', '1000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) VALUES (3, 'Memphis', 'Flight Gate Closing', '1/5/00', '1000');

  4. Click Execute (!) to run the script and create the database.

Note

You can also press F5 or choose Execute from the Query menu to run the query.

The script runs and creates a new database named SQLMobile.

Query the database

  1. Click New Query and choose New SQL Server Compact Edition Query.

  2. In the Connect to SQL Server Compact Edition dialog box, select the database file you created in previous steps, and then click Connect.

    A new blank query window opens.

  3. In the new query window, type the following SQL query:

    SELECT * FROM FlightData F, MemberShipData M WHERE F.MemberId = M.MemberId;

  4. Click ! Execute from the toolbar or press F5 to run the query.

    The query returns six rows of data. These are displayed in the Results window.

View the execution plan

  1. From the Query menu, click Include Actual Execution Plan.

  2. Click ! Execute from the toolbar or press F5 to run the query again.

  3. The query runs and again displays the results in the Results window. Next to the Results window, a new tab is added named Execution plan.

  4. Select the Execution plan tab.

    By hovering the mouse pointer over each component in the execution plan, you can view the details of each component.

Compact the database

  1. In Object Explorer, right-click SQL Server Compact Edition [My Computer\...\sqlmobile], and then choose Properties.

  2. In the Database Properties window, select the Shrink & Repair page.

  3. In the list of Shrink & Repair Options, choose Perform full database compaction, and then click OK.

See Also

Tasks

Creating a Mobile Application with SQL Server Compact Edition

Help and Information

Getting SQL Server Compact Edition Assistance