Vardecimal

Question

Post reply

Vardecimal

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Jeff Moden (3/18/2008)

I think you may have a misunderstanding of what it does... VarDecimal has nothing to do with the MONEY datatype. The only time that VarDecimal saves space is if you have a lot of "0" data or a lot of integer-like data according to what I read...

No - i understood the purpose - but the OP was asking whether it might apply to money (in addition to decimal).

That being said - "Extract foot from mouth...wipe egg from face...."

All right - it was something obvious. By me not inserting ANYTHING into the decimal, then one saves ALL of the space, the "fixed" one allocates the space...duh...

The initial question from the OP was whether it affected money or not, since money and decimal are essentially the "same" data type. I was getting "inconsistent" results this morning (but which were leaning towards "yes", thus the earlier post), so I decided to patch up to CU6 and see if that made a difference. Only to come up with this test (now flawed...) Must have done the same thing this morning too.

All right - let me go tear that other post out before someone listens to my bad advice.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

LOL... Sorry, Matt... I just had this thought of you hanging another bag of coffee... I'm on my third one... 5 gallon bags, mind you... 😛

--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)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (3/18/2008)

LOL... Sorry, Matt... I just had this thought of you hanging another bag of coffee... I'm on my third one... 5 gallon bags, mind you... 😛

It's like - you just keep on staring at the right hand when you know the magician is doing something with the left one.....hehe

At least you jarred it out of me...Not even sure you meant to, but hey - it worked!!!;):P:D:hehe:

It's just a good thing not to be out there starting a false rumor, even if by mistake..

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Man, do I ever understand that... there was this one thread that I had made about 7 posts on based on something I thought I knew... one of the guys threw me a hint... actually, 2 or 3 because I was pretty thick...

I finally got it and had to go back to 7 posts. Crossed out the code so that every one would know that something was up and posted a big ol' piece of humble pie on each one (like you did, right thing to do) saying that I'd made a huge mistake...

--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)
Intro to Tally Tables and Functions

Don Cooper-437855 Old Hand Points: 385 More actions · Answer 5

Don Cooper-437855

Old Hand

Points: 385

March 19, 2008 at 6:53 am

#791411

Sorry for being dense, but is my understanding correct that vardecimal would NOT have any affect on a money type field, even if it is filled with a lot of zero values?

Also, thanks for all the effort

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Don Cooper (3/19/2008)

Sorry for being dense, but is my understanding correct that vardecimal would NOT have any affect on a money type field, even if it is filled with a lot of zero values?

Also, thanks for all the effort

Correct - never mind all of my crazy testing. It affects only Decimals, money doesn't get affected.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Heh... Hence the name... varDECIMAL 😛

--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)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (3/19/2008)

Heh... Hence the name... varDECIMAL 😛

True - but now we KNOW the name is accurate...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Heh... yeah... nothing like the "TimeStamp" data type here... isn't hind site wonderful? :hehe:

--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)
Intro to Tally Tables and Functions

Mauve SSChampion Points: 11316 More actions · Answer 10

Does 2005's (sp2) vardecimal option have any affect on "money" type fields?

Per the BOL, money is 8 bytes and smallmoney is 4 bytes. Which means that they are storing it as an "int" and "bigint" under the covers.

Note that the number of decimal places in the "money" datatypes is limited to four (4) whiich precludes its use in some applications. Side note: I worked on a financial system (in Oracle) where we were computing gain/loss per share per day which needed to support the actuial number of decimal places. It was somewhere around 22 after the final calculation. While being a monetary amount ($), a "money" datatype would not work.

The new VARDECIMAL datatype is equivalent to the manner in which Oracle stores ALL numeric values. For financial systems, e.g., where lots of digits need to be stored, it is a major space savings. See my related post (440631): http://qa.sqlservercentral.com/Forums/FindPost440631.aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

The new VARDECIMAL datatype is equivalent to the manner in which Oracle stores ALL numeric values.

I haven't looked it up and I'm no Oracle ninja especially where datatypes come in... folks at work told me it was all fixed size if "Number" was used. Of course, they're not Ninja's either and they could be wrong. They didn't even know 10G had a MERGE statement and they all thought I meant server connections when I brought up CONNECT BY... 😛

--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)
Intro to Tally Tables and Functions

Mauve SSChampion Points: 11316 More actions · Answer 12

They didn't even know 10G had a MERGE statement

Actually, the MERGE statement was introduced in 9i (circa 2002) along with all of the new date and time datatypes that are now just being introduced in SQL Server 2008 (which will be 2009 when I can finally use it in production).

Trust me, Microsoft's SQL Server is playing "catch up" to Oracle.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

JohnG (3/19/2008)

They didn't even know 10G had a MERGE statement

Actually, the MERGE statement was introduced in 9i (circa 2002) along with all of the new date and time datatypes that are now just being introduced in SQL Server 2008 (which will be 2009 when I can finally use it in production).

Trust me, Microsoft's SQL Server is playing "catch up" to Oracle.

Heh... yeah... I remember the panic when 9i came out... kinda like SS 2000 sp3 and they had to come right out with a 3a because of boo-boos.

SQL Server hasn't been playing catch up forever, though... 9i was when they also came out with CASE to "replace" decode... SQL Server had it for about a million years before that.

--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)
Intro to Tally Tables and Functions

Mauve SSChampion Points: 11316 More actions · Answer 14

9i was when they also came out with CASE to "replace" decode... SQL Server had it for about a million years before that

True. OK, one for the SQL Server side of the "tally sheet".:blush:

However, I still can't code a CASE (switch) statement in pure T-SQL (non DML statement) where I can in PL/SQL. I'm forced to use a ton of nested IF...ELSE...IF...ELSE code.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

For what? Got an example of the PL/SQL that you're talking about?

--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)
Intro to Tally Tables and Functions