Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,061 through 24,075 (of 59,099 total)

RE: Using "not like" to filter out non-numeric values from varchar field

miles_lesperance (3/26/2014)
That worked. I changed my CASE statement to look like the following:
case when ISNUMERIC(SRESULT) = 1 then
CASE WHEN CONVERT(int, SRESULT) between 0 and 500 THEN '2' --Green
ELSE '0'
END
It 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 26, 2014 at 3:57 pm

#1700493
RE: Evenly splitting large DB files

DVSQL (3/26/2014)
I am trying to split up a large database file into several smaller database files at work.
WHY? What advantage do you believe that will bring you?
Unless 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)

March 26, 2014 at 3:40 pm

#1700488
RE: Are the posted questions getting worse?

Ed Wagner (3/26/2014)
Cursors in SQL Server are pathetically slow, but in Oracle they're blazing fast.
I guess I wouldn't say such a thing... I worked in Oracle for about 3...
--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 26, 2014 at 3:09 pm

#1700478
RE: What is mean N'123456'?

adonetok (3/26/2014)
Some users uploaded Access tables to SQL are always NVARCHAR type.
I found that nvarchar cause performace problem. Is it true?
I don't know why anyone that knew anything about 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)

March 26, 2014 at 8:58 am

#1700337
RE: What is mean N'123456'?

It also means that unless the OrderID column is NVARCHAR, the code isn't SARGable (can't do an INDEX SEEK) and will cause a table scan to do an implicit conversion...
--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 26, 2014 at 7:15 am

#1700271
RE: Simple Query taking 8 secs

Sean Lange (3/25/2014)
The table hints and read uncommitted should go away as Gail suggested.
Your function looks very similar to the DelimitedSplit8K with two MAJOR exceptions. You changed the delimiter...
--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 26, 2014 at 7:11 am

#1700268
RE: select with single quotes

sqlserver12345 (3/25/2014)
Thanks Jeff/Eirikur .Should have updated the forum.I got what I was looking for.Thanks again.
What did you end up with finally?
--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 25, 2014 at 5:10 pm

#1700098
RE: Primary Key Violation

todd 87764 (3/25/2014)
Ok this is really driving me crazy. I followed your suggestion but have the same problem with duplicates.
If I make a nonunique index on the ID 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 25, 2014 at 3:02 pm

#1700074
RE: Working with stored text file in TSQL

keith.westberg (3/25/2014)
Well that looks promising. Let me do some reading and see where it leads. I think I may have looked at that once long ago, but shelved...
--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 25, 2014 at 2:59 pm

#1700073
RE: Is Unit Testing Important?

n.arley.dealey (3/25/2014)
Premature optimization is the root of all evil!
I’m not a DBA. In fact, it’s been a long time since I’ve been a developer, but learning the little aphorism above,...
--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 25, 2014 at 2:42 pm

#1700068
RE: What processes can update records without firing a trigger

I agree with Gail. Bulk operations don't fire triggers by default. There is a "Fire Triggers" option that you can use for such things. Other than TRUNCATE...
--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 25, 2014 at 7:21 am

#1699859
RE: Update a column of data with incremental values?

Just to throw my hat into the ring, this can be done with a single scan of the table by updating the table in the cte directly. It 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)

March 25, 2014 at 7:02 am

#1699846
RE: List Files In Directory Using SQL

Heh... no worries. I just love working with xp_CmdShell and wanted to make sure that people knew spaces in a name isn't an obstacle.
Thanks for the feedback..
--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 25, 2014 at 6:12 am

#1699821
RE: select with single quotes

Eirikur Eiriksson (3/25/2014)
sqlserver12345 (3/25/2014)
ascii value for comma
this time the answer is 44 😎
Heh.... I caught the reference. Too funny! 😛
--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 25, 2014 at 6:06 am

#1699819
RE: select with single quotes

If the content of the column is less than 128 characters, the you could also do the following...
SELECT QUOTENAME(Column1,'''') AS quotedColumn1
FROM TableA;
--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 24, 2014 at 10:51 pm

#1699726

Viewing 15 posts - 24,061 through 24,075 (of 59,099 total)