Verbessern Sie die SQL Server-Abfrageleistung für große Tabellen
Ich habe eine relativ große Tabelle (derzeit 2 Millionen Datensätze) und möchte wissen, ob es möglich ist, die Leistung für Ad-hoc-Abfragen zu verbessern. Das WortAd hoc hier der Schlüssel zu sein. Das Hinzufügen von Indizes ist keine Option (es gibt bereits Indizes für die Spalten, die am häufigsten abgefragt werden).
Ausführen einer einfachen Abfrage, um die 100 zuletzt aktualisierten Datensätze zurückzugeben:
<code>select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc </code>
Dauert mehrere Minuten. Siehe Ausführungsplan unten:
Zusätzliches Detail aus dem Tabellenscan:
<code>SQL Server Execution Times: CPU time = 3945 ms, elapsed time = 148524 ms. </code>
Der Server ist ziemlich leistungsfähig (aus Speicher 48 GB RAM, 24-Kern-Prozessor), auf dem SQL Server 2008 R2 x64 ausgeführt wird.
Aktualisieren
Ich habe diesen Code gefunden, um eine Tabelle mit 1.000.000 Datensätzen zu erstellen. Ich dachte ich könnte dann rennenSELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC
auf ein paar verschiedenen Servern, um herauszufinden, ob meine Datenträgerzugriffsgeschwindigkeiten auf dem Server schlecht waren.
<code>WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1), t2(N) AS (SELECT 1 FROM t1 x, t1 y), t3(N) AS (SELECT 1 FROM t2 x, t2 y), Tally(N) AS (SELECT TOP 98 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y), Tally2(N) AS (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y), Combinations(N) AS (SELECT DISTINCT LTRIM(RTRIM(RTRIM(SUBSTRING(poss,a.N,2)) + SUBSTRING(vowels,b.N,1))) FROM Tally a CROSS JOIN Tally2 b CROSS APPLY (SELECT 'B C D F G H J K L M N P R S T V W Z SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW') d(poss) CROSS APPLY (SELECT 'AEIOU') e(vowels)) SELECT IDENTITY(INT,1,1) AS ID, a.N + b.N AS N INTO #testNames FROM Combinations a CROSS JOIN Combinations b; SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName INTO #testNames2 FROM (SELECT firstName, secondName FROM (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows N AS firstName FROM #testNames ORDER BY NEWID()) a CROSS JOIN (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows N AS secondName FROM #testNames ORDER BY NEWID()) b) innerQ; SELECT firstName, secondName, firstName + '.' + secondName + '@fake.com' AS eMail, CAST((ABS(CHECKSUM(NEWID())) % 250) + 1 AS VARCHAR(3)) + ' ' AS mailAddress, (ABS(CHECKSUM(NEWID())) % 152100) + 1 AS jID, IDENTITY(INT,1,1) AS ID INTO #testNames3 FROM #testNames2 SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName, eMail, mailAddress + b.N + b.N AS mailAddress INTO testEnvironment FROM #testNames3 a INNER JOIN #testNames b ON a.jID = b.ID; --CLEAN UP USELESS TABLES DROP TABLE #testNames; DROP TABLE #testNames2; DROP TABLE #testNames3; </code>
Auf den drei Testservern lief die Abfrage jedoch fast augenblicklich. Kann mir jemand das erklären?
Update 2
Vielen Dank für die Kommentare. Bitte halten Sie sie auf dem Laufenden. Sie haben mich veranlasst, den Primärschlüsselindex von nicht geclustert auf geclustert zu ändern. Dies führt zu interessanten (und unerwarteten?) Ergebnissen.
Nicht gruppiert:
<code>SQL Server Execution Times: CPU time = 3634 ms, elapsed time = 154179 ms. </code>
Gruppiert:
<code>SQL Server Execution Times: CPU time = 2650 ms, elapsed time = 52177 ms. </code>
Wie ist das möglich? Wie kann ein Clustered-Index-Scan ohne einen Index in der Spalte er101_upd_date_iso verwendet werden?
Update 3
Wie gewünscht - dies ist das Skript zum Erstellen einer Tabelle:
<code>CREATE TABLE [dbo].[ER101_ACCT_ORDER_DTL]( [ER101_ORG_CODE] [varchar](2) NOT NULL, [ER101_ORD_NBR] [int] NOT NULL, [ER101_ORD_LINE] [int] NOT NULL, [ER101_EVT_ID] [int] NULL, [ER101_FUNC_ID] [int] NULL, [ER101_STATUS_CDE] [varchar](2) NULL, [ER101_SETUP_ID] [varchar](8) NULL, [ER101_DEPT] [varchar](6) NULL, [ER101_ORD_TYPE] [varchar](2) NULL, [ER101_STATUS] [char](1) NULL, [ER101_PRT_STS] [char](1) NULL, [ER101_STS_AT_PRT] [char](1) NULL, [ER101_CHG_COMMENT] [varchar](255) NULL, [ER101_ENT_DATE_ISO] [datetime] NULL, [ER101_ENT_USER_ID] [varchar](10) NULL, [ER101_UPD_DATE_ISO] [datetime] NULL, [ER101_UPD_USER_ID] [varchar](10) NULL, [ER101_LIN_NBR] [int] NULL, [ER101_PHASE] [char](1) NULL, [ER101_RES_CLASS] [char](1) NULL, [ER101_NEW_RES_TYPE] [varchar](6) NULL, [ER101_RES_CODE] [varchar](12) NULL, [ER101_RES_QTY] [numeric](11, 2) NULL, [ER101_UNIT_CHRG] [numeric](13, 4) NULL, [ER101_UNIT_COST] [numeric](13, 4) NULL, [ER101_EXT_COST] [numeric](11, 2) NULL, [ER101_EXT_CHRG] [numeric](11, 2) NULL, [ER101_UOM] [varchar](3) NULL, [ER101_MIN_CHRG] [numeric](11, 2) NULL, [ER101_PER_UOM] [varchar](3) NULL, [ER101_MAX_CHRG] [numeric](11, 2) NULL, [ER101_BILLABLE] [char](1) NULL, [ER101_OVERRIDE_FLAG] [char](1) NULL, [ER101_RES_TEXT_YN] [char](1) NULL, [ER101_DB_CR_FLAG] [char](1) NULL, [ER101_INTERNAL] [char](1) NULL, [ER101_REF_FIELD] [varchar](255) NULL, [ER101_SERIAL_NBR] [varchar](50) NULL, [ER101_RES_PER_UNITS] [int] NULL, [ER101_SETUP_BILLABLE] [char](1) NULL, [ER101_START_DATE_ISO] [datetime] NULL, [ER101_END_DATE_ISO] [datetime] NULL, [ER101_START_TIME_ISO] [datetime] NULL, [ER101_END_TIME_ISO] [datetime] NULL, [ER101_COMPL_STS] [char](1) NULL, [ER101_CANCEL_DATE_ISO] [datetime] NULL, [ER101_BLOCK_CODE] [varchar](6) NULL, [ER101_PROP_CODE] [varchar](8) NULL, [ER101_RM_TYPE] [varchar](12) NULL, [ER101_WO_COMPL_DATE] [datetime] NULL, [ER101_WO_BATCH_ID] [varchar](10) NULL, [ER101_WO_SCHED_DATE_ISO] [datetime] NULL, [ER101_GL_REF_TRANS] [char](1) NULL, [ER101_GL_COS_TRANS] [char](1) NULL, [ER101_INVOICE_NBR] [int] NULL, [ER101_RES_CLOSED] [char](1) NULL, [ER101_LEAD_DAYS] [int] NULL, [ER101_LEAD_HHMM] [int] NULL, [ER101_STRIKE_DAYS] [int] NULL, [ER101_STRIKE_HHMM] [int] NULL, [ER101_LEAD_FLAG] [char](1) NULL, [ER101_STRIKE_FLAG] [char](1) NULL, [ER101_RANGE_FLAG] [char](1) NULL, [ER101_REQ_LEAD_STDATE] [datetime] NULL, [ER101_REQ_LEAD_ENDATE] [datetime] NULL, [ER101_REQ_STRK_STDATE] [datetime] NULL, [ER101_REQ_STRK_ENDATE] [datetime] NULL, [ER101_LEAD_STDATE] [datetime] NULL, [ER101_LEAD_ENDATE] [datetime] NULL, [ER101_STRK_STDATE] [datetime] NULL, [ER101_STRK_ENDATE] [datetime] NULL, [ER101_DEL_MARK] [char](1) NULL, [ER101_USER_FLD1_02X] [varchar](2) NULL, [ER101_USER_FLD1_04X] [varchar](4) NULL, [ER101_USER_FLD1_06X] [varchar](6) NULL, [ER101_USER_NBR_060P] [int] NULL, [ER101_USER_NBR_092P] [numeric](9, 2) NULL, [ER101_PR_LIST_DTL] [numeric](11, 2) NULL, [ER101_EXT_ACCT_CODE] [varchar](8) NULL, [ER101_AO_STS_1] [char](1) NULL, [ER101_PLAN_PHASE] [char](1) NULL, [ER101_PLAN_SEQ] [int] NULL, [ER101_ACT_PHASE] [char](1) NULL, [ER101_ACT_SEQ] [int] NULL, [ER101_REV_PHASE] [char](1) NULL, [ER101_REV_SEQ] [int] NULL, [ER101_FORE_PHASE] [char](1) NULL, [ER101_FORE_SEQ] [int] NULL, [ER101_EXTRA1_PHASE] [char](1) NULL, [ER101_EXTRA1_SEQ] [int] NULL, [ER101_EXTRA2_PHASE] [char](1) NULL, [ER101_EXTRA2_SEQ] [int] NULL, [ER101_SETUP_MSTR_SEQ] [int] NULL, [ER101_SETUP_ALTERED] [char](1) NULL, [ER101_RES_LOCKED] [char](1) NULL, [ER101_PRICE_LIST] [varchar](10) NULL, [ER101_SO_SEARCH] [varchar](9) NULL, [ER101_SSB_NBR] [int] NULL, [ER101_MIN_QTY] [numeric](11, 2) NULL, [ER101_MAX_QTY] [numeric](11, 2) NULL, [ER101_START_SIGN] [char](1) NULL, [ER101_END_SIGN] [char](1) NULL, [ER101_START_DAYS] [int] NULL, [ER101_END_DAYS] [int] NULL, [ER101_TEMPLATE] [char](1) NULL, [ER101_TIME_OFFSET] [char](1) NULL, [ER101_ASSIGN_CODE] [varchar](10) NULL, [ER101_FC_UNIT_CHRG] [numeric](13, 4) NULL, [ER101_FC_EXT_CHRG] [numeric](11, 2) NULL, [ER101_CURRENCY] [varchar](3) NULL, [ER101_FC_RATE] [numeric](12, 5) NULL, [ER101_FC_DATE] [datetime] NULL, [ER101_FC_MIN_CHRG] [numeric](11, 2) NULL, [ER101_FC_MAX_CHRG] [numeric](11, 2) NULL, [ER101_FC_FOREIGN] [numeric](12, 5) NULL, [ER101_STAT_ORD_NBR] [int] NULL, [ER101_STAT_ORD_LINE] [int] NULL, [ER101_DESC] [varchar](255) NULL ) ON [PRIMARY] SET ANSI_PADDING OFF ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRT_SEQ_1] [varchar](12) NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRT_SEQ_2] [varchar](120) NULL SET ANSI_PADDING OFF ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_BASIS] [char](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_RES_CATEGORY] [char](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DECIMALS] [char](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_SEQ] [varchar](7) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MANUAL] [char](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_LC_RATE] [numeric](12, 5) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_FC_RATE] [numeric](12, 5) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_PL_RATE] [numeric](12, 5) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_DIFF] [char](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_UNIT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_EXT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_MIN_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TR_MAX_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_UNIT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_EXT_CHRG] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_MIN_CHRG] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_MAX_CHRG] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_RATE_TYPE] [char](1) NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORDER_FORM] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FACTOR] [int] NULL SET ANSI_PADDING OFF ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MGMT_RPT_CODE] [varchar](6) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROUND_CHRG] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_WHOLE_QTY] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_QTY] [numeric](15, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_UNITS] [numeric](15, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_ROUNDING] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SET_SUB] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TIME_QTY] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_GL_DISTR_PCT] [numeric](7, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_SEQ] [int] NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC] [varchar](255) NULL SET ANSI_PADDING OFF ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_ACCT] [varchar](8) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DAILY] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_AVG_UNIT_CHRG] [varchar](1) NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC2] [varchar](255) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CONTRACT_SEQ] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORIG_RATE] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DISC_PCT] [decimal](17, 10) NULL SET ANSI_PADDING OFF ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DTL_EXIST] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ORDERED_ONLY] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_STDATE] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_STTIME] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_ENDATE] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_ENTIME] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_RATE] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_UNITS] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_BASE_RATE] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_COMMIT_QTY] [numeric](11, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_QTY_USED] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_CHRG_USED] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_TEXT_1] [varchar](50) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_1] [numeric](13, 3) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_2] [numeric](13, 3) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_3] [numeric](13, 3) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_BASE_RATE] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REV_DIST] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_COVER] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_RATE_TYPE] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_USE_SEASONAL] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAX_EI] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TAXES] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FC_TAXES] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PL_TAXES] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_FC_QTY] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_LEAD_HRS] [numeric](6, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_STRIKE_HRS] [numeric](6, 2) NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CANCEL_USER_ID] [varchar](10) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ST_OFFSET_HRS] [numeric](7, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_EN_OFFSET_HRS] [numeric](7, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_FLAG] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_PL] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_TR] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MEMO_EXT_CHRG_FC] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TIME_QTY_EDIT] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SURCHARGE_PCT] [decimal](17, 10) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_INCL_EXT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_INCL_EXT_CHRG_FC] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CARRIER] [varchar](6) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_ID2] [varchar](8) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHIPPABLE] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_CHARGEABLE] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_ALLOW] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_START] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_NBR_END] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_SUPPLIER] [varchar](8) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_TRACK_ID] [varchar](40) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REF_INV_NBR] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_NEW_ITEM_STS] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MSTR_REG_ACCT_CODE] [varchar](8) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC3] [varchar](255) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC4] [varchar](255) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ALT_DESC5] [varchar](255) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_ROLLUP] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MM_COST_USED] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_AUTO_SHIP_RCD] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_FIXED] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ITEM_EST_TBD] [varchar](3) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROLLUP_PL_UNIT_CHRG] [numeric](13, 4) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROLLUP_PL_EXT_CHRG] [numeric](13, 2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_GL_ORD_REV_TRANS] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_DISCOUNT_FLAG] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_RES_TYPE] [varchar](6) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_RES_CODE] [varchar](12) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PERS_SCHED_FLAG] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRINT_STAMP] [datetime] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SHOW_EXT_CHRG] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PRINT_SEQ_NBR] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_PAY_LOCATION] [varchar](3) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_MAX_RM_NIGHTS] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_USE_TIER_COST] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_UNITS_SCHEME_CODE] [varchar](6) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_ROUND_TIME] [varchar](2) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_LEVEL] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_SETUP_PARENT_ORD_LINE] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_BADGE_PRT_STS] [varchar](1) NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_EVT_PROMO_SEQ] [int] NULL ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD [ER101_REG_TYPE] [varchar](12) NULL /****** Object: Index [PK__ER101_ACCT_ORDER] Script Date: 04/15/2012 20:24:37 ******/ ALTER TABLE [dbo].[ER101_ACCT_ORDER_DTL] ADD CONSTRAINT [PK__ER101_ACCT_ORDER] PRIMARY KEY CLUSTERED ( [ER101_ORD_NBR] ASC, [ER101_ORD_LINE] ASC, [ER101_ORG_CODE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY] </code>
Die Tabelle hat eine Größe von 2,8 GB und eine Indexgröße von 3,9 GB.