How to insert values to identity column in SQL Server

Identity field is usually used as a primary key. When you insert a new record into your table, this field automatically assign an incremented value from the previous entry. Usually, you can't insert your own value to this field.

In this article, I am going to expose the tips for inserting your own value to this field. It is simple and easy. Consider you have the following Customer table.

CREATE TABLE Customer
(
 ID int IDENTITY,
 Name varchar(100),
 Address varchar(200)
)

Now, I am trying to insert a record into Customer table with identity field like as then I will get the error message as shown below.

INSERT INTO Customer(ID,Name,Address) VALUES(1,'Shakham','Delhi')

Allow insert into identity field

You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:

SET IDENTITY_INSERT Customer ON

Disallow insert into identity field

You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:

SET IDENTITY_INSERT Customer OFF

Insert Value to Identity field

Now, lets see how to insert our own values to identity field ID with in the Customer table.

SET IDENTITY_INSERT Customer ON
 
INSERT INTO Customer(ID,Name,Address) VALUES(3,'Rahul','Noida')
INSERT INTO Customer(ID,Name,Address) VALUES(4,'Rahul','Noida')
 
SET IDENTITY_INSERT Customer OFF
 
INSERT INTO Customer(Name,Address) VALUES('Rita','Noida')

After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.

Note

1. Usually, we use this trick when we have deleted some rows from the table and we want the data in a sequence.
2. After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF

Reseed the Identity field

You can also reseed the identity field value. By doing so identity field values will start with a new defined value.

Suppose you want to reseed the Customer table ID field from 3 then the new record s will be inserted with ID 4,5,6..and so on.

--Reseeding the identity
DBCC checkident (Customer, RESEED, 3)
 
INSERT INTO Customer(Name,Address) VALUES('Geeta','Noida')

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Difference between inner join and equi join and natural join

SQL join clause is used to to retrieve data from two or more database tables. 

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.

Inner Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;

Inner Join Result

tblEmp.Name

tblEmp.DeptID

tblDept.Name

tblDept.DeptID

Ram

1

HR

1

Raju

2

IT

2

Soya

2

IT

2

Sam

3

ADMIN

3

In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;
--Using Clause is not supported by SQL Server
--Oracle and MySQL Query
SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)

Equi Join Result

tblEmp.Name

tblEmp.DeptID

tblDept.Name

tblDept.DeptID

Ram

1

HR

1

Raju

2

IT

2

Soya

2

IT

2

Sam

3

ADMIN

3

Note

1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
2. Equi join only have equality (=) operator in the join condition.
3. Equi join can be an Inner join, Left Outer join, Right Outer join
4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

--Run in Oracle and MySQL 
SELECT * FROM tblEmp NATURAL JOIN tblDept

Natural Join Result

DeptID

tblEmp.Name

tblDept.Name

1

Ram

HR

2

Raju

IT

2

Soya

IT

3

Sam

ADMIN

In the above join result we have only one column "DeptID" for each pair of equally named columns.

Note

1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.
2. Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

SQL Server Naming Conventions and Standards

In programming, we have many naming conventions like camelCase, PascalCase, under_scores etc. But each and every organization has its own naming conventions. In this article, I would like to share some common and useful naming conventions and standards that you should use while programming with SQL Server.

Table

Tables are used to store data in the database. The naming conventions for a table may have a "tbl" prefix, followed by the table name. Moreover, TableName should be plural. The syntax should be "tbl<TableName>".

Examples are below:

tblEmployees
tblOrders
tblProducts 

Primary Key Constraint

Primary key is a field or a set of fields in the database table that uniquely identify records in the database table. A table can have only one primary key. The naming conventions for a primary key constraints should have a "PK_" prefix, followed by the table name. The syntax should be "PK_<TableName>".

Examples are below:

PK_Employees
PK_Orders
PK_Products 

Foreign Key Constraint

Foreign key is a field in the database table that is primary key in other table. The naming conventions for a foreign key constraint should have a "FK_" prefix, followed by the target table name, followed by the source table name. The syntax should be "FK_<TargetTable>_<SourceTable>".

Examples are below:

FK_Orders_Employees
FK_Items_Products 

Unique Key Constraint

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value. The naming conventions for a unique key constraints should have a "UQ_" prefix, followed by the table name, followed by the column name. The syntax for a unique constraint should be "UQ_<TableName>_<ColumnName(s)>".

Examples are below:

UQ_Employees_EmailID
UQ_Items_Code 

Default Constraint

Default Constraints insert a default value into the column when no value is provided for that column. The syntax for a unique constraint should be "DF_<TableName>_<ColumnName>".

Examples are below:

 DF_Employees_IsActive
DF_Employees_CreateDate

Check Constraint

Check Constraints defines a business rule on a column in the database table that each row of the table must follow this rule. The syntax for a unique constraint should be "CHK_<TableName>_<ColumnName>".

Examples are below:

 CHK_Employees_Salary
CHK_Employees_DOB

User Defined Stored Procedures

Stored Procedures are executed on the server side and perform a set of actions, before returning the results to the client side. This allows a set of actions to be executed with minimum time and also reduce the network traffic. While creating user defined stored procedures we should avoid the prefix "sp_" with the name of procedure. Since "sp_" prefix is already fixed for system defined stored procedures. The naming conventions for user defined stored procedure may have a "usp_" prefix, followed by the action and objects of the procedure. The syntax for a user defined stored procedure should be "usp_<Action>_<Object>".

Examples are below:

usp_Insert_Employees
usp_View_EmployeeOrders 

User Defined Functions

Functions are a set of SQL statements that accepts only input parameters, perform actions and return the result.Function can return only single value or a table. The naming conventions for user defined functions may have a "fn_" prefix, followed by it's action. The syntax should be "fn_<Action>".

Examples are below:

fn_CalulateTax
fn_CalculateAge 

Views

Views are like a virtual table that can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. The naming conventions for a view should have a "vw_" prefix, followed by the namespace, results. The syntax should be "vw_<Result>".

Examples are below:

vw_EmpOrderDetails
vw_SalesProductDetails 

Triggers

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. The naming conventions for a trigger should have a "trg_" prefix, followed by the action, and the table name. The syntax should be "trg_<Action>_<TableName>".

Examples are below:

trg_Ins_Employee
trg_Upd_Employee
trg_Del_Employee 

Indexes

Indexes are database objects that help the SQL Server query engine to find the desired data. Indexes may attached to tables or views. The naming conventions for an index should have an "IX_" prefix, followed by the table name and columns. The syntax should be "IX_<TableName>_<Column(s)>”.

Examples are below:

IX_Employee_Name
IX_Employee_NameMobileNo 

What do you think?

I hope you will enjoy these tips while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Swap the values of two columns in SQL Server

Do you have fun with SQL Server?. Let's start fun with SQL Server. Suppose you want to swap the values of two columns of a table in SQL Server, how could you achieve this tricky task?. Actually, it is simple and so funny.

Suppose you have a Customer table in the database with the following data and you want to interchange the values of columns Name and Address then how do you do?

 SELECT * FROM CUSTOMER 

Don't worry, to do this task, you need to just write a simple update query for Customer table like as :

 UPDATE Customer SET Name=Address , Address=Name 

Now After query execution you will find the the values of columns Name and Address have been interchanged, that's so cool.

 SELECT * FROM CUSTOMER 

Note

1. Data type of both the columns should be same and also length should be enough to hold the swapped column data other wise data will be truncated.

What do you think?

I hope you will enjoy this trick while having fun with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.