Export (0) Print
Expand All

Managing SQL Server Compact Edition Databases with SQL Server Management Studio

SQL Server 2005

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.

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.

  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.

  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.

  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.

  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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft