Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

help in my procedure Expand / Collapse
Author
Message
Posted Wednesday, December 15, 2010 3:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 5,583, Visits: 9,600
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
Author - SQL Server T-SQL Recipes
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
Posted Wednesday, December 15, 2010 3:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 5,583, Visits: 9,600
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
Author - SQL Server T-SQL Recipes
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 #1035513
Posted Wednesday, December 15, 2010 4:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 24, 2015 2:42 PM
Points: 292, Visits: 1,648
I'm not sure if this is the offical standard, but here you go:
http://metadata-stds.org/11179/
Post #1035553
Posted Thursday, December 16, 2010 1:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 27, 2015 8:14 AM
Points: 5,204, Visits: 12,306
WayneS (12/15/2010)
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.


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.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1035682
Posted Thursday, December 16, 2010 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 27, 2015 8:14 AM
Points: 5,204, Visits: 12,306
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.



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1035687
Posted Thursday, December 16, 2010 5:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 6,336, Visits: 7,325
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

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
Posted Thursday, December 16, 2010 5:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 6,336, Visits: 7,325
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

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
Posted Thursday, December 16, 2010 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 6,336, Visits: 7,325
Gianluca Sartori (12/16/2010)
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.



I haven't heard of performance problems with those functions in static SQL. I use CASE and OR all the time. Now I'm going to have to look this up and see what the heck you're talking about.

Will you stop making me do research, darnit!


Brandie Tarvin, MCITP Database Administrator

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 #1035757
Posted Thursday, December 16, 2010 6:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 27, 2015 8:14 AM
Points: 5,204, Visits: 12,306
Brandie Tarvin (12/16/2010)
Gianluca Sartori (12/16/2010)
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.



I haven't heard of performance problems with those functions in static SQL. I use CASE and OR all the time. Now I'm going to have to look this up and see what the heck you're talking about.

Will you stop making me do research, darnit!


When dealing with optional parameters, you can code the expression as static SQL or dynamic SQL.
All the syntaxes to code the expression as static SQL lead to index scans, while adding the expression only when the parameter has a non-null value lets a seek happen.

Example:

-- Let's assume NULL is the default value for an optional parameter when not passed.
-- When @column_name is not passed, you want the statement to return all column names.
DECLARE @column_name varchar(128)

-- This is how you would do it if you didn't have optional parameters
-- It is implemented with an index seek
SELECT name
FROM syscolumns A
WHERE name = @column_name

-- These are some ways to code for the optional parameter.
-- They all lead to index scans
SELECT name
FROM syscolumns A
WHERE name = ISNULL(@column_name, name)

SELECT name
FROM syscolumns A
WHERE name = COALESCE(@column_name, name)

SELECT name
FROM syscolumns A
WHERE name = @column_name OR @column_name IS NULL

SELECT name
FROM syscolumns A
WHERE name = CASE WHEN @column_name IS NULL THEN name ELSE @column_name END

-- This is how you would do it with dynamic SQL
-- The filter expression is added only when needed and goes for and index seek
DECLARE @sql varchar(8000)
SET @sql = 'SELECT name FROM syscolumns A '
IF @column_name IS NOT NULL
SET @sql = @sql + ' WHERE name = @column_name '

EXEC sp_executesql @sql, N'@column_name varchar(128)', @column_name

See what I mean?
I don't want to go into deep details, Erland did it much better than I could ever do.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1035792
Posted Thursday, December 16, 2010 10:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 24, 2015 2:42 PM
Points: 292, Visits: 1,648
Brandie Tarvin (12/16/2010)[hrYou'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.

I am not following you, but I guess by this comment and your pervious one about ISNULL versus COALESCE, it seems you do not understand them.

The OP was using ISNULL to return a non-null value (ie - INSULL(ColName, 0) or ISNULL(@Varibale, '') etc..). So just like any function if you do not use it right it won't work right. If you replaced ISNULL for COALESCE you'd still get the same results. So, again, I'm confused at what point you are trying to make? Can COALESCE return NULL? Yes is can. But, if used appropriatly, it won't and, furthermore, as Joe mentioned its ANSI compliant. So, why would use use anything else?

I'll also add my disgust with ISNULL because of it's difference with the other IS... functions.. ISDATE returns a boolean... ISNUMERIC returns a boolean.. All is good witht he world.. ISNULL returns.. wait what? the first non-null values? WTF?

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.

Partial true.. If people don't use a STANDARD doesn't make it not a standard.. (wait is that english?) :)

I hand out standards to the people I work with. Inclusing ISO standards. If I'm in charge they will follow them. If I'm not in charge, I'll attempt to help the person in charge understand why we should use them. Usually it's an uphill battle because if they don't want to support a coding standard then they ussuall don't even understand the difference between 1st and 2nd normal form. So, baby steps.

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.

Anything you've read by Kimball should be forgotten. If you still have the book you read that in (assuming it was a book) you should burn it now to make sure it doesn't infect any other minds. That man has propbably done more damage to the data industry and any other person on the planet. Brining up his name to people that actuall undertand relation theory is not going to help your arguments at all.

Post #1035990
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse