Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,166 through 51,180 (of 59,098 total)

RE: Help with user defined function

ORDER BY in an UPDATE is only possible in a subquery and then you would have to use TOP.
All that not-with-standing, I wish the OP would post some data 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)

April 13, 2008 at 5:53 pm

#801898
RE: Calculate field in a view

As Barry has shown, ROLLUP should do it. For more information, lookup GROUPING under CUBE in Books Online.
--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 13, 2008 at 5:50 pm

#801897
RE: Table vs View

Dangskie (4/2/2008)
Hi. Can you help me with this? - In terms of simultaneous users access, what's more efficient, a table or a view?
An indexed view will usually blow...
--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 13, 2008 at 9:45 am

#801872
RE: Help with user defined function

Ian Yates (4/13/2008)
If multiple rows in W match up with TIEWF then you could have the update happening multiple times which isn't really predicable.
Heh... why sure it is... it'll...
--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 13, 2008 at 9:24 am

#801871
RE: Adding/updating rows of data that is missing but not in sequential order

Thanks, SQLBlue. :blush: Very happy it does what you want and I sure do appreciate the compliment... you made my weekend. 🙂
--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 13, 2008 at 9:18 am

#801869
RE: Large text variable

Sergiy (4/12/2008)
Actually you can.
Just declare your variable as a parameter of SP.
Never too late to say thanks... you showed me that cool trick a couple of years ago. Thanks...
--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 12, 2008 at 11:42 pm

#801857
RE: Calculate field in a view

Heh... yeah... and some generic data with a generic table structure along with your generic code and a generic sample of what the output should look like would be a...
--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 12, 2008 at 11:38 pm

#801856
RE: Adding/updating rows of data that is missing but not in sequential order

p.s.
Longer code is not always slower code. Longer code does the ol' "Divide and Conquer". The code I posted is what they call "Set Based" programming and 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 12, 2008 at 11:34 pm

#801855
RE: Adding/updating rows of data that is missing but not in sequential order

As you're finding out, the hard part is to "smear" the existing data into the new rows for the missing years. This type of smearing is VERY procedural 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)

April 12, 2008 at 11:15 pm

#801852
RE: Adding/updating rows of data that is missing but not in sequential order

Matt is correct about the Tally table... but a Tally table isn't really worth a hoot when it comes to performance unless you also build the necessary Clustered Primary Key......
--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 12, 2008 at 10:04 pm

#801850
RE: What would be the best way to learn SQL server 2005 BI

JD (4/12/2008)
Do you read or reference one or two tech books evry other month?, do online training? answer posts online? etc etc..its those kinda questions i ask...to get direction...i definetly...
--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 12, 2008 at 9:27 pm

#801849
RE: complex query

Heh... I've made many such a mistake... thanks for the feedback.
--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 12, 2008 at 9:12 pm

#801847
RE: Linking to next row using a join on row_number()+1=row_number()

Sometimes, "old school" works better...
SET NOCOUNT ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT IDENTITY(INT,1,1) AS RowNum,
ID,ST,Date
INTO #MyHead
...
--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 12, 2008 at 4:33 pm

#801835
RE: complex query

So, what was the problem and how did you fix 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)

April 12, 2008 at 2:29 pm

#801825
RE: What would be the best way to learn SQL server 2005 BI

Like practicing the piano, either you invest the time to get really good... or you don't. There are no shortcuts. For example, I recently answered a post where...
--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 12, 2008 at 2:03 pm

#801821

Viewing 15 posts - 51,166 through 51,180 (of 59,098 total)