Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2012-05-21 : 06:33:23
|
| /*The table below shows an existing table which has over 100 Million records...Everyday, around half a million of records are added to it.Sometimes this table is being updated like: update tblMain set LoginName = @LoginName where ... Question: 1- Do you think the design of this query is incorrect? why 2- Should there be MainID column as identity with Primary Key and leave the other columns to be without the Primary Key but set index(es) on the relevant columns?Thanks*/CREATE TABLE [dbo].[tblMain]( [ID] [int] NOT NULL, [Currency] [char](3) NOT NULL, [Code] [varchar](50) NOT NULL, [CurrencyPct] [float] NOT NULL, [loginName] [varchar](50) NOT NULL, [MyDate] [datetime] NOT NULL, [Reference] [int] NOT NULL, [Flag1] [bit] NOT NULL, [Flag2] [bit] NOT NULL, CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED ( [ID] ASC, [Currency] ASC, [Code] ASC, [LoginName] ASC, [MyDate] ASC, [Reference] ASC, [Flag1] ASC, [Flag2] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-21 : 16:47:12
|
| i would have thought of adding an identity column and making it primary key. Also Instead of Flag1,Flag2 etc as separate column i would have added them in separate table with primary column of current table as fk and then category field to indicate type (Flag1,Flag2) and finally a value field to store actual value. This will be more scalable as addition of new flags will be just addition of new records to this table rather than adding a new column to tblMain each time.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2012-05-22 : 07:59:16
|
| What is the dis-advantage of having those fields as PK in the table? |
|
|
|
|
|
|
|
|
table design advice