MS SQL Server is a database management system developed and owned by Microsoft Corporation. The Sql Server database system was originally developed and implemented by Sybase Inc. Microsoft licensed this DBMS in 1988 for OS/2 operating system and began implementing it for Windows Operating System in early 90’s and further development of Sql Server for OS/2 was cancelled.
In April 1994, Microsoft ended their cooperative agreement with Sybase and then came up with MS Sql Server. MS Sql Server runs exclusively under Microsoft Operating Systems windows 2000/2003 server and XP.
SQL Server was designed as a client/server DBMS. A client provides one or more different requests to server. The server processes this request and sends the result back to the client.
SQL Server – Versions
1. SQL Server 4.0
2. SQL Server 4.2
3. SQL Server 6.0
4. SQL Server 6.5
5. SQL Server 7.0
6. SQL Server 8.0 or SQL Server 2000
7. SQL Server 9.0 or SQL Server 2005
8. SQL Server 10.0 or SQL Server 2008
SQL Server – Editions
1. Enterprise Edition
2. Standard Edition
3. Developer Edition
4. Workgroup Edition
5. Mobile Edition or Compact Edition
SQL Server – Features
1. Security – Provides login and password to interact with the data in database.
2. Data Redundancy – Less Data Reputation
3. Data Integrity – Data Validation
4. Backup and Recovery
5. GUI Based Software
6. Low Cost
7. Easy to Install and Maintain
Requirements to install SQL Server 2005 in your work station:
1. Operating System: Windows XP/Windows 2000/NT/Professional, Vista
2. Primary Memory: 512 MB RAM or above
3. Secondary Memory: 1GB or above
4. Processor: 500 MHz or above
5. Internet explorer: 5.0 or above
6. Windows Installer: 3.1
7. .Net Framework 2.0
Data Types in SQL Server:
Data Type means the type of data which users provide to a specific column.
In SQL Server 2 types of data types available this includes
1. System Defined Data Types
2. User defined Data Types
System Defined Data Types:
SQL Server has predefined data types called System Defined data types or Built-in data types. These are again categorized into 4 types:
a. Numeric Data Types
b. String Data Types
c. Date Time Data Types
d. Special Data Types
Numeric Data Types:
These data types are used to provide the numeric information for the columns, these includes
Data Type
|
Size
|
BIT
|
0 or 1
|
TINYINT
|
1 BYTE
|
SMALLINT
|
2 BYTES
|
INT
|
4 BYTES
|
BIGINT
|
8 BYTES
|
REAL
|
4 BYTES
|
FLOAT
|
8 BYTES
|
DECIMAL (P, S)
|
5-17 BYTES
|
TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where REAL and FLOAT are called Approximate Numerics.
String Data Types:
These data types are used to provide character information for the columns, these include:
Data Type
|
Size
|
CHAR [(N)]
|
1 BYTE
|
VARCHAR [(N)]
|
1 BYTE
|
TEXT
|
16 BYTES
|
VARCHAR(MAX)
|
16 GB
|
Data Types in SQL Server:
Data Type means the type of data which users provide to a specific column.
In SQL Server 2 types of data types available this includes
1. System Defined Data Types
2. User defined Data Types
System Defined Data Types:
SQL Server has predefined data types called System Defined data types or Built-in data types. These are again categorized into 4 types:
a. Numeric Data Types
b. String Data Types
c. Data Time Data Types
d. Special Data Types
Numeric Data Types:
These data types are used to provide the numeric information for the columns, these includes
Data Type
|
Size
|
BIT
|
0 or 1
|
TINYINT
|
1 BYTE
|
SMALLINT
|
2 BYTES
|
INT
|
4 BYTES
|
BIGINT
|
8 BYTES
|
REAL
|
4 BYTES
|
FLOAT
|
8 BYTES
|
DECIMAL (P, S)
|
5-17 BYTES
|
TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where REAL and FLOAT are called Approximate Numerics.
String Data Types:
These data types are used to provide character information for the columns, these include:
Data Type
|
Size
|
CHAR [(N)]
|
1 BYTE
|
VARCHAR [(N)]
|
1 BYTE
|
TEXT
|
16 BYTES
|
VARCHAR(MAX)
|
16 GB
|
CHAR [(N)]: It is a fixed length data type, which occupies by default 1 byte memory. When we specify the N value it occupies N bytes of memory where N is an integer. It follows static memory allocation process.
VARCHAR [(N)]: It is a variable length data type, which occupies by default 1 byte memory. When we specify N value then it occupies N bytes of memory when N is an integer. It follows dynamic memory allocation process.
Note: The maximum limit for N is 8000, if it is more than 8000 characters we will use TEXT or VARCHAR (MAX).
Date Time Data Type:
These data types are used to provide date oriented information to the columns, these includes
Data Type
|
Size
|
Range
|
SMALLDATETIME
|
4 BYTES
|
1900-01-01 to 2079-06-06
|
DATETIME
|
8 BYTES
|
1753-01-01 to 9999-12-31
|
Special Data Types:
These data types are used to provide miscellaneous information to the columns, these includes
Data Type
|
Size
|
SMALLMONEY
|
4 BYTES
|
MONEY
|
8 BYTES
|
IMAGE
|
16 BYTES
|
VARBINARY (MAX)
|
Unlimited
|
SQL_VARIANT
|
8016 BYTES
|
Data Bases in SQL Server:
In SQL Server there are two types of databases available.
1. System Defined Databases
2. User Defined Databases
2. User Defined Databases
System Defined Databases: Complete system level information is stored in system databases. SQL Server uses system databases to operate user databases. These are also known as Pre-defined databases or built-in databases, these includes:
a. Master Database
b. Model Database
c. MS Database
d. Temp Database
e. Sample Databases
1. Adventure Works
2. Adventure Works DW
Master Database: The master database is the “database for all databases”. It keeps track of logon accounts, linked servers, system configuration settings and more. It also contains initialization settings for SQL Server.
Model Database: Model is very special database, anything that you place in model db is automatically propagated to all the databases that you can add a state table to model. That state table then appears in all the new databases. You can include almost any object in Model db.
MS Database: The MSDB (Microsoft Database) database is used as an agent between user and SQL Server meantime it concentrates on Back and Recovery process.
Temp Databases: Temp Databases is used to hold temporary objects which are generated automatically when user starts working with SQL Server.
User Defined Databases: SQL Server enables users to create their own databases. Those are called User Defined Databases.
Introduction to Management Studio:
The SQL Server Developer’s and Administrator’s primary tool for interacting with system is SQL Server Management Studio.
Dialog box which appears right after choosing SQL Server Management Studio.
1. For Server type and Server name we need to enter the details.
2. We have 2 authentications Authentication list box
a. Windows Authentication: You will be connected to SQL Server using windows account.
b. SQL Server Authentication: You will be connected to SQL Server using owned credentials.
Note: Default login name is sa and password sa123
After connecting to SQL Server, then it appears as follows:
Click on New Query (available under file menu) to get Query Editor in which you can create queries.
No comments:
Post a Comment