Jeff Moden

Forum Replies Created

Viewing 15 posts - 35,116 through 35,130 (of 59,099 total)

RE: PURPOSE OF TEMPORARY TABLES

SQLCHILD (2/11/2011)
what are the main purposes for which temporary tables are used?
To go along with what Gail said above...
When used properly do "Divide'n'Conquer" a large "problem", you can get 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)

February 12, 2011 at 10:11 am

#1286034
RE: Output 1 Record from 12 million records table ?

sqldba_icon (2/11/2011)
i have a table which has 12 million records. In the execution plan , only one record is sent. There is an nc index which is doing seek. However...
--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)

February 11, 2011 at 11:30 pm

#1285990
RE: Get file size

alex_toronto27 (2/10/2011)
Jeff, amazing SP !!! , can't figure out how to tweak it to pull all properties for files in sub-folders too
I changed EXEC Master.dbo.xp_DirTree @piFullPath,0,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)

February 11, 2011 at 11:12 pm

#1285989
RE: Weird query plan, should I flush my cache?

Gianluca Sartori (2/9/2011)
Thank you, Jeff. I will try and keep you posted.
Just a bit of feedback... I ran into a similar problem with and SCD2 trigger I was building. ...
--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)

February 11, 2011 at 10:59 pm

#1285987
RE: Pivoting Multiple Columns

Khades (2/11/2011)
Thank you Jeff. Any Idea which one would perform better?
I posted that near the end of the article. 😉
--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)

February 11, 2011 at 10:55 pm

#1285986
RE: Need help with sql query

schauhan13 (2/10/2011)
I have a table in which date is divided into 3 columns salesyear,salesmonth and salesday.
Now I need to create a sql select query for a date range say from...
--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)

February 11, 2011 at 4:45 pm

#1285957
RE: Pivoting Multiple Columns

Look for the word multi-aggregate in the following artcle...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

February 11, 2011 at 4:16 pm

#1285952
RE: SQL to split a non delimited string into delimited string

Lynn Pettis (2/9/2011)
@jeff - I haven't been able to find anything as of yet, so some of this has to be taken as personal experience. What this means 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)

February 9, 2011 at 5:26 pm

#1284909
RE: track the db growth

atulkapoor20-965628 (2/9/2011)
please see the attached file... which will help you a lot in tracking DB growth i.e. for capacity planning
but its a easy way.. u can find lots of scripts...
--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)

February 9, 2011 at 7:42 am

#1284468
RE: Extract 3 months worth of data

simflex-897410 (2/8/2011)
Greetings again dearest helpers.
I need some huge favor.
I am asked to extract 3 months worth of data from sql server into a pipe-delimited .txt file with text qualifiers (quotes...
--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)

February 9, 2011 at 7:34 am

#1284458
RE: Weird query plan, should I flush my cache?

Being an ETL system, it sounds like all of your queries are for batch jobs. If that's true, forcing each query to recompile (as some of the others suggested)...
--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)

February 9, 2011 at 7:31 am

#1284453
RE: Create temp Tbale using SSIS

Depending on what you're doing, the use of Global Temp Tables can cause concurrency problems.
--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)

February 9, 2011 at 7:26 am

#1284449
RE: SQL to split a non delimited string into delimited string

Lynn Pettis (2/8/2011)
Careful here, there is a tipping point when accessing a tally table from disk. It all depends on how big the table needs to be to support...
--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)

February 9, 2011 at 7:00 am

#1284427
RE: SQL to split a non delimited string into delimited string

Wolfmeister (2/8/2011)
SwePeso (2/8/2011)
DECLARE @Sample VARCHAR(100) = '12345'
;WITH cte(CurrPos, LastPos, String)
AS (
SELECT1 AS CurrPos,
DATALENGTH(@Sample) AS LastPos,
CAST(@Sample AS VARCHAR(MAX)) AS String
UNION ALL
SELECTCurrPos + 1 AS CurrPos,
LastPos,
STUFF(String, 2 * CurrPos, 0, ',') 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)

February 8, 2011 at 9:22 pm

#1284185
RE: Help with a query

lance.kentwell (2/8/2011)
ok, first I am using this query to get first day of the previous month:
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
Then I take the result and convert it to epoch time suing...
--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)

February 8, 2011 at 8:55 pm

#1284178

Viewing 15 posts - 35,116 through 35,130 (of 59,099 total)