Jeff Moden

Forum Replies Created

Viewing 15 posts - 47,851 through 47,865 (of 59,102 total)

RE: Exclude Word List to filter SELECT results

rbarryyoung (10/9/2008)
Here's another (simple) way to do it:
Select *
From Locations
Where NOT EXISTS( Select * From Excludes
Where Location LIKE '%'+Excludes+'%')
This is probably close to what Jeff intended to write,...
--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)

October 9, 2008 at 5:58 pm

#883183
RE: 'N' number of usage - Tally Table

Sergiy (10/9/2008)
Jeff Moden (10/9/2008)
change all the delimiters to a "thorn" and go for it.
There is no guarantee "thorn" is not or will not be used within the filed for...
--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)

October 9, 2008 at 5:56 pm

#883182
RE: Data Shuffle

Heh... I was hoping you had an IDENTITY PK... I won't have to make many changes to the script I just wrote 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)

October 9, 2008 at 5:54 pm

#883181
RE: Table Variables and performance

Michael Earl (10/8/2008)
While it is running, look for tables in tempdb:
SELECT * FROM TempDB..Sysobjects WHERE Type = 'u'
A table variable usually has to get pretty big before it spills over...
--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)

October 9, 2008 at 4:27 pm

#883168
RE: Table Variables and performance

G Bryant McClellan (10/8/2008)
Michael,
Thanks for the reply. I can see that testing is the only sure-fire method to compare the performance, but there must be some indication that the switch...
--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)

October 9, 2008 at 4:26 pm

#883167
RE: 'N' number of usage - Tally Table

Dang it... I forgot about RegEx. That might work... change all the delimiters to a "thorn" and go for 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)

October 9, 2008 at 1:15 pm

#883113
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

riga1966 (10/9/2008)
I have a very clear understanding of how to do a Cross-Tab now.
How about Uncross-Tab?
Does anyone have any samples?
Thanks,
Heh... that's called either "UnPivot" or "Normalizing". You could probably...
--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)

October 9, 2008 at 1:12 pm

#883112
RE: Dense_Rank Challenging

You bet. Thanks for the feedback.
Just remember... if you can't wedge a clustered index in there like I did, this solution will not work correctly.
--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)

October 9, 2008 at 5:59 am

#882817
RE: Updating NULL Values

cdex3 (10/8/2008)
I have a table in the following format.
ID RecType PKIdentifier RecordData
1 01 acc00108 Text
2...
--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)

October 9, 2008 at 5:50 am

#882812
RE: Dense_Rank Challenging

JohnDBA (10/8/2008)
Jeff,
This solution is close to what I need, but I need to create groups. I cannot create groups with jbmRank. 🙁
I will read that article. Thank 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)

October 9, 2008 at 5:19 am

#882803
RE: Data Shuffle

Ok... I borrowed some data and assume that you don't actually have a primary key... this should give you and idea...
--drop table #shuffle
DECLARE @NameCountPlus1 INT
SELECT @NameCountPlus1 = COUNT(*)+1...
--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)

October 8, 2008 at 9:54 pm

#882689
RE: Dense_Rank Challenging

If you haven't read the article that Matt listed, you really need to go back and read it so you understand why the following works... and it works at 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)

October 8, 2008 at 9:52 pm

#882688
RE: Data Shuffle

What is the primary key of the table?
Also, since this is your first post, you might want to take a look at the link in my signature below... folks will...
--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)

October 8, 2008 at 7:34 pm

#882665
RE: 'N' number of usage - Tally Table

I remember... it's a pretty tough task because it's not just quotes that Sergiy has to worry about with the data he showed me. It's data from a "properly"...
--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)

October 8, 2008 at 7:16 pm

#882658
RE: do operation on last inserted record by a trigger

Absolutely...
--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)

October 8, 2008 at 6:37 pm

#882652

Viewing 15 posts - 47,851 through 47,865 (of 59,102 total)