Jeff Moden

Forum Replies Created

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

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
RE: SQL Query Combine with string

Morean,
SellerTools' answer can easily be modified to handle 8000 characters just by changing "Declare @sql as varchar(200)" to "Declare @sql as varchar(8000)". Still, it's dynamic SQL and might be better...
--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 4:04 am

#545673
RE: SQL Query Combine with string

Actually, it can be done without the overhead of Dynamic SQL by using a CASE statement in the WHERE clause....
SELECT ID,Name,Description
FROM Table1
WHERE Name = 'AA'
AND ID...
--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 5, 2005 at 9:44 am

#545622
RE: Returning a record for each day in a range

A fellow on this forum who goes by the name of "Adam Mechanic" would have you create a permanent date table so that you can easily solve these types 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 3, 2005 at 6:27 am

#545186
RE: Maximum bytes per row?

After reading the links that Frank posted and reading "Estimating Table Size" in BOL AND reading "Data Types" in BOL, I've kind of decided that it's just easier to remember...
--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 3, 2005 at 6:00 am

#545175

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