SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Entering random data into a table


Entering random data into a table

Author
Message
Junior Galvão - MVP
Junior Galvão - MVP
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5547 Visits: 241
Comments posted to this topic are about the item Entering random data into a table
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874

I appreciate anyone that will step up to the plate with an article or a script. Since you brandish the title of "MVP" in your handle and I've verified that you are a Data Platform MVP, I thought you could stand a bit of a critique.

If you hard code an @RowCount of just 100,000 in your code, it takes 48 seconds to complete. That's because of the 100,000 rows you end up inserting in a totally RBAR fashion.

The following does the functional equivalent as your code but in a Set Based fashion and takes only 2 seconds. (The reason for the funky numbers is because your @Texto string is actually only 127 characters long according to the LEN() function and not the 130 you stated.


CREATE TABLE dbo.RandomDataTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,CustomerID int NOT NULL
,SalesPersonID varchar(10) NOT NULL
,Quantity smallint NOT NULL
,NumericValue numeric(18,2) NOT NULL
,Today date NOT NULL
)
;
DECLARE @RowCount INT = 100000
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E10(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
,TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.RandomDataTable
(CustomerID, SalesPersonID, Quantity, NumericValue, Today)
SELECT CustomerID = @RowCount+1-t.N
,SalesPersonID = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
,Quantity = ABS(CHECKSUM(NEWID())%1000)
,NumericValue = RAND(CHECKSUM(NEWID()))*100+5
,Today = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
FROM TallY t
CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
;
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM RandomDataTable
;


How does it all work. Please see the following articles for an introduction to the "Tally Table", Itzik Ben-Gan's method to replace it with inline code, and an introduction to the concept of "pseudo cursors" (which isn't specifically mentioned in the article but that's what it is), all of which are used to replace While Loops, Recursive CTE's, and a wealth of other RBAR with something much faster and, IMHO, easier to write. The article will change your professional career if you look beyond the simple examples in the article to embrace the overall concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As for creating random values, here are a couple of articles on the subject to get you away from the issues of using RAND() as a random source so that you don't have to use a While Loop to generate test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Just a couple of other observations.
1. Consider always using the 2 part naming convention to avoid problems with the eventuality of duplicate table names being using across multiple schemas.
2. Consider right-sizing column data-types. I hate to see folks waste space by using NUMERIC(18,2) (a horrible default from front end designers) and VARCHAR(10) when the column will be filled with 99.9999% 10 character values. VARCHAR(anything) carries an extra 2 byte overhead to remember the length of the value.
3. Consider always using properly places semi-colons. Not using them has actually been deprecated for many years and the proverbial poo is going to hit the fan when they make them fully required.
4. Consider not mixing human languages in the code for things like variable names.





--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874
p.s. I guess you had a couple of special characters in your @Texto string... they didn't copy correctly when I pasted my code. You may have to change some of the numbers associated with the starting position of the substrings.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Junior Galvão - MVP
Junior Galvão - MVP
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5547 Visits: 241
Jeff Moden - Saturday, June 16, 2018 3:10 PM

I appreciate anyone that will step up to the plate with an article or a script. Since you brandish the title of "MVP" in your handle and I've verified that you are a Data Platform MVP, I thought you could stand a bit of a critique.

If you hard code an @RowCount of just 100,000 in your code, it takes 48 seconds to complete. That's because of the 100,000 rows you end up inserting in a totally RBAR fashion.

The following does the functional equivalent as your code but in a Set Based fashion and takes only 2 seconds. (The reason for the funky numbers is because your @Texto string is actually only 127 characters long according to the LEN() function and not the 130 you stated.


CREATE TABLE dbo.RandomDataTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,CustomerID int NOT NULL
,SalesPersonID varchar(10) NOT NULL
,Quantity smallint NOT NULL
,NumericValue numeric(18,2) NOT NULL
,Today date NOT NULL
)
;
DECLARE @RowCount INT = 100000
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E10(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
,TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.RandomDataTable
(CustomerID, SalesPersonID, Quantity, NumericValue, Today)
SELECT CustomerID = @RowCount+1-t.N
,SalesPersonID = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
,Quantity = ABS(CHECKSUM(NEWID())%1000)
,NumericValue = RAND(CHECKSUM(NEWID()))*100+5
,Today = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
FROM TallY t
CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
;
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM RandomDataTable
;


How does it all work. Please see the following articles for an introduction to the "Tally Table", Itzik Ben-Gan's method to replace it with inline code, and an introduction to the concept of "pseudo cursors" (which isn't specifically mentioned in the article but that's what it is), all of which are used to replace While Loops, Recursive CTE's, and a wealth of other RBAR with something much faster and, IMHO, easier to write. The article will change your professional career if you look beyond the simple examples in the article to embrace the overall concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As for creating random values, here are a couple of articles on the subject to get you away from the issues of using RAND() as a random source so that you don't have to use a While Loop to generate test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Just a couple of other observations.
1. Consider always using the 2 part naming convention to avoid problems with the eventuality of duplicate table names being using across multiple schemas.
2. Consider right-sizing column data-types. I hate to see folks waste space by using NUMERIC(18,2) (a horrible default from front end designers) and VARCHAR(10) when the column will be filled with 99.9999% 10 character values. VARCHAR(anything) carries an extra 2 byte overhead to remember the length of the value.
3. Consider always using properly places semi-colons. Not using them has actually been deprecated for many years and the proverbial poo is going to hit the fan when they make them fully required.
4. Consider not mixing human languages in the code for things like variable names.





Hi Jeff,

Thanks for answers.

Junior Galvão - MVP
Junior Galvão - MVP
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5547 Visits: 241
Junior Galvão - MVP - Sunday, June 17, 2018 4:56 PM
Jeff Moden - Saturday, June 16, 2018 3:10 PM

I appreciate anyone that will step up to the plate with an article or a script. Since you brandish the title of "MVP" in your handle and I've verified that you are a Data Platform MVP, I thought you could stand a bit of a critique.

If you hard code an @RowCount of just 100,000 in your code, it takes 48 seconds to complete. That's because of the 100,000 rows you end up inserting in a totally RBAR fashion.

The following does the functional equivalent as your code but in a Set Based fashion and takes only 2 seconds. (The reason for the funky numbers is because your @Texto string is actually only 127 characters long according to the LEN() function and not the 130 you stated.


CREATE TABLE dbo.RandomDataTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,CustomerID int NOT NULL
,SalesPersonID varchar(10) NOT NULL
,Quantity smallint NOT NULL
,NumericValue numeric(18,2) NOT NULL
,Today date NOT NULL
)
;
DECLARE @RowCount INT = 100000
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E10(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
,TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.RandomDataTable
(CustomerID, SalesPersonID, Quantity, NumericValue, Today)
SELECT CustomerID = @RowCount+1-t.N
,SalesPersonID = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
,Quantity = ABS(CHECKSUM(NEWID())%1000)
,NumericValue = RAND(CHECKSUM(NEWID()))*100+5
,Today = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
FROM TallY t
CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
;
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM RandomDataTable
;


How does it all work. Please see the following articles for an introduction to the "Tally Table", Itzik Ben-Gan's method to replace it with inline code, and an introduction to the concept of "pseudo cursors" (which isn't specifically mentioned in the article but that's what it is), all of which are used to replace While Loops, Recursive CTE's, and a wealth of other RBAR with something much faster and, IMHO, easier to write. The article will change your professional career if you look beyond the simple examples in the article to embrace the overall concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As for creating random values, here are a couple of articles on the subject to get you away from the issues of using RAND() as a random source so that you don't have to use a While Loop to generate test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Just a couple of other observations.
1. Consider always using the 2 part naming convention to avoid problems with the eventuality of duplicate table names being using across multiple schemas.
2. Consider right-sizing column data-types. I hate to see folks waste space by using NUMERIC(18,2) (a horrible default from front end designers) and VARCHAR(10) when the column will be filled with 99.9999% 10 character values. VARCHAR(anything) carries an extra 2 byte overhead to remember the length of the value.
3. Consider always using properly places semi-colons. Not using them has actually been deprecated for many years and the proverbial poo is going to hit the fan when they make them fully required.
4. Consider not mixing human languages in the code for things like variable names.





Hi Jeff,

Thanks for answers.


Hi Jeff,
This example has a context of academic studies, so the same was published, some points were not added in the description by a little forgetfulness on my part.
You're right in some observed details, but we should always have a little care with our words.
But I thank you for your observations and respect your criticisms.
I just don't accept being called an amateur.
Still, thank you for your reply.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874
Junior Galvão - MVP - Sunday, June 17, 2018 5:11 PM
Junior Galvão - MVP - Sunday, June 17, 2018 4:56 PM
Jeff Moden - Saturday, June 16, 2018 3:10 PM

I appreciate anyone that will step up to the plate with an article or a script. Since you brandish the title of "MVP" in your handle and I've verified that you are a Data Platform MVP, I thought you could stand a bit of a critique.

If you hard code an @RowCount of just 100,000 in your code, it takes 48 seconds to complete. That's because of the 100,000 rows you end up inserting in a totally RBAR fashion.

The following does the functional equivalent as your code but in a Set Based fashion and takes only 2 seconds. (The reason for the funky numbers is because your @Texto string is actually only 127 characters long according to the LEN() function and not the 130 you stated.


CREATE TABLE dbo.RandomDataTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,CustomerID int NOT NULL
,SalesPersonID varchar(10) NOT NULL
,Quantity smallint NOT NULL
,NumericValue numeric(18,2) NOT NULL
,Today date NOT NULL
)
;
DECLARE @RowCount INT = 100000
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E10(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
,TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.RandomDataTable
(CustomerID, SalesPersonID, Quantity, NumericValue, Today)
SELECT CustomerID = @RowCount+1-t.N
,SalesPersonID = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
,Quantity = ABS(CHECKSUM(NEWID())%1000)
,NumericValue = RAND(CHECKSUM(NEWID()))*100+5
,Today = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
FROM TallY t
CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
;
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM RandomDataTable
;


How does it all work. Please see the following articles for an introduction to the "Tally Table", Itzik Ben-Gan's method to replace it with inline code, and an introduction to the concept of "pseudo cursors" (which isn't specifically mentioned in the article but that's what it is), all of which are used to replace While Loops, Recursive CTE's, and a wealth of other RBAR with something much faster and, IMHO, easier to write. The article will change your professional career if you look beyond the simple examples in the article to embrace the overall concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As for creating random values, here are a couple of articles on the subject to get you away from the issues of using RAND() as a random source so that you don't have to use a While Loop to generate test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Just a couple of other observations.
1. Consider always using the 2 part naming convention to avoid problems with the eventuality of duplicate table names being using across multiple schemas.
2. Consider right-sizing column data-types. I hate to see folks waste space by using NUMERIC(18,2) (a horrible default from front end designers) and VARCHAR(10) when the column will be filled with 99.9999% 10 character values. VARCHAR(anything) carries an extra 2 byte overhead to remember the length of the value.
3. Consider always using properly places semi-colons. Not using them has actually been deprecated for many years and the proverbial poo is going to hit the fan when they make them fully required.
4. Consider not mixing human languages in the code for things like variable names.





Hi Jeff,

Thanks for answers.


Hi Jeff,
This example has a context of academic studies, so the same was published, some points were not added in the description by a little forgetfulness on my part.
You're right in some observed details, but we should always have a little care with our words.
But I thank you for your observations and respect your criticisms.
I just don't accept being called an amateur.
Still, thank you for your reply.


Heh.. oh boy... here we go.

No where in my posts did I call you an "amateur". That's all on you, bud. And, no... no where did you even hint anything about an "academic study". In fact, you stated quite the contrary in the article...


This block of code is intended to demonstrate how we can in Microsoft SQL Server from version 2008. Use any user database to perform a execution of this code block and table storage RandomDataTable.

You can use this block of code to test and study environments, with the purpose of generating and populate a table with random data.



So here's a little friendly advice and tough-love from a retired MVP... Embrace being wrong. Embrace this community as a community of people that's out to help everyone, even MVPs. Embrace the learning you will experience if you humble down a bit and learn from those trying to help you instead of taking offense to their offerings or making back-peddle excuses . Always remember that the MVP award is a "service to the community award" and not a badge of knowledge or in anyway that is a certification of knowledge. Embrace the fact that if such offerings of constructive criticism make you think someone is calling you an "amateur" (and I patently did not nor even infer it never mind saying it), then that's all on you and only you have the power to change that by learning what everyone needs to learn, MVP or not... you are not perfect and, even if you are a true genius on a given subject, expect challenges and rise to the occasion because... that's how this community and the individuals that embrace it keep getting better. Always ask "Is there a better way" and always embrace those that say, "Lemme show you something" even if it's not quite that polite (and I was... I only dealt with facts). And always remember that the most learning occurs in the discussions that follow an article.

Now... shall I welcome a new member of SSC that took the time and effort to post something to the community as a friend? Or would you rather keep knocking that shinny new MVP ring. Wink



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Junior Galvão - MVP
Junior Galvão - MVP
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5547 Visits: 241
Jeff Moden - Sunday, June 17, 2018 6:14 PM
Junior Galvão - MVP - Sunday, June 17, 2018 5:11 PM
Junior Galvão - MVP - Sunday, June 17, 2018 4:56 PM
Jeff Moden - Saturday, June 16, 2018 3:10 PM

I appreciate anyone that will step up to the plate with an article or a script. Since you brandish the title of "MVP" in your handle and I've verified that you are a Data Platform MVP, I thought you could stand a bit of a critique.

If you hard code an @RowCount of just 100,000 in your code, it takes 48 seconds to complete. That's because of the 100,000 rows you end up inserting in a totally RBAR fashion.

The following does the functional equivalent as your code but in a Set Based fashion and takes only 2 seconds. (The reason for the funky numbers is because your @Texto string is actually only 127 characters long according to the LEN() function and not the 130 you stated.


CREATE TABLE dbo.RandomDataTable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,CustomerID int NOT NULL
,SalesPersonID varchar(10) NOT NULL
,Quantity smallint NOT NULL
,NumericValue numeric(18,2) NOT NULL
,Today date NOT NULL
)
;
DECLARE @RowCount INT = 100000
;
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,E10(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j)
,TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.RandomDataTable
(CustomerID, SalesPersonID, Quantity, NumericValue, Today)
SELECT CustomerID = @RowCount+1-t.N
,SalesPersonID = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
+ SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
,Quantity = ABS(CHECKSUM(NEWID())%1000)
,NumericValue = RAND(CHECKSUM(NEWID()))*100+5
,Today = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
FROM TallY t
CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
;
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM RandomDataTable
;


How does it all work. Please see the following articles for an introduction to the "Tally Table", Itzik Ben-Gan's method to replace it with inline code, and an introduction to the concept of "pseudo cursors" (which isn't specifically mentioned in the article but that's what it is), all of which are used to replace While Loops, Recursive CTE's, and a wealth of other RBAR with something much faster and, IMHO, easier to write. The article will change your professional career if you look beyond the simple examples in the article to embrace the overall concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As for creating random values, here are a couple of articles on the subject to get you away from the issues of using RAND() as a random source so that you don't have to use a While Loop to generate test data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Just a couple of other observations.
1. Consider always using the 2 part naming convention to avoid problems with the eventuality of duplicate table names being using across multiple schemas.
2. Consider right-sizing column data-types. I hate to see folks waste space by using NUMERIC(18,2) (a horrible default from front end designers) and VARCHAR(10) when the column will be filled with 99.9999% 10 character values. VARCHAR(anything) carries an extra 2 byte overhead to remember the length of the value.
3. Consider always using properly places semi-colons. Not using them has actually been deprecated for many years and the proverbial poo is going to hit the fan when they make them fully required.
4. Consider not mixing human languages in the code for things like variable names.





Hi Jeff,

Thanks for answers.


Hi Jeff,
This example has a context of academic studies, so the same was published, some points were not added in the description by a little forgetfulness on my part.
You're right in some observed details, but we should always have a little care with our words.
But I thank you for your observations and respect your criticisms.
I just don't accept being called an amateur.
Still, thank you for your reply.


Heh.. oh boy... here we go.

No where in my posts did I call you an "amateur". That's all on you, bud. And, no... no where did you even hint anything about an "academic study". In fact, you stated quite the contrary in the article...


This block of code is intended to demonstrate how we can in Microsoft SQL Server from version 2008. Use any user database to perform a execution of this code block and table storage RandomDataTable.

You can use this block of code to test and study environments, with the purpose of generating and populate a table with random data.



So here's a little friendly advice and tough-love from a retired MVP... Embrace being wrong. Embrace this community as a community of people that's out to help everyone, even MVPs. Embrace the learning you will experience if you humble down a bit and learn from those trying to help you instead of taking offense to their offerings or making back-peddle excuses . Always remember that the MVP award is a "service to the community award" and not a badge of knowledge or in anyway that is a certification of knowledge. Embrace the fact that if such offerings of constructive criticism make you think someone is calling you an "amateur" (and I patently did not nor even infer it never mind saying it), then that's all on you and only you have the power to change that by learning what everyone needs to learn, MVP or not... you are not perfect and, even if you are a true genius on a given subject, expect challenges and rise to the occasion because... that's how this community and the individuals that embrace it keep getting better. Always ask "Is there a better way" and always embrace those that say, "Lemme show you something" even if it's not quite that polite (and I was... I only dealt with facts). And always remember that the most learning occurs in the discussions that follow an article.

Now... shall I welcome a new member of SSC that took the time and effort to post something to the community as a friend? Or would you rather keep knocking that shinny new MVP ring. Wink



Hi Jeff,
I understand and accept your words, your observations and guidelines.
I recognise that we are always learning, I say this every day to myself.
MVP or not doesn't make me a different or better, but I take this with me, not to my knowledge, but at my work and dedication.
Thanks for taking the time to make me your thinking in a different way, I understand that my approach in this code has not been back to a large volume of data, and much less at the thought of the theory of set.
You're right, we have to think about working with the concept of DataSet and returning to columns, not the rows like a procedural development.
I appreciate your attention, to see me as a professional who is always willing to learn, collaborating with the SSC and also always trying to do the best by the community, as MVP or not.
Hugs.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)SSC Guru (779K reputation)

Group: General Forum Members
Points: 779745 Visits: 45874
Junior Galvão - MVP - Sunday, June 17, 2018 6:46 PM


Hi Jeff,
I understand and accept your words, your observations and guidelines.
I recognise that we are always learning, I say this every day to myself.
MVP or not doesn't make me a different or better, but I take this with me, not to my knowledge, but at my work and dedication.
Thanks for taking the time to make me your thinking in a different way, I understand that my approach in this code has not been back to a large volume of data, and much less at the thought of the theory of set.
You're right, we have to think about working with the concept of DataSet and returning to columns, not the rows like a procedural development.
I appreciate your attention, to see me as a professional who is always willing to learn, collaborating with the SSC and also always trying to do the best by the community, as MVP or not.
Hugs.


Well said. Welcome aboard and congratulations on being an MVP.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search