moving SQL 2000 to SQL 2008

[supmktg]

Hi

I've just moved a hosted SQL Server 2000 backend to a new host running 2008. I have 2 questions that I hope someone can answer for me:

1) In 2000, the tables seemed to have a default sort order. In 2008, there seems to be no sort order. Is there a table setting for default sort order and if so where is it?

2) The database was restored from a 2000 bak file. When I open it in my enterprise manager (EMS SQL Manager 2008) I get a message that the compatibility level of the database does not match the server version. The front end was designed using A2K, it runs in A2K3. Will I have other issues if I correct the compatibility level? Should I correct the compatibility level or leave it alone?

Thanks,
Sup

 

[Galaxiom]

Server 2008 has some different datatypes. Check out the date types as they give the most grief with Access. Access is not compatible with the DateTime in 2008. Check if it is using the compatible (if my memory serves me correctly) TinyDate.

Tables don't really have a sort order. They just store records where ever suits their digital logic at the time. All databases should use an Order By clause in all queries.

I have never used Server 2000 but it may be like Access which displays an order in tables but what it actually shows you is a built in query.

 

[mdlueck]

1) In 2000, the tables seemed to have a default sort order. In 2008, there seems to be no sort order. Is there a table setting for default sort order and if so where is it?

Assuming you have MS SQL Server Management Studio, then navigate to a paticular table in said database, right-click, Design, Hover the mouse over the grey border ahead of the "Column Name" column, right-click and click "Indexes / Keys..." and see if there is a "PK_*" defined... PK means Primary Key aka default sort.

 

[supmktg]

mdlueck - you are correct, the default sort order is now the primary key. Not sure how or why but while running on SQL Server 2000, a listbox using a query of employees with no order by clause sorted by last name. Now, in 2008, the same listbox sorts by ID (pk). I was hoping I might be able to correct the sort in the back end without replacing everyone's front end.

At this point, I have left the file in 2000 compatibility mode. The date fields remain datetime and I am able to query the data in Access using date criteria. Everything else seems to be working fine as well so I would like to leave the compatibility alone. Am I jeopardizing the data by not changing the compatibility to 2008?

Thanks,
Sup

 

[tehNellie]

I might be talking out of my backside here, but I believe a clustered index will effectively give a default sort order. If the clustered index was on the lastname field previously but is now on the Primary Key instead, that might explain it.

I wouldn't necessarily recommend messing around with the indexes over fixing the query though.

 

[Galaxiom]

Yes. The clustered index defines the structure of the records and the PK does not have to be the clustered index.

I would still put an Order By on all queries.

 

[SQL_Hell]

In terms of the compatability level, I would change it so you can use 2008 level functionality, for example intellisense doesn't work unless the database is in compatability mode 100.

But make a backup first of course, just in case.

Curious about your default sort order which probably was because of a clustered index on lastname, but I am baffled as to why this would change as a result of restoring the database to a 2008 server. You could always change the pimary key index to be non clustered and put a clustered index on lastname

 

[supmktg]

Hi SQL,

This is an older application using Access 2000 or 2003 as the front end. It was designed using an SQL Server 2000 database, so it is not using any of the new data types or other features of 2008. I don't use MS Enterprise Manager to maintain it. The database is hosted by a 3rd party webhost and they do what little maintainence is required. I seldom make changes to the database and when I do they are simple changes, so intellisense isn't important to me.

I've been using the database in 2000 compatibility mode for a few weeks and everything seems fine. My concern is the integrity of the data. If the compatibility difference does not effect the data integrity, I'd just as soon leave it the way it is so I don't have to deal with changing data types and reworking queries and other code that might break if I changed the compatibility to 2008.

What's your take on data integrity if I leave the compatibility alone?

Thanks,
Sup

 

[SQL_Hell]

It will be fine, set up a periodic checkdb script if you are concerned about data integrity

 

[supmktg]

I appreciate the input!

Thank you very much,
Sup