Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 22nd, 2005, 01:24 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default What is @@identity property?

Hi,

Is there any difference between max(table_column) and @@identity in SQL server 2000?

Subhash

 
Old December 22nd, 2005, 03:25 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi,

Here's what I know. The max function gives you the maximum value for that column. However, I use the @@identity to retrieve the value of the identity column with that row insertion so that if I want to I can go directly to the edit page in a redirect and pull up that just added row. You have to set the nocount value on to retrieve the identity. I don't remember the theory behind it except that SQL has a count of how many rows were affected with a transaction. You can see that when using SQL Query Analyzer. Notice the second tab after a transaction. It will tell you how many rows were affected. That functionality has to be turned off. Here's an example.

SQL = "Set nocount on; Insert into Table (Column1, Column2) Values (Value1, Value2);Select theID = @@identity;Set nocount off;"

Set oRS = oConn.Execute(SQL)
theID = oRS("theID")

This is in classic ASP.

Here's an example you can try in QA.

Create this table.

CREATE TABLE [Table1] (
    [theID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Column1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Column2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Then run this script in QA. You can see the value of the ID column in the just inserted row.

declare @theID integer

Set nocount on;INSERT INTO table1 (Column1, Column2) Values ('Richard', 'Jimmy');SELECT @theID = @@identity;SELECT @theID AS 'The ID';Set nocount off;SELECT * FROM Table1;

Its a good question, however, since running Select max(theID) from table1 gives you the same value but essentially you're getting that value in the same transaction using @@identity.

rstelma






 
Old December 22nd, 2005, 09:01 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

Use the Scope_Identity() Function instead of @@identity otherwise you are in danger of getting the identity value that occurred when a trigger did an insert, as opposed to the insert that you directly caused.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing off the Identity Property happygv SQL Server 2000 17 September 10th, 2007 07:38 AM
dropping the identity property of column sandeep SQL Server 2005 3 March 2nd, 2007 01:57 PM
Identity Property issue Navy1991 SQL Server 2000 2 January 6th, 2006 05:27 AM
what does identity do amruthhr SQL Server 2000 2 January 6th, 2005 09:45 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.