FORMAT that string

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 1, 2018 at 3:42 pm

  #1981718

  Just in case a casual reader that doesn't know about the horrible performance of FORMAT reads this question, here's the reason why so many people on this discussion are saying not to use the FORMAT function...

  Use the following code to create a temp table that contains a million INTEGER values from 0 to 99999 to represent a much larger dataset that that in the original QOTD.
  
 SELECT TOP 1000000
        SomeIntZip = ABS(CHECKSUM(NEWID())%100000)
   INTO #MyHead
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;


  Once that's done, run the following code and then check out the "Messages" tab to see why FORMAT sucks so bad that it has its own center of gravity. 😀
  
  PRINT REPLICATE('-',119);
  PRINT '===== Format Performance =====';
    SET STATISTICS TIME ON;
DECLARE @Bitbucket CHAR(5);
 SELECT @Bitbucket = FORMAT(SomeIntZip,'0####')
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO
  PRINT REPLICATE('-',119);
  PRINT '===== Right(5) of Math Conversion Performance =====';
    SET STATISTICS TIME ON;
DECLARE @Bitbucket CHAR(5);
 SELECT @Bitbucket = RIGHT(SomeIntZip+100000,5)
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO
  PRINT REPLICATE('-',119);
  PRINT '===== Right(5) of Concatenation Performance =====';
    SET STATISTICS TIME ON;
DECLARE @Bitbucket CHAR(5);
 SELECT @Bitbucket = RIGHT('00000'+CONVERT(VARCHAR(5),SomeIntZip),5)
   FROM #MyHead;
    SET STATISTICS TIME OFF;
GO


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

• Carlo Romagnano

  SSC-Insane

  Points: 22670

  More actions

  March 2, 2018 at 1:24 am

  #1981737

  Jeff Moden - Thursday, March 1, 2018 3:42 PM

  RIGHT(SomeIntZip+100000,5)

  It's my favorite

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  March 2, 2018 at 5:40 am

  #1981756

  Eirikur Eiriksson - Thursday, March 1, 2018 1:00 AM

  Although I never use the format function because of it's horrific performance, I am pretty certain that the alleged answer is not the correct one.
  😎

  I'm in the same boat, so I guessed.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions