SQL Server Data Type and Operators

                     Data types in SQL Server

        ---------------------------------------------------------

1. Number Type :

-------------

          Data type                                    Size

        --------------                                 ---------

     ->  tinyint                                        1 byte

     ->  smallint                                      2 byte

     ->  int                                               4 byte

     ->  bigint                                          8 byte

     ->  float(n)                                       4 byte

     ->  decimal(p,s)                               12 byte

     ->  smallmoney                                4 byte

     ->  money                                         8 byte

     ->  numeric(p,s)

2. String type :

----------------

     ->  char(n)                Defined width , Maximum 8,000 characters

     ->  varchar(n)           Variable width character string,  Maximum                                       8,000 characters

     ->  varchar(max)      Maximum 1,073,741,824 characters 

     ->  nchar                         

     ->  nvarchar

     ->  nvarchar(max)

     ->  text

     ->  ntext

  char data type is called fixed length i.e according to the given size than all the bytes will be used in a memory with this memory will be wastage.

varchar is called variable length based on the given characters only those bytes will be used in memory. It can be called as dynamic memory allocation.

char and varchar will works with an ASCII code. ASCII value will be up to 255. it will used 1 byte for 1 character. 

nchar and nvarchar will works with unicode. Unicode values will be upto 65535.

Unicode supports English and other popular languages like Freanch, Lattin, Greak etc. It will uses two bytes for one character.

nchar is called fixed length and nvarchar is called variable length. 

3. Binary type :

-------------------

    ->  bit              1 byte               true/false

    -> image          up to 2 gb

4. Date and time type :

----------------------------

   smalldatetime                  2 bytes            year upto 2079

   datetime                           4 bytes            year upto 9999

   date                                  3 bytes

   time                                  3 bytes

                              Operators In SQL Server

              ------------------------------------------------------------

  1.  Arithmetic Operators

--------------------------------

           +      Plus

            -      Minus

            *     Multiplication

            /      Divide

 2. Relational Operators

------------------------------

         >       greater than

         <       less than

         >=     greater than equal to

         <=     less than equal to

         =        equal to

         !=      not equal to

         <>     not equal to

3.  Logical Operators

----------------------------

         AND,  OR,   NOT

4. Special Operators 

--------------------------

    In

    Not In

    between

    Not between

    like

    not like
    isnull

    not isnull

Read More »

What is SQL Server ?

=>  What is SQL Server ?

------------------------------------------------------------------------------

                            SQL Server is a Client/Server relational database used to store large amount of data or information . SQL Server will works in stand alone computer and will works in any network.

Networks are classified into three types.

1) Intranet  (Local Area Network (LAN))

2) Extranet (Metropolitan Area Network (MAN))

3) Internet (Wide Area Network (WAN))

SQL Server can be used as back end tool or software for any .net project or application. It is used to store and manage the data related to application or projects.

=> Server type in SQL Server

------------------------------------------------------------------

           There are several type of Server in SQL.

1. Database Engine:

-----------------------

     It is default server type. It is used to store ,Manage  and to access the data from database. It will work with online transaction processing database. This server contains normalized data and it is used for current data store.

2. Analysis Services :

--------------------------

 It is used to work with data warehouse. It is used to maintain the data related to old business. It will work with online analytical process. this database contains de-normalized data and used to maintain historical data which is for 10-15 years.

3. Reporting Services :

---------------------------

It is reporting tool used to generate reports in various formats. It can contain report tittle, date & time , page number, Sub total,Grand total etc. And report can be exported to other formats like MS-excel, MS-Word, PDF etc.

 Type of reports:

--------------------

 a) Standard 

        It is used to display report in a form of list.

 b) Ad-hoc Report

       It is a report model on report template and the client can used the report only after necessary changes are made according to their requirement.

 c) Embedded reports

          These are integrated in projects. it can be windows application or website. 

4. Integration Services:

----------------------------

              It is used to transform or used to convert the data from one format to another format. It is used to convert SQL server tables to oracle, My SQL & Ms-Access tables.

In IS(Integrated Services) 27 transformation or conversion can be done and it is called ETL(Extract Transformation Load) tool.

                       System database in SQL Server

---------------------------------------------------------------------------------

1. master :

 ------------

   It is used to perform user and administrative task in sql server. And used to manage sql server i.e the programmer which are used to works with sql server will be in master database.

2. Model :

------------

   It is the template for new database on the properties of model database, New database will be created.

3. Msdb : (Microsoft database)

-----------

   It is used to store job history. job history will contain the SQL commands executed by the user will be store in job history. it is used for data recovery.

4. tempdb : (temporary database)

---------------------------------------

   when ever SQL server is started then tempdb will be created and when SQL server or management studio is closed then it will be deleted. It will exist as long as work with SQL server . It is used to store temporary tables and temporary stored procedure.

             Database Architecture

---------------------------------------------------------

 SQL server can contain 32767 databases. In database architecture every database will contains two files.

Read More »