SQL Server Terminology

Database – A database is an organized collection of data for one or more purposes, usually in digital form.

Relational Database – A relational database stores data in separate tables instead of placing all data in one large table..

Flat File Database – Before relational database this database exists. It has repeated information in the tables.

RDBMS –A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS’s use the Structured Query Language (SQL) to access the database. The leading RDBMS products are Oracle, IBM’s DB2 and Microsoft’s SQL Server

Normalization – It is used to design the relational database. It ensures that the data is stored at one place and is not repeated.

OLTP Database – OnLine Transaction Processing database is used for storing the data that is frequently updated in the database.

OLAP Database – OnLine Analytical Processing database stores the historical data. It is mainly used for business intelligence and for reporting purposes. Example, Datawarehouse /Data Mart. The data from OLTP is copied to OLAP database and is demoralized for the purpose of the reporting which is more efficient.

SQL – Structured Query Language is used for retrieving or manipulating the data in the relational database.

T-SQL – Transact SQL is the SQL Server’s version of the language. There are some standards that need to be followed for writing the language.

MSDE – Microsoft SQL Server Data Engine / Microsoft Data Engine / Microsoft Desktop Engine is a relational database management system developed by Microsoft, this is free version of SQL Server 2000 and can be used in an application which does not have a lot of concurrent users.

SQL Server Express – This is a free edition for SQL server 2005. This is installed when Database Engine is installed for SQL server. This has a tool called as SQL Server Management Studio Express.

Table – The data in relational database is stored in the form of tables. Data in flat file systems is stored in the files. Each table in the database contains particular information. Ex. employee table contains the information of all employees in an organization.

Column – Column has a name with which we can identify, the datatype – where the type of the data that need to be stored in the table and rules for maintaining the integrity in the table which are called as constraints for a table.

Row – row is a collection of items in all columns. Ex. one row in employee table contains the information related to an employee.

Primary Key – Column/Columns in a table that can be uniquely identify a row. If more than one column is specified then it is called as Composite Key.

Foreign Key – Column/columns that can be referred to a column/columns in another table.

Index – For faster retrieval of data from the table, indexes can be created on the table, as the index will scan the rows based on the B-Tree format.

Query – It is used for retrieving the data from the database.

T-SQL Statement –Statements are written in Transact structured query language for retrieving / manipulating the data in the database.

View – Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:

  • To focus, simplify, and customize the perception each user has of the database.
  • As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
  • To provide a backward compatible interface to emulate a table whose schema has changed.

 Store Procedure – Store Procedures contains the batch of SQL statements. These are mainly used for security and performance reasons.

UDF – User Defined Functions returns a value based on the parameters passed to the function. These are like subroutines/functions in other languages. This can also be used for performing the calculations based on the input and sending the output to the user. These can also returns the data in the table format.

DDL – Data Definition Language are used to define / alter the database objects such as tables, views, store procedures, UDF’s.

DML – Data Manipulation Language are used for select, insert, update and delete data in the database.

SELECT Statement – This is used for retrieving the data from the database.

INSERT Statement – This is used for inserting new rows into the table in the database.

UPDATE Statement – This is used for modifying the values of rows in a table in the database.

DELETE Statement – This is used to remove the rows from a table. If where clause is used then only the rows that satisfy the condition in the clause will be deleted. If where clause is not used then all the rows in the table are deleted.

FROM Clause – In this clause from which object (table(s)) the data need to be retrieved is specified.

WHERE Clause – It returns the rows that are affected by the statement.

ORDER BY Clause – It returns the data in a sorted manner. By default the rows are sorted in ascending order.

Aggregate Query – This is used to group / summaries the data.

GROUP BY Clause – It specifies how the data is grouped when used in an aggregate query.

Leave a comment

1 Comment

  1. Hiya! I just want to give an enormous thumbs up for the
    nice data youve here on this post. I might be coming again to your
    blog for more soon.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: