I've been seeing quite a lot of unnecessary dynamic SQL code lately, that's related to ALTER DATABASE statements. It was part of code that was being scripted.
Generally, the code looks something like this:
|
1 2 3 4 5 6 7 |
DECLARE @SQL nvarchar(max); SET @SQL = N'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 150;'; EXEC (@SQL); |
(I've used setting a db_compat level as an example)
Or if it's slightly more reliable code, it says this:
|
1 2 3 4 5 6 7 |
DECLARE @SQL nvarchar(max); SET @SQL = N'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET COMPATIBILITY_LEVEL = 150;'; EXEC (@SQL); |
If you are going to be concatenating things like database or column names, make sure you use QUOTENAME to avoid issues with object names that aren't valid SQL Server object names when they aren't quoted.
But in so many cases, there's no need to have this code as dynamic at all. The ALTER DATABASE statement can take the word CURRENT to refer to the current database instead. So you could write just:
|
1 |
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150; |
Nice one! I must have missed the CURRENT keyword.
Hi Mitch, yep, many people have.