Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,241 through 30,255 (of 59,101 total)

RE: Replacement for Cursors

dwain.c (4/30/2012)
Writing articles ain't that difficult. Look at me. Just over 400 points and I have one published and another accepted/awaiting publication.
The trick is to find something that...
--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)

April 30, 2012 at 5:59 am

#1481169
RE: Optimize query for finding nearest airport based on latitude and longitude using STDistance

Even the Latitude calculations can get a bit screwy on things like trans-Pacific flights because the actual flight path will usually follow the "Great Circle Path" which is hardly ever...
--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)

April 30, 2012 at 5:48 am

#1481160
RE: # of characters

capn.hector (4/27/2012)
If the OP does not have a tally table in there DB they can use the following to create one on the fly with out hitting sys.columns.
;WITH e1(n) AS...
--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)

April 30, 2012 at 5:39 am

#1481150
RE: junk characters in a field

GSquared (4/13/2012)
You don't need to know the bad characters at all. White-list, don't black-list. It's more secure, easier, and faster.
You could modify my version to use a 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)

April 29, 2012 at 6:06 pm

#1481003
RE: Quering with "google-style" operators

Teach the user how to use LIKE. It's easier.
--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)

April 29, 2012 at 5:49 pm

#1481002
RE: Can you have a non-aggregate field?

Is the description all that important if it's not FKd? Probably not. So just do a MAX() on the description and call it a day. 😉
--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)

April 29, 2012 at 5:43 pm

#1481001
RE: txt import

andersg98 (4/20/2012)
This is a job for SSIS.
Oh, horse muffins! 😛 BULK INSERT with a format file makes this a simple T-SQL task.
--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)

April 29, 2012 at 5:17 pm

#1480999
RE: What does mean by two dotes.. ?

jitendra.padhiyar (4/26/2012)
Okay, so even
use msdb
SELECT backup_size/compressed_backup_size FROM backupset
Or
use msdb
SELECT backup_size/compressed_backup_size FROM dbo.backupset
are same as
use msdb
SELECT backup_size/compressed_backup_size FROM msdb..backupset
Am I correct ?
Yes... unless you log is as "Homer"...
--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)

April 29, 2012 at 5:04 pm

#1480998
RE: SSIS - Splitting the YYYYMMDD date from a comma delimited flat file to multiple columns in OLE DB dest

People spend way to much time doing conversions that SQL Server will do on it's own. The following works just fine and will also work with a character based...
--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)

April 29, 2012 at 5:01 pm

#1480997
RE: find gaps in multiple sequences

Thinky Night (4/27/2012)
Also You can use this recursion instead of spt_values:
declare @Table table (Doc_Nbr int,Ticket_Nbr int)
insert into @Table values
(12,907),
(12,908),
(12,909),
(12,913),
(12,915),
(14,1481),
(14,1484),
(14,1485),
(14,1486),
(14,1487),
(14,1489),
(14,1490);
WITH cteSequence ( number) as
(
SELECT 800
...
--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)

April 29, 2012 at 4:54 pm

#1480996
RE: Function review?

Oddly enough, I'll second that recommendation. 😀
--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)

April 29, 2012 at 4:51 pm

#1480995
RE: Create New Records Into Same Table

markpc2009 (4/27/2012)
Table consists of:
Area|Date|Employee|Qty where Area|date|employee are the key
On "Sunday" someone will complete these for Monday. The probability is that the key fields will remain constant for the next...
--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)

April 29, 2012 at 4:47 pm

#1480994
RE: # of characters

dwain.c (4/26/2012)
You gotta love the recursive CTE version of this solution, even though it doesn't perform as well as the tally table version.
CREATE TABLE #t (strings VARCHAR(100));
-- Count the unique...
--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)

April 29, 2012 at 4:42 pm

#1480993
RE: # of characters

ChrisM@Work (7/26/2011)
pdanes2 (7/26/2011)
ChrisM@Work (7/26/2011)
That's slick, and definitely going in my toolbox. I never would have guessed that could be done with set-based code.
Thanks! Must admit though, it's borrowed from 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)

April 29, 2012 at 4:38 pm

#1480992
RE: If proc1 calls procB, can proc1 complete without waiting for procB?

Elliott Whitlow (4/26/2012)
I can't under ANY circumstances endorse an sp_OA method to doing this. As a matter of fact I'm of the opinion that those should not be used...
--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)

April 29, 2012 at 4:29 pm

#1480991

Viewing 15 posts - 30,241 through 30,255 (of 59,101 total)