Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,496 through 30,510 (of 59,101 total)

RE: How to get all rows of a table having NULL in all columns?

[font="Arial Black"]{EDIT} Forget the method below. I was mistaken and it doesn't work. [/font]Dang... I forgot about a very old trick for this because I haven't had...
--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)

March 31, 2012 at 3:50 pm

#1467353
RE: How to get all rows of a table having NULL in all columns?

bharat sethi (3/30/2012)
I am looking for a solution without compromising on Performance & without writing all column names to get all rows those have NULL or BLANK in their all...
--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)

March 31, 2012 at 3:24 pm

#1467352
RE: How to get all rows of a table having NULL in all columns?

capn.hector (3/31/2012)
this kinda sounds like homework. since you want all rows where its all null you have to use the queries you provided.
now lets move to the total idiocy...
--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)

March 31, 2012 at 3:03 pm

#1467348
RE: hierarchial query in sql server 2005

Sure, if you don't mind an example with EmployeeIDs and Names. See the following link.
http://www.sqlservercentral.com/articles/T-SQL/72503/
I also recommend you lookup "Recursive CTEs". I'd recommend looking up the "HierarchyID" except...
--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)

March 31, 2012 at 3:00 pm

#1467347
RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Dean Cochrane (3/30/2012)
Berl (3/30/2012)
Although CPU time difference is not very great, the difference is much bigger for the elapsed time.

Elapsed time includes wait time and is not a good...
--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)

March 31, 2012 at 1:37 pm

#1467334
RE: FK referencing PK in *same* table

drew.allen (3/30/2012)
Jeff Moden (3/30/2012)
Heh... a Rose, by any other name, is still a Rose.
Unless I'm mistaken, all of the examples above all boil down to some form of parent/child relationship.
I'm...
--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)

March 31, 2012 at 1:28 pm

#1467331
RE: Staging Tables do not contain a DateTime Stamp or Source System, etc.

IIRC, the '1100901' is actually a full date with century included but things in the AS400 world are a wee bit different. I believe that the first 3 characters...
--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)

March 31, 2012 at 11:40 am

#1467319
RE: Nth Max /Min value

GuruGPrasad (3/31/2012)
How to find the nth max or min value in a column
...
--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)

March 31, 2012 at 11:30 am

#1467317
RE: records disappearing after insert, occasionally of course

eshulman (3/31/2012)
We might have stumbled onto something - found some suspicious transaction definition in our web service declarations. We will try something different on Monday morning and if 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)

March 31, 2012 at 11:21 am

#1467316
RE: Data in tthe same order in which it is inserted

opc.three (3/31/2012)
Jeff Moden (3/30/2012)
opc.three (3/30/2012)
It will actually do it properly, provided the commit size is 1
It's been a while but, IIRC, if you set MAXDOP to 1 and you 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)

March 31, 2012 at 8:01 am

#1467306
RE: Data in tthe same order in which it is inserted

opc.three (3/31/2012)
PS I do not use BULK INSERT, but I am not finding any articles claiming MAXDOP 1 will have the desired effect. I am reading that TABLOCK may 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)

March 31, 2012 at 7:46 am

#1467305
RE: how temp db works while restarting sqlserver

I agree that the information is a little bit buried but there's not a whole lot that most folks need to know about TempDB on startup other than this...
tempdb 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)

March 31, 2012 at 7:01 am

#1467301
RE: Running Totals on PIVOT in T-SQL

Just to make it a bit more flexible so that you don't have to change the code every year, try the following against the test data you provided. 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)

March 30, 2012 at 11:19 pm

#1467278
RE: Generating Test Data: Part 1 - Generating Random Integers and Floats

Cool... 5 birds with 1 stone. SPAM reported. 😉
--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)

March 30, 2012 at 9:55 pm

#1467277
RE: Data in tthe same order in which it is inserted

opc.three (3/30/2012)
It will actually do it properly, provided the commit size is 1
It's been a while but, IIRC, if you set MAXDOP to 1 and you have no indexes 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)

March 30, 2012 at 9:52 pm

#1467274

Viewing 15 posts - 30,496 through 30,510 (of 59,101 total)