Using ROW_NUMBER()

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER is a temporary value calculated when the query is run.

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

1. Values of the partitioned column are unique.
2. Values of the ORDER BY columns are unique.
3. Combinations of values of the partition column and ORDER BY columns are unique.

Require OVER() clause to use ROW_NUMBER().

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

SELECT 
			CustomerId, ROW_NUMBER() OVER(ORDER BY CustomerId ASC) AS RowNumber 
		FROM 
			dbo.tblCustomer

Have to use subquery to update row using ROW_NUMBER().

UPDATE	
	c2
SET
	c2.CustomerCode = c1.RowNumber
FROM
	(
		SELECT 
			CustomerId, ROW_NUMBER() OVER(ORDER BY CustomerId ASC) AS RowNumber 
		FROM 
			dbo.tblCustomer
	) c1
	INNER JOIN dbo.tblCustomer c2 ON c1.CustomerId = c2.CustomerId

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15