Popraw wydajność zapytań SQL Server na dużych tabelach
Mam stosunkowo dużą tabelę (obecnie 2 miliony rekordów) i chciałbym wiedzieć, czy można poprawić wydajność zapytań ad-hoc. Słowoad-hoc kluczem tutaj. Dodawanie indeksów nie jest opcją (w kolumnach są już najczęściej indeksowane).
Uruchomienie prostego zapytania w celu zwrócenia 100 ostatnio zaktualizowanych rekordów:
<code>select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc </code>
Zajmuje kilka minut. Zobacz plan wykonania poniżej:
Dodatkowe szczegóły ze skanowania tabeli:
<code>SQL Server Execution Times: CPU time = 3945 ms, elapsed time = 148524 ms. </code>
Serwer jest bardzo wydajny (z pamięci RAM 48 GB, 24-rdzeniowy procesor) z uruchomionym serwerem SQL 2008 r2 x64.
Aktualizacja
Znalazłem ten kod, aby utworzyć tabelę z 1 000 000 rekordów. Myślałem, że mogę wtedy uciekaćSELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC
na kilku różnych serwerach, aby sprawdzić, czy szybkość mojego dysku była niska na serwerze.
<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>
Ale na trzech serwerach testowych zapytanie działało niemal natychmiast. Czy ktoś może to wyjaśnić?
Aktualizacja 2
Dziękuję za komentarze - proszę je kontynuować ... doprowadziły mnie do próby zmiany indeksu klucza podstawowego z nieklastrowanego na klastrowany z dość interesującymi (i nieoczekiwanymi?) Wynikami.
Nieklastrowane:
<code>SQL Server Execution Times: CPU time = 3634 ms, elapsed time = 154179 ms. </code>
Klastrowany:
<code>SQL Server Execution Times: CPU time = 2650 ms, elapsed time = 52177 ms. </code>
Jak to jest możliwe? Bez indeksu w kolumnie er101_upd_date_iso w jaki sposób można użyć skanowania indeksu klastrowego?
Aktualizacja 3
Na żądanie - jest to skrypt tworzenia tabeli:
<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>
Tabela ma rozmiar 2,8 GB, a rozmiar indeksu wynosi 3,9 GB.