Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,721 through 48,735 (of 59,093 total)

RE: Need Help In Getting Required Result

karthikeyan (8/11/2008)
Christopher,
why can't we use Tally or Number table ?
My approach :
Select REPLICATE('1',N),N
from Tally,NUM
where N <= 6
and NumberValue = 1
union all
select REP,N from (Select REPLICATE('1',N) as REP ,N
from Tally,NUM
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 17, 2008 at 8:46 pm

#859245
RE: Need Help In Getting Required Result

Hi Trevor...
It's odd that you must use a While loop instead of a cursor because a cursor uses a While loop! They both suck performance dry. 🙂
I'd recommend 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)

August 17, 2008 at 8:32 pm

#859244
RE: Deadlocks rate

Niyala (8/11/2008)
Hi, all,
I am getting the following deadlock message, but I could not figure out what the mentioned rate is.
DBSPI-3271.1: Deadlocks rate for object _Total (110.00/sec) too high (>=3.00/sec)
I...
--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 17, 2008 at 8:00 pm

#859237
RE: MAX() Function Call from Application

... and it will require a change to your front end statement. You will need to select from the indexed view, instead.
--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 17, 2008 at 7:59 pm

#859236
RE: MAX() Function Call from Application

You might be able to get a whole lot more retrieval speed if you created an Indexed View. Be warned that an indexed view can slow down inputs 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)

August 17, 2008 at 7:52 pm

#859235
RE: OpenRowSet Import Excel to SQL Server

I believe the first sheet of an Excel file can be referred to as Sheet1$ regardless of it's actual name.
--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 17, 2008 at 7:46 pm

#859234
RE: Instead of Using Cursor? Other Ways?

pockeyfan (8/14/2008)
Thanks for your suggestion!
I'm new to SQL and SP, will read the article to see whether I could apply it in my case...
Tally table won't do it, in 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)

August 17, 2008 at 7:40 pm

#859232
RE: Instead of Using Cursor? Other Ways?

Lester Policarpio (8/13/2008)
"I think" it can be done using a tally table. Some experts here in the forum maybe can share something about it.
And also please 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 17, 2008 at 7:17 pm

#859228
RE: GOTO vs WHILE

Heck... I wouldn't even use the WHILE loop... it's RBAR. Most WHILE loop solutions are because someone doesn't know T-SQL well enough to come up with the proper set...
--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 17, 2008 at 7:05 pm

#859224
RE: reset sequence when value of one column changes

Loner (8/13/2008)
It is SQL Server 2000 so I cannot use RANK, that is why I posted the question in this forum.
The one you posted for SQL Server 2000 worked liked...
--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 17, 2008 at 7:00 pm

#859222
RE: Arrays

You can get by without creating the extra temp table...
SELECT d.Name,t.N*10 AS DeciCount
FROM (SELECT Name,COUNT(*) AS TheCount
...
--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 17, 2008 at 6:53 pm

#859220
RE: Selecting The Top 3 Numbers Used The Most

Lian Pretorius (8/12/2008)
karthikeyan (8/12/2008)
Hey Trevor,
I have tested the below script, It is giving perfect result.
Same here.
Trevor, Karthik's script above uses a temp table - Which cannot be used in view's...
--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 17, 2008 at 6:45 pm

#859216
RE: dbo

It means "DataBase Owner" and is a server role. Lookup server roles 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)

August 17, 2008 at 6:39 pm

#859213
RE: List out SP's Columns into a Table

sqlindia (8/12/2008)
Hi All
I want to list out columns name of SP into a table. They can be dynamic.
Thank in advance
sonny
I'm pretty sure this can't be done reliably. Sure,...
--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 17, 2008 at 6:38 pm

#859212
RE: Creating CLR assemblies with VS 2008

I guess I'd have to ask... what does the CLR and the C# function do?
--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 17, 2008 at 6:17 pm

#859208

Viewing 15 posts - 48,721 through 48,735 (of 59,093 total)