Auto-increment is a very useful feature that allows a unique number to be generated when a new record is inserted into a table. The DBMS will automatically increment the last number of the table and add it to the auto-incremented column. Unfortunately, different DBMSs use different ways or syntax to do this. In this post, I will show you how to include the auto-increment feature in MySQL, Microsoft SQL Server, and Oracle.

Auto-Increment in MySQL

CREATE TABLE Students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
fullname VARCHAR(80) NOT NULL
);

As you can see from the above code, the auto-increment feature is very simple when it’s applied in MySQL. You only need to add auto_increment as shown in line 2. By default, the auto_increment value starts at 1. If you want the auto_increment to start with value other than 1, you have to set that value using ALTER TABLE:

ALTER TABLE Students AUTO_INCREMENT=2000

Continue reading

I collected these SQL scripts so that whenever I want to check the existency of a table, view, stored procedure, or function in a database, all I have to do is to copy and paste them to the Query Analyzer. FYI, These scripts are only used for SQL Server 🙂 . Hope it’s useful!

Check if the table exists in a database:

IF EXISTS
(
  SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'[dbo].[enterTableNameHere]')
         AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)

DROP TABLE [dbo].[enterTableNameHere]

GO

Continue reading