Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,581 through 41,595 (of 59,098 total)

RE: query optimizing

zukko (10/8/2009)
changed the where a bit, tested with 100,000 rows:
SELECT * FROM (
select acct_code, part_no,location,description,sku_no,list_status
,pub_date,pub_status,succession_status,void,in_stock,avg_cost
,std_cost
,((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var
,(avg_cost - std_cost)*in_stock as extended_diff
,in_stock*avg_cost as extended_avg_cost
from dfi_inv_master_list ...
--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, 2009 at 5:45 pm

#1063807
RE: Stored Procedure to Clone a Table Database Object

VERY cool! Thanks Mark.
--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, 2009 at 4:05 pm

#1063788
RE: Fastest Method to copy data Table to Table on the same Server

PS... don't forget to set the recovery mode to BULK LOGGED.
--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, 2009 at 4:02 pm

#1063787
RE: Fastest Method to copy data Table to Table on the same Server

USE SourceDB
SELECT CAST(columnname1 as NVARCHAR(whatever)) AS columnname1,
CAST(columnname2 as NVARCHAR(whatever)) AS columnname2,
...
--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, 2009 at 4:00 pm

#1063786
RE: Query Help

Mark Marinovic (10/8/2009)
Try this...the two additional columns I added are set apart with a carriage return before and after.
SELECT [CWS Division],
[Zone],
...
--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, 2009 at 3:29 pm

#1063774
RE: Full Time Sr DBA / DBD - Appleton, WI

Compensation
Compensation will be $50,000 - $65,000 based on expertise and experience.
Heh... so despite the laundry list of required expertise and "nice to haves", you really want an entry level programmer....
--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, 2009 at 3:25 pm

#1063773
RE: Stored Procedure to Clone a Table Database Object

It is a nice hint. I wonder if Profiler will actually pick it up.
--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, 2009 at 3:20 pm

#1063772
RE: SQL Statement performance

Look at the code... look at the GROUP BY... it's grouping a sum from one table by the data in 3 other tables. It's skipping the power you built...
--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, 2009 at 3:13 pm

#1063771
RE: splitting the values of column

Please read the following article on "Tally" tables... it explains how to do it for a whole table like that and it also explains why it works.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--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, 2009 at 2:22 pm

#1063727
RE: New Code formatting engine rolled out

Ah... got it. If you type the code in between the code markers, you get the & gt ; character representation. If you paste between the code markers...
--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, 2009 at 1:07 pm

#1063678
RE: New Code formatting engine rolled out

Testing Gails Problem...
SELECT 1 WHERE A > B
SELECT 1 WHERE A > B
--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, 2009 at 1:06 pm

#1063677
RE: New Code formatting engine rolled out

GilaMonster (10/8/2009)
Testing....
Using an inequality character
SELECT session_id FROM sys.dm_exec_requests WHERE session_id > 50
Using escape codes for inequality
SELECT session_id FROM sys.dm_exec_requests WHERE session_id > 50
Using angle brackets to designate a placeholder
USE <Database...
--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, 2009 at 1:04 pm

#1063675
RE: query optimizing

And, to answer your question, "YES"... it's because you are now sorting on an indexed column which contains the answer to the math calculation instead of sorting on a math...
--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, 2009 at 11:11 am

#1063622
RE: query optimizing

Mike McQueen (10/8/2009)
Wow.. that really took me by surprise, i didn't expect that!
I see what you're saying about the sorting after the math is done and not just sorting 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)

October 8, 2009 at 11:10 am

#1063621
RE: query optimizing

If you really want to have some fun, here's a great way to optimize the example queries that Mark wrote... I'll let you find the changes I made because it's...
--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, 2009 at 11:02 am

#1063615

Viewing 15 posts - 41,581 through 41,595 (of 59,098 total)