Design SQL Server Compact Database with SQL Server management studio.

In previous section we have seen how we can use SQL Server Compact 4.0 Database. In this section we would see how we can create and edit and SQL Server Compact Database with SQL Server management studio. Lets start with SQL Server management studio, By default when you open the Management studio you would have two option for Server type and the options are follows

• Database Engine
• SQL Server Compact

We would select SQL Server Compact as our server type and click on database file combo, and it will bring up browse or create new option for the user. Select what ever suits your condition. For explanation I will choose create new for the time being.

After selecting the option you would get a dialog like the bellow screen shot. It is the database creation wizard. I am sure the options are pretty understandable to all. Browse and select the location where you wanted to create the database, if you have an existing database you can choose to override the existing database. Keep the destination Database Properties as follows. Next step is to provide a password for your database. you can choose to keep the password empty, in that case you would see a warning before created. Please choose the Encryption type and select ok. 

That’s it you would see a your database option created in object explorer and you are good to go for going ahead and design your database.

Let’s move ahead with the database table  creation wizard. The table creating wizard is pretty intuitive and a develop can easily understand. Right click on table node on object explorer and select new table option. Give a suitable name for your database table name, then fill the table columns, note that almost all the renowned data types are available, You can set you tables primary key from Primary key combo. And if you want to set the primary key as identity column just modify the column detail at bottom of the wizard and set Identity to True. Click ok to finish creating your table.

Next step is to create relationship among the tables. for this you have to right click on table note and select table properties. This will bring up your table property window. There are four sections of this window, we would concentrate on Add Relations for now. Click on add relation page, next fill primary key table and columns that you want to map, next give a name to the relation and click add columns, this will add relation columns to relation section. click ok to finish and close the dialog.

Now verify that the relation actually added by opening table properties for a table again and visit the Manage relation page on Table properties window. In Manage Relations page you will find your table relation ship in relation name combo. if you wish to modify or delete the relation, you can do it from this window.

Note that while creating database with SQL Server management studio you will eventually create a SQL Server Compact 3.5 database, we would talk about SQL Server Compact 4.0 in near future. Till then happy programming and best of luck.