concatenate with leading zeros

• paul.s.lach

  Old Hand

  Points: 329

  More actions

  September 11, 2013 at 6:23 am

  #1649239

  dwain.c (9/10/2013)

  ---

  Paul - I get fairly stable results with this test harness. Can you confirm?

  I am also running on a SQL Server that is fairly quiescent (no other processes running).

  Even with the recompile option I am getting similarly variable results. I am running on a virtual machine where, at the time of the testing, I am the only active connection. I have 8 GB of memory assigned and the virtual machine thinks it has 2 quad-core processors.

  Can you post your results so I can see your timings?

• paul.s.lach

  Old Hand

  Points: 329

  More actions

  September 11, 2013 at 7:06 am

  #1649278

  Part of the reason why Paul S. might be seeing some instability is because he didn't trust the @Bitbucket variable and took to writing to disk. Depending on what else is running, there could be substantial disruptions that you wouldn't see on a quieter machine.

  Jeff, I do not believe my test harness is writing to disk after the initial setup of the temporary table. By using the WHERE clause in each of the timed sections I believe none of the rows will test as true since the [Result] column already contains the correct 9 character value. However, you know how SQL Server works a heck of a lot better than I do so I am wondering, is my assumption that I have eliminated writing to the disk correct?

  Also, I am running on a virtual machine with 8 GB assigned and the virtual machine thinks it has 2 quad-core processors. When I am doing the test runs my connection is the only active connection. Wait a minute, I think I may have just answered the question about the variability. I just ran the test harness on a physical machine and got much more consistent results so I think the variability issue is caused by the fact that SQL Server is running on a virtual machine.

  If you run the test on your machine, what do your results look like (please post)?

  Here is the full test harness again:

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  ;

  --===== Create and populate the test table on-the-fly.

  CREATE TABLE #TestTable ([C1] INT,

  [C2] INT,

  [Result] CHAR(9),

  );

  INSERT INTO [#TestTable] ( [C1], [C2], [Result] )

  SELECT TOP 10000000

  C1 = ABS(CHECKSUM(NEWID()))%1000000

  ,C2 = ABS(CHECKSUM(NEWID()))%1000

  ,'000000000'

  FROM master.sys.all_columns ac1

  CROSS JOIN master.sys.all_columns ac2

  ;

  -- Initialize the Result column value

  UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);

  --===== Create a target variable that will take the display time out of the picture.

  DECLARE @Billion INT;

  SET @Billion = 1000000000;

  DECLARE @Thousand INT;

  SET @Thousand = 1000;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

• Jeff Moden

  SSC Guru

  Points: 1003870

  More actions

  September 11, 2013 at 7:38 am

  #1649305

  paul.s.lach (9/11/2013)

  ---

  Jeff, I do not believe my test harness is writing to disk after the initial setup of the temporary table. By using the WHERE clause in each of the timed sections I believe none of the rows will test as true since the [Result] column already contains the correct 9 character value. However, you know how SQL Server works a heck of a lot better than I do so I am wondering, is my assumption that I have eliminated writing to the disk correct?

  Apologies for not being clear... I've been in a real crunch for the last week or so.

  What I meant was just exactly what you said. You write the data once and then test against it and the WHERE clauses prevent any additional writes. The problem is that still brings the I/O system, whatever it is for a given machine, into play and you start measuring IO burps instead of just the algorithm. That's why I do the dump to the variable, instead. As I said there, the variable is reassigned a new value for every row processed. To be sure, though, if we were testing the effectivity (to coin a phrase) of WHERE clauses, your test harness would be a good one for that.

  Also, I am running on a virtual machine with 8 GB assigned and the virtual machine thinks it has 2 quad-core processors. When I am doing the test runs my connection is the only active connection. Wait a minute, I think I may have just answered the question about the variability. I just ran the test harness on a physical machine and got much more consistent results so I think the variability issue is caused by the fact that SQL Server is running on a virtual machine.

  I'll have to do that when I get home.

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• paul.s.lach

  Old Hand

  Points: 329

  More actions

  September 11, 2013 at 8:34 am

  #1649339

  Apologies for not being clear... I've been in a real crunch for the last week or so.

  What I meant was just exactly what you said. You write the data once and then test against it and the WHERE clauses prevent any additional writes. The problem is that still brings the I/O system, whatever it is for a given machine, into play and you start measuring IO burps instead of just the algorithm. That's why I do the dump to the variable, instead. As I said there, the variable is reassigned a new value for every row processed. To be sure, though, if we were testing the effectivity (to coin a phrase) of WHERE clauses, your test harness would be a good one for that.

  Man! I have spent way more time on this than I had planned, but I spend a lot of my time wringing speed out of my stored procedures and I find this issue to be fascinating.

  I made 2 versions of the test harness: 1 that uses the @Bitbucket method and 1 that uses the WHERE clause method (full text of both below). I ran both tests on a physical machine to try to reduce the variability in the timings. Somewhat surprisingly, the WHERE clause method is faster than the @Bitbucket method by about 15%.

  Getting back to the original question, it appears that using numbers or variables in the formula does not make any measurably consistent difference, but using the SUBSTRING construct does appear to be slightly faster than using RIGHT.

  Here are the results:

  @Bitbucket Method

  First Run Second Run

  -------------------- --------------------

  RIGHT SUBSTRING RIGHT SUBSTRING

  -------------------- --------------------

  All Numbers 5538 5772 5304 5226

  Billion 6116 5445 5288 5164

  Both 6099 5195 5507 5179

  Total time for all 12 runs: 65833

  WHERE Clause Method

  First Run Second Run

  -------------------- --------------------

  RIGHT SUBSTRING RIGHT SUBSTRING

  -------------------- --------------------

  All Numbers 4587 4399 4976 4477

  Billion 4633 4368 4961 4696

  Both 4711 4462 4742 5132

  Total time for all 12 runs: 56144

  @Bitbucket method test script:

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  ;

  --===== Create and populate the test table on-the-fly.

  CREATE TABLE #TestTable ([C1] INT,

  [C2] INT

  );

  INSERT INTO [#TestTable] ( [C1], [C2] )

  SELECT TOP 10000000

  C1 = ABS(CHECKSUM(NEWID()))%1000000

  ,C2 = ABS(CHECKSUM(NEWID()))%1000

  FROM master.sys.all_columns ac1

  CROSS JOIN master.sys.all_columns ac2

  ;

  --===== Create a target variable that will take the display time out of the picture.

  DECLARE @Bitbucket CHAR(9);

  DECLARE @Billion INT;

  SET @Billion = 1000000000;

  DECLARE @Thousand INT;

  SET @Thousand = 1000;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(1000000000+(C1*1000)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(1000000000+(C1*1000)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  WHERE clause test script:

  --===== Conditionally drop the test table to make reruns in SSMS easier.

  IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  ;

  --===== Create and populate the test table on-the-fly.

  CREATE TABLE #TestTable ([C1] INT,

  [C2] INT,

  [Result] CHAR(9),

  );

  INSERT INTO [#TestTable] ( [C1], [C2], [Result] )

  SELECT TOP 10000000

  C1 = ABS(CHECKSUM(NEWID()))%1000000

  ,C2 = ABS(CHECKSUM(NEWID()))%1000

  ,'000000000'

  FROM master.sys.all_columns ac1

  CROSS JOIN master.sys.all_columns ac2

  ;

  -- Initialize the Result column value

  UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);

  --===== Create a target variable that will take the display time out of the picture.

  DECLARE @Billion INT;

  SET @Billion = 1000000000;

  DECLARE @Thousand INT;

  SET @Thousand = 1000;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Right Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)

  WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Straight Math + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With Billion Variable + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

  PRINT '

  ========== Math With 2 Variables + Substring Method =========='

  SET STATISTICS TIME ON;

  UPDATE #TestTable

  SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)

  WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);

  SET STATISTICS TIME OFF;

• andrewbolding

  SSC Rookie

  Points: 45

  More actions

  September 30, 2014 at 3:12 pm

  #1749266

  Try this

  SELECT RIGHT('000000' + CAST(Col1 as VARCHAR), 6) + RIGHT('000' + CAST(Col2 as VARCHAR), 3)

  Hope this helps.

  Edit:

  Just saw this was an older post lol

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  October 1, 2014 at 3:00 am

  #1749331

  More Integer Math less RIGHT 🙂

  PRINT '========== Integer Math RIGHT(RIGHT) Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  PRINT '========== Integer Math RIGHT Method =========='

  SET STATISTICS TIME ON;

  SELECT @Bitbucket = right(1000000000 + 1000*c1 + c2,9)

  FROM #TestTable;

  SET STATISTICS TIME OFF;

  ========== Integer Math RIGHT(RIGHT) Method ==========

  SQL Server Execution Times:

  CPU time = 968 ms, elapsed time = 933 ms.

  ========== Integer Math RIGHT Method ==========

  SQL Server Execution Times:

  CPU time = 782 ms, elapsed time = 804 ms.

  P.S. Oh, a 1+ year old topic. Problems with my DATE math. 🙁

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  October 1, 2014 at 8:02 am

  #1749402

  Anyone wish to check how 2012 version would do?

  select FORMAT(C1*1000+c2,'000000000')

  from #TestTable

  😉

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]