Basics of SQL Server 2005

Lets begin with basics of SQL Server 2005

In this article you will learn

• How to Create Database in SQL Server 2005
• How to Create Table in SQL Server 2005
• How to Create Database Diagram in SQL Server 2005
• How to Create Relationship among Tables in SQL Server 2005
• How to Insert Data in SQL Server 2005
  

First of all logged in to SQL Server 2005 Database with Administrator rights as most of the stuff we gona do require admin rights.

Creating Database in SQL Server 2005

Right click Database Folder and click Database as shown in Figure.

Note: You can also download the script file and run in newly created database

Now type your desired database name and choose appropriate settings for your desired database. I have selected all default settings here and press OK button



So our "MySampleDatabase" Is created.

While working with Windows Vista
Note: You may receive error "An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)" CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

To overcome this error in Windows Vista you need explicitly logon as Administrator as shown in figure

So after creation of Database it consist of various database object as shown in figure.



Creating Tables in SQL Server 2005 database

Now, right click the newly created MySamplePractise Database and add new table


This open the table in design mode, Lets create customer table and choose appropriate datatype, if you are not sure about choosing the correct datatype in sql server 2005 refer this article



Here, CustomerID is Identity field, that is Auto generated by system. It is similar to AutoNumber field in MS Access and Sequence in Oracle.

Understanding Identity Specification Attribute.

• (Is Identity) - Yes : will generate autonumber
• Identity Increment - It is number of increment you want from the previously generated ID, here i have taken 1.
• Identity Seed - It commands SQL Server 2005 from where to start generating autonumber. Here, I have taken value 1000, so it will generate autonumber from 1001

Constraint
Constraint is a check which is made on table which must be followed by user entering data. It reduce the number of Invalid data feed-in into system by prechecking its validity.

In this table 2 constraint are used that is "Allow Null" and "Primary Key"

• Allow Null Constraint - If checked than will Allow Null values in field, else it will not allow you to bypass the column without entering data.
• Primary Key Constraint - It checks that each record in table is unique and not null. To refer more on Keys
  

Creating Primary Key on Column of SQL Server 2005 Right click the column field and set the field as Primary Key



Now in similar fashion create Order Table, OrderDetails Table, Product Table, ProductType Table.

Order Table


OrderDetails Table


Product Table


ProductType Table


so your object explorer looks as under





Creating Database Diagram in SQL Server 2005




Next step select Table and press Add button




Adding a releationship among tables

Right click the "Many" Table as They don't allow to change foreign key table in Releationship dialog.

Example: Click on Orders Table, as 1 Customer can place Many Orders




Foreign Key Relationship dialog box will appear, press add button and you will find details appear on right pane of dialog box.



Now click the open dialog button on right of "Table And Columns Specification" from right pane of dialog box.

For creating relationship between Orders and Customer, CustomerID is used. Select Table "Customer" for Primary Key Table and table "Orders" for Foreign Key.



And press ok button to create relationship between two table.

Advantage of creating relationship

• It will not allow to enter orders details without customer information. That is it allow to enter orders details of valid customer.
• It will not allow to delete customer information, until all details associated with customer is delete.
• You may change setting as per your need by selecting No from dropdown of "Enforce for Replication" and "Enforce foreign key constraint"

Similarly you need to create relationship between remaining tables.

• 1 Customer can place Many Orders
• 1 Order can have Many OrderDetails
• 1 Product can be in Many OrderDetails
• 1 ProductType consist of Many Product

At the end Relationship diagram looks as under



When you are dealing with large database with 100's of table, you can change the view of table by selecting all table with "ctrl+A" and right clicking any of selected table and change its views so that it is convinent to view.



A sample diagram will now look as under



So now as Database Designing and Table creation is done, lets enter some sample data

Inserting Record in Table for SQL Server 2005, right click the table and select open table, it gives you MS Access type editor to enter data. Note those who prefer inserting data through SQL Query or Script may open SQL Editor. Similarly making change in table design click modify.




Now key-in the data in customer table, as shown in figure. Note: here CustomerID is generated automatically and its value is starting from 1001 as we have set Increment Seed to 1000.

Insert Record into Customer Table.


Similarly enter data for other tables.

Remember, you don't need to enter data into CustomerID, as it is Identity field wherein data is auto generated by system. As we have enforce Referential Integrity by creating relationship, so you are not allowed to add record in orders table with anonymous customerId.