Jeff Moden

Forum Replies Created

Viewing 15 posts - 18,616 through 18,630 (of 59,072 total)

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
RE: using getdate for returning course list running for 2 months

nimmusuya (1/13/2016)
basically I have to create a single stored procedure that adds a new trainer to the existing system, assign that trainer to teach a particular course(already existent) and then...
--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 13, 2016 at 6:16 pm

#1851782
RE: HOW TO: Recycle a service automatically

srinivas.akyana (1/13/2016)
I need to automate a service restart on a Server A...
WHY? What is going wrong where you think 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)

January 13, 2016 at 5:44 pm

#1851781
RE: Is it possible to set fill-factor on partition level?

Jacob Wilkins (1/13/2016)
Not directly, no. FILLFACTOR is not an option for single partition rebuilds https://msdn.microsoft.com/en-us/library/ms188388.aspx.
You could try to achieve this indirectly using the method in Thomas Kejser's answer here: http://dba.stackexchange.com/questions/86588/shrinking-old-partitions
Cheers!
Use...
--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 13, 2016 at 11:55 am

#1851693
RE: Is it possible to set fill-factor on partition level?

Heh... "All that is "better" might not be".
Oddly enough, the "old" method of using partitioned VIEWS allows you to do exactly want you want.
This might also be a good...
--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 13, 2016 at 11:47 am

#1851689
RE: Guarantee entire flat file loaded

Steve Jones - SSC Editor (1/13/2016)
I think you'd need a separate process that counts rows, knowing what a "row" is in the file format as a
delimiter.
You can look 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)

January 13, 2016 at 9:27 am

#1851629
RE: Eliminate WHILE loop

Ok. Here's the equivalent solution to your While Loop. The dbo.fnTally function makes it child's play and requires very little typing especially compared to a While Loop solution.
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 13, 2016 at 8:29 am

#1851593
RE: Eliminate WHILE loop

Pun intended... it's "time" that you built something like the following function into your databases.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including...
--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 13, 2016 at 8:11 am

#1851587
RE: IF Statement with Insert

One possible work around for the problem that Gail has identified is to replace the SQL in the IF blocks with dynamic SQL, which is not parsed until it's executed.
--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 13, 2016 at 8:04 am

#1851586
RE: The Ongoing Costs of Building Software

I find the "Build or Buy" question to be quite the problem. Yes, I absolutely agree with what was stated in the "If you build it, they will complain"...
--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 13, 2016 at 7:58 am

#1851582
RE: Header and Detail Data

Orlando Colamatteo (1/12/2016)
Where it will have trouble is if the header and detail lines need to have different lengths which is why I asked the OP for the file-descriptor info.
Agreed....
--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 13, 2016 at 7:25 am

#1851570
RE: Header and Detail Data

Sergiy (1/12/2016)
Jeff Moden (1/12/2016)
Orlando Colamatteo (1/12/2016)
Anyway, here is a resultset with the data in a fixed-width format and you can tweak it from here. If the header and detail lines...
--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 12, 2016 at 9:40 pm

#1851481

Viewing 15 posts - 18,616 through 18,630 (of 59,072 total)