The following text documents contain scripts to create database tables on --
Microsft sql server 2000 for the fpPAyroll.mdb Database file.

  The following are the text files -- 

	ADMIN.txt      --   Contains script to create a admin table 
			    (also contains sample insert statement 
	   	 	    to demonstrate  how to insert records on admin  table )


	logfinal.txt   --   Contains script to create a log table 
			    (also contains sample insert statement 
	   	 	    to demonstrate  how to insert records on logtable ) 


	visitors.txt   --   Contains script to create a log table 
			    (also contains sample insert statement 
	   	 	    to demonstrate  how to insert records on logtable ) 


 fingerprintsFinal.txt --   Contains script to create a fingerprints table 
			    (also contains sample insert statement 
	   	 	    to demonstrate  how to insert records on fingerprints table ) 



	Groups.txt    --    Contains script to create a Groups  table 
			    (also contains sample insert statement 
	   	 	    to demonstrate  how to insert records on Groups ) 

	
	sequence.txt --     Contains VisitorsSerialNum and logSRNO sequences. These sequences. 
   	 	            are used by log table and fingerprints table (USED FOR ORACLE 8i, as ORACLE DOES NOT SUPPORT 			    AUTONUMBER FIELD ). 



	  









	 

	
   


   note : - 
 Microsoft SQL server does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. You might want to display a set of records where each record is listed with a generated number that identifies the records position relative to the rest of the records in the set. The numbers might be sequential that start at 1 and are incremented by 1 for each following record, like 1,2,3,4, etc. In other cases you may want to sequentially number groupings of records where each specific set of records is numbered starting at 1 and incremented by 1 until the next set is reach where the sequence starts over. This article will show a number of different methods of assigning a record sequence number to records returned from a query.

Sequentially Numbering Records by Having an Identity Column
Even though Microsoft SQL Server does not physically have a row number stored with each record, you can include one of your own. To have your own record number, all you need to do is include an identity column in your table definition. When you define the identity column, you can specify an initial seed value of 1, and a increment value of 1. By doing this the identity column will sequentially number each row inserted into the table. Let me show you a simple CREATE TABLE statement that defines a ROW_NUMBER column, which will sequentially number records. 




 


In Oracle, you can create an autonumber field by using sequences.  A sequence is an object in Oracle that is used to generate a number sequence.   This can be useful when you need to create a unique number to act as a primary key. 

The syntax for a sequence is: 

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH  value
    INCREMENT BY value
    CACHE value; 

 For example: 

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH  1
    INCREMENT BY  1
    CACHE 20; 

This would create a sequence object called supplier_seq.  The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}.  It will cache up to 20 values for performance. 

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object.  To retrieve the next value in the sequence order, you need to use nextval. 

For example: 

supplier_seq.nextval 

This would retrieve the next value from supplier_seq.  The nextval statement needs to be used in an SQL statement.  For example: 

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods'); 

This insert statement would insert a new record into the suppliers table.  The supplier_id field would be assigned the next number from the supplier_seq sequence.  The supplier_name field would be set to Kraft Foods. 

