help in my procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » help in my procedure

34 posts, Page 2 of 4

««1 2 3 4 »»»

help in my procedure

Rate Topic

Display Mode

Topic Options

Author

Message

CELKO

Posted Wednesday, December 15, 2010 1:43 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

>> There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field [sic: columns are not fields] quite a bit, I need to know. <<

Let me do a cut&paste from someone else old postings:

=================
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4);

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult
Output: 2949.0000 2949.8525
To some of the people who said that they don't divide money by money Here is one of my queries to calculate correlations, changing that to money gives wrong results
SELECT T1.index_id,T2.index_id,(AVG(T1.monret*T2.monret)
-(AVG(T1.monret) * AVG(T2.monret)))
/((SQRT(AVG(SQUARE(T1.monret)) - SQUARE(AVG(T1.monret))))
*(SQRT(AVG(SQUARE(T2.monret)) - SQUARE(AVG(T2.monret))))),
CURRENT_TIMESTAMP,@MAXDATE
FROM Table1 AS T1,
Table1 AS T2
WHERE T1.Date = traDate
GROUP BY T1.index_id,T2.index_id;

======================>>
>> Dialect??? Not sure what you mean by that. Please explain. <<

Proprietary and not portable. CREATE INDEX is not in Standard SQL, but it is portable because of the X\Open consortium specs. Dialects just don't port and will be the first things to deprecate when the Standard is in teh next release.

>> This [meta data affixes on data element name] is a personal preference issue. Using "T-" as a naming standards is at best annoying to type, but shouldn't cause any problems unless it's a reserved keyword issue that I'm unaware of.<<

1) Violates ISO-11179 rules about data element names
2) Violates a basic rule of data modeling. Tell me what it is by its nature, not where it is currently stored, how it is currently stored or how it is used locally
3) Screws up your data dictionary; Ever see the Monty Python "Bruce" skit? This is the SQL Version of that nonsense.
4) In a language with only one data structure, it is redundant and looks as silly as putting "noun-" in front of every noun in an English sentence. It says that you are still writing BASIC, where the one-pass interpreter needed the $ tell it this variable was string and not a float.

>> Now Microsoft may or may not have implemented COALESCE() by ANSI-Standards. I don't know. But given that no one can compare one unknown value (NULL) to another unknown value, I don't think using COALESCE() will help the OP. Especially since COALESCE returns NULL if all the expressions being evaluated are NULL. <<

What should it return? 42? The convention in Standard SQL is that NULLs propagate -- "Ab nulo, ex nulo"

Microsoft did implement to ANSI/ISO specs, but it is more complicated. COALESCE() is a member of the CASE expression family. It looks at all the expressions in its list and sets data type of the results to the highest type in the list. ISNULL() uses the data type of the first argument:

BEGIN
DECLARE @nbr INTEGER;
DECLARE @dec DECIMAL (7,5);
SET @dec = 99.999;
SET @nbr = NULL;
SELECT ISNULL (@nbr, @dec); -- 99
SELECT COALESCE (@nbr, @dec); -- 99.999

SELECT ISNULL( NULL, NULL); -- this works!
SELECT COALESCE (NULL, NULL); -- this fails
SELECT COALESCE (NULL, CAST (NULL AS INTEGER)); -- needs a type!

END;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1035453

Evil Kraig F

Posted Wednesday, December 15, 2010 2:05 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

CELKO (12/15/2010)

=================
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4);

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult
Output: 2949.0000 2949.8525

The problem here isn't necessarily in the money type, but it's in the override that occurs.

In particular, @Num1/@Num2 turns into either a float, or a decimal (20, 20)... leading me to assume the float.

Expanding on your code above:

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4);

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult

select @mon1, @num1
select @mon2, @num2
select @mon3, @num3
SELECT @mon1/@mon2, @Num1/@Num2
SELECT CONVERT( DECIMAL( 19, 4), @Num1) / CONVERT( DECIMAL( 19, 4), @Num2)
SELECT CONVERT( DECIMAL( 19, 4), @Num1 / @Num2)
SELECT @Mon2*@Mon3, @Num2*@Mon3
SELECT 0.2949*10000, 0.2949852507374631268 * 10000

In particular, the results from SELECT @mon1/@mon2, @Num1/@Num2 are incredibly disparate, which is causing the difference.

The reconversion from the float into the correct # of significant digits also changes the resultant rounding, a known issue with float. It depends on what level of accuracy to what significance you want.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1035473

LutzM

Posted Wednesday, December 15, 2010 2:11 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

CELKO (12/15/2010)

...
ISO-11179 is big and hard to read. That is why I put it into English in my SQL PROGRAMMING STYLE book. The DoD and Feds are hot for it and it is showing up in contracts now. ...

Really? You made DoD and Feds to include your book as one of their contract requirements???
That really makes it easy: If they require your book as a standard they either have to provide a copy of it or the price will be included in the quotation.
I can assure you, this specific cost will be a single item all by itself... (including a nice and warm side note regarding a "wanna-be-standard").
If the backlink from "it" to "your book" is a misinterpretation from my side, please replace it with "wanna-be-standard".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1035479

WayneS

Posted Wednesday, December 15, 2010 3:00 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235

CELKO (12/15/2010)

>> There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field [sic: columns are not fields] quite a bit, I need to know. <<

Actually, (when dealing with SQL Server) money is neither a field or column. Money is a data type.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1035510

WayneS

Posted Wednesday, December 15, 2010 3:03 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235

Craig Farrell (12/15/2010)

Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. Since 1=1 is always true, it never evaluates (as far as a limited testing I did showed), but saves you some complex semantics, since all statements, no matter if first or not, can start with the AND field = @_param1 structure.

I think that this should be qualified with: "when writing dynamic SQL". It is incredibly useful then; and utterly useless any other time.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Post #1035513

Lamprey13

Posted Wednesday, December 15, 2010 4:14 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:22 PM
Points: 285, Visits: 1,575

I'm not sure if this is the offical standard, but here you go:
http://metadata-stds.org/11179/

Post #1035553

spaghettidba

Posted Thursday, December 16, 2010 1:26 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

WayneS (12/15/2010)

Craig Farrell (12/15/2010)

I think that this should be qualified with: "when writing dynamic SQL". It is incredibly useful then; and utterly useless any other time.

The optimizer can strip off this kind of expressions using a feature called "contradiction detection", that prevents the engine from evaluating expressions that are always true or false (tautologies or contradictions).
Tautological expressions don't affect performance, on the contrary, they can boost them. Try issuing that query changing "1 = 1" to "1 = 2" and look at the exec plan: it will be a single constant scan.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1035682

spaghettidba

Posted Thursday, December 16, 2010 1:49 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

CELKO (12/15/2010)

3) Why are you wasting time with dynamic SQL?

Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.
Using COALESCE, ISNULL, CASE and OR to express the same in static SQL leads to severe performance problems:
1) They all lead to index scans instead of seeks
2) Static SQL has issues with plan caching and parameter sniffing. See Erland Sommarskog's site for a more detailed explanation.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1035687

Brandie Tarvin

Posted Thursday, December 16, 2010 5:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 6,668, Visits: 5,695

CELKO (12/15/2010)

>> Now Microsoft may or may not have implemented COALESCE() by ANSI-Standards. I don't know. But given that no one can compare one unknown value (NULL) to another unknown value, I don't think using COALESCE() will help the OP. Especially since COALESCE returns NULL if all the expressions being evaluated are NULL. <<

What should it return? 42? The convention in Standard SQL is that NULLs propagate -- "Ab nulo, ex nulo"

You're missing the point of my comment. He's trying to match a potentially NULL value to another value. You can't do that with a function that is designed to return NULL. NULL doesn't match anything. It simply isn't mathematically possible to match an unknown value against a known value and get an answer of any worth. Hence COALESCE() will not work and his use of ISNULL() is entirely appropriate because he's substituting a blank string for any possible NULL response.

-------Back to the beginning--------------

RE: The money. I did research after I asked that question because it really concerned me. After looking at it, and looking at other people's responses, I disagree that there's really a math problem with this data type. It's how the data type is used (usually in equations its not meant to be used in) that would cause a problem. Having worked in Accounts Payable / Receivable for most of my life before I became a DBA, I know how to use it. One doesn't go around dividing it down until it gets past the digits it needs to be unless one is prepared to lose change.

There are certainly situations where it's inadvisable to use it, such as when you need more decimal places that money allows for, but to be fair, it's a perfectly solid data type that does exactly what it should do. No more, no less. Exactly like a lot of things in SQL Server.

CELKO (12/15/2010)

>> Dialect??? Not sure what you mean by that. Please explain. <<

Proprietary and not portable. CREATE INDEX is not in Standard SQL, but it is portable because of the X\Open consortium specs. Dialects just don't port and will be the first things to deprecate when the Standard is in teh next release.

So you're telling people they can't use the tools that are provided to them in the software that they use? That's just silly. Especially as most companies are so loath to change expensive software programs for another that it makes portability almost a non-issue.

CELKO (12/15/2010)

>> This [meta data affixes on data element name] is a personal preference issue. Using "T-" as a naming standards is at best annoying to type, but shouldn't cause any problems unless it's a reserved keyword issue that I'm unaware of.<<

1) Violates ISO-11179 rules about data element names

I have to disagree with this one. Standards aren't standards unless everyone adopts them. You can call Blue Ray a standard if you want, it won't prevent people from buying DVD. Companies don't provide ISO rules to their programmers. Programmers aren't going to go out and buy standards books on their own. And where are the training classes? I used to work at a university that certified people in ISO standards via Conferences and other CTE classes. I can promise you that this standard was not one of them.

CELKO (12/15/2010)

2) Violates a basic rule of data modeling.

Not sure what rule you're talking about. A lot of the books I've read specifically instruct people to name tables, views, procs and functions using an enterprise-wide standard that tells everyone what they're dealing with when they're coding. In fact, I think I remember reading a Kimball paper that said the same thing.

CELKO (12/15/2010)

3) Screws up your data dictionary;

What's your definition of "data dictionary"? Mine is a report I wrote up that specifically lists tables, views, procs and functions in different areas of the report and then lists dependencies on each. I don't see how my data dictionary would be screwed up by something as simple as a name.

CELKO (12/15/2010)

4) In a language with only one data structure, it is redundant and looks as silly as putting "noun-" in front of every noun in an English sentence. It says that you are still writing BASIC, where the one-pass interpreter needed the $ tell it this variable was string and not a float.

Again, this is your personal preference, a statement of opinion, like the statement right before it. Nothing in this statement is scientific or logical. Reason 1 is the closest thing you have to an actual fact in your response. Reason 2 is skirting the line because it again depends on what books someone's been reading about data modeling.

I can see both sides of the naming argument, but nothing about either side has any "proof" that the data (or reports) will get hosed if you go one way or another on the issue. That being the case, I stand by my opinion that naming conventions are a matter of choice and the only thing that can be wrong about them is if they aren't consistently implemented across the enterprise.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1035754

Brandie Tarvin

Posted Thursday, December 16, 2010 5:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 6,668, Visits: 5,695

WayneS (12/15/2010)

CELKO (12/15/2010)

>> There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field [sic: columns are not fields] quite a bit, I need to know. <<

Actually, (when dealing with SQL Server) money is neither a field or column. Money is a data type.

Yeah, that was my bad. I found myself typing field and column all day yesterday every time I meant to type data type. I don't know what happened. DOH.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1035755

« Prev Topic | Next Topic »

34 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions