Max function

Hi,

The question is about the way max function works in SQL. I have to implement a database design where in i have to write a stored procedure which will perform an insert in the table/tables. This table/tables will in the long run have a large number of records, i have been instructed to create another table (lets call it tableA) which will have the primary keys of all the other tables and their respective maximum values being used. While performing an insert into one of these tables, i will have to probe the tableA and find out the currently used id for that table ( in which i have to insert a new row) and use the value for insertion.

According to me, we can use a max clause in the stored procedure and achieve the following or use an Indentity column to do the same.

I will need insights into how max function works when it tells me the number of records (does it traverse thru the entire table or has the number of rows stored somewhere? I just need the number of rows in the table and nothing else but would like to know the max function working)
Also how will identity column perform under these situations.

Any information required, i will be glad to give.

VihangJust use the identity field, it's what it's designed for. If you need a table which reflects the maximum id field in each for some reason (but hopefully you don't any more), then use a view which queries the max(id) field from each table, and a 'union all' to show the lot. Something like this:

create view maxids as
select 'table1' as tablename, max(id) as maxid
from table1
union all
select 'table2' as tablename, max(id) as maxid

from table2
union all
...etc

I hope this helps...

Rob

--
Rob Farley
President - Adelaide SQL Server User Group
rob_farley@.hotmail.com (Email &

Msgr)
http://msmvps.com/blogs/robfarley|||Hi Rob,

Thank you for your prompt input. I appreciate it, I have understood what you have suggested.

I need to understand how does the max function work? does it go thru all the rows to calculate the maximum of a column?.

Regards,
Vihang|||Not when there's a useful index it can use. As these 'id' fields will be your primary keys (if not, they should be!), then the system can very easily find out what the maximum value is. Being identity columns, the system is designed to be able to very quickly report what the largest value is.|||Hi Rob,

Thanks for your insights, I now know how it really works.

Regards,
Vihang