Jeff Moden

Forum Replies Created

Viewing 15 posts - 26,266 through 26,280 (of 59,102 total)

RE: Using CTE - Does this help the optimiser

Tim Walker. (8/7/2013)
Erland, Jeff, your replies are as always much appreciated.
I now have confidence that I know how this all hangs together.
Shame in a way though. Table variables always compile...
--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)

August 7, 2013 at 9:16 pm

#1639603
RE: Using CTE - Does this help the optimiser

To add to what has already been stated, CTEs are sometimes referred to as "inline views" and they act pretty much like a view would. It is not 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)

August 7, 2013 at 3:36 pm

#1639576
RE: Sorting Months By Number (SQL Spackle)

aobermueller (8/7/2013)
I'd just alter the Order By and call it good.
---------------------------------------------------------
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
...
--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)

August 7, 2013 at 3:25 pm

#1639573
RE: Top from store procedure

Erland Sommarskog (8/7/2013)
Jeff Moden (8/6/2013)
What would you use as an alternative? Insert/Exec?
If you've read my article, you know that nor am I fond of INSERT/EXEC.
As long as I have...
--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)

August 7, 2013 at 3:22 pm

#1639571
RE: SQL Query help

shani19831 (8/6/2013)
Hi
Thanks for the query, its returning what i want, but i have never used CTE and have no knowledge of it, guess its time to learn.
is there no easy...
--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)

August 7, 2013 at 12:25 am

#1639226
RE: how to Convert rtf format text from database ?

Personally, I would find the person who stored such formatted data in the database and take them to the woodshed for a serious talk about what databases are.
--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)

August 7, 2013 at 12:18 am

#1639223
RE: Pass inter year and month return last day of month

adonetok (8/6/2013)
How to code to pass inter year and month return last day of month?
For example,
pass 2012, 7 will return 07/31/2012
pass 2014, 6 will return 06/30/2014
Do you actually want...
--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)

August 7, 2013 at 12:14 am

#1639220
RE: Is possible create numbered sequences

azdeji (8/6/2013)
Both SQL codes work perfectly!
Thanks Peter Brinkhaus and ChrisM@home
Do you understand how they work?
--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)

August 7, 2013 at 12:11 am

#1639218
RE: help with incrementing

I wouldn't aadd the sequence number to the original data because it makes it more difficult to use criteria not to mention that it's a form of denormalization. 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)

August 6, 2013 at 11:59 pm

#1639215
RE: Remembering that it is good to "get back to basics" every now and then

jarid.lawson (8/6/2013)
I have a new position where I finally have the long sought after Sr. prefix included. Only time will tell if I truly deserve the prefix, but I believe...
--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)

August 6, 2013 at 11:55 pm

#1639214
RE: Remembering that it is good to "get back to basics" every now and then

Luis Cazares (8/6/2013)
Lowell (8/6/2013)
no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was 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)

August 6, 2013 at 11:53 pm

#1639212
RE: Using MERGE to perform the INSERT?

Abu Dina (8/6/2013)
Thanks I've sorted it out.. currently testing the results.
Yes, RTFM I know I know 😀
Would you post your solution, please?
--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)

August 6, 2013 at 11:36 pm

#1639207
RE: Top from store procedure

Erland Sommarskog (8/5/2013)
Beware that this is a very bad solution. If were to meet in a code review I would never approve of this solution. Did you read the article...
--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)

August 6, 2013 at 11:19 pm

#1639205
RE: Maximum Number of Values in an In in a where clause

andymay-496937 (8/6/2013)
Thanks for that, I'm not sure that Books on Line advice about putting the values in a table are that helpful for SSRS, but it's good stuff in general.
thanks
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)

August 6, 2013 at 11:15 pm

#1639204
RE: Creating comma separated values for a column in TSQL

ck9663 (8/6/2013)
How about this?
To change the number of batch, change the value of @batch variable.
declare @batch as smallint
set @batch = 6
if object_id('tempdb..#sample') is not null
drop 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)

August 6, 2013 at 11:05 pm

#1639201

Viewing 15 posts - 26,266 through 26,280 (of 59,102 total)