If we have a varchar column as a Primary Key (don't ask - we also are not able to alter the schema) then we can still use Max to generate IDs programmatically.

However, because the column is char, then SQL Server will determine that '9' is actually greater than '1','0' [10] so we need to be a little bit smarter.

Use this:

1. DECLARE @Length INT
2. SET @Length=(SELECT MAX(LEN(ID)) from test)
3. SELECT TOP 1 ID FROM Test WHERE LEN(ID)=@Length ORDER BY ID DESC

Thanks to http://www.dotnetspider.com/forum/192995-Can-we-apply-Max-function-SQL-Query-for.aspx

And if you want to call this using LINQ to SQL (or variant thereof), you could try:

CREATE PROCEDURE web_GetMaxCampaignCode
(@maxcode int output)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  DECLARE @Length INT
  SET @Length=(SELECT MAX(LEN(campaign_code)) from ref_campaign_code)
  SET @maxcode = (SELECT TOP 1 campaign_code FROM ref_campaign_code WHERE LEN(campaign_code)=@Length ORDER BY campaign_code DESC)
END
GO


Calling Code:

public static string GetFirstName(int id)
{
using (MyDataContext dataContext = new MyDataContext())
{
string FirstName;
var query = dataContext.GetFirstName(id, ref FirstName);
return FirstName;
}
}

Then just drag your sproc onto the dbml and call it like above.