General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>>>Hi all,
>>>>
>>>>When adding records with views I don't where is the best place to create the primary key, the view or the table.
>>>>I generate the PK with a newId() function, and I don't know whether to put it on the default value of the view field or the default value of the table itself. I "have" to put the code in the table just in case I don't use the view; so I thought that if I inserted the newId() in the view I would end up creating twice the PK (and wasting PK), but I tested and it was not the case. So I don't really know what should I do from a "good practices" point of view.
>>>>
>>>>TIA,
>>>>Javier.
>>>
>>>Javier,
>>>Personal suggestion: Both and mark key updatable. A newid() function returning GUID fit into this perfectly. Key would be 'at least' 16 bytes so a trade off there.
>>>PS:SQL server NewID() function returns a GUID.
>>>Cetin
>>
>>Cetin, thanks for the answer. I think I'm going your way, but I will not use GUIDs. I don't like them because of performance issues (in some informal testing I made, SQL selects where 20%-30% slower using 16 bytes GUIDs than 4 byte integers).
>
>Javier,
>As I said it's a trade off. On the long run you might be glad you wasted 20-30% performance (I didn't test at all in depth).
>Integer keys are not for replication and if it's never a possibility to merge data from different sources then integers might do it very well.
>Cetin
In this solution I don't plan to merge data from different locations (I know, I know, whenever you say "I don't plan" you end up doing it); and performance is somewhat a priority.
What I found in my informal testings is that performance degrades as the PK fields grow in size. I found that a 4 byte character was pretty similar to a 4 byte integer. But as the size of the field grew over 7 bytes you started to notice less performance.
I have always taken a difference of less than 10% in performance as not noticiable by the user. This 10% is not a scientific figure, it is an approximation I did. I would like to know if there is a paper on this. So as long as performance gets into this 10% I don't sacrifice it. But 20%-30% was too much in this project.
Thanks for your answer,
Javier.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only