Jeff Moden

Forum Replies Created

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

RE: what is the difference if we create index (at time create table or after insert data into that table ?)

Heh... I'll second the "It Depends" aspect.
What Recovery Model is the database in?
What kind of index are you trying to build?
How many rows are you inserting in the initial batch...
--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 4:48 pm

#1700508
RE: Evenly splitting large DB files

I have to ask again, why are you going through all of this? What advantage do you think you're getting out of all 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)

March 26, 2014 at 4:27 pm

#1700500
RE: Datediff

Using Jason's setup code for this problem, here's a way to simplify the code a bit...
SELECT CAST(DATEDIFF(hh,0,@MaxDate-@MinDate) AS VARCHAR(10))
+RIGHT(CONVERT(CHAR(8),@MaxDate-@MinDate,108),6);
--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 4:23 pm

#1700499
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

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