SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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)
)
Code

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) --
Code

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
Code

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)
Code

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.

SQL Server database table 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
Code

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
Code

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) --
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.