Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,701 through 35,715 (of 59,102 total)

RE: Hidden Formatting Troubles with STR() (SQL Spackle)

Jason Lees-299789 (12/15/2010)
Like others Ive always used the CAST or Convert functions for converting numbers to strings and so didnt know the pitfalls of using the STR() function so it...
--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)

December 15, 2010 at 9:18 am

#1262956
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

Nadrek (12/15/2010)
Hardy21 (12/15/2010)
Nice article.
I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.
May I suggest 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)

December 15, 2010 at 9:11 am

#1262950
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

nigel. (12/15/2010)
Nice little eye opener Jeff, thanks.
I particularly like :
...formatting data in SQL Server is your basic "Bozo-no-no"
Can I quote that the next time I see another question...
--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)

December 15, 2010 at 8:58 am

#1262940
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

Hardy21 (12/15/2010)
Nice article.
I have checked STR() usage in my application but usage is safe because we are not dealing with more than 12 digit number.
Very cool. I love...
--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)

December 15, 2010 at 8:58 am

#1262939
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

Carlo Romagnano (12/15/2010)
I always use this this syntax:
RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)
Because I didn't know that STR() is also used for formatting, I thought it only converts string to number.
I...
--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)

December 15, 2010 at 8:56 am

#1262938
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

rmechaber (12/15/2010)
Thanks Jeff, for your customary focused, useful style and content!
<math_complaint>
Your article points out a secondary annoyance here in SQL: the ROUND() function returns a value of the same type...
--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)

December 15, 2010 at 8:52 am

#1262935
RE: Round to next 1000 or 100 without Ceiling function

parthi-1705 (12/13/2010)
But i want to know only CEILING can do this or some other functions can do this or not
Thanks
Parthi
Lookup ROUND in Books Online and learn what happens when...
--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)

December 15, 2010 at 1:49 am

#1262676
RE: Round to next 1000 or 100 without Ceiling function

Tom.Thomson (12/15/2010)
GSquared (12/14/2010)
GilaMonster (12/13/2010)
Seriously?
Use the one most suited to what you're trying to do. You don't get prizes for the fanciest, most complex way of writing code.
If you're trying to...
--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)

December 15, 2010 at 1:47 am

#1262673
RE: SQL2K -> SQL 2008, EXTREMELY slow cursor fetch

One of the things I've found is that 2K8 is a whole lot less tolerant of implicit data conversions than 2K or 2K5 was. Make sure that any joins...
--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)

December 15, 2010 at 12:01 am

#1262644
RE: Construct an EXECUTE string

Mac_85 (12/13/2010)
We have a number of functions that have been built with differing permutations of parameters, allowing us to call any stored procedure from these functions.
It seemed to make the...
--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)

December 14, 2010 at 11:58 pm

#1262643
RE: SELECT All Columns from a table - without re-engineering if table stucture changes

ASDL (12/14/2010)
I understand that but in my case I have to get all columns.
Please understand that that's usually a bad thing to do because it makes it real tough on...
--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)

December 14, 2010 at 11:52 pm

#1262642
RE: We Are Not a Meritocracy

I'll take both, please. You've got to know your stuff and be able to work with people to get the job done. Well... unless you're flippin' your own burgers....
--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)

December 14, 2010 at 11:39 pm

#1262637
RE: Hidden Formatting Troubles with STR() (SQL Spackle)

Aye. Thanks Wayne. After I found out (the hard way, for sure) about some of the faults of STR(), I don't use it anymore... unless I'm extremely caffeine...
--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)

December 14, 2010 at 11:30 pm

#1262634
RE: Tuning slow query

LOOKUP_BI-756009 (12/13/2010)
Yes, I tried John's response and did much more quicker. Thank You all.
Still, I believe you have a many-to-many join and a bit of extra criteria would help. ...
--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)

December 14, 2010 at 7:37 am

#1262218
RE: Unable to modify table. Timeout expired. The timeout period elapsed prior

shyam-792781 (12/13/2010)
Thanks.
My Problem have been resolved
Shyam
That's nice. Would you mind telling us how you resolved the problem please? Two way street here, ya know. 😉
--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)

December 14, 2010 at 7:32 am

#1262214

Viewing 15 posts - 35,701 through 35,715 (of 59,102 total)