Showing posts with label sqltips. Show all posts
Showing posts with label sqltips. Show all posts

Feb 9, 2015

How to Create an Auto Increment Field Using Sequence

Let's say we have a table called "test" with two columns, id and testdata. (This is just a dumb quick example, so I won't bother to specify any constraints on id.) 


create table test (id number, testdata varchar2(255)); 
 
2. Next we'll create a sequence to use for the id numbers in our test table. 

create sequence 

test_seq start with 1 
increment by 1 
nomaxvalue; 

You could change "start with 1" to any number you want to begin with (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with "start with 214"). The "increment by 1" clause is the default, so you could omit it. You could also replace it with "increment by n" if you want it to skip n-1 numbers between id numbers. The "nomaxvalue" tells it to keep incrementing forever as opposed to resetting at some point. i (I'm sure Oracle has some limitation on how big it can get, but I don't know what that limit is). 

3. Now we're ready to create the trigger that will automatically insert the next number from the sequence into the id column. 


create trigger test_trigger
 before insert on test

 for each row 
begin 

select test_seq.nextval into :new.id from dual; 

end;

You've demonstrated an implementation using triggers. This is not necessary, since instead it can be included as part of the INSERT statement. Using your example, my INSERT statement would be:
    
insert into test values(test_seq.nextval, 'Vamsi');



 

Apr 1, 2010

SQL Server Stored Procedures

Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.

Benefits of Stored Procedures

Why should you use stored procedures? Let's take a look at the key benefits of this technology:
  • Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
  • Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
  • Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
Stored procedures are very similar to user-defined functions, but there are subtle differences. For more information, read Comparing Stored Procedures and User-Defined Functions.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.

Example

Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.

We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to access that area's inventory.
EXECUTE sp_GetInventory 'NY'
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.

Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!

Inventory Table
ID Product Warehouse Quantity
142 Green beans NY 100
214 Peas FL 200
825 Corn NY 140
512 Lima beans NY 180
491 Tomatoes FL 80
379 Watermelon FL 85
Suggested Reading

Feb 26, 2010

Dyamically Creating Id Column And Increment For Each Row Automatically

declare @tab table(id int identity (0,1), moviename  varchar(200), movieid varchar(200),
showdate varchar(100),showtime datetime )INSERT INTO @tab select distinct top 4 m.moviename,m.movieid,convert(char(11), s.showtime, 106 ) as showdate,
convert(char(10), s.showtime, 111 ) as showtime  from tblshow s,tbltheater t,tblmovie m
where m.movieid=s.movieid and s.theaterid=t.theaterid and m.movieid=833
and convert(char(10), s.showtime, 111 )>=CONVERT(VARCHAR(10),GETDATE(),111)
order by showtime  asc select * from @tab

o/p:

0    MAA NANNA CHIRANJEEVI    833    02 Mar 2010    2010-03-02
1    MAA NANNA CHIRANJEEVI    833    03 Mar 2010    2010-03-03