hello welcome to preim Technologies I am wenet this is part 61 of SQL Server video series in this video we'll discuss about inserting large amounts of random data into SQL Server tables for performance testing in the next video session we'll use these large tables to test the performance of queries that uses joins and subqueries let's flip to SQL Server management Studio there are three parts to the SQL script the first part will actually check if the tables exist TBL products and product sales if the tables exist they will be dropped and in 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. taes system table let's see what this table contains so let's execute this query select star from information _ schema. tabls now look at this this query has returned me a list of table names where are these tables present these are tables are present in the sample database because we executed this query within the context of the sample database I got all the tables that are present within that sample database and we are checking here if the table name is product sales table so do we have product sales table within that information schema.
tabls yes and we are passing that to exist function this exist function will return true if the table exists otherwise it's going to return false so if if this function returns true that means the table is present we want to drop it so drop table TBL product sales okay so that will drop TBL product sales and this query will drop TBL products table so at the moment look at this we have TBL products sales and products table let me execute this query command completed successfully now if I refresh this we shouldn't see those tables so that's the first part of the query it checks if the tables exist and if they do it's going to drop them the second part will actually recreate those tables create table products table and product sales table okay and the important thing to notice here is that product ID is a foreign K so this is referring the ID column in TBL products table okay so I'm going to create TBL products table and TBL product sales table let's refresh this tables folder here we should have those tables back now okay so the third and final step is to actually insert sample data inserting sample data into this tables is very simple and straightforward so if you look at TBL products table it has got three columns within that ID name and description ID column is the identity column so when we are creating the table we we told ID is an identity column meaning when we insert a row into this table TBL products 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 andw c okay so the name is going to be dynamically computed so the name of the product you know since this a random data I'm going to call my products as product one product two product th000 Etc similarly uh product description is going to be product one description product two description Etc and how we are calculating the values for those names look at that I am doing this in a while loop so while we declared a variable of type uh in at ID and the ID is initialized to one so while that ID is less than or equal to 10,000 which means we're going to Loop through this 10,000 times I'm going to insert 10,000 products into TBL products table so what we are doing insert into T products values so first we need to supply the value for name column so this is the hardcoded text product hyphen and to that what we are doing we concatenating the ID value first time the ID value will be one so one we are converting that to an nare car because ID is an integer variable but we since we are concatenating that to another string we need to typ cast that to be of type nare which is what we are doing here so that will give me Product you know value for the name column product one and this one will give me Product one description which is going to be the value for description column the same thing product that's the hardcoded string to that we are concatenating ID which is going to be one the first time and then another hardcoded string description okay so this is going to be the value for description column this is going to be the value for name column and we have this insert statement inside this while loop okay so it's going to run 10,000 times and look at this we are printing ID this is for you know uh you know tracking the progress when the when this query is running and finally we are incrementing the value by one this is most important otherwise the loop will never end at some point of time we want the ID to be 10,000 so that the loop can end otherwise this will become a never ending Loop okay so let's go ahead and run this query and see what happens so execute query executing query look at the messages look at the rows are being inserted okay 5,000 6,000 the query has just completed successfully so when we scroll down you should see 10,000 rows now let's select data and see if it is present so select star from T build products press F5 look at that here I get 10,000 rows in the status bar and look at the name and description product one is the name product one description is the description okay so the next step is to actually insert um random data into TBL product sales table now this is a bit um you know I wouldn't say it's complex uh because here we have one 2 3 columns so this TBL product sales table has again got uh total four columns within that ID is again the identity column so we don't have to supply a value for that but whereas we have to supply values for product ID unit price and quantity sold columns and if you remember product ID is a foreign key this value must match with one of the values that are present you know in TBL product sales table uh products table for this ID column so it has to match up with that so if you remember within TBL products we have uh IDs between 1 and 10,000 okay so when I insert a row into this TBL product sales table product ID has to be a number between one and 10,000 okay unit price can be any random number quantity sold can also be any random number but then unit price and quantity sold I'm going to have a random number between 1 and 10 but whereas product ID will be a random number between 1 and 10,000 it can't fall outside of those values that's because it's a foreign key to the ID column in TBL products table okay so so to understand this all you have to do is I mean we are going to use again while loop look at that so while and I'm going to insert 15,000 rows because I might have sold one product maybe 10 times it's possible right so we're going to have 15,000 rows within TBL product sales table look at this again insert into TBL product sales I have this random product ID random unit price and random quantity sold uh you know variables so what are these These are the variables and what are these variables we are using round and Rand functions to generate a random product ID random unit price and random quantity so okay let's see how we are generating and if you remember we spoke about this the product ID should be a random number between 1 and 10,000 whereas unit price and quantity sold should be a random number between 1 and 10 now let's see how to generate a random number using Rand and round functions and that's very simple to do all you have to do is know how to use this random round function we have spoken about these random and round functions in the previous sessions of this video series please check those if you haven't uh watched them already so uh first let's see how to actually compute the values let's say I want you to write a script which will Generate random numbers between a given range for example I want to generate a random number between 1 and 10 so I'm going to declare a variable uh of type integer at LL lower limit and and I'm going to set that to one so my lower limit is going to be one and along the same lines I'm going to create another variable of type integer so declare at upper limit and that's going to be integer I want to set that let's say five let's say I want to Generate random numbers between 1 and five okay so I have created two variables of type integer initialize them to 1 and five so my lower limit is going to be one and upper limit is going to be five so how do I generate a random number between 1 and five so I'm going to use this uh Rand function for that but then look at this what I'm going to do first I'm going to subtract from the upper limit the lower limit okay and then I'm going to multiply that with Rand function okay so Random function okay let's execute this as it is okay let's execute this ENT code now if you look at this I get a random number look at that I execute 0. 9 I got 1 3 1 3 3 0 I don't want zero I want a number between 1 and you know five so but with this piece of code I'm getting number 0 1 2 3 you know four but I'm not getting five and I'm getting zero so what I basically can do to this add one okay so now let's execute all this together now I should always get a number between 1 2 3 4 and 5 but then I have decimal places we'll we'll get rid of those decimal places in just a bit so how to get rid of those decimal places use the round function that's what the round function is for so I'm going to use the round function so which is the number I want to round I want to round this number okay whatever is returned by this expression I want to round that number and how many decimal places I want I want zero decimal places that's it so now let's execute this all together look at this I get four 2 1 3 Etc just to make sure that it's working you know according to our expectations you know let's put in a simple testing code for this I want to quickly test this so I'm going to use uh you know while a while loop here while let's say 1 is equal to 1 just to convince you guys it always generate numbers between 1 and five so while 1 is equal to 1 that means this condition will always be true I'm creating a NeverEnding Loop here so while 1 is equal to 1 begin n so what do I want to do I want to generate this random number in an infinite Loop okay so Generate random number so to hold that number what I'm going to do I'm going to take a declare a variable so declare at Rand I'm going to say integer so let's take that variable so I'm saying at r so we are initializing this variable with whatever random number we get and print that so print that random number so we can see it so print that random number and then what I'm going to say I'm going to use a if condition here if that at random number is less than zero so it cannot be less than less than one it cannot be less than one because we want a random number between 1 and five so if it is less than zero or less than one or if that random number is greater than 5 that means our code is wrong so what I'm going to do here I'm going to have another begin and end I'm going to print saying error and we want to see what is that number that is generated so I'm going to okay so anytime if it comes to this you know if block we know that that number is less than one or it is greater than five in which case our script this piece of script which is generating the random number is incorrect so what we are going to do here we're going to cast that and print that incorrect number so that's an integer so I'm going to cast that to be of type nare maybe four okay so let's execute the script so if we execute the script now it it should never end because I am confident this piece of script here is going to always generate a number between 1 and 5 it is never going to be less than 1 or greater than 5 and what I want to do is after we print we want to break out of the loop okay so so if it comes here we know for sure it prints eror message and it breaks out of this while loop if it is not coming out of that while loop then we know for sure it's always going to generate numbers between one and five a quick test basically so I execute that look at this executing query it's it's still executing it's more than 5 seconds now so if I go to the message just look at this it's it's printing those numbers and it's never going to end we have to cancel that at some point of time look at this it's still executing let me cancel that okay and if you look at these numbers this always will be between 1 2 3 4 and five I mean between one and five basically okay now just to make this a little bit interesting I'm going to say where at random is greater than or equal to five now if we execute this look at that we got 4423 at some point of time it ended okay now let me execute that look at that 25 and then error okay so it's working it's only generating the numbers between 1 and five so our script is working as expected so we have just seen how to generate random numbers between a given range we are creating do declaring two variables initializing them to one and five and it is this simple piece of code which generates random number between a given range okay so if we have understood that the rest of the script should be very simple forward uh straightforward so to insert data into TBL product sales table we need values for product ID unit price and quantity sold all of them of type integers okay so I want so I have created two variables upper limit for product ID lower limit for product ID now the lower limit for product ID is going to be one and upper limit is going to be 8,500 you can make it 10,000 if you want but I have just chosen 8,5 00 because when I do a left join you know if if I I basically want to use this for the next video session to demonstrate the performance of subqueries and joins okay so I don't want um all the products in TBL product sales table so I'm going to have 1,500 of them not present in TBL product sales table okay and the unit price um you know is going to be a number between 1 and 100 or it can be 1 and 10 it's up to and the same is the quantity quantity is again 1 and 10 okay so these are all Declaration of variables and initializing them and finally what we are doing we are having a while loop here which starts at one and Loops through 15,000 times so we're going to have 15,000 records inserted and obviously what is this script doing here it is generating a random um product ID random unit price and random quantity sold it's exactly the same thing that we have seen how to gen you know just now how to generate a random number okay and then we are inserting those you know values into this table and we are printing the counter incrementing the counter by one okay so let's execute this script and see if it inserts uh 15,000 rows so executing query so in the messages you should see it's still executing 4, 4,900 9,400 12,400 and query completed successfully 15,000 so now if we go back to the tables we should see 10,000 records in products and 10,000 uh I mean 15,000 records in TBL product sales table so look at that 10,000 in TBL products 15,000 in TBL product sales table so if you look at TBL product sales table we have product ID unit price and quantity sold some random numbers okay so we have seen how to insert 10,000 and 15,000 rows but if you want you can even insert 1 million 2 million rows all you have to change is the number for this W Loop that's about it okay on this slide you can find resources for asp.
