Sample Header Ad - 728x90
in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] NOT NULL, [skillDetails] [varchar](500) NULL, [skillLevelID] [tinyint] SPARSE NULL, [dateAdded] [datetime2](7) NOT NULL, [lastModified] [datetime2](7) NOT NULL, CONSTRAINT [PK_tbl_applicant_skill] PRIMARY KEY CLUSTERED ( [ApplicantSkillID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG], CONSTRAINT [uc_appSkillID] UNIQUE NONCLUSTERED ( [applicantID] ASC, [skillID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG] ) ON [UserFG] GO which has 1 sparse column - skillLevelID when I have to re-create my index: CREATE NONCLUSTERED INDEX I_applicantID ON [app].[applicantSkill] ( [applicantID] ASC , [dateAdded] ASC ) INCLUDE ( [ApplicantSkillID] , [skillDetails] , [skillID] , [skillLevelID]) WITH ( PAD_INDEX = OFF, FILLFACTOR = 100 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = OFF, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES] I get the following error message: Msg 10622, Level 16, State 1, Line 18 The index 'I_applicantID' could not be created or rebuilt. A compressed index is not supported on table that contains sparse columns or a column set column. How can I find out which one the sparse column or the data_compression should I keep?
Asked by Marcello Miorelli (17274 rep)
Oct 18, 2018, 02:53 PM
Last activity: Oct 18, 2018, 03:05 PM