Jeff Moden

Forum Replies Created

Viewing 15 posts - 50,446 through 50,460 (of 59,099 total)

RE: getting only date from datetime

gaurava16fc (5/20/2008)
select count(datetime) from XYZ where convert(datetime,left((convert(nvarchar,datetime),11)) = '2008-05-15'
Yes, that works... but no chance of an Index SEEK. The code should be written similar to the following to allow...
--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)

May 20, 2008 at 1:36 am

#817621
RE: query

Actually, we still need a Create Table statement, and some data. Otherwise, we're just guessing as to what your data actually looks like.
--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)

May 20, 2008 at 1:14 am

#817610
RE: Loading data from Flat File to a database

SQL King (2/7/2008)
Now, some of the data may OR may NOT change on day 2, how do I compare Day1's data with Day 2 data?
Like Matt suggested, it's not possible...
--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)

May 20, 2008 at 1:12 am

#817609
RE: Is there a sql function to split strings?????

I've found XML splits to be just a tad on the slow side. Take a look at the following URL.
http://www.sqlservercentral.com/articles/TSQL/62867/
Also, fairly big article on splits coming out on Wednesday.
--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)

May 20, 2008 at 1:02 am

#817605
RE: Computed column question

Can't be done... computed columns cannot refer to other tables.
You might be able to use a trigger to accomplish this. Why do you need to do this?
--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)

May 20, 2008 at 12:57 am

#817602
RE: Shifting columns

David Lester (5/19/2008)
This one has me baffled. I have a table with five columns. As follows
RowID Char(16) {Primary Key, Unique}
Code1 Char(6)
Code2 Char(6)
Code3 Char(6)
Code4 Char(6)
What I need 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)

May 20, 2008 at 12:55 am

#817601
RE: A Worthwhile Goal

ntaylor (5/19/2008)
I prefer the mantra of one of my better managers: "If you're going to do something out of the ordinary then document it, in the code, very thoroughly and...
--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)

May 19, 2008 at 6:49 pm

#817542
RE: sum(),Cummulative addition,Case statement

karthikeyan (5/19/2008)
Once I repair the error in the #2 code you provided, it gives me the following...
-----------------------------------------------------
3.0
(1 row(s) affected)
...
--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)

May 19, 2008 at 6:47 pm

#817541
RE: Only Keep The First Occurance

Heh.. "Oh no"... I've earned the nickname of the code I hate the most 😛
Glad it helped, Chris.
Do keep in mind that the others are correct, though... if you have...
--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)

May 19, 2008 at 6:44 pm

#817540
RE: Data Conversion question - bad data points

...or 52,000...
--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)

May 19, 2008 at 6:40 pm

#817539
RE: Previous Row + Current Row calculation

Only thing left is to ask Karthik if he understands how it works and why... 😉
--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)

May 19, 2008 at 6:34 pm

#817534
RE: Out of ideas

In that case, it may very well be that it's remembering a "bad" plan based on a previous query. That's why I use stored procedures instead of embedded SQL...
--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)

May 19, 2008 at 6:17 pm

#817532
RE: Querying Same Number of Rows in 2 tables

Philip Yale (5/19/2008)
Thanks again for the reply Jeff.
I didn't actually ask why the copy to the heap was faster. In fact, the copy takes roughly the same length 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)

May 19, 2008 at 6:09 pm

#817530
RE: help with duplicates delete please

Michael Meierruth (5/18/2008)
First of all, the delete statement I posted has a slight problem. It works correctly for the duplicates and would delete the duplicates and leave only one row...
--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)

May 19, 2008 at 5:58 pm

#817528
RE: help with duplicates delete please

george sibbald (5/19/2008)
the results are in:
straight delete took 8 min 47 secs and created 4GB of tran log
crawler delete took 21:49 at batches of 100,000 (40MB of log) and 9:22...
--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)

May 19, 2008 at 5:52 pm

#817527

Viewing 15 posts - 50,446 through 50,460 (of 59,099 total)