Sql-server – Compatibility level for SQL Server database

compatibilitysql serversql-server-2005sql-server-2008

My database hoster allows to restore SQL Server databases with a compatibility level of 90 (SQL Server 2005). My database is created locally with a compatibility level of 100 (SQL Server 2008).

So, I generated script of my database (version 2008) and run in SQL Server 2005, backup and restore to my database hoster, it works. Currently I do likes it.

And then I found ALTER DATABASE that can change compatibility level of database likes

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }

 90 = SQL Server 2005
 100 = SQL Server 2008 and SQL Server 2008 R2
 110 = SQL Server 2012

I turn my database compatibility level using this script in SQL Server 2008, backup my database and restore to my database hoster. But it doesn't work. I want to know why? Is there a better way to fix it ?

Best Answer

You can NEVER restore a database from a newer version of SQL Server (like your 2008 version) onto an older SQL Server (2005) instance.

No matter what you try, no trick, no hack, no workaround - it just cannot be done.

The compatibility level doesn't make any difference either. It just makes your newer database (like on 2008) behave as if it were an older one (like 2005) - so the features you can use are limited to what the older database supported.

But internally - it's still a 2008 database and it cannot be restored to a 2005 instance.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql-server – How to check if a column exists in a SQL Server table

Related Topic