Jeff Moden

Forum Replies Created

Viewing 15 posts - 18,631 through 18,645 (of 59,098 total)

RE: Result set should return all values inspite of a "Where clause"

sqlnewbie17 (1/14/2016)
If my result set has a million rows wouldn't a case statement be very slow than a where clause.
CASE won't make it slow...
Shifting gears... I don't know what you're...
--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)

January 14, 2016 at 5:00 pm

#1852129
RE: Partition woes

I'd say the first thing to do is look for a proc or a job that does a monthly handling of the partitioning of the table. The person 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)

January 14, 2016 at 4:55 pm

#1852128
RE: Fast CSV Reader

Phil Parkin (1/14/2016)
I wonder whether anyone here fancies the challenge of trying to make this work in SSIS?
Of course, implicit in that is that you make it work faster than...
--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)

January 14, 2016 at 4:25 pm

#1852126
RE: Guarantee entire flat file loaded

Orlando Colamatteo (1/14/2016)
Jeff Moden (1/14/2016)
Orlando Colamatteo (1/13/2016)
Counting the number of lines in a flat-file in a Script Task in SSIS is trivial and wicked fast. You can then take 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)

January 14, 2016 at 9:40 am

#1851984
RE: how to Select getdate () in yyyy/M format ?

Eirikur Eiriksson (1/14/2016)
Jeff Moden (1/14/2016)
Orlando Colamatteo (1/13/2016)
Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you...
--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)

January 14, 2016 at 8:59 am

#1851964
RE: Best practice for temporary working tables.

David McKinney (1/14/2016)
Thanks Jeff...although.....
Hmmmm.....I must have been unclear in my question....but I'll say it again....I'm asking about Oracle (hence I posted in the Working with Oracle forum.)
.....I hate for your...
--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)

January 14, 2016 at 7:35 am

#1851908
RE: Tracking Professional Athletes

Insurance companies are already doing this although I don't know if they actually track geo-location but it seems logical they would because they can. They have this little gizmo...
--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)

January 14, 2016 at 7:31 am

#1851904
RE: Best practice for temporary working tables.

Igor Micev (1/14/2016)
You have many options when you need to use Temp object in SQL Server:
•Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, 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)

January 14, 2016 at 7:18 am

#1851896
RE: how to Select getdate () in yyyy/M format ?

Orlando Colamatteo (1/13/2016)
Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some...
--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)

January 14, 2016 at 7:03 am

#1851887
RE: how to partition a table on multiple columns

vamshikrishnaeee (8/10/2010)
Hi,
I have a table workcachedetail with 40 million rows which has 8 columns.We decided to partition the table.Most of the queries in our environment uses 4 columns in 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)

January 14, 2016 at 7:00 am

#1851884
RE: how to partition a table on multiple columns

Deepa V (1/13/2016)
You can partition by creating a persisted computed columns.
https://msdn.microsoft.com/en-us/library/ms186241.aspx
Please explain how that would help in this 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)

January 14, 2016 at 6:53 am

#1851882
RE: Alternative to splitting text

sqlquaker (1/13/2016)
You may want to check this article by Aaron Bertrand. He explains various methods to split values in a string separated by delimiter such as #.
http://sqlperformance.com/2012/07/t-sql-queries/split-strings
Also check out...
--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)

January 14, 2016 at 6:43 am

#1851877
RE: Eliminate WHILE loop

BWFC (1/14/2016)
After having time to look at that properly, I see exactly what it does.
I can't believe I didn't think of using the Tally table method. I've...
--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)

January 14, 2016 at 6:19 am

#1851871
RE: Guarantee entire flat file loaded

Orlando Colamatteo (1/13/2016)
Counting the number of lines in a flat-file in a Script Task in SSIS is trivial and wicked fast. You can then take that count and compare it...
--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)

January 14, 2016 at 6:16 am

#1851870
RE: Guarantee entire flat file loaded

cycle90210 (1/14/2016)
I may do a write up on this as it seems to generate several good remarks and approaches!
I think there are 2 real ways to address 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)

January 14, 2016 at 6:13 am

#1851869

Viewing 15 posts - 18,631 through 18,645 (of 59,098 total)