Jeff Moden

Forum Replies Created

Viewing 15 posts - 58,816 through 58,830 (of 59,067 total)

RE: Can this procedure be optimized?

I mentioned indexes (rather, the lack of) in my last reply to Scott.
--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)

March 17, 2005 at 8:23 pm

#547700
RE: Problem with bigint datatype in user defined function

Checkout the ParseName function... it was designed to parse octets in addresses and name in 4 part named database objects. Could help you to shorten your function a bit...
--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)

March 16, 2005 at 8:18 pm

#547440
RE: Seeking great resources for learning to optimize db access

You're welcome.... just in case you didn't know... Books-on-Line is free and comes with SQL Server... it's the name of the rather voluminous on-line help that comes with SQL Server.
--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)

March 16, 2005 at 8:01 pm

#547437
RE: Can this procedure be optimized?

Scott,
No offense was meant and I agree except that I've seen the optimizer give the non-Case method the win 2 out of 3 times when testing with and without proper indexes...
--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)

March 16, 2005 at 7:13 pm

#547433
RE: Can this procedure be optimized?

The CASE statement is very useful, indeed, although I don't think it will increase the performance in this particular case.
--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)

March 16, 2005 at 12:35 am

#547201
RE: Seeking great resources for learning to optimize db access

"Book-on-Line" is a great resource, once you learn how to use it and actually take the time to read it. And, I've found that some of the authors that write...
--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)

March 13, 2005 at 1:49 pm

#546776
RE: Converting textual dates to actual dates

Eheraux,
Your original problem stated:
I have an "OpeningDate" field in a table that's set up as a varchar, but the data it contains is all dates (3rd party app. I didn't...
--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)

March 10, 2005 at 9:09 pm

#546532
RE: Convert date from UTC to PST

I believe the time difference is -8 hours so try this...
DATEADD(hh,-8,yourUTCdatetime)
--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)

March 10, 2005 at 12:57 am

#546300
RE: List all tables and filenames for each

Very nice alzdba! It's exactly what I needed. Thanks for coming through on such short notice... I was really in a crunch and you've just made it possible for me...
--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)

March 9, 2005 at 6:48 am

#546130
RE: datetime stamp question

Here's a straight forward way that probably won't use available indexes...
SELECT yada-yada
FROM yourtable
WHERE datediff(mi,0,datecolumn)= datediff(mi,0,datetimelookingfor)
Here's a less straight forward way that will use available indexes...
SELECT...
--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)

March 9, 2005 at 6:44 am

#546127
RE: SQL Bulk Insert - using " to wrap text fields

David,
As promised, here's the actual BCP format file and the call to BCP to use it (from a command window). This has been working correctly for almost a year. Of...
--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)

March 9, 2005 at 6:15 am

#546113
RE: SQL Bulk Insert - using " to wrap text fields

I'll find the production code and see if I did anything different... sorry for the confusion.
--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)

March 8, 2005 at 11:40 am

#545970
RE: SQL Bulk Insert - using " to wrap text fields

Hi David,
The example I gave was modified to be generic from production code that I currently have working in the same fashion offered. Dunno what to say about the notation...
--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)

March 8, 2005 at 4:05 am

#545837
RE: SQL Bulk Insert - using " to wrap text fields

All good suggestions and I like the simplicity of Fred Wadley's solution. However, sometimes you can't use Bulk Insert because the DBA won't give you either "SA" permissions or "BULK...
--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)

March 7, 2005 at 6:20 am

#545690
RE: How to Remove Identity Property

Niel,
There is no such simple T-SQL to remove the IDENTITY property as you would remove a constraint. Even Enterprise Manager has to do a little song and dance to 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)

March 7, 2005 at 5:01 am

#545686

Viewing 15 posts - 58,816 through 58,830 (of 59,067 total)