Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,221 through 32,235 (of 59,099 total)

RE: Finding the column containing the minimum value

That's essentially the same as the UNPIVOT solution already posted, Eric. I've not tested it but I'm pretty sure the CASE statement that Drew wrote will beat it fairly...
--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)

October 20, 2011 at 8:43 pm

#1397726
RE: How do I consolidate multiple rows into 1 row of output

Kabelo. (10/20/2011)
hi
try the following script
regards,
Kabelo
CREATE TABLE #Fruits(FruitID INT,FruitName VARCHAR(10))
INSERT INTO #Fruits
VALUES('1001','Apple')
INSERT INTO #Fruits
VALUES('1002','Orange')
INSERT INTO #Fruits
VALUES('1003','Mango')
INSERT INTO #Fruits
VALUES('1004','Banana')
INSERT INTO #Fruits
VALUES('1005','Grape')
--SELECT *FROM #Fruits
DECLARE @FruitNames VARCHAR(8000)
SELECT @FruitNames = COALESCE(@FruitNames + ', ',...
--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)

October 20, 2011 at 8:40 pm

#1397725
RE: Selecting particular rows

I believe we can shorten up the code just a bit... it probably won't make much of a difference in performance... (shamelessly leveraging Chris' test data)
-- CTE mimics real table
;
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)

October 20, 2011 at 8:35 pm

#1397724
RE: DBA in Space

I've found that the only things that most DBA's are "risk adverse" about are safety of the data and the safety of "the system". I know many that ride...
--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)

October 20, 2011 at 8:14 pm

#1397721
RE: Any limitation for sqlcmd?

Chiang (10/20/2011)
Hi all,
I used
sqlcmd -U dbName -P password -S hostName -y 0 -u -d dbSchema -i C:\MyLargeScriptFile.sql
Is there a limit for the file size for MyLargeScriptFile.sql?
The reason is 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)

October 20, 2011 at 7:57 pm

#1397720
RE: problem running system proc

I've run into similar problems and was loath to grant any privs to any login other than PUBLIC. The way I've solved the problem was very simple. 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)

October 20, 2011 at 7:53 pm

#1397719
RE: Is there a "tables collection" in T-SQL?

brad.woody (10/19/2011)
I'm not even sure how to ask this question. I'm an ASP.Net/C# developer who uses Management Studio to noodle around in our SQL Server databases, but I'm still...
--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)

October 20, 2011 at 7:41 pm

#1397716
RE: A good place to shamelessly plug my blog?

OCTom (10/19/2011)
Shannonjk,
I hope you don't mind a bit of criticism about your blog. I find it difficult to read. I don't know if it's the grey background, the font, or...
--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)

October 19, 2011 at 9:52 pm

#1397043
RE: Finding the column containing the minimum value

Lowell (10/14/2011)
maybe it's just me, but both my solution and Chris's seem to produce the exact same execution plan?, but Chris's is 48% relative to the batch where mine...
--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)

October 18, 2011 at 10:28 pm

#1396443
RE: Why does the same code yield different results running in T-SQL interactively vs. as a TVF?

[font="Arial Black"]Danger!!!! DO NOT USE RECURSIVE CTES FOR COUNTING!!!! PERIOD!!!![/font]
See the following article for why...
http://www.sqlservercentral.com/articles/T-SQL/74118/
[font="Arial Black"]Danger!!!! DO NOT USE ANY FORM OF CONCATENATION WHEN SHREDDING CSVS!!!! PERIOD!!!![/font]
See 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)

October 18, 2011 at 10:22 pm

#1396441
RE: Give us all the features

Conversly, I'd also like to see them stop deprecating useful features such as sp_MakeWebTask, etc, etc.
--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)

October 18, 2011 at 10:14 pm

#1396435
RE: interview

jared-709193 (10/18/2011)
GilaMonster (10/18/2011)
Be honest, don't exaggerate your skills, don't claim to know stuff you don't.
The job that I took in April of this year was one of these types 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)

October 18, 2011 at 10:11 pm

#1396434
RE: System Center DB concerns! Microsoft Databases

stebennettsjb (10/17/2011)
Hi all,
We just upgraded SCCM to r3 and ive started monitoring the back-end with Red Gates SQL Monitor. Over the weekend ive got over 1k warnings about long queries....
--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)

October 18, 2011 at 10:07 pm

#1396433
RE: Cummulative percents

Amy.G (10/18/2011)
Read the article and re-wrote the code. I now know I was trying to do a "triangular join". The performance went from nearly a full day (it never did...
--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)

October 18, 2011 at 9:53 pm

#1396431
RE: How to generate all Possible Alphabit pattern in a sequence?

MidBar (10/17/2011)
Hi,
In one query, starting either at "Select" OR ";With", how can we generate all possible patterns of capital alphabits, order by All alphabits.
For example the desired output is follows.
A
AB
ABC
ABCD
ABCDEFGHIJKLMNOPQRSTUVWXYZ
...
...
...
B
BC
BCD
BCDE
BCDEFGHIJKLMNOPQRSTUVWXYZ
...
...
...
YZ
Z
May...
--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)

October 18, 2011 at 9:45 pm

#1396430

Viewing 15 posts - 32,221 through 32,235 (of 59,099 total)