Know about SQL server Objects

Introduction to SQL server Objects

In databases, when we talk about database objects, it means we are talking about SQL Tables. SQL Tables are also known as SQL Objects which manages all the records in a specific order in SQL server database. The data, stored in a Table are managed in a column and row manner. Each column in a Table stored a specific value. Each row and column must be unique in a Table. By assigning a key constraint on the table you can uniquely specify each record in a table and can also show the relationship between one or more tables. You can have a number of Tables in SQL Server database, depends on its storing capacity. The Tables are arranged by the database Schema.

Role of Tables in SQL server

A table is everything in a SQL server database. It contains information in a specific order and provides easy accessibility to the user through SQL queries. SQL queries are used to perform create, drop, alter, duplicate, rename..etc Tasks on the Table.

• The Create command creates a table in SQL server.
• The drop command deletes the table with its structure.
• The alter command in SQL server modifies the structure of a table.
• Through duplicate command you can create a new table, which holds the same properties as old one.
• Rename command is used to rename an existing table.

Through SQL commands we can also perform the insert, delete, update…etc tasks on Table elements.

Create Table Syntax in SQL Server

CREATE TABLE table_name
(
Column _name 1 data _type (size),
Column _name 2 data _type (size),
Column _name 3 data _type (size),
. . . . . 
);

Types of Tables in SQL server

For a superior result and a good accessibility, SQL server basically uses four types of tables.

1. Partitioned Tables
2. Temporary Tables
3. System Tables
4. Wide Tables

From a small introduction, let’s consider the basic concept of each table.

1.Partitioned Tables

To quickly access to data, Tables are divided horizontally in SQL server. Thus may be Tables can be expanded across more than one file group in SQL server database. Partitioning makes more accessible to large tables and indexes.

2.Temporary Tables

SQL server uses two types of temporary tables. The first one is Local temporary tables which have only one sign (#) as the first character of their names and they only visible until the connection is not closed by the user. The second one is global temporary tables, which have two signs (##) as the first character of their names and they are visible whether the connection is closed or not. Both tables are stored in tempdb.

3.System Tables

System tables are those which cannot directly altered through a SQL query. The information of System tables is made available by the System Views. System tables are also used to track the database objects.

4.Wide Tables

It is a special kind of tables in SQL server, which uses column sets as well as sparse columns.It is important to limit both fixed and variable length data within 8019 bytes. One other thing is that the transaction or merge replications do not work with wide tables.

Tables Components

1.Field

A Field in a table is a single block or a set of blocks which contains records, related to the subject.

2.Record (row)

A record is a single or multiple rows, in a database table, which contains information according to table fields.

3.Column

A column is a single or multiple blocks, in a database table, which contains the information of a particular field in a vertical manner.

Constraints of a Table in SQL server

Constraints are the rules for a table or column, which provides accuracy, integrity and reliability of the data. There are two types of SQL constraints available in SQL server.

1.Column level constraints:column level constraints are those which applied only to one column.

2.Table level constraints:Table level constraints are those which applied to the whole table.

Some commonly used constraints of SQL server:

NOT NULL Constraints, DEFAULT Constraints, UNIQUE Constraints, PRIMARY KEY Constraints, FOREIGN KEY Constraints, CHECK Constraints and INDEX.

Note:

Using constraints in a table prevent invalid entries in the table. But it cannot prevent to corruption issues of a table.

Corruption issue in a SQL Table

Issue: Sometimes while fetching a record from a table in SQL server; it displays an error message 823. It occurs when the systems operating system failed to perform the input/output operations.

Recovery Options

Solution:To solve this error, you need to restore the database from a good backup. However, if you don’t have a good backup of your database, the CHECKDB provides an alternate way to repair this error.

The REPAIR_ALLOW_DATA_LOSS is used to repair these errors. This method can be applied under a transaction, which allows rolling back the changes, made by the user.

It doesn't mean that, this repairing option will solve all the Errors; even it takes too much time to resolve the problem. To solve these issues you need to have a third party tool like SysTools SQL Recovery tool to recover your lost and corrupted database.