Technically everything within a database is an object. However, let’s talk about some important database objects. But before going through, you must create a user database. In order to do so, you should open Management Studio, connect to your instance, right click on database and then create new. Follow on the screen instructions to create database.

Now you have created database, you must see number of

Table

SQL Server database stores every information in a two dimensional objects of rows and columns – this structure called table. In general, there are system tables and user tables.

Data types

Data types specify the type of data that can be stored in a column of a table. Data types are used to apply data integrity to the column. SQL Server supports many data type like char, varchar, integer, binary, decimal, money etc. You can also create your own data type (User defined datatype) using system data type.

Function

Microsoft SQL server allows you to create functions. These functions are known as User Defined Functions. It represents business logic using one or more transact SQL statements. It can accept parameter(s) and can return scalar data value or a table data type. It can be used in the SQL statement anywhere which is added advantage over stored procedure.

Index

Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record, to locate record. Index is used to speed up query performance. Kind of the indexes are clustered and non-clustered. Both exist as B-tree structure. We will talk about indexes in detail later.

Constraint

Using Constraint, SQL Server enforces the integrity to the database. It defines the rules that restrict unwanted data in the column. Constraints can be table constraints or column constraints. We will talk about constraints in detail. For now, a quick note about different constraints.

• Primary Key Constraint
• Foreign Key Constraint
• Unique Key Constraint
• Check Key Constraint

Stored Procedures

A stored procedure is a compiled set of Transact-SQL statements. The business logic can be encapsulated using stored procedure. It improves network traffic by running set of Transact-SQL statements at one go.

Trigger

A trigger is a special type of event driven stored procedure. It gets initiated when Insert, Delete or Update event occurs. It can be used to maintain referential integrity. A trigger can call stored procedure.

View

View can be created to retrieve data from one or more tables. Query used to create view can include other views of the database. You can also access remote data using distributed query in a view.

 

Users

Each database has users who are allowed to access data with in database. Its an identity of a login when its connected to database. A database user can have the same name as login. However there is no restriction. You can map a login with different database user name.

 

Schema

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

Synonyms

A synonym is an alias or alternate name for a table, view, sequence, or other schema object.

You must go and try creating each and every object which are listed above. You will get better understanding only after practicals. Once you are done, check your progess.