Jeff Moden

Forum Replies Created

Viewing 15 posts - 26,521 through 26,535 (of 59,099 total)

RE: substring comparison for last 2 characters

timscronin (7/1/2013)
wont happen, the records with the 2 characters at the end are state abbreviations added on
Then it's easy... Build a "State" table (you should have one anyway) 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)

July 1, 2013 at 5:08 pm

#1628859
RE: Dynamic Vertical Rows to Horizontal

N.B. (7/1/2013)
I ended up going the Copy/Paste route and iterating every possible number in order to get this working - it's ugly, but it does work.
The article that you were...
--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)

July 1, 2013 at 4:58 pm

#1628858
RE: Database in suspected mode

Krasavita (7/1/2013)
error message,I have differential and full backup
You do realize that most of the files in your attachment are empty, right?
--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)

July 1, 2013 at 9:52 am

#1628764
RE: Display More Than 8000 Characters (SQL Spackle)

rac.coons (6/28/2013)
Here's a very quick and easy way to display all characters that I did not see here.
select convert(xml,'<txt>' + @txt + '</txt>')
In SSMS, it returns a link that opens...
--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)

June 30, 2013 at 9:34 pm

#1628589
RE: Display More Than 8000 Characters (SQL Spackle)

opc.three (6/29/2013)
If the goal is only to see the data in the variable for purposes of debugging then here is an alternate method which I find works quite well. 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)

June 30, 2013 at 9:28 pm

#1628588
RE: Transaction log growing huge if DB is in simple recovery model

GilaMonster (6/27/2013)
girijesh.ipec (6/27/2013)
Shink Log Files and if it occurs frequently then automate Shrink file on Daily/weekly/Monthly depending on growth of log file size.
No, no, no! That is terrible advice.
If...
--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)

June 28, 2013 at 4:23 pm

#1628441
RE: Are the posted questions getting worse?

rodjkidd (6/27/2013)
Jeff Moden (6/24/2013)
Steve Jones - SSC Editor (6/24/2013)
Just so the rest of you can feel jealous, I got a hug from Gail and a handshake from Rodney last week...
--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)

June 28, 2013 at 11:53 am

#1628384
RE: Return numbers in a range

Bala' (6/28/2013)
WITH RECURSIVE CTE
CREATE TABLE #TestTable ([Index] int,[StartNumber] int,[Endnumber] int)
INSERT INTO #TestTable
SELECT 1,1000,1002 UNION ALL
SELECT 2,1550,1555
;WITH TableCte
AS
(
SELECT [Index],[StartNumber] ,[Endnumber] FROM #TestTable
UNION ALL
SELECT [Index],[StartNumber]+1 ,[Endnumber] FROM...
--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)

June 28, 2013 at 7:46 am

#1628295
RE: Stored Procedure Tuning

I agree. This proc probably isn't the problem.
--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)

June 27, 2013 at 9:03 pm

#1628185
RE: "Running totals" query

Barkingdog (6/27/2013)
Alan,
Thanks for the great URL on triangular JOINS and an equally great solution.
BD
Be really, really careful. It's not called the "Quirky Update" just for fun. If you...
--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)

June 27, 2013 at 8:56 pm

#1628182
RE: "Running totals" query

Alan.B (6/27/2013)
Barkingdog (6/27/2013)
I had never heard of a "triangular" join but I verified it is incredibly slow. . (I actually took the very data involved, exported it to 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)

June 27, 2013 at 8:50 pm

#1628181
RE: "Running totals" query

sivaj2k (6/27/2013)
Hi, Please check the following code .
declare @table table (id int identity(1,1),item varchar(10),amt int)
insert into @table values (1,100),(2,200),(3,300),(4,400)
declare @sumtotal int
select @sumtotal = SUM(amt) from @table
SELECT a.id, a.amt,@sumtotal-SUM(b.amt)
FROM ...
--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)

June 27, 2013 at 8:36 pm

#1628180
RE: Return numbers in a range

mickyT (6/27/2013)
To be honest I thought it would perform like a dog:-). but using the following setup to test it and using a physical Tally table rather than an inline...
--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)

June 27, 2013 at 8:32 pm

#1628178
RE: Return numbers in a range

NM. Misread something.
--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)

June 27, 2013 at 7:40 pm

#1628176
RE: Temporary Stored Procedures

Solomon Rutzky (6/27/2013)
Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can...
--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)

June 27, 2013 at 4:57 pm

#1628164

Viewing 15 posts - 26,521 through 26,535 (of 59,099 total)