SQL to skip running Create Partition Function under SQL Server Express

Question

Our database schema is stored in a single file that is intended for production use but we also use when running functional tests on our local boxes. We drop the database and rebuild it from this file and then add a known set of test data. All was working well.

Recently we added some performance improvements. The trouble came when we adding partitioning to one of the tables.

Msg 7736, Level 16, State 1
Server 'SERVERNAME\SQLEXPRESS', Line 4
Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition
of SQL Server supports partitioning.

Undaunted I added an if statement to check to see if we were running under Enterprise before enabling partitioning:

IF CHARINDEX('Enterprise', CONVERT(varchar(1000), SERVERPROPERTY('edition'))) > 0
BEGIN
    CREATE PARTITION FUNCTION [MyPartitionRange] (int)
    AS RANGE LEFT
    FOR VALUES (14200000, 14225000, 14250000, 14300000, 14350000, 14375000, 14400000, 14475000, 14500000, 14525000, 14550000, 14575000, 14600000, 14625000, 14650000, 14675000, 14700000, 14725000, 14750000, 14775000, 14800000, 14825000, 14850000, 14875000, 14900000, 14925000, 14950000, 14975000, 15000000 )
END

Unfortunately this still fails. I think SQL Server is telling us about the issue at the time it parses the CREATE PARTITION FUNCTION so it throws the error even if we don't use it.

Any suggestions?

Answer 1

You may consider this a hack, but you could put the 'create partition...' in a string and then call sp_executesql on it if you are on the right edition.