|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Theory on composit keyI know that it is good practice not to use data as keys.
Would one think of a smalldatetime as data ? My situation is that i collect historical information on financial instruments. I have a price table with a composit primary key of instumentId and smalldatetime(but no time). Besides the key, I have a value for the instrument. I think it is ok to use the smalldatetime, but my collegue dissagrees and I yould like a second opinion. -- Best regards Mikael Mikael
>I know that it is good practice not to use data as keys. What did you mean? Can you show us your table's structure?>Would one think of a smalldatetime as data ? Show quote "Mikael" <Mik***@discussions.microsoft.com> wrote in message news:C87DFC32-7FC0-42EA-84A9-46268EF539C2@microsoft.com... >I know that it is good practice not to use data as keys. > Would one think of a smalldatetime as data ? > > My situation is that i collect historical information on financial > instruments. > I have a price table with a composit primary key of instumentId and > smalldatetime(but no time). Besides the key, I have a value for the > instrument. > > I think it is ok to use the smalldatetime, but my collegue dissagrees and > I > yould like a second opinion. > > > -- > Best regards > > Mikael Yes:
CREATE TABLE price ( [InstrId] int not null, [Stamp] smalldatetime not null, [Price] decimal(28,10) not null, primary key ([InstrId],[Stamp]) ) [InstrId] is a forign key to another table that contains the instruments characteristics. -- Show quoteBest regards Mikael "Uri Dimant" wrote: > Mikael > >I know that it is good practice not to use data as keys. > >Would one think of a smalldatetime as data ? > > What did you mean? Can you show us your table's structure? > > > > "Mikael" <Mik***@discussions.microsoft.com> wrote in message > news:C87DFC32-7FC0-42EA-84A9-46268EF539C2@microsoft.com... > >I know that it is good practice not to use data as keys. > > Would one think of a smalldatetime as data ? > > > > My situation is that i collect historical information on financial > > instruments. > > I have a price table with a composit primary key of instumentId and > > smalldatetime(but no time). Besides the key, I have a value for the > > instrument. > > > > I think it is ok to use the smalldatetime, but my collegue dissagrees and > > I > > yould like a second opinion. > > > > > > -- > > Best regards > > > > Mikael > > > Mikael
http://www.sql-server-performance.com/clustered_indexes.asp http://www.sql-server-performance.com/composite_indexes.asp Show quote "Mikael" <Mik***@discussions.microsoft.com> wrote in message news:1A438DD4-C93C-4F86-BB00-844B682EF44D@microsoft.com... > Yes: > CREATE TABLE price > ( > [InstrId] int not null, > [Stamp] smalldatetime not null, > [Price] decimal(28,10) not null, > primary key ([InstrId],[Stamp]) > ) > > > [InstrId] is a forign key to another table that contains the instruments > characteristics. > > -- > Best regards > > Mikael > > > "Uri Dimant" wrote: > >> Mikael >> >I know that it is good practice not to use data as keys. >> >Would one think of a smalldatetime as data ? >> >> What did you mean? Can you show us your table's structure? >> >> >> >> "Mikael" <Mik***@discussions.microsoft.com> wrote in message >> news:C87DFC32-7FC0-42EA-84A9-46268EF539C2@microsoft.com... >> >I know that it is good practice not to use data as keys. >> > Would one think of a smalldatetime as data ? >> > >> > My situation is that i collect historical information on financial >> > instruments. >> > I have a price table with a composit primary key of instumentId and >> > smalldatetime(but no time). Besides the key, I have a value for the >> > instrument. >> > >> > I think it is ok to use the smalldatetime, but my collegue dissagrees >> > and >> > I >> > yould like a second opinion. >> > >> > >> > -- >> > Best regards >> > >> > Mikael >> >> >> "Uri Dimant" <u***@iscar.co.il> wrote in message From that page:news:%23fipG1lTGHA.2244@TK2MSFTNGP14.phx.gbl... > Mikael > http://www.sql-server-performance.com/composite_indexes.asp "A composite index is an index that is made up of more than one column. In some cases, a composite index is also a covering index. See this URL for information on covering indexes. Generally speaking, composite indexes (with the exception of covering indexes) should be avoided. This is because composite indexes tend to be wide, which means that the index will be larger, requiring more disk I/O to read it, hurting performance." This is plain bad advice. A non-covering composite index is very useful if a sargable query matches the indexed columns for example. That last sentence is no argument at all. The index is as large as the data - no more and no less. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Well , I think that author means that we try to avoid creating an index on
'wide' column My understanding is if the the columns col1,col2..... have a complosite index it is considered covered Show quote \"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:%23xLFDBmTGHA.1236@TK2MSFTNGP11.phx.gbl... > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:%23fipG1lTGHA.2244@TK2MSFTNGP14.phx.gbl... >> Mikael >> http://www.sql-server-performance.com/composite_indexes.asp > > From that page: > "A composite index is an index that is made up of more than one column. In > some cases, a composite index is also a covering index. See this URL for > information on covering indexes. Generally speaking, composite indexes > (with the exception of covering indexes) should be avoided. This is > because composite indexes tend to be wide, which means that the index will > be larger, requiring more disk I/O to read it, hurting performance." > > This is plain bad advice. A non-covering composite index is very useful if > a sargable query matches the indexed columns for example. That last > sentence is no argument at all. The index is as large as the data - no > more and no less. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > > "Mikael" <Mik***@discussions.microsoft.com> wrote in message That's nonsense.news:C87DFC32-7FC0-42EA-84A9-46268EF539C2@microsoft.com... >I know that it is good practice not to use data as keys. > Would one think of a smalldatetime as data ? If a unique constraint on the datetime makes sense as a business rule then > > My situation is that i collect historical information on financial > instruments. > I have a price table with a composit primary key of instumentId and > smalldatetime(but no time). Besides the key, I have a value for the > instrument. > > I think it is ok to use the smalldatetime, but my collegue dissagrees and > I > yould like a second opinion. create a key for it. That doesn't stop you adding an artificial surrogate key if you want to - I expect that's what your colleague had in mind. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx --  |
|||||||||||||||||||||||
Other interesting topics