Welcome to Freedom Technologies, In this post, we’ll discuss inserting large amounts of random data into SQL tables for performance testing in the next post we will use these large tables to test the performance of queries that use Joins and subqueries.
How populate sql table with random data?
There are three parts to the SQL script.
The first part will actually check if the tables exist, TblStudent and TblDonation
If the tables exist, they will be dropped. And then the second part, we recreate those tables and the final part will insert random sample data into those tables. Let’s see how we are checking for the existence of those tables and dropping them.
So to check the existence of tables, I’m using the system table called information_schema.tables. Let’s see what this table contains. So let’s execute query, select * from information_schema.tables
And look at this. This query has returned me with a list of table names. Where are these tables present? These tables are present in the TestDatabase because we executed a query within the context of the TestDatabase database.
I got all the tables that are present within that TestDatabase database and we are checking here if the table name is TblStudent. So do we have TblStudent within information_schema.tables? Yes, and we are passing that to Exists() function.
Exist() function will return true if the table exists, otherwise, it’s going to return false. So if this function returns true, that means the table is present. We want to drop it. So drop table TblStudent.
then the second part will actually recreate those tables, create table TblStudent and TblDonation table. and the important thing to notice here is that StudentId is a foreign key. So this is referring to the ID column in the TblStudent table.
So the third and final step is to actually insert sample data, inserting sample data into tables. It’s very simple and straightforward.
So if you look at the TblStudent table, it has got three columns within that id, StudentName , FatherName Id column is the identity column.Meaning when we insert a row into table TblStudent, we don’t have to supply a value for the identity column.
We only need to supply values for name and description columns which are of type and [nvarchar]. so the name is going to be dynamically computed. So the name of the student, since this random data I’m going to call my student says student1,student2,student3 …Studentn, etc.. Similarly, the father’s name description is going to be Father Name 1, Father Name 2, etc.
So while we declare a variable of type integer @Id and the @Id is initialized to 1, so while that @Id is less than or equal to 200000, which means we are going to loop through 200000 times, I’m going to insert 200000 students into the TblStudent table. So what we are doing insert into TblStudent values. So first we need to supply the value for the name column and then for the first name.
And to that what we are doing, we are concatenating the @Id value first-time @Id value will be 1. We are converting that to nvarchar because @Id is an integer variable. But we are since we are concatenating that to another string, we need to typecast that to be of type nvarchar, which is what we are doing here.
So that will give value for the name column, Student Name-1, Student Name-2, etc.
And ‘Father Name – ‘ + CAST(@Id as nvarchar(20)) will give me Father Name -1, which is going to be the value for the Father Name column.
Now you execute the above query it will take approx 2-3 minutes.
The post How to generate random data in sql server for performance testing appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?