Trying to figure out why this is a full table scan

Question

Trying to figure out why this is a full table scan

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

aurato (3/1/2012)
GilaMonster (3/1/2012)
aurato (3/1/2012)
GilaMonster (3/1/2012)
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraints
This makes more sense I guess. I do eventually want to change the source table because I'm sure this join happens in lots of other queries (both very heavily used tables around here).
No point in doubling the storage size unnecessarily. If that column is 17-20 ASCII characters, then changing it to nvarchar wastes up to 20 bytes per row. On a 3 million row table that's 57MB of wasted space in the table and in index that the column appears in.
Rather make sure that any table joining to it is char/varchar of an appropriate length
Did I mistype somewhere? I actually want it to go from unicode to non-unicode.
EDIT: In this case nvarchar(50) to varchar(21), as in GP the SOPNUMBE is a char(21)

You said you eventually want to change the source table (which I assume is SOP30300), and that's already a char(21)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aurato SSCrazy Points: 2575 More actions · Answer 2

GilaMonster (3/1/2012)
aurato (3/1/2012)
GilaMonster (3/1/2012)
aurato (3/1/2012)
GilaMonster (3/1/2012)
Chance the #StatusHistory temp table not the permanent table. You'll need to drop all indexes and constraints on that column first, change the data type and then recreate the indexes and constraints
This makes more sense I guess. I do eventually want to change the source table because I'm sure this join happens in lots of other queries (both very heavily used tables around here).
No point in doubling the storage size unnecessarily. If that column is 17-20 ASCII characters, then changing it to nvarchar wastes up to 20 bytes per row. On a 3 million row table that's 57MB of wasted space in the table and in index that the column appears in.
Rather make sure that any table joining to it is char/varchar of an appropriate length
Did I mistype somewhere? I actually want it to go from unicode to non-unicode.
EDIT: In this case nvarchar(50) to varchar(21), as in GP the SOPNUMBE is a char(21)
You said you eventually want to change the source table (which I assume is SOP30300), and that's already a char(21)

Oh I'm sorry I meant the source table that I populate #StatusHistory with which is called PLCTrack_StatusHistory.

Jared SSC Guru Points: 61793 More actions · Answer 3

Umm... Unless I missed something, you are trying to change the data type on a production table that is accessed by Great Plains? I'm not so sure it is a good idea to change a data type on a GP table. GP, in my experience, is extremely finicky. You change something on the database and you could be in for a world of hurt.

Jared
CE - Microsoft

aurato SSCrazy Points: 2575 More actions · Answer 4

SQLKnowItAll (3/1/2012)
Umm... Unless I missed something, you are trying to change the data type on a production table that is accessed by Great Plains? I'm not so sure it is a good idea to change a data type on a GP table. GP, in my experience, is extremely finicky. You change something on the database and you could be in for a world of hurt.

Yeah read my last post I didn't mean to give that impression. I'm not gonna touch Great Plains. There is a third, unmentioned table in the original post that supplies the sopnumbes for my temp table. I meant that I wanted to change THAT column to match the GP one.

Jared SSC Guru Points: 61793 More actions · Answer 5

aurato (3/1/2012)
SQLKnowItAll (3/1/2012)
Umm... Unless I missed something, you are trying to change the data type on a production table that is accessed by Great Plains? I'm not so sure it is a good idea to change a data type on a GP table. GP, in my experience, is extremely finicky. You change something on the database and you could be in for a world of hurt.
Yeah read my last post I didn't mean to give that impression. I'm not gonna touch Great Plains. There is a third, unmentioned table in the original post that supplies the sopnumbes for my temp table. I meant that I wanted to change THAT column to match the GP one.

So you don't need to ALTER anything... Just change your definition or SELECT ... INTO

Jared
CE - Microsoft

aurato SSCrazy Points: 2575 More actions · Answer 6

SQLKnowItAll (3/1/2012)
aurato (3/1/2012)
SQLKnowItAll (3/1/2012)
Umm... Unless I missed something, you are trying to change the data type on a production table that is accessed by Great Plains? I'm not so sure it is a good idea to change a data type on a GP table. GP, in my experience, is extremely finicky. You change something on the database and you could be in for a world of hurt.
Yeah read my last post I didn't mean to give that impression. I'm not gonna touch Great Plains. There is a third, unmentioned table in the original post that supplies the sopnumbes for my temp table. I meant that I wanted to change THAT column to match the GP one.
So you don't need to ALTER anything... Just change your definition or SELECT ... INTO

Yeah that seems to be the consensus. Thanks for making sure I wasn't doing anything stupid.

Jared SSC Guru Points: 61793 More actions · Answer 7

aurato (3/1/2012)
SQLKnowItAll (3/1/2012)
aurato (3/1/2012)
SQLKnowItAll (3/1/2012)
Umm... Unless I missed something, you are trying to change the data type on a production table that is accessed by Great Plains? I'm not so sure it is a good idea to change a data type on a GP table. GP, in my experience, is extremely finicky. You change something on the database and you could be in for a world of hurt.
Yeah read my last post I didn't mean to give that impression. I'm not gonna touch Great Plains. There is a third, unmentioned table in the original post that supplies the sopnumbes for my temp table. I meant that I wanted to change THAT column to match the GP one.
So you don't need to ALTER anything... Just change your definition or SELECT ... INTO
Yeah that seems to be the consensus. Thanks for making sure I wasn't doing anything stupid.

Not stupid... Ignorant. Which, unfortunately, I am king of 😛

Jared
CE - Microsoft