Basically 'MAX' return maximum from a given column, if you want to get a next maximum number then you can get it using MAX+1
But there may be problem when multiple read, so it is recommended to use AutoIncrement(Identity seed) features of SQL
to get MAX auto increment number from table you can use below query
SELECT IDENT_CURRENT('table_name');