Jeff Moden

Forum Replies Created

Viewing 15 posts - 42,736 through 42,750 (of 59,067 total)

RE: Index scan hint

Thanks for the feedback. I learned some stuff myself. Good thread! 🙂
--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 24, 2009 at 4:37 pm

#1030085
RE: Problem with Query

Lynn Pettis (7/24/2009)
.,.,., why not let Jeff post the url? Others, like myself, may just benefit ... oh, you want to keep us in the dark, like mushrooms! 😛
Agh......
--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 24, 2009 at 4:31 pm

#1030083
RE: sql query

Ever try to hit a thrown pork chop in mid air? 😉
--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 24, 2009 at 2:12 pm

#1030043
RE: Map IP Address to a Geographical Location

ozkary (7/24/2009)
hi,
yes, fine tuning the queries to get the best performance is always welcome.
For production environment, I suggest to process/publish the conversion of IP to num during 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)

July 24, 2009 at 2:07 pm

#1030040
RE: Map IP Address to a Geographical Location

@ Mark...
Heh... that's what I get for looking at stuff before the caffeine reaches the brain. Thanks, Mark... nicely done.
--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 24, 2009 at 10:05 am

#1029835
RE: Map IP Address to a Geographical Location

Mark (7/13/2009)
I think both functions can be simplified
create function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin
return ((cast(parsename(@ip,4) as bigint)*256+
cast(parsename(@ip,3) as bigint))*256+
...
--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 24, 2009 at 9:01 am

#1029756
RE: Map IP Address to a Geographical Location

tdcheek (7/10/2009)
IP's fit much better in binary(4) than bigint - the conversion is pretty simple and cuts down heavily on reads and index size
What is your method of 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)

July 24, 2009 at 8:57 am

#1029752
RE: Existance Check - Performance

happycat59 (7/23/2009)
Using COUNT(1) requires SQL Server to return all records that meet the criteria.
Ummmm.... nope... it doesn't... it stops searching as soon as it finds one record that meets 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)

July 24, 2009 at 8:31 am

#1029725
RE: Problem with Query

. (7/24/2009)
I just want to say again that your solution was really pretty Jeff. Mine was also based on a ctes using row_number(), but yours was...
--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 24, 2009 at 8:26 am

#1029718
RE: Problem with Query

ChandraMohan Nandula (7/23/2009)
Hi Jeff,
Thanks a lot for providing the solution. I will check it and revert back to you.
Thanks, Chandra. I appreciate 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)

July 24, 2009 at 8:22 am

#1029711
RE: Splitting up a large database

Yeah... unfortunately, triggers are the only way to do any type of DRI between databases. Make sure that they're not RBAR and that they're well written (ie: performant) 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 24, 2009 at 8:20 am

#1029708
RE: Check table in use

If you just want to guarantee exclusive use for a heavy operation, you don't need to do that. Just use the WITH(TABLOCKX) hint and when it's able, it'll lock...
--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 24, 2009 at 8:16 am

#1029703
RE: how to write a function that executes Sub- Store procedure

. (7/24/2009)
I wasn't going to bring that up, because, despite the name, I don't consider extended stored procedures to be stored procedures in the sense the author of the question...
--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 24, 2009 at 8:14 am

#1029699
RE: differiniate quotes as data vs quotes as column identifier

Thanks for the feedback, Ghanta...
There's gotta be a way to do this (auto recognition and intermitent use of text qualifiers) without having to fire up SSIS or writing a CLR...
--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 24, 2009 at 6:51 am

#1029625
RE: tsql to store converted values

Almost forgot... it would be a help if you attached the CREATE TABLE statment for your table as a text file. Thanks.
--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 24, 2009 at 6:47 am

#1029618

Viewing 15 posts - 42,736 through 42,750 (of 59,067 total)