CHAR vs VARCHAR

Question

CHAR vs VARCHAR

Viewing 4 posts - 31 through 33 (of 33 total)

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

Will1922 SSCertifiable Points: 7189 More actions · Answer 1

I wasn't modifying the old table. I was copying to the new table with all varchar. I know that I could have done the trimming as I copied, but I didn't.

I don't want to change the size of my database file. I'm prety sure that SHRINKing a file will only reclaim lost space at the end of a file. I don't think that it will actually defrag indexes. I have not read anything that says it will. I've read some stuff online and went through a couple of articles in Books Online and not found any reference to any kind of INDEXDEFRAG when talking about shrinking a file or database.

I have autogrow turned off (I don't use autogrow on ANY of my databases).

Live to Throw
Throw to Live
Will Summers

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I believe Will is correct. Shrinking just removes space from the end, not in between tables. You need to rebuild clustered indexes to remove internal space and it will be set according to your fill factor.

BTW, I always use Autogrow. Just set a limit and an alert to let you know when it happens. Worse thing is production stops because of some process filling the db and you get called away from dinner with your wife just as the food arrives , set a limit and let the db grow within reason if it needs to and you can check it soon after.

I might also recommend using a placeholder file, something like a few GB to save space in case you need it. Never know when someone drops stuff onto a drive because they see space.

Will1922 SSCertifiable Points: 7189 More actions · Answer 3

I keep a good eye on my database sizes. I'm lucky in the fact that I only have to manage 4 servers, 2 in production. I have about 12 databases on my main production server.

I have plenty of room for growth in all the databases. I know how much data is going to be inserted into each database daily. If I run out of space in one of my databases, then I have bigger problems than users not being able to insert.

I have a script that checks table sizes that I run about every 1-2 weeks. This helps me monitor table sizes and double-check file growth.

My nightly jobs backup to a drive that also has database files on it. I wouldn't want my backups to fail because of some database grew too much and ate up all the free space on the drive.

To me, backing up my production data comes first.

Live to Throw
Throw to Live
Will Summers

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Just for giggles, I'd still like to know what the SN columns are for... what are they?

--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)