SQL Server Identity Columns Tips

Inserting into Identity column
There are times when we need to insert values in SQL Identity column. SQL Server does not allow us implicitly to insert value into Identity columns. But SQL Server allows us to do so explicitly. For example if you have a table named Employees with EmpID column as identity column, and you want to insert values into it, you have to execute the following command before the insert command.

SET IDENTITY_INSERT Employees ON

Alter this command you can insert values into the identity column using insert command.

Once you are finished with insert commands you have to switch Identity off with the following command.

SET IDENTITY_INSERT Employees OFF

Note: The important thing to keep in mind here is that at any given point of time in one session there can be only one table which can have identity ON

Reseeding the identity column value:

You can reseed the indentity column value, that meand you can restart or start at a new predefined value by using DBCC CHECKIDENT.  For example, if you have a table named Employees and you want to reseed the indentity column to 1 I would execute the following:

DBCC CHECLIDENT (Employees, reseed, 0)

Here u must have noticed that the to set the value to 1 I have set the value to 0.

DB Design Stages

Here in this post I will explain different stages in DB design. Most of us implement these stages but are hardly aware of the same. There are three stages in DB design Conceptual Design, Logical Design and Physical design. Now let’s have a look at each stage one by one.

Conceptual Design: – This is the first stage in the DB designing. Depending on clients requirement first you define the conceptual model involves with only identifying entities and relationship between. Fields/Attributes are not planned at this stage. It’s just an identifying stage but not in detail.

Logical Design: – This is the second stage and is involves in actually identifying the attributes, primary keys, many-to-many relationships etc of the entity. In short it’s the complete detail planning of what actually has to be implemented.

Note: – Conceptual and logical model are mostly done with Visual tools like VISIO and in most of the cases we combine these models in one time. So we are not be able to distinguish between both.

Physical Design: - Physical model is where we actually develop structure like tables, fields, primary keys, foreign leys etc. You can say it’s the actual implementation of the project.