Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,066 through 40,080 (of 59,098 total)

RE: Exceptional DBA Voting

What would really be cool is to see the email or letter from the person(s) that nominated these folks. Let's see what they have to say about their exceptional...
--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)

January 29, 2010 at 10:09 pm

#1111115
RE: Add standard text to all stored procedures

MANU-J. (1/29/2010)
Try the below mentioned code on test server.
DECLARE @tbl TABLE
(
RowText varchar(max)
)
INSERT INTO @tbl
exec sp_helptext 'dbo.test1'
DECLARE @sp-2 varchar(max)
SELECT @sp-2 = ISNULL(@sp, '') + RowText
FROM @tbl
SET @sp-2 = REPLACE(@sp, 'AS', '/****************************
SQL2008...
--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)

January 29, 2010 at 9:50 pm

#1111112
RE: Stored Proc with table variables running very slow?

I forgot about that and that's very true. Have you ever seen code that does that, though?
--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)

January 29, 2010 at 9:42 pm

#1111110
RE: Index Where Name = NULL

Unless a table is nothing more than a temp table, I put clustered indexes on all tables as a matter of rote. That way, I don't have to worry...
--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)

January 29, 2010 at 9:40 pm

#1111109
RE: Stored Proc with table variables running very slow?

san43 (1/29/2010)
Also is it possible that data is more fragmented in production which is causing the slowness, if so how can I find out that?
My apologies... I missed that part....
--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)

January 29, 2010 at 9:34 pm

#1111106
RE: Stored Proc with table variables running very slow?

As some have already stated, the optimizer thinks all Table Variables only have one row and, therefor, may not pick the best execution plan when the data finally reaches what...
--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)

January 29, 2010 at 9:29 pm

#1111105
RE: hourly average

rpatil22 (1/29/2010)
I have a data in table
Heh... but we don't. We have text on a page and would have to spend a bit of time turning it into 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)

January 29, 2010 at 9:19 pm

#1111103
RE: hourly average

Another way to skin the same cat except this looks a little cleaner...
SELECT ID, StartTime, StartTime + '00:59:59' AS EndTime, AVG(Produced) AS HourlyAverage
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)

January 29, 2010 at 9:05 pm

#1111102
RE: hourly average

You can cheat the hell out of this one, BaldingLoopMan... 🙂
Using your good test data setup...
SELECT ID,
DATEADD(hh,DATEDIFF(hh,0,UtcTd),0) AS StartTime,
...
--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)

January 29, 2010 at 8:57 pm

#1111101
RE: Compare values from 2 tables

Hopefully, you ended up with something like this...
SELECT m.MachineID,
m.Description,
CASE
...
--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)

January 29, 2010 at 8:37 pm

#1111098
RE: Patern Matching

UT- (1/29/2010)
Hi,
I have a text column that contains notes about a customer, in this column there are mutiple reference numbers of a customer in the format of xxxxxx-xxxxx-xx (e.g 123456-12345-12).
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)

January 29, 2010 at 8:18 pm

#1111093
RE: Patern Matching

Lowell (1/29/2010)
if your column is varchar or nvarchar, it's fairly easy; you know the exact length of the string to catch, and can use charindex or patindex to find where...
--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)

January 29, 2010 at 8:13 pm

#1111092
RE: How do I do an update/insert to certain fields in a certain order?

If it's only 5 DB's, why not simply have a stored proc that takes the parameters and does 5 inserts using the 3 part naming convention?
--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)

January 29, 2010 at 7:53 pm

#1111091
RE: Rotating Keys

Ummm... I'm confused. Wouldn't the real key be to keep people from getting a copy of the database to begin with??? :blink:
--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)

January 29, 2010 at 7:49 pm

#1111090
RE: Why moving a database slowed it down a lot?

Heh... Rumor has it that you probably need to rebuild stats because they don't "move" with the 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)

January 29, 2010 at 7:17 pm

#1111086

Viewing 15 posts - 40,066 through 40,080 (of 59,098 total)