Puedes generar un campo tipo identity (mayor referencia en ayuda de sql server), este se incrementa automaticamente al insertar el nuevo registro.
Para consultar en el formato que mencionas, puedes concatener la cadena CD y rellenar con los ceros que hagan falta...algo como esto:
Código:
Select 'CD' + right(replicate('0',7) + cast(isnull(MicampoIdentity,0) as varchar), 6) as clave
From MiTabla
Saludos!