A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
66

From the docs: Sets certain database behaviors to be compatible with the specified version of SQL Server. ... Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the ...


29

Seemingly preferred approach I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do indeed work at the DB level, even when connecting via .NET SqlClient. These have been tested and verified by others. Server-wide You can set the user options ...


15

From Developer to Enterprise will be fine, just be sure that if you are using processor licensing you have licenses on the target server to cover all of the CPUs. And it's not enough to just hide them from SQL, if they're physically connected to the machine, you're responsible for them. Also when you go from a lower build to a higher build your database ...


9

Have a look at the following link: ALTER DATABASE Compatibility Level Scroll down and you will see the section "Differences Between Lower Compatibility Levels and Level 110" and identify if any of these items will affect you or not. If not, then just change the level to 110.


9

Compatibility Levels are only present to allow a controlled migration from an earlier version of SQL Server. Compat Level 90 does not preclude using new features, it simply means certain aspects of the database are retained in a way that is compatible with how SQL Server 2005 worked. See http://msdn.microsoft.com/en-us/library/bb510680.aspx for more info.


9

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for ...


7

PIVOT and UNPIVOT are indeed not supported under compatibility level 80. However, you can unpivot rows using a nested VALUES constructor. The resulting query in my case looks slightly unwieldy because of the double nesting, but it works in SQL Server 2008 with any supported compatibility level: SELECT id, median = ( SELECT AVG(val) FROM ...


7

A method that uses CROSS APPLY (so I think it will work in a database with compatibility level 80). This will work correctly only if there are no nulls in these columns: select t.id, z.val from temp as t cross apply ( select top (1) y.val from ( select top (11) x.val from ( values (a),(b),(c),(d),(e),(f),(g),(h),(i)...


6

Option 1 Aside from Sankar's solution, setting the arithmetic abort setting at the server level for all connections will work: EXEC sys.sp_configure N'user options', N'64' GO RECONFIGURE WITH OVERRIDE GO As of SQL 2014 it's recommended to be on for all connections: You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to ...


6

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works: SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date FROM sys.objects AS o ORDER BY o.modify_date; Technically, it should be as follows, since o.modify_date is not in the SELECT list: SELECT CONVERT(SMALLDATETIME, modify_date) AS ...


6

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo. First, create a #temp table to hold results: CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname, physical_name sysname, FileSizeMb ...


6

You're using the correct method, you're just not setting all the right options. Go through the Tasks > Generate scripts wizard again, there are steps in there where you can choose the all object types, and other places where you can choose what properties of those objects to include (permissions, indexes, etc). There's also a 'Script for Server Version' ...


6

It does not introduce past vulnerabilities, and applies mostly to T-SQL syntax and T-SQL features. As the documentation states, Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Later on the ...


6

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server? No, not that I am aware of. This is an issue of datatype precedence with regards to the CASE expression. The CASE expression needs to return a consistent datatype no matter which branch is selected. Since VARCHAR ...


5

All the compatibility level does is tell SQL Server to use the older version of the T-SQL language that was available in that version. For example if within a SQL 2008 R2 server with the database running in SQL 2000 compatibility mode if you try and access some of the new syntax it won't work. For example select * from sys.dm_db_index_physical_stats (...


5

There is also the Upgrade Advisor which will help you identify anything in your DB which might cause problems when changing the compatibility level (basically a semi-automated way of doing what @steoleary has suggested which reduces the chances of you missing anything).


5

Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both? Yes. No. No. The database compatibility level is irrelevant, and you would never set the database compatibility on the distribution database, as it's a system database. A SQL ...


4

Would this cause any impact on application side? Even if you run BPA or Upgrade Advisor, they are not going to catch everything that would potentially break your application (if it is using deprecated stuff). The best way is to take a full backup of your database in question, restore it on a test machine with newer compatiblity mode and do a regression ...


4

How about spinning up a SQL Server 2000 or 2005 server. Then script out the 80 database objects and create them on the earlier version SQL Server. Then run the SQL Server 2008 R2 upgrade advisor to collect the 2000 and 2005 errors that might exist. Once you fixed those issues on 2008 R2, then you can run the 2014 Upgrade Advisor.


4

Microsoft Data Migration Assistant helps you quickly and easily find any issues that may prevent or complicate an upgrade. If it finds no issues (or if it does, and you've addressed them) you can simply upgrade by running these commands, one at a time: USE master go ALTER DATABASE [yourdatabasesname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE go ALTER ...


4

There's several parts to this question: Do I need to set the database to single user mode to change the compatibility level? No. Will changing the compat mode from 90 to 110 break log shipping? No. Can I change a log shipped database to single user mode? Yes, but don't do that unless you have to. Sometimes, you might not end up being the single user (like ...


4

The client library used to connect does not care at all about the compatibility level of the database. The code issued by the client may have some issues (for example, you needed 90 to use APPLY many years ago). But the mechanism used to send the code and get results does not care


4

It's not precisely because there are duplicate entries in the SELECT list, it's because the ORDER BY mentions a column that is listed twice in the SELECT list, and SQL Server doesn't know which one you mean (think about the case where you could say SELECT ID, ID = 1-ID..., where which one you mean is important). The right fix is (in order of my personal ...


3

The results from that query will not be scoped to particular database. They will show activity across the whole instance. In general, it would be quite difficult to provide information at this granularity, because queries can access data from more than one database (Azure SQL Database aside). In non-trivial queries, accounting for costs on a per-database ...


3

I have recently seen this exact same problem with an upgrade from SQL Server 2008 R2 to SQL Server 2014 where a very small number of Stored Procedures (SPs) (approx 5 out of 3,000 ) performed worse in SQL 2014 Compatibility Level 120 than in SQL 2014 Compatibility Level 110 or in SQL 2008. In SQL 2014 the Cardinality Estimator (CE) has changed which may ...


3

Developer and Enterprise are the same software, just with different licensing agreements. You should be fine restoring this database at your destination.


3

Looks like you have an old compatibility collation, which comes from SQL Server 2005 or SQL Server 2000. The format of this compatibility collation name is: Compatibility_SortOrderDecimal_lcidHex_CompStyleHex Explanation: SortOrderDecimal 60 = Case-insensitive Scandinavian dictionary sort order, without case preference lcidHex (Locale id) 0x406 = 1030 (...


3

The best match to your requirement that I can think of is the Query Designer in SSMS. You can open the designer by right-clicking any table in your database and selecting "edit top 200 rows". Then, open the SQL pane by clicking the button in the toolbar: Now you can paste your statement in the SQL pane and use the syntax checker to convert the code: The ...


3

Is there anything else I can do, or is there a step I missed, to revert back to the old Cardinality Estimator? In SQL Server 2014, reverting to OLD CE can be done in 2 ways Setting the database compatibility mode to less than 120. (or) Enabling trace flag 9481 globally or at query level using option querytraceon Now that you have reverted to old CE, your ...


Only top voted, non community-wiki answers of a minimum length are eligible