May 29, 2012 at 12:43 am
Hi
As we use Ident_current('Table Name') for Max or Last Value of the table,
is thr a function to know lowest value ?
I do not want to use min(field) function it slow the down the process,
I have a two tables one is Transaction(TRACKPOINT_INDIA_New) table and one is Reference table(tempLastRowId_New).
what i do in Transaction(TRACKPOINT_INDIA_New) table fetch and process(dump into major table)
the data and delete the data from Transaction table, but i keep the record(Ident Value of the transaction)
in reference table,
DECLARE
@StartRowId BIGINT
,@LastRowId BIGINT
,@MinRowId BIGINT
SELECT @StartRowId = 0
SELECT @StartRowId = ISNULL(LASTRowId ,0) FROM [tempLastRowId_New] WITH (NOLOCK)
PRINT @StartRowId
PRINT Ident_Current('[dbo].[TRACKPOINT_INDIA_New]')
IF @StartRowId > Ident_Current('[dbo].[TRACKPOINT_INDIA_New]')
BEGIN
SELECT @MinRowId = MIN(RowId)
FROM [dbo].[TRACKPOINT_INDIA_New] WITH (NOLOCK)
SELECT @StartRowId = @MinRowId - CAST( 1 AS BIGINT)
END
DELETE FROM [dbo].[TRACKPOINT_INDIA_New] WHERE RowId <= @StartRowId
SELECT @LastRowId = @StartRowId + CAST( 1200 AS BIGINT)
SELECT
RowId
,UUId
,UnitId
,TrackingTime
,GPSStatus
,Longitude
,Latitude
,Speed
,Mileage
,Direction
,IStatus
,SStatus
,InputStatus
,TT1
,TT2
,TT3
FROM dbo.TRACKPOINT_INDIA_New
WHERE RowId > @StartRowId AND RowId <= @LastRowId
If someone deletes the data from my reference table(tempLastRowId_New) then i am unable to retrieve the data from my transaction table(TRACKPOINT_INDIA_New) because the "rowid" range.
Please suggest some better technique...
Thanks in advance...
Patel Mohamad
May 29, 2012 at 2:12 am
Ident_current doesn't give you the max value in the table, it just gives you the current identity seed. That can be way different from the maximum value in the identity column.
For max value in a column, use MAX, for min value in a column use MIN and watch that nolock usage. See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2012 at 6:24 am
according to http://msdn.microsoft.com/en-us/library/ms175098.aspx
"IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope...When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value."
May 30, 2012 at 8:01 am
WILLIAM MITCHELL (5/30/2012)
according to http://msdn.microsoft.com/en-us/library/ms175098.aspx"IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope...When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value."
Yes it the last identity value generated for a table. That does NOT mean that value is actually in the table.
Consider the following code. I documented it at each step to help demonstrate what is happening.
create table IdentityDemo
(
ID int identity,
SomeVal varchar(10)
)
go
select IDENT_CURRENT('IdentityDemo') --this is 1, not null as the documentation suggests
select * from IdentityDemo --no rows
insert IdentityDemo select '12345678901' --this will fail because the data is too long
select * from IdentityDemo --still no rows
select IDENT_CURRENT('IdentityDemo') --What do you think this is now?
begin transaction
insert IdentityDemo select '1234567890'
rollback transaction
select IDENT_CURRENT('IdentityDemo') --how about now?
select * from IdentityDemo --still now rows so clearly IDENT_CURRENT is not giving us the max value in the table ;)
insert IdentityDemo select '1234567890' --this is a valid insert
select * from IdentityDemo --see we do have a row finally
select IDENT_CURRENT('IdentityDemo') --ok so 3 now makes sense
delete IdentityDemo --what will be the max value in the table now?
select IDENT_CURRENT('IdentityDemo') --hhmmm IDENT_CURRENT is still not the same as mas
truncate table IdentityDemo
select IDENT_CURRENT('IdentityDemo') --even after a truncate this is not null also contradicting the documentation
drop table IdentityDemo
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply