Sql-Server
只有 SQL Server 企業版支持壓縮 - 如何解決這個問題？
我有一個腳本需要創建一個臨時表，並在該臨時表中創建一個索引。
我注意到在我的一些伺服器中，腳本會一直執行下去，當從不同的會話檢查目前正在執行的內容時，沒有可用的明確消息。
取消查詢後，我收到此消息：
Message: Cannot enable compression for object '#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A923A'. Only SQL Server Enterprise Edition supports compression. Error: 7738 Severity: 16這是生成此錯誤消息的腳本部分：
IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL DROP TABLE #RADHE_sp_getsubscriptions create table #RADHE_sp_getsubscriptions ( publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NOT NULL, replication_type int, subscription_type int, last_updated datetime, subscriberd_db sysname, update_mode int, last_sync_status int, last_sync_summary nvarchar(4000), last_sync_time datetime) CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , SORT_IN_TEMPDB = ON , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , DATA_COMPRESSION=PAGE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]稍後這是我儲存在此表中的內容：
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED TRUNCATE TABLE #RADHE_sp_getsubscriptions insert into #RADHE_sp_getsubscriptions exec sp_MSenumsubscriptions select * from #RADHE_sp_getsubscriptions這提供了這個有價值的資訊：
在檢查了這個關於 sql server version check 的連結和下面的這個連結之後：
我剛剛得到這個腳本來檢查他在目前伺服器中的內容：
SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' --WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' ELSE 'Unknown on April 2019' END AS MajorVersion, SERVERPROPERTY('productversion') as 'Product Version', SERVERPROPERTY('productlevel') as 'Product Level', SERVERPROPERTY('edition') as 'Product Edition', SERVERPROPERTY('buildclrversion') as 'CLR Version', SERVERPROPERTY('collation') as 'Default Collation', SERVERPROPERTY('instancename') as 'Instance', SERVERPROPERTY('lcid') as 'LCID', SERVERPROPERTY('servername') as 'Server Name', SERVERPROPERTY('EditionID') AS 'EditionID'並在創建表之後在下面的創建索引腳本中使用它：
IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL DROP TABLE #RADHE_sp_getsubscriptions create table #RADHE_sp_getsubscriptions ( publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NOT NULL, replication_type int, subscription_type int, last_updated datetime, subscriberd_db sysname, update_mode int, last_sync_status int, last_sync_summary nvarchar(4000), last_sync_time datetime) IF ( SELECT RADHE = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END ) = 1 CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , SORT_IN_TEMPDB = ON , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , DATA_COMPRESSION=PAGE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ELSE CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , SORT_IN_TEMPDB = ON , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , DATA_COMPRESSION=NONE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] --checking the index creation script -- EXEC SP_COUNT 'TEMPDB..#RADHE_sp_getsubscriptions' --Results: -- SERVER 1 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A9250] ( [publisher] ASC , [publisher_db] ASC , [publication] ASC ) WITH ( PAD_INDEX = OFF, FILLFACTOR = 100 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO -- SERVER 2 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________00000001B274] ( [publisher] ASC , [publisher_db] ASC , [publication] ASC ) WITH ( PAD_INDEX = OFF, FILLFACTOR = 100 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO現在一切正常。