Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,676 through 51,690 (of 59,091 total)

RE: SELECT WITH NOLOCKS

I agree with the others... find and fix the problem... and, Know this... Setting the Transaction Isolation Level and using WITH (NOLOCK) is NOT a panacea for fixing deadlocks.
We were...
--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)

March 18, 2008 at 12:44 pm

#791061
RE: How can we show numbers when they dont exist in Database.

Sorry... forgot to add the line that makes it more than twice as fast...
DECLARE @Year INT
SET @Year = 2008
;WITH cteDates AS
(
SELECT DATEADD(yy,@Year-1900,0)+Number AS TheDate
...
--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)

March 18, 2008 at 12:32 pm

#791052
RE: How can we show numbers when they dont exist in Database.

Matt is absolutely spot on. Without actually getting into using a Tally table to do this, here's one way to programmatically generate a Year's worth of dates using 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)

March 18, 2008 at 12:20 pm

#791044
RE: Scheduling data extraction to excel

Just my 2 cents... I'm always pretty much amazed at such requests... I do one of two things in most cases where someone "has to have it" in an Excel...
--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)

March 18, 2008 at 12:05 pm

#791036
RE: Query Problem

In other words, Dragon3486... messy code is hard to troubleshoot. 95% of all such simple syntactical problems can easily be avoided altogether if you format your code in an...
--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)

March 18, 2008 at 11:55 am

#791030
RE: Select from view differs from its definition

More likely, since you said you had the window open for days, the data in the tables the view references changed. Doesn't take a lot... if your execution plan...
--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)

March 18, 2008 at 11:50 am

#791023
RE: stored procedure Vs select statement

And, the "re-used" execution plan for 1 set of parameters might be absolutely terrible with another set or sometimes a thing called "parameter sniffing" kicks in and the whole server...
--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)

March 18, 2008 at 11:46 am

#791021
RE: How can i prevent the client to open or see the sql dataBase file (mdf) out of the application

If, what you really mean, is that you don't want anyone to be able to "steal" your code, then you must check all of your code into some safe place...
--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)

March 18, 2008 at 11:40 am

#791016
RE: joins

swathichilukuri85 (3/18/2008)
thank u jeff
can u provide me the script
The others are correct... We can't write a script for your tables because you haven't posted them or any sample data....
--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)

March 18, 2008 at 11:32 am

#791014
RE: Common Table Expressions

Both are nothing more than "in-line" views... views can use indexes just like any query can. Same goes for CTE's and Derived tables... "Have Index, Will Compute". 😀
--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)

March 18, 2008 at 11:20 am

#791010
RE: Are temp tables better than table variables

I've had a great many similar experiences especially with poor performance due to Table Variable usage. If you add in the fact that TempTables persist in Query Analyzer whereas...
--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)

March 18, 2008 at 10:58 am

#790986
RE: Are temp tables better than table variables

If you really want technical... scroll back up to the early stages of this thread and look at the URL I recommended... :hehe:
--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)

March 18, 2008 at 10:25 am

#790968
RE: Common Table Expressions

srienstr (3/18/2008)
I'll stick to indexed temp tables for self-links then. (The base table used in this process has around 300k rows)
If the underlying tables are correctly indexed, the CTE...
--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)

March 18, 2008 at 10:22 am

#790965
RE: Vardecimal

But I want my Easter Eggs NOW!!! Where's my porkchops? 😛
--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)

March 18, 2008 at 8:45 am

#790873
RE: Common Table Expressions

Tao Klerks (3/18/2008)
Thanks GSquared - this makes sense to me.
I don't think I agree about the clarity of using CTEs for derived tables, but I guess that might be...
--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)

March 18, 2008 at 8:37 am

#790864

Viewing 15 posts - 51,676 through 51,690 (of 59,091 total)