Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,801 through 49,815 (of 59,099 total)

RE: use a sequence of column values in a query

Heh... in this situation, you're rebuilding the view! It's gonna recompile no matter what... 😉 Works fine in production code especially in 2k where there's no such thing...
--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)

June 22, 2008 at 8:59 pm

#832438
RE: Pivot... I don't think it's possible...

Just in case someone wants to "play", here's the Cross Tab code I'm trying to duplicate with a single pivot...
SELECT Company,
Year,
...
--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)

June 22, 2008 at 8:44 pm

#832437
RE: transpose rows into columns

sriharivittal1 (5/28/2008)
Can some one provide Sample Code?
You want sample code? Then provide some sample data. Please see, read, understand, and follow the article in the link in my...
--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)

June 22, 2008 at 4:33 pm

#832420
RE: For loop in sql server

venki (6/22/2008)
Do we have for loop or for each statement in SQL Server?
I tried to use the for loop in my stored procedure but I haven't found it's working and...
--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)

June 22, 2008 at 4:27 pm

#832418
RE: use a sequence of column values in a query

Whether you use SELECT * or not, if you want to see the table changes in a view, you have to recompile the view. Like Barry said, this 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)

June 22, 2008 at 4:23 pm

#832417
RE: Converting a stored procedure into a View

Save us all some time... post the stored procedure that you're trying to convert into a view.
--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)

June 22, 2008 at 2:05 pm

#832408
RE: use a sequence of column values in a query

Jeff W...
SELECT * works perfect on these type of "pass through" or "surrogate views". You can go through the process of naming all the columns if you want, but...
--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)

June 22, 2008 at 1:58 pm

#832405
RE: Query for Huge Tables

Bilal (6/22/2008)
I expect about 1000 rows to return , and their is one type of index defined which is traffic1
each table have millions of records
any thing else ?
Yes...
--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)

June 22, 2008 at 9:32 am

#832375
RE: Best Practices for Database Design

Ummm... you guys are getting awfully serious about this... you do realize that the article was written and first posted about 3 years ago, huh? 😉
--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)

June 22, 2008 at 8:59 am

#832370
RE: Condional Update/insert from one table to other.

Ok... thanks...
Does BitBucket's code do it for you or do you still need help with something?
--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)

June 22, 2008 at 8:56 am

#832369
RE: Rules, Defaults and the SQL Standard

Even though I don't like cursors and I don't use them, that would be the first thing to change... I'd have them stop deprecating stuff that works. That's 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)

June 22, 2008 at 8:54 am

#832368
RE: use a sequence of column values in a query

JRJ (6/22/2008)
That's possible. How can I change a views definition in a proc? Also I am deeling with duplicate indexes, is that ok?
thanks
A view's definition can easily be changed using...
--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)

June 22, 2008 at 8:48 am

#832367
RE: help with data type and default value

No, NO, NO!!! Never ever store formatted data! You will pay dearly for doing so someday in the future. This type of (leading zero) formatting should really...
--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)

June 21, 2008 at 9:08 pm

#832330
RE: Get data between specific week days

giontech (6/21/2008)
Hello:
I need help in how to get data from a table with a daterange from sunday to saturday of the last week.
What I'm looking for is to be able...
--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)

June 21, 2008 at 8:47 pm

#832329
RE: Generate 5 digit random numbers

Mani Singh (6/21/2008)
you can use this and manipulate this query to code as needed..
DECLARE @GENRANDOM nvarchar(9)
SET @GENRANDOM =LEFT(SUBSTRING (RTRIM(RAND()) + SUBSTRING(RTRIM(RAND()),3,11), 3,11),9)
--SELECT SUBSTRING(@GENRANDOM ,3,1)
SELECT @GENRANDOM as 'RANDOM_NUMBER'
Yep... you could do...
--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)

June 21, 2008 at 8:05 pm

#832327

Viewing 15 posts - 49,801 through 49,815 (of 59,099 total)