Jeff Moden

Forum Replies Created

Viewing 15 posts - 22,321 through 22,335 (of 59,072 total)

RE: Approach to archiving tables with foreign key constraints

anni00 (11/28/2014)
Hi,
I'd like to get your thoughts on the approach to archiving data from a set of tables with foreign key constraints.
The scenario is we have a target table holding...
--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)

November 30, 2014 at 8:35 am

#1761556
RE: Are the posted questions getting worse?

GilaMonster (11/30/2014)
Jeff Moden (11/28/2014)
It would be really cool if we could get MS to retrofit this to 2012 and 2014.
Snowball's chance in hell.
They don't back-port features, can't think 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)

November 30, 2014 at 8:22 am

#1761555
RE: trying to find a more efficent way

pietlinden (11/29/2014)
Sorry, can't remember if any windowing functions were available in 2008R2. Haven't used 2008 in an age (partly because windowing functions are super handy!)
Not your fault... With...
--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)

November 30, 2014 at 8:19 am

#1761554
RE: trying to find a more efficent way

itmasterw 60042 (11/29/2014)
Hi,
And thanks for the alternate way of doing this, but when I compard the execution plans the cost was about the same; I was looking for a more...
--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)

November 29, 2014 at 10:34 am

#1761518
RE: Types don't match between the anchor and the recursive part in column "Hierarchy" of recursive query

BTW, although this is kind of a "set based" recursive CTE (rCTE from here on) because it processes a whole level in the hierarchy at a time, rCTEs can be...
--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)

November 29, 2014 at 8:54 am

#1761515
RE: Types don't match between the anchor and the recursive part in column "Hierarchy" of recursive query

peterausger (11/29/2014)
Dear all
Following is my db table
student_id student_code student_parent_id student_name
1 11 NULL a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id ...
--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)

November 29, 2014 at 8:37 am

#1761513
RE: How to free up the drive space in SQL that has data files

A 1 TB 15k-RPM drive costs less than $300 for a SAN drive. For a local drive, you can get one for about $70. You've probably already spent...
--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)

November 28, 2014 at 8:57 pm

#1761496
RE: trying to find a more efficent way

Take a look at the first link under "Helpful Links" in my signature line below. For this one, we're going to need to know the actual table structure is...
--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)

November 28, 2014 at 7:48 pm

#1761494
RE: Query to show table Products with its modes

It won't be slow if you avoid the self join of the CTE, which causes it to go through both tables twice. This will work almost as fast 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)

November 28, 2014 at 7:38 pm

#1761493
RE: tsql with out using cursors

stook (11/28/2014)
if the cursor is not working then try the insensitive cursor. Without cursor - you mean something like this?
declare @dbname nvarchar(255)='', @found bit=1
while @found=1 begin
set @found=0
select top 1...
--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)

November 28, 2014 at 5:35 pm

#1761490
RE: Please vote for the "built in Tally Table" function.

P.S. Personally, I don't care if ANSI, ISO, INCITS, or any other organization agrees or not. Sometimes you have to first build the tool before you try to...
--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)

November 28, 2014 at 5:32 pm

#1761489
RE: Please vote for the "built in Tally Table" function.

CELKO (11/28/2014)
I have mixed feeling on this. It sounds like a schema level table constant with contiguous exact numeric values. Nobody can change a constant. Why would it start at...
--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)

November 28, 2014 at 5:25 pm

#1761488
RE: sql server cpu schedulers

You might also want to look into Trace Flags 1117 and 1118.
http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/
http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
--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)

November 28, 2014 at 1:01 pm

#1761484
RE: Are the posted questions getting worse?

TomThomson (11/28/2014)
Divine Flame (11/26/2014)
Voted. 153 now.
Now 167.
Needs quite a few more vote, I think.
Agreed. I'll try to submit an "editorial" for inclusion on the front page for sometime 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)

November 28, 2014 at 12:35 pm

#1761482
RE: Please vote for the "built in Tally Table" function.

TomThomson (11/28/2014)
voted.
It's now 167. Not growing fast enough.
Agreed. I'll write up an "editorial" and see if we can get it published next week.
Keep up the great work folks....
--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)

November 28, 2014 at 11:57 am

#1761480

Viewing 15 posts - 22,321 through 22,335 (of 59,072 total)