sdf file conncet

Jump start working with Microsoft SQL Server Compact 3.5 

Microsoft SQL Server Compact is an embedded(able) database ideal for desktop, (handheld) mobile devices and web clients. It is free to download (also free to deploy and redistribute) and comes in the form of just one code-free file. Its small foot print makes it easily deployable to a variety of device sizes and requires no administration. It also supports a subset of T-SQL and a rich set of data types. It can be used in creating desktop/web applications using Visual Studio 2008 and Visual Studio 2010. It also comes with a sample Northwind database. It is ideally suited to offline and occasionally connected clients.

It may be noted that the frequent updates to software programs sometimes does not help user to have a uniform experience in using the programs as the programs are updated. For example, when first released you could connect to SQL Server Compact right from the Database Engine dialog. However this got changed in 2008 version just before release. As described here,you will be getting connected first time through a query, but later you can find it in the Database Engine.

In this article managing SQL Server Compact 3.5 from the SQL Server Management Studio which is installed with SQL Server 2008 R2 Nov-CTP (or R2 RTM) will be described. Alternately one could also use SSMS installation with SQL Server Express 2008. Creating databases, tables, indexes and nested queries are also described.

Download details

It may be downloaded from this site. Make sure you download detailed features of this program from the same site. Also several bugs have been fixed in the program as detailed in the two SP's. Link to the latest service pack SP2 is here. By applying SP2 the installed version on the machine is upgraded to the latest version. SQL Server Compact 3.5 is also installed when you install SQL Server 2008 R2, Visual Studio 2008 and Visual Studio 2010 RC.

Connecting to SQL Server Compact from SQL Server Management Studio

Connecting to SQL Server CE for browsing or running queries was easily accessed in earlier versions of SQL Servers. For example as recent as SQL Server 2008 one could connect to SQL Server Compact in SSMS through the user interface as shown earlier in the blog post. However a recent version of Microsoft SQL Server Management Studio Complete 10.50.1352.12 installed with SQL Server 2008 R2 Nov-CTP did not support browsing to the installed database as shown.

Connecting to SQL Server Compact from a query in SSMS

There was no option to connect to SQL Server Compact from the Database Engine connection interface. However after a little bit of searching you find that there is indeed an option to connect to SQL Server Compact from a query. This needs to be enabled before you can run a query.

Click View | Toolbars | SQL Server Compact Edition Editor as shown.

This adds a SQL Server Compact query editor page as shown.

Click the icon of the editor in the toolbar. The Connect to SQL Server Compact Edition window is displayed as shown.

Click the drop-down handle for Database file and choose <Browse for more...>.

The Browse For Files window is displayed as shown. The C:\ node is expanded to locate the sample file.

Highlight Northwind.sdf and click OK.

The Database file location is added to the Connect to SQL Server Compact Edition dialog as shown.

Click OK.

Depending on the permissions to the file for the user you may or, you may not be able to connect to the database.

With the present permissions to the file for the user as shown the connection was refused.

The permissions were changed. The Full Control was given to the user as shown.

With this the SQL query window for SQL Server Compact was allowed as shown where a SELECT query has returned the rows in the Employees table.

SQL Server Compact query options

There are several options for running the query as well as tasks that can be accomplished once connected to the SQL Server Compact. Some of these are available as toolbar items of SQL Server Compact shown here.

These same toolbar items as well as few more are available if you were to invoke the contextual menu in the query pane. Bring up the contextual menu items by making a right click on an empty area of the query pane as shown.

When you click Query option... in the contextual menu you display the following window.

You could also see the table, views etc. if you click the Open Server in Object Explorer menu item as shown. You may have to display the Object Explorer from the View menu if it is not in display.

Creating a New database in SQL Server Compact 3.5

Out of the box there is a sample database called Northwind (shown in the above figure). You could build your own database as SQL Server Compact supports most of T-SQL. You can create the database by following these steps.

1. Connect to SQL Server Compact as shown from the Connect to Server dialog after it has been in use once.

2. Click Database file drop-down handle.

3. The Create New SQL Server Compact Database window is displayed as shown.

4. Provide a name for the file and accept other options as shown.

5. You get a warning question as shown. Since we will remove the database soon just accept the option. Creating a database without a password is not a recommended practice, but for the pourposes of demonstration it is set as described.

6. Click Yes.

7. The database file C:\userjay\Documents\PubsCE.sdf gets created.

8. When you connect to it in SSMS you will see that a typical node tree would have been created as shown. These are the only views present by default. Views, Stored Procedures etc are not supported.

Creating a table in SQL Server Compact 3.5

SQL Server Management Studio can be used to create tables in a database either using T-SQL or using the built-in graphic user interface. The GUI is recommended if you are not used to T-SQL as many of the finer details of the table are explicitly displayed in the GUI.

Using the Graphical User Interface

Right click the Table node in SQL Server Compact database node in SSMS. From the drop-down click New Table. This opens up the following New Table window where you can design each of the table columns.

After designing all the columns click OK and a table will be created. This shows a completed table. Note that the column names should be inside single quotes.
This appears to be quirk of the GUI. The T-SQL does not require this restriction.

Populating the table is done easily by using the Insert Into Table Values()  statement using the following which will insert two rows into the OurCustomer table.

Insert Into OurCustomer values('Jay','Krishna','8 Olive Street','Bengaluru','India','5/7/1999')
Go
Insert Into OurCustomer values ('Stan','Oliver','25 Elm Street','Mumbai','India','8/8/1988')

Note that SQL Server Compact does not support insertion of multiple rows of data in a single Insert statement.

Creating table with T-SQL

Right click the SQL Server Compact database node and choose the option to create a New query from the drop-down. In the new query window insert the following query and execute. Note that not all data types in SQL Server 2008 R2 are supported in SQL Server Compact. Also not all T-SQL statements are supported.

CREATE TABLE BCustomer(
First_Name nvarchar(25),
Last_Name nvarchar(25),
Address nvarchar(100),
City nvarchar(50),
Country nvarchar(25),
Birth_Date datetime
)

This would create a BCustomer table in the SQL Server Compact database.

Indexing a table column

We can use the following syntax for creating a column index.

CREATE INDEX "<index_name, sysname, sample_index>" ON "<table_name, sysname, sample_index_table>"("<column_name, sysname, c1>");

In the present database you can create an index for the City column in the OurCustomer table with the following statement.

Create index CityInd on OurCustomer(['City'])

This will create a non-clustered, non-unique CityInd index in the table.

Nested queries in SQL Server Compact 3.5

You can run nested queries (See also, derived tables in documentation) against the database as shown. In the first commented statement all columns are retrieved. In the second commented statement the First_Name is retrieved from all the columns retrieved by the inner query. The highlighted statement goes one more level deeper. Note that aliases (a and b in the highlighted query) are required for the nested queries to work correctly.

Summary

The article described the installation details of  SQL Server Compact 3.5. Connecting to SQL Server Compact in SQL Server Management Studio was discussed in detail. Starting from creating databases, managing tables; creating  indexes and running nested queries were briefly described.