How to reset the sequence number of a table in Sql Server

The sequence numbers are used as primary keys in most of the table. Main purpose of this is to generate a unique sequential number. During the development, we would face many situations where we need to reset or reseed the sequence number. The syntax varies in different Database software like DB2, Oracle, Sql Server, MySQL and so on.

We are going to see how to reset this.

use 'Database Name'
dbcc checkident('Schema.Table',RESEED,0);

The above command will reset it to 0. The sequence will start from 1.

How to modify the table structure in SQL Server

Once the table is created in SQL Server, you can modify the table structure either through GUI or through script. I am going to list down the possible cases of updating the table structure

Add a column:

ALTER TABLE "TABLE-NAME" ADD "COLUMN-NAME" varchar(50) NULL

Modify the Data Type:

ALTER TABLE "TABLE-NAME" ALTER COLUMN "COLUMN-NAME" "NEW DATA TYPE" NULL

Modify the NOT-NULL Constraint:

ALTER TABLE "TABLE-NAME" ALTER COLUMN "COLUMN-NAME" VARCHAR(30) NOT NULL

Left or Right Padding a column in SQL Server

I had a task to retrieve a number column and do left padding with zeros. The total number of digit is 7. I used the REPLICATE method to achieve this.

For example, the empNum in an Employee table is 123 and I want to do the left padding with zeros thereby totalling it to be with 7 digits. It should look like 0000123.

Select REPLICATE('0',7 - LEN(empNum) + CAST (empNum AS varchar) as EmpNum from Employee

How to get the generated sequence/key after the insertion into DB

There is a table with multiple columns where one of the columns is an ID, which indicates the running sequence number. When a row is inserted into the Database, the DB will generate the sequential number and insert it. When the same operation is achieved through Java program and you want to use the generated sequence number for the consequence process in Java, you need to write the following code.

Connection con = getDBConnection();
Statement stmt = con.prepareStatement("Your SQL Query, Statement.RETURN_GENERATED_KEYS");
stmt.setString(1,"Input");

if(stmt.executeUpdate()>0)
{
    ResultSet keySet=stmt.getGeneratedKeys();
    if(keySet!=null && keySet.next())
    {
        id = keySet.getLong(1);
        keySet.close();
    }
}

How to left pad zeros for a column in Sql Server

I had a requirement where I need to left pad zeros for a column in MS SQL Server. I thought the below methods would be helpful for the concerned.

select empid,          
       replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary
from   emp;  

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)