InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:26:30 GMT20http://www.sqlservercentral.com/Forums/Topic1293635-373-1.aspxHi Guys,I have a main table called File:[code="sql"]/****** Object: Table [dbo].[File] Script Date: 05/02/2012 08:29:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[File]( [FileID] [bigint] IDENTITY(1,1) NOT NULL, [VesselID] [bigint] NULL, [VoyageNo] [varchar](255) NULL, [OpenedDate] [datetime] NULL, [FileStatusID] [int] NULL, [ClosedDate] [datetime] NULL, [ReopenedDate] [datetime] NULL, [ClosedReason] [varchar](255) NULL, [InstructedHowID] [bigint] NULL, [ClaimTypeID] [bigint] NULL, [ClaimSubType1ID] [bigint] NULL, [ClaimSubType2ID] [bigint] NULL, [ClaimSubType3ID] [bigint] NULL, [OpenedByID] [uniqueidentifier] NULL, [InstructedByID] [bigint] NULL, [DiarisedToID] [uniqueidentifier] NULL, [DiarisedDate] [datetime] NULL, [InvoiceDiary] [datetime] NULL, [OfficeHandlerID] [uniqueidentifier] NULL, [OutToID] [uniqueidentifier] NULL, [BranchID] [bigint] NULL, [Remarks] [text] NULL, [BoxedFile] [bit] NULL, [AgentID] [bigint] NULL, [FileNumber] [varchar](100) NULL, [TimeBar] [datetime] NULL, [Summons] [bit] NULL, [LOU] [bit] NULL, [Samples] [bit] NULL, [DebtorID] [bigint] NULL, CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED ( [FileID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[File] ONINSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (69, 4990, N'1', CAST(0x00009EF000000000 AS DateTime), 2, NULL, NULL, N'', 1, 93, 630, 152, 417, N'9758029f-7bec-4d77-8e45-f2241c43b654', 1, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x0000A03700000000 AS DateTime), CAST(0x0000A03900000000 AS DateTime), NULL, NULL, 1, N'', 1, 1, N'001', CAST(0x00009EF600000000 AS DateTime), 0, 1, 0, NULL)INSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (70, 1, N'2', CAST(0x00009EF400000000 AS DateTime), 1, NULL, NULL, N'', 1, 6, 46, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', 1, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EF000000000 AS DateTime), NULL, NULL, NULL, 1, N'', 0, NULL, N'002', NULL, 0, 0, 0, 1)INSERT [dbo].[File] ([FileID], [VesselID], [VoyageNo], [OpenedDate], [FileStatusID], [ClosedDate], [ReopenedDate], [ClosedReason], [InstructedHowID], [ClaimTypeID], [ClaimSubType1ID], [ClaimSubType2ID], [ClaimSubType3ID], [OpenedByID], [InstructedByID], [DiarisedToID], [DiarisedDate], [InvoiceDiary], [OfficeHandlerID], [OutToID], [BranchID], [Remarks], [BoxedFile], [AgentID], [FileNumber], [TimeBar], [Summons], [LOU], [Samples], [DebtorID]) VALUES (71, 839, N'3', CAST(0x00009EF400000000 AS DateTime), 1, NULL, NULL, N'', 1, 8, 49, NULL, NULL, NULL, 1, N'9758029f-7bec-4d77-8e45-f2241c43b654', CAST(0x00009EF500000000 AS DateTime), CAST(0x0000A02B00000000 AS DateTime), NULL, NULL, 3, N'', 0, 1, N'003', NULL, 0, 1, 0, NULL)[/code]Then I have a table called Costings:[code="sql"]USE [FileMan]GO/****** Object: Table [dbo].[Costings] Script Date: 05/02/2012 08:29:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Costings]( [CostingID] [bigint] IDENTITY(1,1) NOT NULL, [Description] [varchar](150) NULL, [isDisbursement] [bit] NULL, [Active] [bit] NULL, [sortOrder] [int] NULL, CONSTRAINT [PK_Costings] PRIMARY KEY CLUSTERED ( [CostingID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Costings] ONINSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (1, N'Surveyor''s Fees', 1, 1, 1)INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (2, N'Chemists', 1, 1, 2)INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (3, N'Lawyers', 1, 1, 3)INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (4, N'Office Fee', 0, 1, 4)INSERT [dbo].[Costings] ([CostingID], [Description], [isDisbursement], [Active], [sortOrder]) VALUES (5, N'Photocopy', 0, 1, 5)SET IDENTITY_INSERT [dbo].[Costings] OFF[/code]I display each costing in a gridview for each file. Every File must have each costing associated with it, so when I click save I insert the FileID and CostingID in a table called File_Costings:[code="sql"]/****** Object: Table [dbo].[File_Costings] Script Date: 05/02/2012 08:29:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[File_Costings]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [FileID] [bigint] NULL, [CostingID] [bigint] NULL, [Value] [decimal](18, 2) NULL, [InvoiceID] [bigint] NULL, CONSTRAINT [PK_File_Costings] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[File_Costings] ONINSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (16, 69, 1, CAST(2100.00 AS Decimal(18, 2)), 27)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (17, 69, 1, CAST(1200.00 AS Decimal(18, 2)), 29)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (18, 69, 1, CAST(300.00 AS Decimal(18, 2)), 31)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (19, 69, 1, CAST(100.00 AS Decimal(18, 2)), 30)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (20, 69, 2, CAST(500.00 AS Decimal(18, 2)), 27)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (21, 69, 2, CAST(600.00 AS Decimal(18, 2)), 29)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (22, 69, 2, CAST(700.00 AS Decimal(18, 2)), 31)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (23, 69, 2, CAST(800.00 AS Decimal(18, 2)), 30)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (24, 69, 3, CAST(900.00 AS Decimal(18, 2)), 27)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (25, 69, 3, CAST(0.00 AS Decimal(18, 2)), 29)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (26, 69, 3, CAST(0.00 AS Decimal(18, 2)), 31)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (27, 69, 3, CAST(0.00 AS Decimal(18, 2)), 30)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (28, 69, 4, CAST(120.00 AS Decimal(18, 2)), 27)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (29, 69, 4, CAST(0.00 AS Decimal(18, 2)), 29)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (30, 69, 4, CAST(0.00 AS Decimal(18, 2)), 31)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (31, 69, 4, CAST(0.00 AS Decimal(18, 2)), 30)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (32, 69, 5, CAST(150.00 AS Decimal(18, 2)), 27)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (33, 69, 5, CAST(0.00 AS Decimal(18, 2)), 29)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (34, 69, 5, CAST(0.00 AS Decimal(18, 2)), 31)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (35, 69, 5, CAST(0.00 AS Decimal(18, 2)), 30)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (36, 69, 1, CAST(0.00 AS Decimal(18, 2)), 33)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (37, 69, 2, CAST(0.00 AS Decimal(18, 2)), 33)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (38, 69, 3, CAST(110.00 AS Decimal(18, 2)), 33)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (39, 69, 4, CAST(0.00 AS Decimal(18, 2)), 33)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (40, 69, 5, CAST(50.00 AS Decimal(18, 2)), 33)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (41, 73, 1, CAST(73.00 AS Decimal(18, 2)), 34)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (42, 73, 2, CAST(74.00 AS Decimal(18, 2)), 34)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (43, 73, 3, CAST(75.00 AS Decimal(18, 2)), 34)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (44, 73, 4, CAST(76.00 AS Decimal(18, 2)), 34)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (45, 73, 5, CAST(77.00 AS Decimal(18, 2)), 34)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (46, 73, 1, CAST(70.00 AS Decimal(18, 2)), 35)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (47, 73, 1, CAST(78.00 AS Decimal(18, 2)), 36)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (48, 73, 2, CAST(0.00 AS Decimal(18, 2)), 35)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (49, 73, 2, CAST(0.00 AS Decimal(18, 2)), 36)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (50, 73, 3, CAST(0.00 AS Decimal(18, 2)), 35)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (51, 73, 3, CAST(0.00 AS Decimal(18, 2)), 36)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (52, 73, 4, CAST(0.00 AS Decimal(18, 2)), 35)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (53, 73, 4, CAST(0.00 AS Decimal(18, 2)), 36)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (54, 73, 5, CAST(0.00 AS Decimal(18, 2)), 35)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (55, 73, 5, CAST(0.00 AS Decimal(18, 2)), 36)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (56, 72, 1, CAST(1.00 AS Decimal(18, 2)), 37)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (57, 72, 2, CAST(0.00 AS Decimal(18, 2)), 37)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (58, 72, 3, CAST(0.00 AS Decimal(18, 2)), 37)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (59, 72, 4, CAST(0.00 AS Decimal(18, 2)), 37)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (60, 72, 5, CAST(0.00 AS Decimal(18, 2)), 37)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (61, 74, 1, CAST(74.00 AS Decimal(18, 2)), 38)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (62, 74, 2, CAST(75.00 AS Decimal(18, 2)), 38)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (63, 74, 3, CAST(10.00 AS Decimal(18, 2)), 38)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (64, 74, 4, CAST(10.00 AS Decimal(18, 2)), 38)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (65, 74, 5, CAST(20.00 AS Decimal(18, 2)), 38)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (66, 70, 1, CAST(170.00 AS Decimal(18, 2)), 39)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (67, 70, 2, CAST(10.00 AS Decimal(18, 2)), 39)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (68, 70, 3, CAST(10.00 AS Decimal(18, 2)), 39)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (69, 70, 4, CAST(10.00 AS Decimal(18, 2)), 39)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (70, 70, 5, CAST(0.00 AS Decimal(18, 2)), 39)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (71, 69, 1, CAST(0.00 AS Decimal(18, 2)), 40)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (72, 69, 2, CAST(0.00 AS Decimal(18, 2)), 40)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (73, 69, 3, CAST(0.00 AS Decimal(18, 2)), 40)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (74, 69, 4, CAST(0.00 AS Decimal(18, 2)), 40)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (75, 69, 5, CAST(0.00 AS Decimal(18, 2)), 40)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (76, 69, 1, CAST(1500.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (81, 185, 1, CAST(2000.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (82, 185, 2, CAST(100.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (83, 185, 3, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (84, 185, 4, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (85, 185, 5, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (86, 185, 1, CAST(5000.00 AS Decimal(18, 2)), 41858)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (87, 185, 2, CAST(1.00 AS Decimal(18, 2)), 41858)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (88, 185, 3, CAST(0.00 AS Decimal(18, 2)), 41858)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (89, 185, 4, CAST(0.00 AS Decimal(18, 2)), 41858)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (90, 185, 5, CAST(0.00 AS Decimal(18, 2)), 41858)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (91, 185, 1, CAST(100.00 AS Decimal(18, 2)), 41859)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (92, 185, 2, CAST(1.00 AS Decimal(18, 2)), 41859)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (93, 185, 3, CAST(1.00 AS Decimal(18, 2)), 41859)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (94, 185, 4, CAST(14.00 AS Decimal(18, 2)), 41859)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (95, 185, 5, CAST(0.00 AS Decimal(18, 2)), 41859)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (96, 69, 2, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (97, 69, 3, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (98, 69, 4, CAST(0.00 AS Decimal(18, 2)), 0)INSERT [dbo].[File_Costings] ([ID], [FileID], [CostingID], [Value], [InvoiceID]) VALUES (99, 69, 5, CAST(0.00 AS Decimal(18, 2)), 0)SET IDENTITY_INSERT [dbo].[File_Costings] OFF[/code]The above table also saves an InvoiceID because a costing is also attached to an Invoice.[code="sql"]USE [FileMan]GO/****** Object: Table [dbo].[Invoice] Script Date: 05/02/2012 08:39:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoice]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[Invoice]( [InvoiceID] [bigint] IDENTITY(1,1) NOT NULL, [FileID] [bigint] NULL, [InvoiceNumber] [varchar](50) NULL, [InvoiceDate] [datetime] NULL, [StartDate] [datetime] NULL, [Amount] [decimal](18, 2) NULL, [PaidDate] [datetime] NULL, [PaidAmount] [decimal](18, 2) NULL, [Reference] [text] NULL, [CreatedBy] [uniqueidentifier] NULL, [CreatedDate] [datetime] NULL, [DebtorID] [bigint] NULL, [CreditNoteNumber] [varchar](150) NULL, CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ( [InvoiceID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]ENDGOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Invoice] ONINSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (33, 69, N'001E', CAST(0x00009F6E00000000 AS DateTime), CAST(0x00009F5100000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), CAST(0x00009F7200000000 AS DateTime), NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6300BA96CC AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (34, 73, N'73A', CAST(0x00009F7300000000 AS DateTime), CAST(0x00009F5100000000 AS DateTime), CAST(2000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64002DFB7C AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (35, 73, N'73B', CAST(0x00009F8A00000000 AS DateTime), CAST(0x00009F7300000000 AS DateTime), CAST(300.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64003B5A88 AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (36, 73, N'73C', CAST(0x00009FAB00000000 AS DateTime), CAST(0x00009F9C00000000 AS DateTime), CAST(100.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F64003D0518 AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (37, 72, N'72A', CAST(0x00009F5000000000 AS DateTime), CAST(0x00009F3200000000 AS DateTime), CAST(200.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6500B1240C AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (38, 74, N'74A', CAST(0x00009F3100000000 AS DateTime), CAST(0x00009F0200000000 AS DateTime), CAST(500.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F6500BFB3C8 AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (39, 70, N'70A', CAST(0x00009FBF00000000 AS DateTime), CAST(0x00009F5C00000000 AS DateTime), CAST(14000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F65001967E8 AS DateTime), 1, NULL)INSERT [dbo].[Invoice] ([InvoiceID], [FileID], [InvoiceNumber], [InvoiceDate], [StartDate], [Amount], [PaidDate], [PaidAmount], [Reference], [CreatedBy], [CreatedDate], [DebtorID], [CreditNoteNumber]) VALUES (40, 69, N'001A', CAST(0x00009F8500000000 AS DateTime), CAST(0x00009F7000000000 AS DateTime), CAST(3000.00 AS Decimal(18, 2)), NULL, NULL, NULL, N'a6600e6c-6402-4a9b-a2df-d4093917dee5', CAST(0x00009F7300D34910 AS DateTime), 2, NULL)SET IDENTITY_INSERT [dbo].[Invoice] OFF[/code]So in order to display my data as in the attached image I use this SP to pivot the data per InvoiceNumber:[code="sql"]/****** Object: StoredProcedure [dbo].[PivotBreakdown] Script Date: 05/02/2012 08:29:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE [dbo].[PivotBreakdown] -- Add the parameters for the stored procedure here @FileID intASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ColumnHeaders VARCHAR(MAX)SELECT @ColumnHeaders = COALESCE( @ColumnHeaders + ',[' + convert(varchar(MAX),invoiceid) + ']' ,'[' + convert(varchar(MAX),invoiceid) + ']' ) FROM ( SELECT 0 as invoiceid UNION ALL SELECT DISTINCT invoiceid -- get distinct invoice numbers FROM invoice WHERE FileID = @FileID )aliasName/* view results from above statement */--SELECT @ColumnHeadersDECLARE @PivotTable NVARCHAR(MAX)SET @PivotTable = N' SELECT * FROM ( SELECT c.CostingID, c.Description, c.isDisbursement, 0 as isTimeSheet, fc.invoiceid, fc.value as [value], c.sortorder FROM Costings C LEFT JOIN File_Costings FC ON (FC.CostingID = c.CostingID) AND (fc.FileID=' + CONVERT(varchar(MAX),@FileID) + ') LEFT JOIN Invoice i ON i.invoiceid = fc.invoiceid WHERE (C.Active = 1) GROUP BY i.invoiceid ) AS TableToPivot PIVOT ( SUM(value) FOR invoiceid IN ( ' + @ColumnHeaders + ' ) ) AS PivotedTableORDER BY sortorder'EXECUTE(@PivotTable)ENDGO[/code]As you can see, if I pass through a FileID of 69 I get all the costings listed and the Invoice Numbers attached to that file are displayed as columns in my grid. If I save an entry is made into the File_Costings Table for each row of data with the FileID, CostingID and InvoiceID.This all works great as the need at the time was to save all costings per file, but my client now needs to add new Costings per file. So the open FileID 69. They want to add a Costing to the Grid called "Example Cost". This new costing must only apply to FileID 69.How can I alter my existing structure to cater for this. Do I add a Boolean flag "isdefault" , set to true to all the current costings(because all current costings must appear for each file), then for any new costings save the flag as false into the Costings table, then insert a record into the File_Costings table?Please help. I need to have this done as soon as possible. ThanksIn terms of functionality is this the best way? What other options do I have?Wed, 02 May 2012 00:53:23 GMTniteshrajgopalhttp://www.sqlservercentral.com/Forums/Topic1304340-373-1.aspxHi All,Looking for opinions on something. I've never seen this before, and I'm of mixed opinion on how I feel about it:When naming a column that is a foreign key to another table, prefix the related table name to the column name. Example:Table1 Table1Id INT more columnsTable2 Table2Id INT Table1_Table1Id INT more columnsWhat are your thoughts?Tue, 22 May 2012 11:22:05 GMTTom Bakermanhttp://www.sqlservercentral.com/Forums/Topic1298767-373-1.aspxI have been happily using Tally tables to good effect since I first read about them here (Thanks!), but there is a usage question which comes up now and again and I was wondering how some of you seasoned veterans of Tally Table usage generally deal with the situation.Basically, most of the Tally Table examples I have seen start the tables at 1. In most cases that seems to work fine but there are some cases where the inclusion of 0 seems like a useful thing. To be sure, if there's a 0 there, for some things where 0 is not appropriate it may be necessary to ensure it is explicitly excluded where the table is used, but the alternative would seem to be that where the zero is needed in, say, a join, but the tally table starts at 1, then something like "x=tally-1" becomes necessary and though that works, that doesn't strike me as more desirable than just including the zero in the table.What is your take on this? Do you generally always have the Tally table start at 1 and just make adjustments as necessary or do you tailor the Tally table to the problems to which it is being applied and make the range whatever is needed for those problems? Do you have specific reasons why you do it one way or the other or did you just make an arbitrary choice and stick with it?Thanks -- I look forward to hearing your opinions.- LesFri, 11 May 2012 10:53:28 GMTlnolandhttp://www.sqlservercentral.com/Forums/Topic904806-373-1.aspx-- Apologies for not posting here first time :) ....Hi,I've been working on putting together a CI (continuous integration) environment, and had fun trawling through MSBuild books, the msdn forums on DB Pro, and MSBuild etc.Would it be worth having a sub-group here to draw together discussion and ideas on (from a SQL perspective)?:-- MSBuild- Deployment- VS Db Pro- SQL Unit testing- Load / Stress testing etc.Basically all the subjects that one needs to cover to setup and manage a CI / automated deployment environmentOnly an idea :)Thanks for reading.Fri, 16 Apr 2010 07:03:26 GMTCraig@Workhttp://www.sqlservercentral.com/Forums/Topic1285094-373-1.aspxI'm attempting to develop a scale out plan for existing SQL Servers. Current configuration is irrelevant--let's just say I was thrown a garbled mess and tasked with "fixing" it. So I have the option of either attempting to make the bad somehow less bad or designing an entirely new configuration.What's needed:- high-availability- some semblance of load balanceWhat I'm working with:- 7-9 physical servers - 14+ virtual servers- approx. 20 databases, each with a minimum of 20 tables & approximately 400000+ records per tableWhat I'm thinking (highly abstracted version):-Build load balancing into the databases themselves. (literal load balancing of databases)-Configure clustering across servers with 1 physical server & x virtual servers. Then use either Continuous Replication or AlwaysOn Availability Groups for clusters (to handle failover & synchronization).Most of the users require only read access. There also needs to be access to live dynamic--on some databases, at least. Any feedback regarding alternate solutions, where I'm straying, and so forth, is much appreciated. Thanks.Tue, 17 Apr 2012 10:27:05 GMTFelicia Coonshttp://www.sqlservercentral.com/Forums/Topic1282204-373-1.aspxhi all,how to Designing a database for a tennis league .how to design and Architecture and tables and everything on tennies game.pls guide to me....thanks.Thu, 12 Apr 2012 02:58:55 GMTkranthi.india123http://www.sqlservercentral.com/Forums/Topic1283053-373-1.aspxNEWBIE to DW : the IT Manager wants to use varchar2(2000) for all fields in all tables.I thought we should be more realistic. Sure, use varchar2, but if it is a state, use 2 or even 4.Any norms is this area? Anything "bad" about using varchar2(2000) for everything?Manager's idea is to make sure we capture the data in staging exactly like it is in the flat file. Thanks!JoeFri, 13 Apr 2012 05:52:56 GMTdevereauxjhttp://www.sqlservercentral.com/Forums/Topic1281375-373-1.aspxDoes anyone have any suggestions on how to implement generating events to the CQRS pattern from the database? I have got some ideas (and some doubts) of using triggers to write to a event table. Is anyone actually using this with SQL Server or another DBMS? I Service Broker the way?http://blog.fossmo.net/post/Command-and-Query-Responsibility-Segregation-(CQRS).aspxhttp://elegantcode.com/2009/11/11/cqrs-la-greg-young/Wed, 11 Apr 2012 02:23:54 GMTpeter.skoglundhttp://www.sqlservercentral.com/Forums/Topic1280155-373-1.aspxHello, I have a design question about how most people deal with having to track users input changes to a sql server backend. Do you add a column or shadow table that the input tool is responsible for tracking user x changed this to that at whatever time? Or is there something built into sql server that i can use to do this and would i have to create a sql user for each input user to connect as in order to leverage it? Any thoughts would be appreciated.Mon, 09 Apr 2012 08:07:52 GMTMooch279http://www.sqlservercentral.com/Forums/Topic1273412-373-1.aspxHi Guys i relly need help i need to do normalisation but i cannot get my head wraped around itcan someone please help me and normalise this data plzCustomer ID Customer ForenameCustomer SurnameStreetTownPostcodeEmailCategoryOrder IDOrder DateStock IDStock DescriptionPriceQty in StockSupplier IDSupplier NameSupplier AddressSupplier TownSupplier PostcodeSupplier Tel NoTue, 27 Mar 2012 05:55:51 GMTAty01http://www.sqlservercentral.com/Forums/Topic1271897-373-1.aspxUntil today I never saw a Database that contained both the Staging Tables and Data Warehouse Tables in the same Database.The Staging Tables are in a different schema than the Data Warehouse Tables.Maybe I'm missing something but I have to make a solid case that this is a good practice.Any information would be greatly appreciated.http://www.bidn.com/blogs/TomLannen/bidn-blog/1277/separating-data-warehouse-tables-from-your-relational-and-staging-tables For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Fri, 23 Mar 2012 11:10:27 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1213524-373-1.aspxI have 4 types of entities Buildings, Floors, Rooms, and Computers. The Computer table will store attributes about the computer along with it's location. I’m looking for some constructive criticism on these approaches so I get a better idea about modeling correct relationships between entities. I’ve read many articles about the pros and cons regarding natural vs. surrogate so I’m not wanting to get into that becuase I'm just doing this for a beter understanding, but I am looking for opinions regarding how my entities relate to each other and if they make sense. [u][b]Natural Key Model 1: [/b][/u]I’d say this approach to modeling says that a computer relates to one Building, one Floor, and one Room. However, Building, Floor, and Room are not directly modeled to relate to each other. To me this looks like a star schema design for BI with the Building, Floor, Room being dimensions and the Computer as a fact table.[quote]CREATE TABLE [Building](BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED)CREATE TABLE [Floor](FloorName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED)CREATE TABLE [Room](RoomName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED)CREATE TABLE [Computer](ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,BuildingName VARCHAR(20) NOT NULL,FloorName VARCHAR(20) NOT NULL,RoomName VARCHAR(20) NOT NULL)ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)ALTER TABLE dbo.Computer ADD FOREIGN KEY (FloorName) REFERENCES dbo.Floor (FloorName)ALTER TABLE dbo.Computer ADD FOREIGN KEY (RoomName) REFERENCES dbo.Room (RoomName)[/quote]See attached image DatabaseDiagramNatualOption1.png for the diagram…[b]Natural Key Model 2: [u][/u][/b]I’d say this approach to modeling says that a computer relates to one “Location” meaning a combination of Building, Floor, and Room. The Building, Floor, and Room tables are directly related to each other becuase a Building has many Floors, a Floor has many Rooms, but a Computer has only one "Location". [quote]CREATE TABLE [Building](BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED)CREATE TABLE [Floor](FloorName VARCHAR(20) NOT NULL ,BuildingName VARCHAR(20) NOT NULL CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED ( [FloorName] ASC, [BuildingName] ASC))CREATE TABLE [Room](RoomName VARCHAR(20) NOT NULL ,FloorName varchar(20) NOT NULL ,BuildingName varchar(20) NOT NULL CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ( [RoomName] ASC, [FloorName] ASC, [BuildingName] ASC))CREATE TABLE [Computer](ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,BuildingName VARCHAR(20) NOT NULL,FloorName VARCHAR(20) NOT NULL,RoomName VARCHAR(20) NOT NULL)ALTER TABLE dbo.Floor ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)ALTER TABLE dbo.Room ADD FOREIGN KEY (FloorName, BuildingName) REFERENCES dbo.Floor (FloorName, BuildingName)ALTER TABLE dbo.Computer ADD FOREIGN KEY (RoomName,FloorName,BuildingName) REFERENCES dbo.Room (RoomName,FloorName,BuildingName)[/quote]See attached image DatabaseDiagramNatualOption2.png for the diagram…[b]Natural Key Model 3: [u][/u][/b]Other ways to model this data that I didn’t toss out??Tue, 29 Nov 2011 13:23:33 GMTSQL Dude-467553http://www.sqlservercentral.com/Forums/Topic1272240-373-1.aspxI had someone hand off a schema for a "Dimensional Model".I looked at it and I did not see anything that closely resembled a fact table.Almost all of the tables are using natural keys as the primary key as opposed to a Surrogate Key.The article listed below list some of the reasons why you should not use natural keys.I just need a clear and concise quick argument as to what they have done is wrong.Your input would be greatly appreciated.[url]http://msdn.microsoft.com/en-us/library/aa905979(v=sql.80).aspx[/url]Sat, 24 Mar 2012 08:49:44 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1272367-373-1.aspxHave there been improvements with the Database Diagram Tool?If you have to restore a Database and how can you preserve your diagram?Can you export & import?I seem to recall that there are some Stored Procedures that facilitate this?Can I export and load into Visio to create a reversed engineered diagram?Sun, 25 Mar 2012 11:43:38 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1272280-373-1.aspxCould someone please help me explain to a nontechnical person why it is bad practice to use the Natural Keys from the source systems as the Primary key that relates to the Fact Table?I appreciate any input on this topic.Sat, 24 Mar 2012 16:15:16 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1271930-373-1.aspxI use standard naming conventions but a developer used ERwin to create staging tables in two schemas and he did not use a Name rule to create the Primary Key so it created system generated Primary Keys.I told him what standard that I wanted to use which is consistent with the article listed below.So then he publishes the Data Warehouse Tables in the same Database with a different schema.He only creates Primary Keys, no other constraints but after telling him what I wanted he uses the naming convention XPKTableName.I never saw anything like it.To make matters worse I do not see anything that closely resembles fact or dimensions. He must have come up with a new Dimensional Model that I never heard of. :w00t:[url]http://www.databasedesign-resource.com/constraint-naming-standard.html[/url]Your thoughts and ideas would be greatly appreciated.Fri, 23 Mar 2012 11:41:44 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1269711-373-1.aspxI was handed off DDL to create a Database.The DDL was generated in ERwin.The Primary Keys were auto generated which I do not care for.It has been a while since I used ERwin and I do not currently have a copy but can't you set up a rule to name the Primary Key?The naming convention is PK_SchemaName_TableName.So if you can't do this but the Primary Keys have been dropped and recreated explicitly with the Naming Convention that I specified and you need to apply schema changes. If you load the Database into ERwin it will bring in the Names of the Primary Keys and you can subsequently make your changes in ERwin and generate the script and preserver the new Primary Key names? Tue, 20 Mar 2012 10:23:00 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1270541-373-1.aspxTechnically you can normalize the crap out of any entity model. this means adding more tables, primary/foreign key links etc.And although you will have a very well normalized database, your sprocs may be much more complicated. so where do you draw the line?Let me ask with a scenario:-A college has students.-Students take a course.-Each course has a number of subjects-Each student takes a subset of subjects-A student takes a course at a certain level (Advanced, intermediate, basic)and they gain a certain grade (A, B, C, D, E)So we have a students table.-We have a course table-we have a subjects table-we have a students_Courses table linking students with the course they are taking-we have a SubjectLevel table containing master data for the subjectLevel-We have a Grade table containing all available grades.-we have a Student_subject_level_Grade table linking students with the subject, level and grade they got.Is having lots of bridging tables that link entities over-normalization (such as the Student_subject_level_grade table)?Wed, 21 Mar 2012 16:03:16 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic1265271-373-1.aspxHello,I am converting data from thousands of old DBF tables into SQL Server 2008. Each Old DBF table has a maximum of 25 fields. There are 300 different fields in total from all the Tables. There are 3 similar groups of data from these tables. Similar meaning each group would be indexedthe same. There is a total of 45 million records. The groups are not equal and the largest group will have 30 million records. Also relevant to myquestion is number of joins to get to this data. A very common query will be something like this:Select xxx From Client Join On Warehouse Join On Boxes Join On Files And Files would of course be my 45 million records. I'm a bit Gun-Shy about creating a table with 300 fields. I don't know why, it just feels wrong. It seems to large, like I'd always be searching for the right column when viewing data. I also have never used a data dictionary with a table containing 30 million records nor have I introduced one knowing it would be a 5th or 6th join in common queries. So, my question, which is more like advise, Do I make my Files table 25 fields and use a data dictionary or make the 300 column table?I realize how generic this is, I am hoping to tap into some extensive expirence for a general guideline.Thanks,TPMon, 12 Mar 2012 09:02:33 GMTtp 25072http://www.sqlservercentral.com/Forums/Topic1259607-373-1.aspxHave you experienced the need for analyzing data temporally in your business? Do your existing information management systems allow you to view historical data? Typically, when you update or delete information in any application the previous state of that data is lost forevever. Have you ever wanted to be able to see what your information "used to be" as opposed to what it is now? In addition, have you ever wanted to know the state of your data in the past "as I knew it to be then" in comparison to "as I currently know it to have been then"?What is your experience in solving these problems?Wed, 29 Feb 2012 08:30:41 GMTJQAllenhttp://www.sqlservercentral.com/Forums/Topic1193688-373-1.aspxA conceptual thought: We have now (from SQL Server 2008) got filtered indexes. What do you think of the idea of having filtered foreign keys as well. In the example below we can think of ReferenceId being a SID, Guid or other value that is unique across both users and groups.[code]alter table AuditLog add constraint fk_AuditLog_Users foreign key (ReferenceId) references Users (UserId) where IsUser = 1;alter table AuditLog add constraint fk_AuditLog_Groups foreign key (ReferenceId) references Groups (GroupId) where IsGroup = 1;[/code]Thu, 20 Oct 2011 07:41:23 GMTokbangashttp://www.sqlservercentral.com/Forums/Topic1262003-373-1.aspxI am busy building a database, and now I have a question about my design.I have got the following tables:CustomerEmployeeProjectAttachment AttachmentTypeI want to be able to add multiple attachments to Customers, Employees and Projects.What is the best design for this?Now I store the id of the customer, employee or project in the attachment table in a RefId column and based on the attachmenttype I can tell to which table the attachment links.This design prevends me from using foreign keys in the attachment table.Is this the best design, or are the better ways to do it?Thanks in advanceTue, 06 Mar 2012 02:21:18 GMTfritshoutsmahttp://www.sqlservercentral.com/Forums/Topic1260133-373-1.aspxI've seen this before and I've had to do this again lately and I can't think of an alternative strategy. Basically I have a column that contains a foreign key to more than one table. Because it may be a key to one of N tables the column cannot have a foreign key and we have to add a code column so we know which table the fk is for.Are there any common design patterns or alternative strategies for doing this ?Example:MainTableID INT NN PKOtherTableCode VARCHAR NNOtherTableID INT NN **cannot have a fk here **SomeOtherTableAID INT NN PKCol1Col2...SomeOtherTableBID INT NN PKCol1Col2...MainTableRows1 TabA 1002 TabB 100SomeOtherTableA99 xxx xxx100 xxx xxx101 xxx xxxSomeOtherTableB99 xxx xxx100 xxx xxx101 xxx xxxThu, 01 Mar 2012 07:38:52 GMTWilliam Plourdehttp://www.sqlservercentral.com/Forums/Topic1258979-373-1.aspxHello,So I was hoping to get some opinions on a design issue I was wondering about (pros, cons, considerations, etc.). Here's the background:One of the systems I support handles requisitions and the client would like to be able to link certain requisitions together. It doesn't really matter why. This seems to me to be a typical many-to-many relationship issue except that instead of the many-to-many relationship being between 2 different types of things it is between the same type of thing. So I need to be able to join the Requisitions table back to itself.My initial thought was to use a Junction table with 2 Requisition ID columns in it - like this:[code="sql"]CREATE TABLE linkedRequisitions( requisitionID INT NOT NULL, linkedRequisitionID INT NOT NULL)[/code] But then I figured I'd have to insert 2 records for each relationship - one with requisition A in the first column and requisition B in the second column, then one with requisition A in the second column and requisition B in the first column (flip-flopped). This would get exponentially worse as the group of linked requisitions grew (i.e., 6 records for 3 linked requisitions, 12 records for 4 linked requisitions, etc.)My next idea was to create a table like this:[code="sql"]CREATE TABLE linkedRequisitions( groupID INT IDENTITY(1,1) NOT NULL, requisitionID INT NOT NULL)[/code]This would create one record for each requisition in the group and a group ID. This would make it easier for larger groups of linked requisitions but isn't what I have seen for implementing many-to-many relationships in DBs. Ultimately, I will need to be able to easily grab all linked requisitions' data based on a single requisition's ID.I'd be interested in seeing what people thought about these alternatives.Thanks,GeorgeTue, 28 Feb 2012 09:04:26 GMTGeorge H.http://www.sqlservercentral.com/Forums/Topic1149242-373-1.aspxOne of the biggest problems i have with SQL Server now, is it scalability. SQL does not scale out and after a while scaling up is not an option. Were I currently work we are using SQL for a web based application. Our current load is in the millions of connections a day (not a problem atm). But we are expecting growth that will take us up to the billions of connections per day. I have serious concerns whether or not SQL will be able to handle it.I am taking steps to redesign the database and implementation now to support this kind of load and still met the 10 ms overall response times. If you have scaled out SQL to support your application I would like to hear how you did it. Merge replication seems to be the only answer at present.Wed, 27 Jul 2011 08:32:06 GMTRay Lauberthttp://www.sqlservercentral.com/Forums/Topic1240515-373-1.aspxThe other DBA here left for greener pastures and I've finding all the things that were default named with his login. I thought I had them all and then found out that replication broke because he had been listed as owner on a number of databases. I have been changing the ownership of all DBs to SA, so that no matter who comes or goes we don't have this problem again.Is there any problem any of you know with making the SA account the owner of databases or other objects?On most of my instances, login to the SA account is disabled, but it still seems to take ownership just fine.We are running various versions from 2000 (soon to be retired!) to 2008 R2.Thanks,Dave BennettMon, 23 Jan 2012 15:03:55 GMTdavid.bennett2http://www.sqlservercentral.com/Forums/Topic1245599-373-1.aspxI have a process that crosses many technologies and servers. Im not sure what diagram will display the info clearly and concisely.A high level overview of the process is:- Button on a form is clicked and kicks off a job.-Job runs a cmd file that kicks off an ssis package.-last step of job kicks off a job on a 2nd server.-job on 2nd server calls a cmd that runs an ssis package.It goes on like that pretty much. Im thinking a simple flow chart will look cluttered and have a lot of text. would a sequence diagram be better or is there another diagram i have not considered?Thu, 02 Feb 2012 03:35:57 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic1211198-373-1.aspxThis is more of a general question on where everyone saves when and who created and last edited a record in the database.My standard is to have CreateBy, CreateDate, ModifyBy, and ModifyDate on the end of each major Entity table, but is this a best practice? The only problem for me is I prefer these four columns to always be the last four columns in the table, but given adding a column in the middle of a table with a PK being referenced is not a simple task this can lead to some ARG's when the table needs to be modified.One way I've worked around this is to split my data into two tables, so for example I'd have Customer.Master and Customer.Supplemental. Master will have CustomerID and maybe Name or some other basic information, it's main purpose is just to identify the PK for customers, but this will have my ModifyBy/Date and CreatedBy/Date columns here. Then any columns that might need to be added would go into Customer.Supplemental instead. Whether through a trigger or SP when Customer.Supplemental is updated it will update ModifyBy/Date in Customer.Master, and if a new customer is added to Customer.Master it sets ModifyBy/Date and CreatedBy/Date there and inserts a row into Customer.Supplemental with default values. Not partitioned tables per say, but they can be combined through a View when needed to bring Master and Supplemental together.Just curious if this is a methodology used by anyone else, or if there are other suggested paths.Thanks for any suggestions.SamWed, 23 Nov 2011 10:20:57 GMTsam.alexanderhttp://www.sqlservercentral.com/Forums/Topic1240517-373-1.aspxHi,We have database in our company comprising of about 800+ tables. Reason being, we try to use same database for variety of our customers sharing about 75% of features(and about 30 customers with independent features). My questions are: 1) How do some one control architect of this ever growing database, with new features implemented quarterly?2)How can DBA architect make sure DB sticks as close to industry standards. i.e. Non repetitive tables or columns.3)If there is already some junk columns(or redundant tables/view/S.P.) created in DB, how does some identify them?Any input will be appreciated.ThanksMon, 23 Jan 2012 15:09:03 GMTKal Pennhttp://www.sqlservercentral.com/Forums/Topic1240167-373-1.aspxSo just ran into a situation where I was asked to see if it would be a good idea or not to take our two production databases and merge them into one, or have one database as the code/engine and the other for the data.So my question: Why would you have one database for code/engine and a second for just the data? I have never heard of this setup so am curious.StephenJr. SQL DevMon, 23 Jan 2012 07:16:50 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1239772-373-1.aspxI need help with this small task that has become huge for me. I do appreceiate your guidance in advance.I have some data that I need to import into new tables in a SQL server database. The data consist of categfories, produts and scores.One product may belong to different categories and one category may have different products; therefore, I created a linked table that will take care of many-to-many categories and products.I think I got this issue solved as you can see in the below sudo-code.However, I am encontering a problem with the scoring table. How do I relate the scores to the product and the categories? Every product has a score value per category and every category has a score value per product.Any help will be greatly appreciated. [code="sql"]--Category TableCREATE TABLE Category(CategoryID int,CategoryName varchar(50))INSERT into Category VALUES (1,'Category1');INSERT into Category VALUES (2,'Category2');INSERT into Category VALUES (3,'Category3');INSERT into Category VALUES (4,'Category4');INSERT into Category VALUES (5,'Category5');--Category and Product Link Table to support many to manyCREATE TABLE CatProdLink(CategoryID int,ProductID int)--Product tableCREATE TABLE Product(ProductID int,ProductName varchar(150))INSERT INTO Product VALUES (1, 'ProductName1');INSERT INTO Product VALUES (2, 'ProductName2');INSERT INTO Product VALUES (3, 'ProductName3');--Score tableCREATE TABLE Score(ScoreID,ScoreValue)INSERT INTO Score VALUES (1, '1');INSERT INTO Score VALUES (2, '2');INSERT INTO Score VALUES (3, '3');INSERT INTO Score VALUES (4, '4');INSERT INTO Score VALUES (5, '5');INSERT INTO Score VALUES (6, '6');INSERT INTO Score VALUES (7, '7');INSERT INTO Score VALUES (8, '8');INSERT INTO Score VALUES (9, '9');INSERT INTO Score VALUES (10, '10');[/code]For excample:ProductName1 has score values so lets say 3 (1 through 10)but also ProductName1 has 5 categories and each category has a score (1 through 10)Fri, 20 Jan 2012 15:53:10 GMTJohnDBAhttp://www.sqlservercentral.com/Forums/Topic1234679-373-1.aspxHi, we have a SQL 2008 database in which one table contains images. The database backup size is now too big to backup and remotely copy.My requirement, which I wonder if anyone can help with, is to separate that table to another database, but have it linked seamlessly as though a table in the original database.Can that be done? Can anyone help?Thank you.Thu, 12 Jan 2012 05:34:48 GMTkevin.smith-1067125http://www.sqlservercentral.com/Forums/Topic1231612-373-1.aspxHi All,Can anybody suggest any good resources for building your first database and learning about how to design and create one. I have been an administrator for a few years, but never actually built a database from scratch. I would like to find some tutorials in either video,cbt or book form... well any form to be honest, that will teach me via practicals so that I can actually build one rather just learn the theory as thats how I learn best. I would prefer to be able to do this in sql 2008 as thats what most of my companies database servers are running.Thank you in advance.Fri, 06 Jan 2012 10:51:32 GMTacrutchleyhttp://www.sqlservercentral.com/Forums/Topic1228996-373-1.aspxSo basically the scenario is let's say I have 100,000 'items' and 1,000,000 users.Each user can rate each item 0 or 1 times. Let's assume the rating itself is just a 1,2,3,4,5.What's the best way to store the information on the user/item ratings such that - I can quickly determine whether a user has already rated an item - I can to whatever extent possible minimize the size of the rating table involvedI guess what I'm hoping for is something more elegant than what I have now which is a simple table with columnsUSERID ITEMID RATINGUltimately I'd like to avoid having this table have 100B rows if possible. Would it make sense to somehow use a binary field in the user table where each item is represented by a corresponding bit (so for the 1000th item if the user has rated it the bit is a 1 otherwise a 0)Mon, 02 Jan 2012 15:55:05 GMTbaal32http://www.sqlservercentral.com/Forums/Topic1215731-373-1.aspxthanks ...Fri, 02 Dec 2011 15:57:24 GMTtangoo111http://www.sqlservercentral.com/Forums/Topic1223853-373-1.aspxWere implementing poor man partition using base tables and a view on top. the base tables have currently an INT identity column as the primary key, but I was hoping to partition by year from the modifieddate. As per the books online to create an updatable partitioned view the partitioning column has to be part of the primary key. Which isn't to bad from a logical sense, other than the increasing the size of the primary key from just an INT, to an INT & DATETIME. So my question is, is it worth it to change this key, or to just implelement changes using update & insert triggers? As I see it my question is increasing the size of the primary key worth having & maintaining update/insert triggers? Are there other considierations? The entire view has about ~155 million rows with about ~65GB of total data.Thanks in advance for any adviceMon, 19 Dec 2011 08:30:50 GMTsean hawkeshttp://www.sqlservercentral.com/Forums/Topic1215990-373-1.aspx[b]Sign(MemberID, MemberName, ActivityID, ActivityName, SessionID, CoachID, CoachName, Day, From, To) The table Sign record the enrolment information of sports club members in sports activities. The information recorded are the the member name, member id, the activity name, the activity id, a session id that is unique within the same activity, the day, start and end times of the activity session enrolled, and the id and name of the coach supervising the activity session. Each session must be supervised by only one coach and the duration of all activity sessions is one hour. •what is functional dependencies that covers all the non-trivial dependencies[/b]My answer was [b]definition of functional dependencies (FD) [/b]SessionId->From, and SessionId->To mambId->mambName ActivityId->ActivityName coachId->CoachName, activityName[b]•what is all candidate keys for the relation Sign and choose a primary key[/b]candidate keys membId , activityId , coachId .[b]•What is the highest normal form to which the relation Sign conforms? Why?[/b]this relation is in first Normal Form[b]•Normalize the relation Sign to the next higher normal form. Indicate to which normal form(s) the resulting relations now conform? And why?[/b]3NF Sign(MemberID, ActivityID, SessionID, CoachID ) Member (MemberID, MemberName) Activity (ActivityID, ActivityName, Description) ActivitySession ([sessionID, ActivityID) Session (SessionID, Day, From, To) Coach (CoahID, Coach Name)[b] Can any Body help me to check it ..?[/b]Sun, 04 Dec 2011 10:00:24 GMTsun37http://www.sqlservercentral.com/Forums/Topic1215991-373-1.aspxCan anyone help me in this Question . found the entity type and the constraint , limitation, assumption and diagram Represent part of the mlitary command structure of the armies of world. It is desired to represent every command unit and all army personnel, along with their ranks and personal details. Each country has one army who has a Commander-In-Chief. Each army is divided into a number of field armies. Each field army has between 90,000 - 200,000 soldiers and is headed by a General. There is only one Field Marshall in each army. Not all generals head a field army. I think that entity type is : country army field I think the attribute of countru is the name and the primary key is code >> what you said country( countryCode, countryName) so the entity and there attribute but i am not sure it's correct country : name , code , army name Army (army name , country name , field army ) - field army : solders number , field army name , army name - Solders : name , ID , rank - General : army name , field army name , - Marshall : army name , ID , filed army name - commander – in chief: name ,ID , army name , country name So please if any one can help me??:crying:Sun, 04 Dec 2011 10:01:50 GMTsun37http://www.sqlservercentral.com/Forums/Topic1207045-373-1.aspxHello, I am having some issues in deciding how to store data taken from a specific device. The device is a spectrometer, that means it takes samples every minute. Each sample contains 1024 readings, one for each channel of the spectrometer. So a typical sample record from this device would be:Time: 2011-01-01 01:01:01DeviceID: 1Channel1Counts: 25Channel2Counts: 29Channel3Counts: 38...Channel1024Counts: 72The goal of this project is to get a device logging its data to a database and then to build a web app to view the data, look at trends, etc. How could I express this data in a table and keep the ability to assemble this data back into a given sample? I see only two ways:1.) (Wide solution) Structure a table with 1024+2 columns. Pros: the sample reading is held together in the database. Cons: requires a lot of columns (not even sure if this is getting close to the SQL limit) and if more channels are required in the future there will be lots of modifications to code.2.) (Tall solution) Structure two tables, 1 to create a unique sample ID and label it with a timestamp and Device ID, and another table that has unique sample ID as a foreign key and then has a column for "Counts Number" and "Channel Number". Pros: flexible solution and seems to be more relational based design. Cons: Makes assembling records harder, would need to use a view to do this likely. Also makes updating data harder. Suggestions? Wed, 16 Nov 2011 12:20:37 GMTloki1049http://www.sqlservercentral.com/Forums/Topic1207829-373-1.aspxI understand normalization, but where I get into trouble is the logical breakdown of what an entity ends and another begins. Many are clear cut, but others I can just go based on feel. Then when someone tries to violate this and I call them on it, I can't vocalize and justify where that line is.Example: A student entityStudentNamedobageethnicityincomeaddressSSNDrivers License numbergradeStatusschoolyearenrollmentdateTeacher (in a single teacher school)schoolhome languageprimary languagespecial edpregnantremedialever suspendedand the list can go on and on. I can strongly justify any breakout of a 1:many relationship. People still argue that a course a student takes is an attribute of the student, but nobody will argue that it belongs in the student entity because a student takes multiple courses. To me this is like saying that everything about a car is an attribute of the car, so the car table can be a flat 800 fields. I know it's not true, but what is the distinction where you draw the lines?IMO, a student entity is basically the most immediate information about what a student is. Maybe just their name, SSN, birthdate. A lot more can be broken out into StudentDemographic, Studentgrade, StudentAddress, studentethnicity.The issue that comes along with this is that this table with 90 fields on it, might only have 20 required upon enrollment and only 3 required to be entered into the system at all before enrollment (name, ssn).I can argue the normalization of having all of the null fields, but the non dbas would rather have this than what they assume would be one entity broken out amongst 80 tables to accomidate the systems nullability rules. It's not any one thing in specific, I'm just looking for pointers where I can just know and argue where these divisions lie.Thu, 17 Nov 2011 12:33:21 GMTShawnTherrien