Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,416 through 32,430 (of 59,099 total)

RE: HierarchyID performance problems... Really???

Sure... the following code creates a million row "clean" hierarchy in the form of a classic Adjacency List. I have it as a stored procedure so that I 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)

October 2, 2011 at 12:20 pm

#1389225
RE: Find first day of a week/month

codebyo (9/27/2011)
Hmm. What do you mean? September 1st is Thursday, not the first day of the week.
Day of week would be like this:
SELECT CASE DATEPART(WEEKDAY, '20110901')
...
--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 2, 2011 at 12:10 pm

#1389224
RE: Without using While Loop How to it Work

CELKO (9/28/2011)
Where is the DDL? Why do you have so many OUTER JOINs? That is a really bad code smell. Why are you doing all that silly COBOL-style string handling?...
--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 2, 2011 at 12:06 pm

#1389223
RE: Query to combine two fields into one output

codebyo (9/29/2011)
For a variable size code, you can do something like this:
DECLARE @Size INT = 7;
SELECT
REPLICATE('0', @Size-LEN(CAST(1234 AS VARCHAR(MAX)))) + CAST(1234 AS VARCHAR(MAX)) +
REPLICATE('0', @Size-LEN(CAST(76541 AS...
--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 2, 2011 at 11:37 am

#1389222
RE: Urgent Help!!! on Function

pespes009 (9/29/2011)
Thanks a lot guys !!!
So, what did you end up with? Two way street here... Please post the code that you ended up using so that others may...
--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 2, 2011 at 11:31 am

#1389219
RE: Difference between Except and Not In

~Dev~ (9/30/2011)
You should not compare NULL with equality operators. you must use 'IS NULL' or 'IS NOT NULL' operators.
Thus (1=NULL) and (NULL=NULL) are bad operations.
Else, you may override default behavior...
--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 2, 2011 at 11:26 am

#1389218
RE: How to change query so that it does not have to group by the Unit ID column

theeggman (9/30/2011)
The following query needs to be changed so that the total QTY, total Pallets, total Baskets, total Carts are displayed on the same record. Currently, since I have to...
--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 1, 2011 at 4:02 pm

#1389157
RE: DataType Table or TempTable Which is Best?

Gianluca Sartori (9/30/2011)
This great article[/url] by Waye Sheffield will help you.
Regards,
Gianluca
Agreed... it's one of the better articles on Table Variable v.s. Temp Tables that I've ever seen.
--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 1, 2011 at 3:54 pm

#1389156
RE: Best approach to import text file?

BCP with a format file that uses the end of line characters as delimiters (as well as some others).
In actuality, that's not the "best" way... the absolute best way would...
--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 1, 2011 at 3:51 pm

#1389155
RE: how to display single column in result

dastagiri16 (10/1/2011)
select
case when Profesenalism=1 then count(Profesenalism) end as 'Profesenalismpoor',
case when profesenalism=2 then count (profesenalism) end 'profesenalismavg',
case when Communication=1 then count(Communication) else 0 end 'Communicationpoor',
...
--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 1, 2011 at 3:48 pm

#1389154
RE: get results from 2 tables?

Mick,
Read the link that BitBucket pointed you to and post your data using the methods therein... you'll get answers much quicker.
--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 1, 2011 at 3:41 pm

#1389153
RE: Code help, may be can be acheived through CTE

Sorry folks... I've been burning some midnight oil at the job and the answer to the question of how this works is a bit of a long one. To...
--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 1, 2011 at 1:57 pm

#1389149
RE: Data Grouping

I fully realize that this thread is 3-1/2 years old and the OP probably got a solution a very long time ago. I found this thread totally by accident...
--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 1, 2011 at 1:33 pm

#1389145
RE: Database Diagram printing problems

You bet, Grant. Thank you for taking the time to post some 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)

September 30, 2011 at 4:58 pm

#1389054
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

Mushroom (9/30/2011)
This is a great article Jeff and going to be extremely useful.
But, I'm wondering why you appear to be generating a recordset with 10000 rows and then limiting that...
--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)

September 30, 2011 at 6:39 am

#1388818

Viewing 15 posts - 32,416 through 32,430 (of 59,099 total)