| Author |
Topic |
|
SqlZ
Yak Posting Veteran
69 Posts |
 Posted - 2002-02-15 : 16:16:44
|
| Might be taking a side gig working with SQL Server 6.5. I have never worked with 6.5 before but have a ton of experience with 7.0 and 2000. Whats the major differences between 6.5 and the later versions? I am sure all of you that have this experience could write a novel but was just wondering if I could get the Cliff Notes version, or maybe a couple of bullets listing out major differences?Thanks in advance.========================My username represents the two things that consume most of my time.SQL, and my Z |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-15 : 16:30:53
|
| Actually Merkin is in kindred spirits, he's just now starting to play with it for the first time as well.Probably the biggest thing to get used to are devices. You need to create a device, which is just a regular file, and then create a database on that device. It's similar to filegroups, but in reverse; you can put multiple databases (and their logs) on a single device, but that's not a good practice. You should follow the one-device one-database rule for best performance (DO NOT EVER put the log and the data on the same device, you'll get problems later).You also lose autogrowth, so you need to keep an eye on the transaction log and data sizes...even if the device is larger than the database, you have to manually increase the database and log size. The log has a habit of filling up more quickly than expected, so setting truncate log on checkpoint might be a good idea. It's also best to start a database small, because if you make it too big you cannot shrink it below its initial size.Varchar and char max out at 255 characters, and data pages are 2K in size instead of 8K. Row locking isn't available except with SP5, and I think only for UPDATEs.Check out the bookstores and the bargain bins for Inside SQL Server 6.5, its by Microsoft Press, written by Ron Soukup, and there's no better 6.5 reference IMHO. If you can find it it shouldn't be more than $15. You should also look at Books Online for 7.0 and read the What's New? section; anything that's in there is NOT in 6.5. |
 |
|
|
SqlZ
Yak Posting Veteran
69 Posts |
Posted - 2002-02-15 : 16:48:18
|
| Thanks Rob.========================My username represents the two things that consume most of my time.SQL, and my Z |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-15 : 20:53:53
|
| I would also recommend sql server unleashed by Kalen Delaney (but not later versions which weren't authored by her).With 6.5 you will need to be a lot more careful about everything you do.It is very sensitive to use of memory - if you run queries without sufficient memory you will get a vast degradation in performance.If you run large transactions and fill up the transaction log (should never happen) the server has a good chance of crashing and corrupting the database.select .. into .. locks system tables - if it is creating a table in tempdb then it will likely stop all other action on the server until it completes.Don't trust enterprise manager at all.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-15 : 21:51:51
|
As a previous poster mentioned, SQL Server 6.5 uses 2k pages so you will have some pretty tight (relatively speaking) limitations on number of tables and columns in addition to datatype limitations like VARCHAR(255). Also be very careful if implementing TEXT / IMAGE datatypes -- the way they work in 6.x is that they are VARBINARY pointers to 2k data pages. If you initialize a TEXT / IMAGE row in SQL Server 6.x you will automatically take up 16 bytes for the pointer PLUS 2k REGARDLESS of how much data you actually store.One cool thing is you can see your transaction log by doing a SELECT * FROM syslogs.Index builds, DBCCs, and backups will take a lot longer. Also, doing periodic DBCCs are a MUST with 6.x to ensure your backups are in a consistent state -- don't be too surprised if one day you find a corrupt database.Good luck! Edited by - spyder on 02/15/2002 21:55:25 |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-16 : 00:34:54
|
Follow this link , i thought should be some help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12658 , the same guys (Robvolk,Spyder,nr ) have some positive things to say about 6.5 --------------------------------------------------------------"Happiness is not something you experience, it's something you remember."Edited by - Nazim on 02/16/2002 00:36:16 |
|
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-02-18 : 05:16:41
|
| If you're planning on doing any transactional replication then they actually took a step backwards with SQL7. In 6.5 you can remove a single table from replication > modify it > and put it back in without effecting the rest of the replication. In SQL7 they removed this feature for some stupid reason. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-18 : 06:58:08
|
| They also had a nifty LOAD TABLE command that let you restore individual table(s) from a backup set, not just the whole database. WHY they got rid of that feature will forever elude me (of course, I never needed to use it in 6.5, only 7.0!) |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-18 : 11:16:12
|
| Well Rob, ask and you shall receive. It will elude you no more. The restore table was removed because it did not make sense for an ACID DBMS. It allowed the user to make the database transactionally inconsistant. Let's say you take a backup Sunday, and have a heavy transaction load all week, then you restore a heavily used table on Saturday. Now you have a situation where one table is at last Sunday, and the rest of the DB is at today, Everything is F'ed up. It created more problems than it solved. There is nothing you cannot solve through DTS that LOAD TABLE gave you, but it just makes it a little more difficult.-Chad |
|
|
|
|
SQL Server 6.5?