Saturday, October 06, 2007

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.














13 comments:

shital said...

Hey this post was really helpful.I got my answer thru this post for not able to create database.
Thanks a lot.
Shital

Ram Sharma said...

nice article. I was scratching my head for vista issue. Hope it will resolve my issue.

~Ram

Ram Sharma said...

nice article. I was scratching my head for vista issue. hope it will solve the issue.

~Ram

Anonymous said...

Many thanks for posting this - the article was very helpful, informative and easy-to-follow. Much appreciated

Michael Jackson...Who's Bad? said...

Excellent artical, it's been a real help in learning how to use MS SQL 2005. If there are anyother articals like this for MS SQL 2005 could you please make a post with the URLS.

Thank you very much again!
Mark

gopal said...

hi,

nice article for beginners.

It helps me alot


gopal

Lashaq said...

Its realy helpful in making database in sql server 2005 but a problem here, when I follow all these steps and want to create a relationship betweeb tables, I am able to select only one table, there is no any table displaying when I click on add button after clicking on relationships. PLease clarify this. Also when I proceed with this only one table an I select same key in different tables and press ok then a message appears that selected tables must have equal number of columns.

Anonymous said...

thanks bundle bud

Arun Adur said...

looks good anyone learn easily

thanks
arun

jireh said...

very helpful :)

praveen said...

niceeeeeeeeeeeeeeeeeeeeeeeee

Prabhu Palaniappan said...

nice article.... it helped me ....

Ujala Dwivedi said...

its really a helpful article
n i really learn so much from this article specially how to make relationship between two tabels..
so thank you so much for publishing these type of articles.....

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape