Jeff Moden

Forum Replies Created

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

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
RE: How do I know when to use temp tables/table variables?

Tom.Thomson (12/13/2010)
Junglee_George (12/2/2010)
Hi
Please have a look into this article also
http://www.mssqltips.com/tip.asp?tip=1556
Thanks
Rather a dangerous article with false statements like
# Table variables can not have Non-Clustered Indexes
and
# You can not create constraints...
--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:31 am

#1262210
RE: How do I know when to use temp tables/table variables?

SQLNW (12/13/2010)
hi everyone ,request your help urgently.
Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure
thanks for your help.
That's...
--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:27 am

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

shyam-792781 (12/7/2010)
Hi Rich Script is not working in my Case it is taking too much in the DROP TABLE dbo.tableName
I have backup for the table before deleting main table
Thanks
Shyam
What do...
--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 12, 2010 at 8:20 pm

#1261498

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