Looking for a Tutor Near You?

Post Learning Requirement » x
Ask a Question
x

Choose Country Code

x

Direction

x

Ask a Question

x

Hire a Tutor
Bangalore

DBMS Sequences

Home > PowerPoint Slides > DBMS Sequences
Published in: DBMS & RDBMS
4,506 Views

This PPT explains the functionality of sequences in Database programming.

Alok / Mumbai

8 years of teaching experience

Qualification: B.Tech/B.E. (RGPV, Bhopal - 2014)

Teaches: Computer, IT, CSS Training, HTML Training, Web Designing, Web Development, MBA Entrance, C, C++, C# (C Sharp), Java And J2EE, Visual Basic, Android Training, AJAX Training, Java Script

Contact this Tutor
  1. SEQUENCES
  2. AGENDA OF DISCUSSION What are sequences, Syntax of creating sequences. Various clauses which can be used while creating sequences. Using sequence in INSERT statements. Dropping sequences. PRESENTATION BY ALOK GUPTA
  3. Definition In SQL Server, you can create an autonumber field by using sequences. A sequence is an object in SQL Server (Transact-SQL) 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. Can be used in creating sales order ids, item ids etc... PRESENTATION BY ALOK GUPTA
  4. Syntax CREATE SEQUENCE [ AS datatype ] [ START WITH value ] [ INCREMENT BY value ] [ MINVALUE value I NO MINVALUE ] [ MAXVALUE value I NO MAXVALUE ] [ CYCLE I NO CYCLE ] [ CACHE value I NO CACHE l; PRESENTATION BY ALOK GUPTA
  5. Syntax Details AS datatype : It can be BIGINT, INT, TINYINT, SMALLINT, DECIMAL, or NUMERIC. If datatype is not specified, the sequence will default to a BIGINT datatype. START WITH value : The starting value that the sequence returns initially. INCREMENT BY value : It can be either a positive or negative value. If a positive value is specified/ the sequence will be an ascending sequence of values. If a negative value is specified, the sequence will be a descending sequence of values. PRESENTATION BY ALOK GUPTA
  6. Syntax Details (continued...) MINVALUE value : The minimum value allowed for the sequence. NO MINVALUE : It means that there is no minimum value specified for the sequence. MAXVALUE value : The maximum value allowed for the sequence. NO MAXVALUE : It means that there is no maximum value specified for the sequence. CYCLE : It means that the sequence will start over once it has completed the sequence. NO CYCLE : It means that the sequence will raise an error when it has completed the sequence. It will not start the sequence over again. CACHE value : It caches the sequence numbers to minimize disk 10. NO CACHE : It does not cache the sequence numbers. PRESENTATION BY ALOK GUPTA
  7. BE CAREFUL WHEN CACHING SEQUENCES: The cache clause preallocates a set of sequence numbers and keep them in cache so that next sequence number can be accessed faster. When the last number in cache has been used/ database server will store new set of sequence numbers in cache. Now, when database server is abnormally shut down, in that case the cache will be cleared and all the numbers in cache will be lost. The database server will store the next set of numbers in cache. Next time when we will read value from sequence, it will give a value from the cache. So there is a risk of numbers being skipped. PRESENTATION BY ALOK GUPTA
  8. Example First create a table called Orders CREATE TABLE ORDERS( ORDERID BIGINT NOT NULL, ORDERDATE DATETIME, ORDERAMOUNT INT); We need to fill Orderld column of above table using SEQUENCES. PRESENTATION BY ALOK GUPTA
  9. SQL for Creating Sequence CREATE SEQUENCE order_seq AS BIGINT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 NO CYCLE CACHE 10; PRESENTATION BY ALOK GUPTA
  10. GETTING NEXT NUMBER FROM SEQUENCE SELECT NEXT VALUE FOR order_seq; INSERT INTO TABLE USING SEQUENCE INSERT INTO DBO.ORDERS (ORDERIDI ORDERDATEI ORDERAMOUNT) VALUES (NEXT VALUE FOR order_seq, 1200); To Check Data : , SELECT * FROM DBO.ORDERS; PRESENTATION BY ALOK GUPTA
  11. VIEWING PROPERTIES OF SEQUENCE SELECT * FROM sys.sequences where name = lorder_seql, DROP SEQUENCE DROP SEQUENCE order_seq; PRESENTATION BY ALOK GUPTA
  12. THANK YOU PRESENTATION BY ALOK GUPTA

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related PPTs

Upload PPTs

If you have your own PowerPoint Presentations which you think can benefit others, please upload on LearnPick. For each approved PPT you will get 50 Credit Points and 50 Activity Score which will increase your profile visibility.

Upload Now