Best practice when to use an index
-
Mikey:
If you are joining tabA with tabB ON tabA.[Date] BETWEEN tabB.StartDate AND tabB.EndDate I have absolutly no problem whatsoever. I do not see any reason for not doing that either (preferrably using a clustered index on that one though). And if reading my last post you could see my disussion behind it.
Though this was not my initial thought with my prior post. That was when you were explicitly WHEREing instead of ONing (which of course yields the same result in some cases)
Then again, JOINing on a DateTime field should not have any worse performance in any way compared with another similar join with another 8 bytes field!
Regards, Hans!
-
quote:
I don't understand your point? Why do you so forcefully express that your Identity column HAVE to be your primary key?
Why would anyone bother to have an identity column if it is not to be used for the primary key?
I don't do joins on dates, and where possible I try and avoid joins on CHAR and VARCHAR fields. My preference is to join on INT fields.
I read somewhere that although you can specify 8, 16 and 32 bit integers windows converts these all into 32 bit integers in any case so byte and smallint were superfluous and you don't really save any storage capacity. I'm not sure if this is still true, but until I find out otherwise I'm going to stick with it.
I've been putting my clustered index on my date column because the most common query performed by users is dependent on a date range and there aren't necessarily joins in their queries.
-
Hi Hans,
quote:
I don't understand your point? Why do you so forcefully express that your Identity column HAVE to be your primary key?
correct me if I'm wrong, but when I make a column identity it is already some kind of index.
The following is a snippet from BOL.
...
The IDENTITY property is typically used to automatically generate unique identification numbers or primary keys.
..
I'm not saying your identity column MUST be your primary key, but at a minimum level it is not 'bad practice' to do so.
As David wrote I, too, like joining on int rather than anything else, and unless someone else proofs me wrong, I'll stick to this behaviour.
As for indexing datetime columns:
I save all my emails in SQL Server DB utilizing CDO. There are at least two datetime values in an email that are of importance. Date Sent and Date Received. So it is pretty logical to me to include these fields in my clustered index. And the Index tuning wizard proofed me right.
But, I think my thread has turned into something else. to get back to the origin, I decided to leave my PrimaryKey on this parameter tables.
However, I've have found a very promising alternative, I'm trying out right now. The keyword is 'disconnected recordsets'.
Let's keep the thread alive!
Any comments on this?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello Frank 🙂
Well, don't get me wrong. I also like joining on INTs. But the case of always using your IDENT as your PK seems to me like a waste, why are you using a PK in this case anyway?
Putting a PK (UNIQUE constraint) on an IDENT is quite puzzeling to me. (BTW, an IDENT does not imply an index, just the storing of the value IDENT_CURRENT('table_name') ) If anyone would see a reason for enforcing UNIQUEness with a PK on a AutoIncrement, please tell me 🙂
Of course for the people that thinks that a PK on this column is the only way of doing FKs , this seems like the only approach, but for the rest (most people here at sqlservercentral!) knowing that a ordinary UNIQUE index would do the same thing I would like to know their point of view.
So guys (and Girls, or maybe I should say Women 🙂 I am here, waiting patiently for getting a better understanding of the world around me!
Regards, Hans!
-
quote:
I read somewhere that although you can specify 8, 16 and 32 bit integers windows converts these all into 32 bit integers in any case so byte and smallint were superfluous and you don't really save any storage capacity. I'm not sure if this is still true, but until I find out otherwise I'm going to stick with it.
On disk storage for these datatypes will be 8, 16 and 32 bits respectively. If you saw my post earlier in this thread, my point was that for important reference or lookup tables, keeping row size as small as possible is important for minimising disk I/O (although in practice, small, frequently used reference tables will be cached in memory anyway).
In-memory storage is a different thing though, and I can't really comment on whether Windows "expands" any of the integer types to 32-bits or not. It would depend on the internal implementation of SQL Server.
--
Si Chan
Database Administrator
-
Hi Hans,
quote:
Well, don't get me wrong.
I won't, your opinion is very welcome!
What a nice coincidence, someone else replied to this article
today.
I'm going to read this first, before going any further
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hans,
If you use Full Text Search facilities then you must have a primary key because SQL Server saves various status flags and other bits and bobs in the primary key.
I know that primary keys don't allow nulls and that a unique index does, but from a performance point of view I don't know what other differences there are.
-
quote:
http://qa.sqlservercentral.com/columnists/awarren/worstpracticesnotusingprimarykeysandclusteredindex.aspI'm going to read this first, before going any further
I must admit after reading that thread I'm still there where I was before. There a lots of pro and cons on identities, PK, FK and so on.
Can we come over to conclude, that Identities CAN make life easier, but MUST NOT neccessarily and that they have a reason for being there? And that it is NOT 'bad practice' to use them?
Posted with massively confused mind
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello Everyone!
quote:
Can we come over to conclude, that Identities CAN make life easier, but MUST NOT neccessarily and that they have a reason for being there? And that it is NOT 'bad practice' to use them?
I would agree 🙂
I like IDENTs, they really do make life easier in alot of cases. I also really like PKs. And I like using them both in my tables.
The combination of making a PK on a IDENT is the only thing that I have doubts on. I would prefer the theory behind Relational DB's, that PKs are there for Explicitly pointing to the Logic of the table. Of course, if you have your IDENT in your column, that shows also why the table is there, but say putting the PK on the fields that the ID is a 'substitue' for, makes a whole lot more sense to me.
Regards, Hans!
P.S Thanx everyone for giving your point of view 🙂
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply