table icon Table: TransactionHistory

Properties

Property Value
Name TransactionHistory
ID 318624178
Owner dbo
Schema Production
Filegroup PRIMARY
Data Size (KB) 6304 KB
Index Size (KB) 5144 KB
Rows 113443
Unused (KB) 360 KB
ANSI Null On true
Quoted Identifier true
Lock Escalation TABLE
Lock On Bulk Load false
Lob Data Space
Filestream Data Space ID
Has Unchecked Assembly Data false
Text In Row Limit (bytes) Option not set
Large Value Types Out Of Row false
FullText Change Tracking On false
Tracked By Cdc false
Published false
Schema Published false
Replicated false
Replication Filter false
Merge Published false
Sync Tran Subscribed false
MS Shipped false
Modify Date 10/26/2011 11:51:14 PM
Create Date 8/8/2010 5:21:53 PM

Extended Properties

Name Value
MS_Description Record of each purchase order, sales order, or work order transaction year to date.

Columns

Column ID Column Name Datatype Nullable Is Sparse Is Column Set
1 TransactionID int false false false
2 ProductID int false false false
3 ReferenceOrderID int false false false
4 ReferenceOrderLineID int false false false
5 TransactionDate datetime false false false
6 TransactionType nchar(1) false false false
7 Quantity int false false false
8 ActualCost money false false false
9 ModifiedDate datetime false false false

Identity Columns

Column Seed Value Increment Value Last Value Is Not For Replication Is Computed Is Sparse Is Column Set
TransactionID 100000 1 213442 false false false false

Primary Key Constraints

Name Is System Named Extended Property Name Extended Property Value
PK_TransactionHistory_TransactionID false MS_Description Primary key (clustered) constraint

Primary Key Columns

Name
TransactionID

Foreign Keys

Name Referenced Object Update Action Delete Action
FK_TransactionHistory_Product_ProductID Production.Product No action No action

Column Level Check Constraints

Name Column ID Column Definition Is Disabled Is Not For Replication Is Not Trusted Uses Database Collation Is System Named
CK_TransactionHistory_TransactionType 6 TransactionType (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W') false false false true false

Table Level Check Constraints

No table constraints defined.

Default Constraints

Name Column Definition Is System Named Extended Property Name Extended Property Value
DF_TransactionHistory_ReferenceOrderLineID ReferenceOrderLineID ((0)) false MS_Description Default constraint value of 0
DF_TransactionHistory_TransactionDate TransactionDate (getdate()) false MS_Description Default constraint value of GETDATE()
DF_TransactionHistory_ModifiedDate ModifiedDate (getdate()) false MS_Description Default constraint value of GETDATE()

Indexes

Name Index Type Primary Key Unique Unique Constraint Ignore Duplicate Key Disabled
PK_TransactionHistory_TransactionID CLUSTERED true true false false false
IX_TransactionHistory_ProductID NONCLUSTERED false false false false false
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID NONCLUSTERED false false false false false
IX_TransactionHistory_ReferenceOrderID NONCLUSTERED false false false false false

FullText Indexes

No fulltext indexes defined.

Referencing Tables

No referencing tables defined.

Object Level Permissions

No object level permissions defined.

Column Level Permissions

No explicit column permissions granted to this object

Statistics

Detailed Statistics General

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
PK_TransactionHistory_TransactionID Aug 8 2010 5:23PM 113443 113443 3 1 4 false 113443

Detailed Statistics Density

All Density Average Length Columns
8.815E-06 4 TransactionID

Detailed Statistics Histogram

Range High Key Range Rows Estimated Rows Distinct Range Rows Avg Range Rows
100000 0 1 0 1
213441 113440 1 113440 1
213442 0 1 0 1

Detailed Statistics General

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
IX_TransactionHistory_ProductID Aug 8 2010 5:23PM 113443 113443 200 0.01581469 8 false 113443

Detailed Statistics Density

All Density Average Length Columns
0.002267574 4 ProductID
8.815E-06 8 ProductID, TransactionID

Detailed Statistics Histogram

Range High Key Range Rows Estimated Rows Distinct Range Rows Avg Range Rows
1 0 45 0 1
3 45 307 1 45
316 45 307 1 45
319 144 118 2 72
322 218 108 2 109
324 45 307 1 45
327 90 307 2 45
329 307 307 1 307
331 307 307 1 307
347 357 52 7 51
350 106 307 2 53
355 150 38 2 75
358 182 45 2 91
375 335 25 16 20.9375
381 127 25 5 25.4
398 284 307 16 17.75
400 307 307 1 307
401 0 307 0 1
410 319 20 8 39.875
422 212 28 11 19.27273
437 405 28 14 28.92857
462 435 46 24 18.125
479 213 13 16 13.3125
491 230 91 11 20.90909
496 222 56 4 55.5
506 135 45 3 45
509 184 36 2 92
513 247 90 3 82.33334
514 0 285 0 1
516 0 307 0 1
517 0 301 0 1
518 0 307 0 1
519 0 257 0 1
521 246 200 1 246
522 0 294 0 1
524 91 91 1 91
529 183 307 4 45.75
531 45 307 1 45
533 307 307 1 307
534 0 307 0 1
679 38 74 1 38
707 199 2278 2 99.5
708 0 2192 0 1
711 0 2220 0 1
712 0 2348 0 1
713 0 394 0 1
714 0 645 0 1
715 0 784 0 1
716 0 525 0 1
722 141 288 3 47
736 27 235 5 5.4
738 202 369 1 202
739 0 261 0 1
742 0 313 0 1
743 0 378 0 1
747 206 307 1 206
748 0 386 0 1
779 37 771 7 5.285714
780 0 739 0 1
781 0 751 0 1
782 0 852 0 1
783 0 808 0 1
784 0 746 0 1
792 2 295 1 2
793 0 385 0 1
794 0 399 0 1
795 0 359 0 1
796 0 295 0 1
797 0 509 0 1
798 0 486 0 1
799 0 503 0 1
800 0 418 0 1
801 0 519 0 1
802 0 306 0 1
804 307 307 1 307
806 306 307 1 306
807 0 294 0 1
808 0 423 0 1
810 210 388 1 210
811 0 320 0 1
812 0 307 0 1
813 0 364 0 1
815 0 285 0 1
816 0 307 0 1
818 0 301 0 1
819 0 307 0 1
820 0 257 0 1
821 0 307 0 1
823 283 285 1 283
824 0 309 0 1
827 303 307 1 303
828 0 257 0 1
829 0 307 0 1
834 246 238 2 123
835 0 399 0 1
836 0 368 0 1
838 0 163 0 1
858 240 637 8 30
859 0 712 0 1
860 0 505 0 1
864 11 584 3 3.666667
865 0 456 0 1
867 186 566 1 186
868 0 604 0 1
869 0 620 0 1
870 0 4187 0 1
871 0 1832 0 1
872 0 1533 0 1
873 0 3003 0 1
874 0 506 0 1
875 0 514 0 1
876 0 678 0 1
877 0 1157 0 1
878 0 1918 0 1
880 224 920 1 224
881 0 609 0 1
882 0 365 0 1
883 0 679 0 1
884 0 781 0 1
886 242 170 1 242
888 162 155 1 162
889 0 267 0 1
891 221 232 1 221
892 0 305 0 1
893 0 282 0 1
894 0 434 0 1
896 158 143 1 158
898 58 68 1 58
900 161 158 1 161
904 159 317 3 53
905 0 319 0 1
907 167 133 1 167
909 170 156 1 170
910 0 232 0 1
913 131 82 2 65.5
916 121 184 2 60.5
918 253 181 1 253
921 242 2859 2 121
922 0 2182 0 1
923 0 1416 0 1
924 0 190 0 1
926 251 195 1 251
928 46 865 1 46
929 0 1148 0 1
930 0 1342 0 1
931 0 1023 0 1
932 0 916 0 1
933 0 814 0 1
934 0 925 0 1
936 235 216 1 235
938 250 332 1 250
939 0 288 0 1
940 0 246 0 1
943 212 52 2 106
945 185 538 1 185
946 0 262 0 1
947 0 393 0 1
948 0 274 0 1
949 0 466 0 1
950 0 322 0 1
951 0 492 0 1
952 0 258 0 1
953 0 377 0 1
954 0 518 0 1
955 0 434 0 1
956 0 370 0 1
957 0 497 0 1
958 0 296 0 1
959 0 269 0 1
961 212 331 1 212
962 0 306 0 1
964 255 175 1 255
965 0 331 0 1
966 0 522 0 1
967 0 433 0 1
968 0 355 0 1
969 0 512 0 1
970 0 329 0 1
971 0 265 0 1
972 0 406 0 1
973 0 662 0 1
974 0 556 0 1
975 0 438 0 1
976 0 652 0 1
977 0 656 0 1
979 188 307 1 188
980 0 349 0 1
981 0 358 0 1
983 320 317 1 320
985 224 232 1 224
987 206 240 1 206
989 251 180 1 251
991 213 207 1 213
993 255 172 1 255
994 0 442 0 1
995 0 307 0 1
996 0 499 0 1
997 0 620 0 1
998 0 786 0 1
999 0 806 0 1

Detailed Statistics General

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID Aug 8 2010 5:23PM 113443 113443 99 0.3337781 12 false 113443

Detailed Statistics Density

All Density Average Length Columns
2.694111E-05 4 ReferenceOrderID
8.815E-06 8 ReferenceOrderID, ReferenceOrderLineID
8.815E-06 12 ReferenceOrderID, ReferenceOrderLineID, TransactionID

Detailed Statistics Histogram

Range High Key Range Rows Estimated Rows Distinct Range Rows Avg Range Rows
417 0 3 0 1
1255 1897 24 837 2.266428
1465 462 6 209 2.210526
1705 493 9 239 2.062762
2045 745 14 339 2.19764
2225 368 10 179 2.055866
2485 534 26 259 2.061776
2695 466 13 209 2.229665
3485 1702 13 789 2.157161
3805 666 10 319 2.087774
41798 613 1 403 1.521092
53452 11656 44 11653 1.000257
53465 360 72 12 30
53485 528 60 19 27.78947
53514 545 59 28 19.46428
53536 514 62 21 24.47619
53560 323 63 23 14.04348
53583 632 46 22 28.72727
53621 540 55 37 14.59459
53699 280 5 77 3.636364
54893 4110 4 1193 3.445096
55052 512 3 158 3.240506
55234 640 47 181 3.535912
55268 725 60 33 21.9697
55301 576 58 32 18
55407 674 4 105 6.419048
56800 4885 2 1392 3.509339
57012 681 50 211 3.227488
57046 586 68 33 17.75758
57065 287 56 18 15.94444
57093 511 42 27 18.92593
57118 236 36 24 9.833333
57144 561 49 25 22.44
57186 489 60 41 11.92683
57250 255 3 63 4.047619
58080 2827 3 829 3.410133
58894 2829 5 813 3.479705
58920 492 48 25 19.68
58930 252 32 9 28
58959 522 33 28 18.64286
58972 248 51 12 20.66667
59011 507 34 38 13.3421
59044 505 33 32 15.78125
59095 540 3 50 10.8
60041 3339 5 945 3.533333
60194 508 5 152 3.342105
60336 517 5 141 3.666667
61087 2606 6 750 3.474667
61184 488 45 96 5.083333
61219 514 21 34 15.11765
61248 497 50 28 17.75
61297 255 6 48 5.3125
61907 2146 2 609 3.523809
62208 1000 7 300 3.333333
62941 2559 5 732 3.495902
63132 759 29 190 3.994737
63216 1062 50 83 12.79518
63257 502 32 40 12.55
63283 247 37 25 9.88
63392 509 3 108 4.712963
64571 4104 4 1178 3.483871
64706 507 5 134 3.783582
65154 1528 24 447 3.418344
65191 641 37 36 17.80556
65228 484 29 36 13.44444
65257 448 39 28 16
65303 374 34 45 8.311111
65410 511 4 106 4.820755
66012 2052 5 601 3.41431
66153 510 3 140 3.642857
67204 3596 4 1050 3.424762
67273 505 58 68 7.426471
67294 490 40 20 24.5
67322 476 36 27 17.62963
67364 509 4 41 12.41463
68552 4147 6 1187 3.493682
68689 510 2 136 3.75
69309 2155 9 619 3.481422
69406 493 45 96 5.135417
69435 491 24 28 17.53572
69461 500 36 25 20
69501 493 36 39 12.64103
69528 486 51 26 18.69231
69560 495 31 31 15.96774
69762 766 3 201 3.810945
71736 6927 4 1973 3.510897
71794 501 62 57 8.789474
71819 538 45 24 22.41667
71841 526 51 21 25.04762
71881 479 43 39 12.28205
71936 709 47 54 13.12963
72083 767 3 146 5.253425
72310 765 4 226 3.384956
72521 763 6 210 3.633333
72780 766 4 258 2.968992
74338 3841 2 1557 2.466923
74692 766 2 353 2.169972
75122 1008 2 429 2.34965
75123 0 3 0 1

Detailed Statistics General

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
IX_TransactionHistory_ReferenceOrderID Oct 26 2011 11:51PM 113443 113443 99 0.3337781 16 false 113443

Detailed Statistics Density

All Density Average Length Columns
2.694111E-05 4 ReferenceOrderID
1.777462E-05 12 ReferenceOrderID, TransactionDate
8.815E-06 16 ReferenceOrderID, TransactionDate, TransactionID

Detailed Statistics Histogram

Range High Key Range Rows Estimated Rows Distinct Range Rows Avg Range Rows
417 0 3 0 1
1255 1897 24 837 2.266428
1465 462 6 209 2.210526
1705 493 9 239 2.062762
2045 745 14 339 2.19764
2225 368 10 179 2.055866
2485 534 26 259 2.061776
2695 466 13 209 2.229665
3485 1702 13 789 2.157161
3805 666 10 319 2.087774
41798 613 1 403 1.521092
53452 11656 44 11653 1.000257
53465 360 72 12 30
53485 528 60 19 27.78947
53514 545 59 28 19.46428
53536 514 62 21 24.47619
53560 323 63 23 14.04348
53583 632 46 22 28.72727
53621 540 55 37 14.59459
53699 280 5 77 3.636364
54893 4110 4 1193 3.445096
55052 512 3 158 3.240506
55234 640 47 181 3.535912
55268 725 60 33 21.9697
55301 576 58 32 18
55407 674 4 105 6.419048
56800 4885 2 1392 3.509339
57012 681 50 211 3.227488
57046 586 68 33 17.75758
57065 287 56 18 15.94444
57093 511 42 27 18.92593
57118 236 36 24 9.833333
57144 561 49 25 22.44
57186 489 60 41 11.92683
57250 255 3 63 4.047619
58080 2827 3 829 3.410133
58894 2829 5 813 3.479705
58920 492 48 25 19.68
58930 252 32 9 28
58959 522 33 28 18.64286
58972 248 51 12 20.66667
59011 507 34 38 13.3421
59044 505 33 32 15.78125
59095 540 3 50 10.8
60041 3339 5 945 3.533333
60194 508 5 152 3.342105
60336 517 5 141 3.666667
61087 2606 6 750 3.474667
61184 488 45 96 5.083333
61219 514 21 34 15.11765
61248 497 50 28 17.75
61297 255 6 48 5.3125
61907 2146 2 609 3.523809
62208 1000 7 300 3.333333
62941 2559 5 732 3.495902
63132 759 29 190 3.994737
63216 1062 50 83 12.79518
63257 502 32 40 12.55
63283 247 37 25 9.88
63392 509 3 108 4.712963
64571 4104 4 1178 3.483871
64706 507 5 134 3.783582
65154 1528 24 447 3.418344
65191 641 37 36 17.80556
65228 484 29 36 13.44444
65257 448 39 28 16
65303 374 34 45 8.311111
65410 511 4 106 4.820755
66012 2052 5 601 3.41431
66153 510 3 140 3.642857
67204 3596 4 1050 3.424762
67273 505 58 68 7.426471
67294 490 40 20 24.5
67322 476 36 27 17.62963
67364 509 4 41 12.41463
68552 4147 6 1187 3.493682
68689 510 2 136 3.75
69309 2155 9 619 3.481422
69406 493 45 96 5.135417
69435 491 24 28 17.53572
69461 500 36 25 20
69501 493 36 39 12.64103
69528 486 51 26 18.69231
69560 495 31 31 15.96774
69762 766 3 201 3.810945
71736 6927 4 1973 3.510897
71794 501 62 57 8.789474
71819 538 45 24 22.41667
71841 526 51 21 25.04762
71881 479 43 39 12.28205
71936 709 47 54 13.12963
72083 767 3 146 5.253425
72310 765 4 226 3.384956
72521 763 6 210 3.633333
72780 766 4 258 2.968992
74338 3841 2 1557 2.466923
74692 766 2 353 2.169972
75122 1008 2 429 2.34965
75123 0 3 0 1

Objects that depend on [TransactionHistory]

Objects on which [TransactionHistory] depends

SQL

Script
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory]( [TransactionID] [int] IDENTITY(100000,1) NOT NULL, [ProductID] [int] NOT NULL, [ReferenceOrderID] [int] NOT NULL, [ReferenceOrderLineID] [int] NOT NULL, [TransactionDate] [datetime] NOT NULL, [TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Quantity] [int] NOT NULL, [ActualCost] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY]
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for TransactionHistory records.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'TransactionID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product identification number. Foreign key to Product.ProductID.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'ProductID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Purchase order, sales order, or work order identification number.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'ReferenceOrderID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Line number associated with the purchase order, sales order, or work order.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'ReferenceOrderLineID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time of the transaction.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'TransactionDate'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'W = WorkOrder, S = SalesOrder, P = PurchaseOrder' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'TransactionType'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product quantity.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'Quantity'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product cost.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'ActualCost'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Record of each purchase order, sales order, or work order transaction year to date.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'TransactionHistory'

See also

List of Tables