Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,716 through 41,730 (of 59,102 total)

RE: Deleting (non-PK) duplicate values based on another column

Heh... Same here... abbreviated post. Looking back at it, I certainly could have made it more apparent that it was directed at the OP. Sorry.
--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)

October 2, 2009 at 8:14 pm

#1061290
RE: The Dynamic Tally or Numbers Table

BTW... you can also use a WHERE NOT EXISTS against a Tally table and it'll be fast as well. Just don't try the ol' outer join with a NULL...
--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)

October 2, 2009 at 8:03 pm

#1061284
RE: ROUND() function doesn't round properly

Dave Ballantyne (10/2/2009)
Heres how to calculate an age
select CASE
WHEN dateadd(year, datediff (year, d1, d2), d1) > d2
THEN datediff (year, d1, d2) - 1
ELSE datediff (year, d1, d2)
END as Age
I'll take...
--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)

October 2, 2009 at 7:38 pm

#1061280
RE: SQL Book Signing : Reminder

Congrats on the book.
As a side bar, you should post more often... lot's of folks could use your 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)

October 2, 2009 at 5:28 pm

#1061270
RE: Finding Bad Data in CSV Import

max-1156434 (10/2/2009)
hi,
i was just having the same problem. Weird thing is, I looked for the lines causing the problems, and copied a couple of "bad" ones and a couple 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)

October 2, 2009 at 5:25 pm

#1061269
RE: Finding Bad Data in CSV Import

I'm not sure this will help but this is why I like BCP... you can tell it to put the bad lines of data in an isolation file.
--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)

October 2, 2009 at 5:24 pm

#1061268
RE: Run EXE from SQL

The exe would first have to be in a directory where SQL Server has privs. Then, you should either add that directory to the permanent PATH or explicitly name...
--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)

October 2, 2009 at 5:17 pm

#1061267
RE: Deleting (non-PK) duplicate values based on another column

Understood. But I don't believe one should make that assumption which is why I asked the question. Hopefully the OP will 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)

October 2, 2009 at 5:13 pm

#1061266
RE: Deleting (non-PK) duplicate values based on another column

All of the methods shown so far keep "ties" for the MAX if they are duplicated. Do you want to keep the ties?
--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)

October 2, 2009 at 4:29 pm

#1061252
RE: Converting Datetime Field To Text

I agree with Wayne and I'll go a bit further. Study ALL of the functions in SQL Server. At least learn what they do so you know what...
--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)

October 2, 2009 at 4:02 pm

#1061242
RE: How I can make look like I expert on SQL Server?

It takes the one thing that so few are actually willing to give... time. Pouring over books and articles will certainly give you some good ideas (and bad ones,...
--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)

October 2, 2009 at 3:29 pm

#1061227
RE: The Dynamic Tally or Numbers Table

TheSQLGuru (10/2/2009)
The key thing to be learned here is that optimal varies GREATLY depending on the situation! Having many tricks up your sleeve for solving tsql problems (especially 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)

October 2, 2009 at 3:11 pm

#1061216
RE: The Dynamic Tally or Numbers Table

Matt Whitfield (10/2/2009)
When I was at school there used to be a game called 'Betrayal at Krondor' - where you would meet people as you travelled around. They might 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)

October 2, 2009 at 3:05 pm

#1061211
RE: The Dynamic Tally or Numbers Table

Sure... most of this code is just to setup the test. The last SQL paragraph is the GAP checker.
DROP TABLE #MyTest
GO
--===== Create and populate a 1,000,000 row test table.
...
--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)

October 2, 2009 at 1:02 pm

#1061133
RE: The Dynamic Tally or Numbers Table

Matt Whitfield (10/2/2009)
I would go for an inline table function - especially if dealing with small ranges much higher up the range (for example checking 12 digit account numbers for...
--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)

October 2, 2009 at 12:47 pm

#1061126

Viewing 15 posts - 41,716 through 41,730 (of 59,102 total)