Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,841 through 24,855 (of 59,099 total)

RE: t-sql key on a table

Christian Graus (1/6/2014)
I did not know that a clustered index is the default for a PK. Doesn't a clustered index mean the entire row is written in the index...
--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 6, 2014 at 10:09 pm

#1677948
RE: t-sql key on a table

Just to add to that, "It Depends".
If this table is a lookup table that is mostly static, leaving out an IDENTITY column and using the the two columns...
--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 6, 2014 at 10:04 pm

#1677947
RE: t-sql key on a table

Actually, it's not a terrible idea to add an IDENTITY column to this table especially if it suffers a lot of inserts. If you make the clustered index (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)

January 6, 2014 at 9:54 pm

#1677944
RE: T-SQL Help

Other than that, this is a LEAD/LAG problem and that does require SQL Server 2012... which I don't have installed anywhere, yet.
--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 6, 2014 at 9:46 pm

#1677942
RE: T-SQL Help

PSB (1/3/2014)
Hi,
CREATE TABLE #TempTable
(
[GROUP] VARCHAR(100),
[Status] Varchar(10),
CreateDate Datetime,
ClosedDate DateTime,
RequestID INT
)
INSERT INTO #TempTable
(...
--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 6, 2014 at 9:45 pm

#1677941
RE: Need help with max value in date column

Christian Graus (1/6/2014)
Damn - you're right. I did that, without the row number, and didn't think the right values came out, that's why I started looking at group by.
To...
--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 6, 2014 at 9:24 pm

#1677937
RE: Need help with max value in date column

mxy (1/6/2014)
thanks Jeff you are so helpful as always !!!!!!
Thanks but Christian likely had it, as well. It just didn't look right and so he took another path. ...
--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 6, 2014 at 9:23 pm

#1677936
RE: Need help with max value in date column

Christian Graus (1/6/2014)
Damn - you're right. I did that, without the row number, and didn't think the right values came out, that's why I started looking at group by.
To...
--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 6, 2014 at 9:21 pm

#1677933
RE: Need help with max value in date column

Christian Graus (1/6/2014)
True - the real question is, does his calculated column come from a single row, or is it an aggregate ? I couldn't find any combination that gave...
--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 6, 2014 at 9:14 pm

#1677930
RE: Need help with max value in date column

We can use ROW_NUMBER() to assign sequences for each unique combination in descending order by date_ and then select the rows that have a"1" in them. Like this... (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)

January 6, 2014 at 9:07 pm

#1677928
RE: DBAs Need to Learn to Develop

Considering the "DBAs" that I've interviewed in the last six months, I'd say they shouldn't take the time learning more about development... they should first learn how to be 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)

January 6, 2014 at 8:47 pm

#1677924
RE: Updating 2 databases at the exact same time

Vincent_Poirier (1/6/2014)
Jeff Moden (1/6/2014)
Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it...
--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 6, 2014 at 1:58 pm

#1677879
RE: Clustered Index Rebuild Space Requirements ?

ELLEN-610393 (1/6/2014)
In the case of a very large table, 1.4 TB, that is not partitioned, what is the least amount of space that will need to be available to build...
--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 6, 2014 at 1:56 pm

#1677877
RE: Updating 2 databases at the exact same time

Yes... rename each table and build a synonym for each table using the original table name. Build an additional table for each table and populate it with the "current"...
--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 6, 2014 at 1:00 pm

#1677866
RE: Exec Trigger On Import

jeandlauro (1/6/2014)
I just mean I thought it would be easier to link Microsoft data tools together.
I've been wondering the same thing for years (almost 2 decades, now). When I've...
--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 6, 2014 at 12:56 pm

#1677862

Viewing 15 posts - 24,841 through 24,855 (of 59,099 total)