Data Definition Language

 Create Data Base:

Method 1:

create database db_test





Method 2: Using SSM UI




Drop Database: database should not be in use.

drop database if exists db_test



Database schema: A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects.


SQL Server provides us with some pre-defined schemas which have the same names as the built-in database users and roles, for example: dbo, guest, sys, and INFORMATION_SCHEMA.

The default schema for a newly created database is dbo, which is owned by the dbo user account. By default, when you create a new user with the CREATE USER command, the user will take dbo as its default schema.

Syntax to create schema:

create schema employee

The CREATE SCHEMA statement allows you to create a new schema in the current database.





Drop Schema: 

drop schema if exists employee


Schema will not be drop if any objects rely on it.

Alter schema:

The ALTER SCHEMA statement allows you to transfer a securable from a schema to another within the same database.

ALTER SCHEMA target_schema_name   

    TRANSFER [ entity_type :: ] securable_name;

In this syntax:

  • target_schema_name is the name of a schema in the current database, into which you want to move the object. Note that it cannot be SYS or INFORMATION_SCHEMA.
  • The entity_type can be Object, Type or XML Schema Collection. It defaults to Object. The entity_type represents the class of the entity for which the owner is being changed.
  • object_name is the name of the securable that you want to move into the target_schema_name.

ALTER SCHEMA employee TRANSFER OBJECT::dbo.employeetest; 


Create Table: Tables are used to store data in the database. Tables are uniquely named within a database and schema. Each table contains one or more columns. And each column has an associated data type that defines the kind of data it can store e.g., numbers, strings, or temporal data.


create table employee(emp_id int primary key identity,emp_name varchar(100) not null,salary decimal(5,2),dept_id int,constraint fk foreign key(dept_id) 
references department(dept_id))

Identity Column: 

Identity(seed,increment)

SQL Server does not reuse the identity values. If you insert a row into the identity column and the insert statement is failed or rolled back, then the identity value is lost and will not be generated again. This results in gaps in the identity column.

To insert explicit value for identity column used below code:

set identity_insert employee on


Sequence:

sequence is a user-defined schema-bound object that generates a sequence of numbers according to a specified specification. A sequence of numeric values can be in ascending or descending order at a defined interval and may cycle if requested.


CREATE SEQUENCE seq_no

    AS INT

    START WITH 10

    INCREMENT BY 10;

Sequence vs. Identity columns

Sequences, different from the identity columns, are not associated with a table. The relationship between the sequence and the table is controlled by applications. In addition, a sequence can be shared across multiple tables.

Alter table 

alter table emp

add name varchar(50) 

In this statement:

  • First, specify the name of the table in which you want to add the new column.
  • Second, specify the name of the column, its data type, and constraint if applicable.
Modify column data

ALTER TABLE emp

ALTER COLUMN [name] char(50);

If you want to add the NOT NULL constraint , you must update NULL to non-null first for 

  • create column with null 
  • update values
  • modify columns

Alter column:

ALTER TABLE emp

drop COLUMN [name] 

We can't drop column if any constraint applied.

create table price_list

(

price decimal(5,2) not null constraint check_p check(price>0)

)

alter table price_list

drop constraint check_p

Computed Column: 

SQL Server provides us with a feature called computed columns that allows you to add a new column to a table with the value derived from the values of other columns in the same table.

create table com_col(first_n varchar(50),last_n varchar(50))

ALTER TABLE persons

ADD full_name AS (first_n + ' ' + last_n);

Persisted computed columns

When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically. When you query the data from the persisted computed columns, SQL Server just needs to retrieve data without doing any calculation. This avoids calculation overhead with the cost of extra storage.

ALTER TABLE persons
ADD full_name AS (first_n + ' ' + last_n) persisted;

Drop table:

drop table if exists com_col

Drop a table with a foreign key constraint example

SQL Server does not allow you to delete a table that is referenced by a foreign constraint. To delete this table, you must drop the referencing foreign key constraint or referencing table first. 

Truncate Table:

Truncate table emp

The TRUNCATE TABLE has the following advantages over the DELETE statement:

1) Use less transaction log

The DELETE statement removes rows one at a time and inserts an entry in the transaction log for each removed row. On the other hand, the TRUNCATE TABLE statement deletes the data by deallocating the data pages used to store the table data and inserts only the page deallocations in the transaction logs.

2) Use fewer locks

When the DELETE statement is executed using a row lock, each row in the table is locked for removal. The TRUNCATE TABLE locks the table and pages, not each row.

3) Identity reset

If the table to be truncated has an identity column, the counter for that column is reset to the seed value when data is deleted by the TRUNCATE TABLE statement but not the DELETE statement.



Subscribe to: Posts (Atom)