SQL SERVER - Notes

In this article we are going to see some important notes of SQL SERVER, Database is one of the important thing in IT industry, to store data's but not the Database alone can survive with out the front end application. So database alone can't survive. But any one can run the life in there whole carrier by knew the few things in Database in a single company or multiple company. Even they can rise there designation in the carrier by knew the few things in DB. So from this post up to ten post , I will cover the basics and intermediate of SQL SERVER, Which will helpful for the new comers who enter the IT Industry as well as who fears to take the Technology Domain. Querying Database is the one of the easiest thing in IT industry.

First Let we start from the some notes.

1. Difference between SQL SERVER 2005 and 2008 ?

SQL SERVER 2005

1. Xml datatype is introduced
2. Encryption of entire database is not supported.
3. Datetime is used for both date and time.
4. No Table data type is present 
5. CMS is not available
6. PBM is not available

SQL SERVER 2008

1. Xml datatype is used
2. Encryption of entire database is supported
3. Data and Time have separate datatypes
4. Table datatype is present
5. CMS (Central Management Server is available)
6. PBM (Policy Based Management is available)

2. What is the Maximum storage available in Varchar(max) and NVarchar(max) ?

       Varchar(max) is used to store the 2 GB of Data and Nvarchar also stores the 2GB of Data but in Nvarchar each character takes the 2 byte to store.

3.What is BLOB ?

       BLOB - Binary Large Object , is a large file typically a image

4. Data types present in SQL SERVER 2008 ?

         Let we see some of the data types from the list ,

      Date

      Time

      Datetime

      int

      smallint 

      bigint

      char

      nchar

      varchar

      nvarchar

      binary

      varbinary

      cursor

      table

      xml

      uniqueidentifier

      text

      image

5.   What is a Database ?

        Database is consists of  many tables, which is used to store the data in rows and columns format.

6. What is a Server ?

        Server or instance which consists of multiple database, Server have an engine to run the query against the database.we can interact between the two servers by create a linked server from one server to other.

In a computer we can install various sql server engine in a computer , to access a specified sql server we have to use the sql server name along with the computer name. eg : rajesh-pc\sqlexpress

7. What are modes present to connect the SQL SERVER ?

     There are two modes present in SQL SERVER 

      1. Windows Authentication    - uses windows password

      2. Sql Server Authentication   - uses the user specified name and credentials.

8. what is the varchar(n) and varchar(max) ?

        varchar(n) - Stores the n bytes length + 2 bytes

        varchar(max) - Stores the max bytes + 2 bytes.

9. What are the services present in the SQL SERVER ?

       Integration service, Reporting Service, Analysis Service,Full Text Search, Sql Server Agent, Sql Browser etc.

10. Create a Database :

      Now we create a HRMS DB to hold the HR details and tables.

      CREATE DATABASE HRMS

   USE HRMS

      

11. Create a Table :
      Now we are going to create a two tables one for employee another for maintain the employee salary details, how we are going to link the two tables based on the employee id. Create a id column in employee table as primary key , i.e no repeatation of values in the column values must be unique not allow any empty values, now create a employee salary table to id column as foreign key i.e this column refers the employee table id column because if the id is exists in the employee table then only it allows to insert the record in the EMPLOY_SALARY table otherwise it shows error that values is not present in the primary key column.

CREATE TABLE EMPLOYEE

  (

    ID      INT IDENTITY(1,1) PRIMARY KEY,

    NAME    VARCHAR(40),

    ADDRES  VARCHAR(250),

    CONTACT INT

  )

INSERT INTO EMPLOYEE(NAME,ADDRES,CONTACT)

VALUES ('SIVA','CHENNAI',222222)

INSERT INTO EMPLOYEE(NAME,ADDRES,CONTACT)

VALUES ('DANIEL','HYDERABAD',224332)

INSERT INTO EMPLOYEE(NAME,ADDRES,CONTACT)

VALUES ('LINC','US',72233)

CREATE TABLE EMPLOY_SALARY

  (

    ID INT FOREIGN KEY REFERENCES EMPLOYEE(ID),

    SALARY INT,

    GRADE CHAR(1)

  )

INSERT INTO EMPLOY_SALARY(ID,SALARY,GRADE)

VALUES (1,80000,'D')

SELECT * FROM EMPLOYEE