Create View instead of Alter Table on SQL Server Database
This SQL tutorial shows an alternative approach how database tables which are under high usage can be altered instead of using a basic ALTER TABLE ADD column command syntax.
In theory it is very easy to add a new column to a SQL Server database table. But if your SQL database table is very large and includes millions of rows, are you brave enough to run an ALTER TABLE command? I'ld suggest SQL developers to test it on a test database, better on a clone of the productive database with similar loads.
Prepare Test Tables and Test Data for SQL Scenario
Let's first create our sample SQL database tables on a test environment where we can run our SQL scripts without any risk.
create table UserCredential (
user_id int identity(1,1) primary key,
user_name varchar(15),
user_password varchar(15)
)
create table UserInfo (
user_id int references UserCredential(user_id),
firstname varchar(50),
lastname varchar(50)
)
Now we can populate our SQL Server database tables with sample data using following batch script.
Please note that I used SQL stored procedure Generate_Password to generate random string values for my test data.
Besides, I use SQL Insert command with OUTPUT clause which enables me to use same identity value between two different tables to keep data relations.
-- insert data script --
declare @username varchar(15), @password varchar(15), @firstname varchar(50), @lastname varchar(50)
declare @user_id int
declare @counter int = 1
declare @idlist TABLE(id int)
while @counter < 100
begin
execute Generate_Password @username OUTPUT
execute Generate_Password @password OUTPUT
execute Generate_Password @firstname OUTPUT
execute Generate_Password @lastname OUTPUT
insert into UserCredential (user_name, user_password)
output inserted.user_id into @idlist(id)
select @username, @password
set @user_id = (select top 1 id from @idlist)
insert into UserInfo select @user_id, @firstname, @lastname
delete from @idlist
set @counter = @counter + 1
end
-- insert data script (END) --
Create Copy of Database Table and Populate with Clonned Data
Now SQL programmer can now create a copy of the source table and populate all data from source table into new database table After this INSERT command all new data inserted in source table or main table UserCredential till the time UserCredential2 is active should be inserted into the UserCredential2. So it is important to keep the time after this step minimized for deploying the solution.
Please note that our new table has a new column validity_date and slightly different than its original version.
create table UserCredential2 (
user_id int identity(1,1) primary key,
user_name varchar(15),
user_password varchar(15),
validity_date date
)
set identity_insert UserCredential2 on
insert into UserCredential2 (user_id, user_name, user_password, validity_date)
select user_id, user_name, user_password, null from UserCredential
set identity_insert UserCredential2 off
Of couse copying table data and inserting it into a new table will take some time. But during this time, the original source table is still active and the productive system is responding to incoming requests.
Foreign Key Constraints on SQL Tables
Database administrator should change UserInfo table references from UserCredential to UserCredential2 new created and cloned SQL table. This is an important step. For the sake of data integrity, all check constraints like foreign keys, indexes, etc should be created on the new table too.
alter table UserInfo drop constraint FK__UserInfo__user_i__49c3f6b7
alter table UserInfo with check add foreign key(user_id) references UserCredential2 (user_id)
If you don't know foreign key constraint, you can use the SSMS (SQL Server Management Studio) to see the name of the foreign key constraint.
An other option is to query SQL Server catalog view sys.foreign_keys for foreign key check constraints as shown in referred SQL tutorial.
Drop Database Table
After the foreign key is created on new table UserCredential2 we can drop original table UserCredential.
If you have not yet deleted the foreign key constraint on UserCredential, sql programmer will not be able to drop the original database table.
Because foreign key constraints prevent drop table command
Database administrator can not drop database table and create a SQL Server view object with the same name.
drop table UserCredential
Create View Object on Database
And create SQL view object with dropped table name (UserCredential) which returns all data from new table UserCredential2
create view UserCredential as select * from UserCredential2
Test SQL Solution
Now in theory all applications will be able to work seamless the queried object UserCredential is a database table or an SQL view.
Let's continue to populate tables using the insert data script which I shared above and see if we can insert new data into new table UserCredential2 using SQL view UserCredential
-- insert data into SQL View --
declare @username varchar(15), @password varchar(15), @firstname varchar(50), @lastname varchar(50)
declare @user_id int
declare @counter int = 1
declare @idlist TABLE(id int)
while @counter < 100
begin
execute Generate_Password @username OUTPUT
execute Generate_Password @password OUTPUT
execute Generate_Password @firstname OUTPUT
execute Generate_Password @lastname OUTPUT
insert into UserCredential (user_name, user_password)
output inserted.user_id into @idlist(id)
select @username, @password
set @user_id = (select top 1 id from @idlist)
insert into UserInfo select @user_id, @firstname, @lastname
delete from @idlist
set @counter = @counter + 1
end
-- insert data into SQL View (END) --
Insert into SQL view object script works successfully just like inserting new data into a database table. So at the end, we have clonned a table with its data into a new database table in our SQL Server instance. Then we dropped the original database table and created a new database view object with the same name which SELECT all records from the copy table.
There may be pros and cons of this approach. On the other hand, I hope databae administrators and SQL programmers have a good hands-on practise.