About Tables In DataBase By Sagar Jaybhay

About Tables By Sagar Jaybhay

Tables are database objects which contain data in the database.  The table logically organized as row and column format like spreadsheet. Row in table represents the unique record and column represent field in that record.

How many tables are allowed in the database?

It is actually how many objects are allowed In the database and value is 2,147,483,647.

How many columns does the table contain?

A user-defined table may contain 1024 columns.

How many rows does the table contain?

There is no limitation on rows it is directly proportional to the storage capacity of that machine.

Different types of Tables in SQL Server?

  1. Partitioned table
  2. Temporary tables
  3. System tables
  4. Wide tables

Partitioned tables:

It means a table whose data is horizontally divided into units. These units may spread across different or more than one file-group in the database. Using partitioned it makes large table or index more manageable and you can access subset of data quickly. By default SQL server 2019 supports 15,000 partitions.

Temporary Tables

  1. These temporary tables are stored in tempdb. In these 2 types of temporary tables, one is local and another is global. The difference between these 2 as the name suggests which is visibility, availability.
  2. Local:- Local temporary tables have a single # sign as the first character of their name. these local tables are visible for the current connection for this user and it is deleted when the user gets disconnected from the SQL server.
  3. Global: these global tables have two # sign before the name start like ## as the first character of their names and it is visible to any user irrespective to the user-created that table or not and this table gets deleted when all user who uses this table gets disconnected.

System Tables

In SQL server these tables store data that defines the configuration of a server and all its tables in a special set of tables known as system tables. User can-not directly update or query these system tables.

Wide Tables

These tables use sparse columns that are used to increase total columns and these tables have up to 30,000 columns.  These sparse columns are like ordinary columns who optimize for null values and it reduces the space requirement for null values but has more overhead to retrieve that null. The maximum size of a wide table row is 8019 bytes. This means most of data in any row should NULL.

How to create a table in SQL Server?

There are 2 ways one is graphically and another is by using query we will see by using query

create table Person(ID int not null Primary key,name varchar(100),email varchar(100),genederID int)

This is a query for creating the table and created table looks like below

create table tbGeneder(genederID int primary key, gender varchar(100))

above query create tblGender in a database whose name is temp which we selected. Remember one thing when you select some database and execute a query it runs against that database but if you on A database and want to execute a query on B database then use below line first

use [database_name]
Go

This is used to select the database.

Now in the below query, you can see we create the ID field as primary key.

create table Person(ID int not null Primary key,name varchar(100),email varchar(100),genederID int)

In this person table ID is our primary key which means by using the primary key you can identify the row uniquely and it’s value never repeated or duplicated. The primary key column never allows Null value in that.

alter table Person add constraint tbl_person_geneder_id_fk
foreign key (genederID) references tbGeneder

Now you can see the above query where we use alter table syntax to create a foreign key means it referencing the two tables.

why we use foreign keys because it is used to enforce database integrity. In simple word a foreign key in one table is referred to primary key in another table. Why foreign key is used?

It is used to prevent invalid data from inserted in foreign key columns. When you mark a column as a foreign key then the table allows only values that are present in second table where we refer that field.

Now see below the image in that we enter 3 rows in that table.

When you insert values in the Person table and in that person tables gendered columns if you specify value other than this gender table then it will throw error so it will restrict you to not put invalid data.

insert into Person values(5,'sham1','sham1@gmail.com',4)

It will throw below error

The INSERT statement conflicted with the FOREIGN KEY constraint “tbl_person_geneder_id_fk”. The conflict occurred in database “temp”, table “dbo.tbGeneder”, column ‘genederID’

Point to remember NulL values are allowed in a foreign key column but not other than that base table.

See below image NULL value is allowed their

By doing all this foreign key constraint you able to make database integrity.

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts