Data usually comes through raw format in an unorganized manner and processing of such data is called ‘Information’.
Our
Data are stored in Databases [db] consists set of Tables (Rows & Columns). A Table is an organized collection of data stored in the form
of Rows and Columns. Columns
can be categorized as vertical, while Rows as horizontal. The Columns in a
table are called Fields in records while the Rows can be referred to as unique Records.
For example, a Table that contains Employee data for a company might
contain a row for each employee and columns representing employee information
such as employee number, name, address, job title,
etc.
A Computer can have one or more than one instance of SQL
Server installed. Each instance of SQL Server can contain one or many
databases. Within a database, there are one or many object ownership groups
called schemas. Within each schema there are database objects such as tables,
views, and stored procedures. Some objects such as certificates and asymmetric
keys are contained within the database, but are not contained within a schema.
At a minimum, every SQL Server database has two
operating system files: a data file and a log file. Data files contain data and
objects such as tables, indexes, stored procedures, and views. Log files
contain the information that is required to recover all transactions in the
database. Data files can be grouped together in file-groups for allocation and
administration purposes.
The
number of tables in a database is limited only by the number of objects allowed
in a database (2,147,483,647). A standard user-defined table can have up to
1,024 columns. The number of rows in the table is limited only by the storage
capacity of the server.
Rules to create Database:
a) Must accumulate Mass
Storage.
b) Should Remove
Duplicate Data.
c) Multiple Users can
access Database.
d) Protect your data.
Users
are required to do regular tasks of managing / manipulating data in a System or
Server box which is called Database
Management System (DBMS).
Different
types of DBMS:
1) Centralized DBMS (Multiple Users access the Data in central
level)
2) Distributed DBMS (Multiple Databases distributed across
different locations can access by Multiple Users)
3) Cloud Database (Databases hosted in the Cloud e.g. Maria
DB, Azure SQL)
4) Personal Database (for Personal use)
5) Relational Database (e.g. RDBMS like MySQL)
6) Commercial Database (these are Premium or Pro version not Open
Source -- e.g. Oracle, Mongo DB)
7) Graph Database (creating and manipulating graphs – (e.g.
Neo4j, ArangoDB)
Besides the standard role of basic user-defined tables, SQL
Server provides the following types of tables that serve special purposes in a
database:
a)
Partitioned tables are tables whose data is horizontally
divided into units which may be spread across more than one filegroup in a
database. Partitioning makes large tables or indexes more manageable by letting
you access or manage subsets of data quickly and efficiently, while maintaining
the integrity of the overall collection. By default, SQL Server supports up to
15,000 partitions.
b)
Temporary tables are stored in tempdb. There are two types of temporary tables: local and global.
They differ from each other in their names, their visibility, and their
availability.
Local temporary tables have a single number sign (#) as the
first character of their names hey are visible only to the current
connection for the user, and they are deleted when the user disconnects from
the instance of SQL Server.
Global temporary tables have two number signs (##) as the
first characters of their names; they are visible to any user after they are
created, and they are deleted when all users referencing the table disconnect
from the instance of SQL Server.
No comments:
Post a Comment