Odd columns datatype precisions showing in SSMS View...

• MyDoggieJessie

  SSC-Forever

  Points: 44486

  More actions

  August 24, 2023 at 3:41 pm

  #4270249

  Okay... haven't posted in a long time and was hoping I'd have a better topic to share but... we're seeing issues when expanding the view columns in SSMS and it's showing the wrong precision.  The view is pulling from underlying tables using DECIMAL(11,2) but the view shows the columns as DECIMAL(12,2) - what would cause this?

  In the view one of columns look like:

  ,a.[MyCostPerMonth] - ISNULL(b.[MyCostPerMonth], 0) AS [MyCostPerMonth]

  The underlying table column definition:

  [MyCostPerMonth] [decimal](11, 2) NOT NULL,

  Has anyone ever seen this?

   

  Attachments:

  You must be logged in to view attached files.

  ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

• MyDoggieJessie

  SSC-Forever

  Points: 44486

  More actions

  August 24, 2023 at 3:53 pm

  #4270256

  I did come across this... which does explain it... but geeez, never in 24 years have I been bitten by this in a VIEW

  https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

  "Operators can always change precision scale..."

  <table>

  <thead>

  <tr>

  <th>Operation</th>

  <th>Result precision</th>

  <th>Result scale <i>1</i></th>

  </tr>

  </thead>

  <tbody>

  <tr>

  <td>e1 + e2</td>

  <td>max(s1, s2) + max(p1 - s1, p2 - s2) + 1</td>

  <td>max(s1, s2)</td>

  </tr>

  <tr>

  <td>e1 - e2</td>

  <td>max(s1, s2) + max(p1 - s1, p2 - s2) + 1</td>

  <td>max(s1, s2)</td>

  </tr>

  <tr>

  <td>e1 * e2</td>

  <td>p1 + p2 + 1</td>

  <td>s1 + s2</td>

  </tr>

  <tr>

  <td>e1 / e2</td>

  <td>p1 - s1 + s2 + max(6, s1 + p2 + 1)</td>

  <td>max(6, s1 + p2 + 1)</td>

  </tr>

  <tr>

  <td>e1 { UNION | EXCEPT | INTERSECT } e2</td>

  <td>max(s1, s2) + max(p1 - s1, p2 - s2)</td>

  <td>max(s1, s2)</td>

  </tr>

  <tr>

  <td>e1 % e2</td>

  <td>min(p1 - s1, p2 - s2) + max(s1, s2)</td>

  <td>max(s1, s2)</td>

  </tr>

  </tbody>

  </table>

   

  ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

• Jeff Moden

  SSC Guru

  Points: 1004323

  More actions

  August 25, 2023 at 5:34 pm

  #4271339

  After more than 2 decades of having CPU's that have some serious math co-processors, you'd think they change the code for the floating point math that occurs in the NUMERIC and DECIMAL datatypes.  Even Granny's old 4 function drugstore calculator handles such calculations better in a lot of cases.

   

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