September 20, 2010 at 6:25 am
Jeff Moden (9/20/2010)
steve-893342 (9/20/2010)
Jeff Moden (9/19/2010)
Yowch... I just tried XML-3 in a 1000 row by 1000 element test rig... not good. I stopped the run after two hours. Needless to say, I probably won't include the XML-3 test in any of the big tests I'm running.Only 1000 X 1000, you were lucky.;-) I ran it on 10,000 Row X 1,000 Element and let it go to completion :crazy: although I wouldn't recommend it. Full results available on request.
Hell yeah! That would indeed be an interesting data point to have, Steve. Thanks. 🙂
10,000 Row X 1,000 Element monster split enclosed:-)
September 20, 2010 at 6:30 am
rob.symonds (9/20/2010)
Anyone else a little concerned about the 2k8 results for the inline Tally?
It's not the Tally table in 2k8. We'd have seen a similar problem in all the other testing if it was. The problem has to be what it's joining to at the character level because the Execution Plans indicate a cartesian join is forming (121 million rows).
Also, thanks for the test results but they seem to be cutoff, Rob. Can you try again? Also, it would be a huge help if some Carriage Returns were in the output. Pasting the output into Word (97-2003 format please) would be very helpful. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:32 am
HowardW (9/20/2010)
There you go (Same SS2k8 SP1 Quad-Core as previous post):
--===== Split8KTally mlTVF ===========================================================================================SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 6, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2000, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#619B8048'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 99 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Where's the rest of it for your machine? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:36 am
Paul White NZ (9/20/2010)
Jeff Moden (9/19/2010)
Would some of you other good folks run this code and post the results please? We need both 2k5 and 2k8 runs for anyone willing to participate Again, the code is all there including a fresh copy of the Tally table because this all runs in TempDB. And, again, many thanks to all of you.I can confirm Brad's results on my (2K8) instance:
116868,36286,6828,39319,1631,112932,6602,31247,35366,23110,49229,61040,15453,11,30952,62765,31788,33386,89922,31867,88301,48823,115051,14778,99044,60315,11354,82368,9999,90142,43485,63035,26761,4175,8875,97662,66726,111437,116108,117068,34159,104802,52725,78269,50186,113969,101296,5510,31866,6301,79689,11948,29538,109308,88559,63525,17836,81417,85874,64395,6924,109219,74067,56730,33098,27945,17577,97142,89434,84901,13325,106137,69298,31017,118087,26674,49383,90955,36489,43383,967,82859,86301,72167,110468,96183,75115,109963,22454,44367,101399,53877,6992,530,108095,64564,4131,7552,62910,53055,106011,117404,93043,117744,22769,97248,112950,102191,32283,21162,27206,85992,24090,46291,85908,20197,16553,50164,29397,17950,117210,108062,28142,39041,9394,91680,82186,32553,6877,7348,96899,115409,37314,73066,11301,59225,97553,43243,52854,97396,91959,30173,106913,64559,34539,33606,81802,113011,95517,10096,42727,11807,58880,58279,92303,114233,55349,109259,21641,83093,48403,108134,103145,17485,79841,57895,116646,8623,73579,94961,76796,40617,26389,29283,45379,5227,46760,37955,6661,88068,28341,120690,1723,7359,27484,13692,7686,113735,46920,84059,80302,109258,90028,22357,33108,20316,45692,67891,119512,30967,3409,75197,46206,34274,119420,116339,8911,67821,113361,13904,93105,90630,121304,16061,71898,110182,106055,112890,11316,30040,90307,56510,58490,60350,26055,14496,103356,114870,109356,41157,67961,69721,29913,597,4935,76974,61009,46567,47225,67458,51971,62708,38352,73603,112922,16239,57123,28262,149,44055,77328,35016,62544,13943,98090,114473,106931,85173,77655,27760,90252,11994,6664,97770,51688,110641,73262,106133,50095,81159,54193,22370,8044,95430,81804,75110,15296,28442,4310,50648,34395,92855,25447,45073,100992,99148,70300,76953,78491,78175,114912,40346,59298,14566,41845,70835,17213,73604,104548,110532,41949,74373,31820,61193,97247,67062,97069,6470,17654,74938,65525,100358,44925,61335,89994,67649,84234,47144,31952,51677,32968,55366,100492,105432,30734,40338,87346,73936,78591,106565,86566,56435,56292,81617,102646,16175,87031,102356,22815,96894,56418,2313,80763,55418,25922,120219,105668,100293,28684,53450,95831,68306,101783,22175,56035,109637,77101,47663,75826,42068,49183,8558,51851,20565,98999,43704,34628,262,91207,52337,101929,32854,23957,79201,34213,105805,47583,86790,59975,46405,2857,117338,24900,13209,26238,110579,58273,92648,4270,66477,18767,75628,91598,110142,39424,60934,44015,85703,66287,52681,72815,97439,43820,28560,15638,63960,62623,23868,106043,44511,99742,5763,95156,120720,82988,91449,6280,1876,18683,56177,66010,54691,72752,28169,54147,9091,63190,49487,89205,89645,72738,37613,3765,56555,7151,5145,117868,7526,32441,4167,106705,104611,49248,118974,50820,87196,11816,87258,15509,30985,117409,77449,3822,94639,53315,60213,61444,4358,44153,86324,9851,61986,66238,28185,56266,31321,55449,117193,21602,108318,16388,29073,77194,43345,10195,30391,67141,24162,59370,1153,110922,17229,62194,4897,100993,31050,50841,99353,60509,25440,47526,11964,6316,19784,92427,9416,85434,74928,50378,4170,21480,18015,81031,117775,63436,72010,13159,61745,50800,119,907,84603,58981,11439,108614,93111,110231,119407,76749,27836,83289,59816,47788,820,104562,54277,70036,43220,4063,5913,8885,26814,23655,60907,41037,48099,33580,84381,43369,102899,88829,98047,61431,78838,56544,5311,23829,86715,106783,93653,50060,68176,62649,94667,22741,35822,114019,40813,51919,68584,52826,36326,30615,49272,48851,65228,94205,17858,43838,86467,780,30801,82922,12987,102707,21367,69694,33601,16280,8052,38227,16023,115023,31130,109013,116860,55992,45890,42843,98135,21523,52374,114666,94098,54007,110640,17639,121215,63141,23459,107320,34076,49460,35839,84452,31049,25476,88485,42206,67880,50458,89039,29751,63380,37157,46880,38621,20438,40489,24914,46383,17978,5504,97205,42774,120082,109066,80865,59775,48572,24096,52919,53896,14022,58363,31988,15994,42446,19438,57242,98264,39636,28858,32840,2993,44579,44661,98165,7823,116984,102332,102864,104670,46499,64556,49754,25323,102951,37353,7715,114083,14521,70813,11793,23677,80956,89613,117701,93968,43856,120074,10854,91769,76873,90719,117815,86923,72929,81150,50126,49165,16681,68530,94774,10184,10773,65786,74727,77294,64108,31509,26919,73637,12751,22543,11980,9924,80521,9317,36451,80704,75221,26198,30179,59029,29388,46194,16824,112158,86541,98290,59597,20223,39139,105011,106278,108402,119696,71827,35322,120875,28359,59956,28211,31223,54073,79057,74890,81445,94281,105407,17367,55506,108659,89465,48119,101730,35288,75114,16989,93162,110737,17784,71701,113783,19031,106481,76916,63828,58482,120671,68529,42061,93590,12941,29726,20899,44057,5098,81548,55429,69632,50895,22008,64293,104332,69591,2449,65010,105149,31714,119647,25920,63230,63203,42131,39919,38702,104765,58265,111951,71262,79395,106087,65858,19509,112665,1113,83387,90285,67611,105233,57813,105753,13845,70008,4733,69182,21832,69827,49323,86536,29157,50147,50678,89001,35848,65968,21796,114463,76751,87048,86836,70737,99905,97110,8999,91251,74699,48471,109884,115156,5160,90609,105565,70966,31573,72217,61332,104893,92708,13241,105119,51962,5223,86134,44927,59308,93008,59488,91521,52609,49287,56254,4252,43155,104144,46709,29724,58834,110661,29483,116149,96220,39374,79072,75098,12542,84560,115826,79596,78008,96057,1131,109927,104250,46059,119919,67913,22661,93042,99615,96718,66501,98544,81924,65469,119134,70111,20209,41195,101468,100117,113920,27117,11681,55567,103539,116495,23109,96085,62080,32780,62117,59213,86802,38757,24830,36306,80308,111253,3135,25627,87439,97290,99820,104871,114859,16714,92873,100377,44458,53175,6375,8722,34376,106379,30560,57858,32566,110506,118106,91183,72850,59028,40869,89319,58532,107963,106871,116016,114431,116158,82223,55726,118200,46533,44945,54554,102579,977,74719,70798,80771,73251,44971,104080,83372,53743,17742,59498,6085,82762,52891,53732,29958,52987,115513,80745,117810,8145,68888,99266,113003,107652,94960,33278,70113,73173,101716,16482,46937,60671,56891,72371,31326,44741,2520,91267,31537,112762,64784,36618,25910,119010,64041,26818,41392,35125,112966,115545,58526,86033,26310,76065,89120,12877,49792,14191,10207
--===== Split8KTally ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 38 ms.
Table 'Worktable'. Scan count 1, logical reads 487274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 228, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 868172 ms, elapsed time = 959781 ms.
--===== Split8KXMLBrad ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1000, logical reads 2147, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 174 ms.
--===== Split8KL1 ===========================================================================================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 6, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#07C12930'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 627 ms.
Paul
Ah... perfect, Paul. It looks like the Tally Table join became a Cartesian Join of sorts. I haven't loaded up 2k8, yet. Would you generate the CREATE TABLE statement for the SalesOrderDetail table including indexes and collation, please? I've seen this particular problem before and would like to confirm. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:38 am
Jeff Moden (9/20/2010)
HowardW (9/20/2010)
There you go (Same SS2k8 SP1 Quad-Core as previous post):
--===== Split8KTally mlTVF ===========================================================================================SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 6, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 0, logical reads 2000, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#619B8048'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 99 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Where's the rest of it for your machine? 😉
Ah... never mind. I found it. Thanks, Howard.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:38 am
Where's the rest of it for your machine?
In my previous post... 🙂
September 20, 2010 at 6:39 am
Results using the Brad Schulz (as posted by Jeff)
Again SQL 2005 and SQL 2008 on the larger faster machine, and SQL2005 Express on the smaller slower box.
September 20, 2010 at 6:42 am
Hi Jeff
I'm unable to run the second script as the servers I can use here do not have AdventureWorks installed. I will run the second one from my home machine though, where I believe I do have AW
September 20, 2010 at 6:45 am
mazzz (9/20/2010)
Hi JeffI'm unable to run the second script as the servers I can use here do not have AdventureWorks installed. I will run the second one from my home machine though, where I believe I do have AW
Understood. It's a common "problem" on work servers. Thank you for your efforts.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:50 am
bitbucket-25253 (9/20/2010)
Results using the Brad Schulz (as posted by Jeff)Again SQL 2005 and SQL 2008 on the larger faster machine, and SQL2005 Express on the smaller slower box.
Whoa! That 2k8 Box is a monster! Thanks, Ron. It also shows the problem is limited to when it's used on the Adventure works in 2k8. There's something about the SalesOrderDetail table and I'm thinking it just a datatype mismatch on the ProductID (according to what I see in the code).
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 6:55 am
Ok folks... I'm pretty sure we've collectively narrowed down the problem. For those of you who have 2k8, especially Ron, Paul, and Howard (because you've all submitted runs that confirm Brad's problem using my second script which I absolutely trust), would you generate the script for the creation of the AdventureWorks.Sales.SalesOrderDetail including all indexes and collations, please? I believe we may have a datatype mismatch which drives the Tally Table into a full Cartesian join and I'd like to confirm it. I just haven't loaded 2K8 on any of my boxes (yet) because I've not yet needed it for work.
Thanks folks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 7:07 am
Let me know if I've missed any options that you need:
USE [AdventureWorks]
GO
/****** Object: Table [Sales].[SalesOrderDetail] Script Date: 09/20/2010 14:05:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) COLLATE Latin1_General_CI_AS NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [AdventureWorks]
/****** Object: Index [AK_SalesOrderDetail_rowguid] Script Date: 09/20/2010 14:05:48 ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [AdventureWorks]
/****** Object: Index [IX_SalesOrderDetail_ProductID] Script Date: 09/20/2010 14:05:48 ******/
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key. Foreign key to SalesOrderHeader.SalesOrderID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'SalesOrderID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key. One incremental unique number per product sold.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'SalesOrderDetailID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shipment tracking number supplied by the shipper.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'CarrierTrackingNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Quantity ordered per product.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'OrderQty'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product sold to customer. Foreign key to Product.ProductID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'ProductID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Promotional code. Foreign key to SpecialOffer.SpecialOfferID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'SpecialOfferID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Selling price of a single product.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'UnitPrice'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Discount amount.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'UnitPriceDiscount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'LineTotal'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Individual products associated with a specific sales order. See SalesOrderHeader.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique nonclustered index. Used to support replication samples.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'INDEX',@level2name=N'AK_SalesOrderDetail_rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nonclustered index.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'INDEX',@level2name=N'IX_SalesOrderDetail_ProductID'
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing SalesOrderHeader.PurchaseOrderID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID'
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY([SpecialOfferID], [ProductID])
REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing SpecialOfferProduct.SpecialOfferIDProductID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID'
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK (([OrderQty]>(0)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [OrderQty] > (0)' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'CK_SalesOrderDetail_OrderQty'
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK (([UnitPrice]>=(0.00)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [UnitPrice] >= (0.00)' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'CK_SalesOrderDetail_UnitPrice'
GO
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK (([UnitPriceDiscount]>=(0.00)))
GO
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [UnitPriceDiscount] >= (0.00)' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'CK_SalesOrderDetail_UnitPriceDiscount'
GO
ALTER TABLE [Sales].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)) FOR [UnitPriceDiscount]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 0.0' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'DF_SalesOrderDetail_UnitPriceDiscount'
GO
ALTER TABLE [Sales].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()) FOR [rowguid]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of NEWID()' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'DF_SalesOrderDetail_rowguid'
GO
ALTER TABLE [Sales].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'SalesOrderDetail', @level2type=N'CONSTRAINT',@level2name=N'DF_SalesOrderDetail_ModifiedDate'
GO
September 20, 2010 at 7:10 am
HowardW (9/20/2010)
Where's the rest of it for your machine?
In my previous post... 🙂
heh... yeah... I saw that right after I posted. Thanks Howard. Interesting on how the mlTVF gets around the datatype mismatch that I suspect. Thanks for posting it.
Would you post the script that would create AdventureWorks.Sales.SalesOrderDetail including indexes and collations, please? I'm pretty sure that the problem is an implicit datatype causing the Tally Table to do a Cartesian join and I don't have 2K8 loaded on my boxes, yet. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 7:11 am
Jeff Moden (9/20/2010)
HowardW (9/20/2010)
Where's the rest of it for your machine?
In my previous post... 🙂
heh... yeah... I saw that right after I posted. Thanks Howard. Interesting on how the mlTVF gets around the datatype mismatch that I suspect. Thanks for posting it.
Would you post the script that would create AdventureWorks.Sales.SalesOrderDetail including indexes and collations, please? I'm pretty sure that the problem is an implicit datatype causing the Tally Table to do a Cartesian join and I don't have 2K8 loaded on my boxes, yet. Thanks.
Heh... I just can't type fast enough. Thanks for posting the CREATE TABLE. I'm looking at it now.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2010 at 7:13 am
HowardW (9/20/2010)
Let me know if I've missed any options that you need:
Howard, can you confirm that's from a 2K8 Version of Adventure works? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 91 through 105 (of 214 total)
You must be logged in to reply to this topic. Login to reply