MS-SQL server express is good database for developers, hobbyists and it includes all the features that you need to develop database for website or VB/VC application. If you’re using access for your visual basic application then switching to MS-SQL is easy & beneficial. It is also available as free of cost and you can get support for this product on MSDN and its respective forums. This article will discuss MS-SQL express in few easy steps.
Getting Started
This article discusses MS-SQL express 2005, if you’ve downloaded 2008 express then there are some changes or functionality update that will differ as per discussion in this article. Please Note: Before installing MS-SQL express make sure you’ve .Net framework 2.0 or above.
Note: If your system has disabled the WMI service then you should Start WMI (Windows management instrumentation) otherwise you are unable to install SQL express.
If you’re unsure about your needs and don’t know which package to download for MS-SQL express then you should download the 2 packages:
1. MS-SQL database engine (Configuration Tool)
2. MS-SQL management studio express
Install these packages in same order as mentioned above. After successful installation you’ll see the application installed as:
Start>All programs>Microsoft SQL server >
• SQL management studio express
• Configuration tools
SQL Server Management Studio
In order to create database using MS-SQL follow the steps mentioned below:
1. From the Start menu, select All Programs > SQL Server 2005 > SQL Server Management Studio.This will open up the connection dialog as shown below:
Server name: <yourservername>/SQLEXPRESS
Sever type: Database engine
Authentication: Windows Authentication
Click on connect so that MS-SQL connects with the specified server using the authentication provided. Make sure you select windows authentication whenever possible. Besides, SQL authentication service is disabled by default by configuration tool. You can use SQL Server Configuration manager to start and stop the services.
Once you connect to the server, SQL server management studio window will open. This window is two pane window with object explorer and summary. In object explorer window you’ll see the nodes with following: Database, Security, Server object, Replication and management. These same nodes can be seen on summary window.
To create database:
1.Right-click the Databases node in the Object Explorer and select New Database.
2.Type <databasename> for the database name and then click OK.
3.Expand the Databases node in the Object Explorer and verify that the database is present. If not recreate the database or rename the same if you want.
4.To delete the database, Right-click the same database again and select Delete. Confirm the action by clicking OK in the Delete Object dialog.
Now you can easily create, rename and delete the database.
Query editor
Click on “New Query” button to start query editor. A new tab with query interpreter starts on the other side of summary. Make sure in summary Master database is selected. You can test if it shows you the selected databases. Use the following command in the query editor:
SELECT *FROM sysdatabases;
After this you can hit the F5 key or execute button to compile the SQL query. You can see the result in result and message tabs. If you want to view the result on text file you can do so by pressing Result to File > Press F5 or execute button. This will open up dialog box asking you to save the file with the filename, after you save the filename with suitable name. It can be open with any text editor, as it is saved with .rpt extension.
When you add more and more SQL statement inside query editor, make sure new statements are highlighted using cursor otherwise when you press F5 will execute previous queries as well.
You can even drag and drop objects inside the query editor like table or database etc. Comments can’t be executed & can be written as /* */ or two dashes –.
If you know SQL language then you will be able to create your database easily using MS-SQL. As most of the queries are same with the MS-SQL, still we’ll have a sneak peak into some of the queries.
Data definition language
Data definition language allows us to create or destroy database objects. While writing identifiers make sure that you don’t use the reserved keywords or violate any variable or identifier naming rules. You can use the following SQL commands with query editor:
1. CREATE is used to create tables or databases.
2. DROP lets you remove the table or database.
3. ALTER can be used to change or modify the elements from table or database.
Data Manipulation language
Data manipulation language allows you capability to modify or manipulate your database or table. You can use the following queries in query editor:
1.SELECT can be used to view the table or particular rows in the database.
2.INSERT allows you to insert the values into the rows using FROM clause.
3.UPDATE can be used to update the existing values from the records/rows.
4.DELETE allows you to delete the particular row or column from record. If you want to delete the individual records then you should use this command.
So let’s create simple table with booklist which has ISBN number and book name. Write down the following code inside your query editor:
SELECT *FROM book;
INSERT INTO book values (1024,’TCP-IP’);
INSERT INTO book values (21,’FTP’);
INSERT INTO book values (25,’SMTP’);
This will create simple table something like shown below:
ISBN name
———– ——-
21 FTP
25 SMTP
1024 TCP-IP
More MS-SQL Express
You can learn more SQL database and its usage using following resources:
MS-SQL Home http://www.microsoft.com/sql/default.mspx
MS-SQL Object Explorer http://msdn2.microsoft.com/en-us/library/ms173849.aspx
MS-SQL server management studio
http://msdn2.microsoft.com/en-us/library/ms174173.aspx
If you’ve any problems or have any questions then you can go through MSDN website, forums and blogs. There is lot of information out there on MSDN.
I hope the above information helps. If you find any error or have any feedback please don’t hesitate to write it.
Leave a Reply