× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
JennyNishimura reported 2017-03-13T20:24:18Z  · tariqrahiman last modified 2017-03-14T16:42:18Z

Scripting indexes issue in SQL Server for partitioned tables using include


customer request
Dev
Jenny Nishimura
JennyNishimura
QA
Tariq Rahiman
tariqrahiman
Priority Major
Complexity Unknown
Component DB - MS SQL Server
Version 18.0

Run the following script in SQL Server.

IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'TEST_PARTFUNC')
   CREATE PARTITION FUNCTION [TEST_PARTFUNC] (varchar(20)) AS RANGE LEFT FOR VALUES (N'ABCDE',N'ASDSAD',N'FGHIJ',N'HFSER',N'POSAW',N'RUZDAW')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'TEST_PARTSCHEME_INDEX')
   CREATE PARTITION SCHEME [TEST_PARTSCHEME_INDEX] AS PARTITION [TEST_PARTFUNC] TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])
GO
CREATE TABLE dbo.TEST_TABLE
(
    STAGE_SOURCE VARCHAR(20)  NOT NULL,
    ID INT NOT NULL,
    [COL1] VARCHAR(20) NOT NULL,
    [COL2] VARCHAR(20) NOT NULL
) 
GO
CREATE NONCLUSTERED INDEX [TEST_INDEX]
   ON [dbo].[TEST_TABLE]([ID], [STAGE_SOURCE])
   INCLUDE ([COL1], [COL2])
   WITH (
      FILLFACTOR = 100,  
      DATA_COMPRESSION = PAGE
   )
   ON [TEST_PARTSCHEME_INDEX] (STAGE_SOURCE)
GO

In schema browser, select the index TEST_INDEX and choose Script Object -> CREATE. The following script is generated.

CREATE NONCLUSTERED INDEX [TEST_INDEX]
   ON [dbo].[TEST_TABLE]([ID], [STAGE_SOURCE])
   INCLUDE ([COL1], [COL1], [COL1], [COL1], [COL1], [COL1], [COL1], [COL2], [COL2], [COL2], [COL2], [COL2], [COL2], [COL2])
GO
1 attachment

Issue #15072

Closed
Fixed
Resolved 2017-03-13T20:37:08Z
 
 
Completion
No due date
Fixed Build ADS 18.0.3-5
No time estimate

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017