Adding dynamic column names to a table

I'm using Microsoft SQL Server 2014 Express.

I found myself wanting to add the Columns CompanyNameChangeDate (1 through 10) and CompanyNameChangePreviousName (1 through 10) to a table in my database.

I wrote the following query to do it:

declare @i int
set @i = 0

Declare @nameChangeDatePrefix as varchar(255) = 'CompanyNameChangeDate'

Declare @nameChangePreviousNamePrefix as varchar(255) = 'CompanyNameChangePreviousName'

declare @sql as varchar(255)

while (@i < 10)
begin
    set @i = @i + 1

    set @sql = 'ALTER TABLE company_registry_data ADD ' + @nameChangeDatePrefix + Cast(@i as varchar(255)) + ' DATE'
    execute(@sql)

    set @sql = 'ALTER TABLE company_registry_data ADD ' + @nameChangePreviousNamePrefix + Cast(@i as varchar(255)) + ' VARCHAR(255)'
    execute(@sql)
end

• How could I make it better?

• What naming conventions have I contravened?

• What habits should I start building right now?

• What are the code smells?

I would consider your whole proposal for this schema to be a code smell. In SQL, if there are many data to be treated alike, they should be stored in one column, rather than in multiple columns. If you store your data using this proposed schema, any code that tries to use the schema is likely to be nasty too. (What's the average time interval between name changes? Good luck writing that query!) You would also have an arbitrary limit of 10 name changes per company, and accommodating more names would necessitate another schema change!

Therefore, it would make more sense to have a CompanyNameChange table, with (companyId, changeDate) as the primary key, and additional attributes previousName as an additional attribute. If, for some reason, you do need the data to be presented in that proposed tabular format, you could always perform some joins.

StackExchange Code Review Q#128380, answer score: 4