1

  1. Why use rowguid and what are the benefits?

  2. Suppose a company has thousands of customers, is it a good approach to divide them on the basis of their gender for the performance and fast query if no then why?

  3. How do large companies like Facebook handle their primary key for their comments, users and for other things for example:

    Suppose there are five users with primary key 1,2,3,4,5...

    What if user 3 is deleted from there, now it's 1,2,4,5 will be left, which is kind of gap between continuous chain. How do they deal with it?

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Saqy Gee
  • 43
  • 8
  • 2
    You shouldn't try to write question in title. Keep the title simple and the ask the question (with extra information) in the body – musefan Oct 28 '11 at 09:39
  • 1
    For #2: you have at most three genders - male, female, company - and that doesn't really help with the large number of rows. Also: a table with thousands of rows (for all your clients) is **nothing** for SQL Server - it can handle hundreds of millions of rows in a single table, no problem. – marc_s Oct 28 '11 at 09:41

1 Answers1

3

  1. Don't know - maybe you use a non-auto value so you can keep it constant across other databases (maybe for use with 3rd part integration etc.)

  2. Do not divide on a field such as gender, when you don't know gender (or want a full list) you are going to have to search two tables, also when you want to add other filtering/searching you will have to do over multiple tables again

  3. So what if there is a gap in the ID chain - it does not effect anything. Why would you think it is important?

Curtis
  • 101,612
  • 66
  • 270
  • 352
musefan
  • 47,875
  • 21
  • 135
  • 185
  • @SaqyGee: No, do not divide a table. as marc_s said in comment, SQL Server can handle it without performance worry. Splitting a table into 2 lots of 1000 rows will probably take more of a performance hit then 1 table with 10000 rows (if not more) – musefan Oct 28 '11 at 10:14
  • thx for answer but 2nd question i just supposed an example to be more exact lets suppose if its a category of stackoverflow sql server and c# where people ask questions regarding these technologies so than is it a good approach to divide them on the basis of these respected tech's and in the 3rd question i consider that important matter..suppose that int can save upto 8 numbers where there are 1,2,3,4,5 users where user 3,4,5 is deleted than in database 1,2 is left what if more user register PK increment's 1,2,6,7,8 and 9 causes overflow but we still got the index 3,4,5 which went wasted – Saqy Gee Oct 28 '11 at 10:29
  • 1
    @SaqyGee: **no, it's NOT a good idea** to artificially split up your tables! Analyse your queries, have the proper indices - and your SQL Server will handle **hundreds of millions of rows** in a single table... – marc_s Oct 28 '11 at 10:48
  • @SaqyGee: What do you mean "int can save upto 8 numbers"? I think you have something seriously wrong with your application here – musefan Oct 28 '11 at 11:47
  • no no brother dont get me wrong i know integer can save upto large amount of values actually i am building database for my own web and i said suppose int can save 8 numbers ....u know website holds really big amount of the data in it like facebook where millions of people post comments daily and from posted comments 1000's are also being deleted by users ...and maybe facebook also deletes the older comments so if they dont manage the gap between their primary keys their PK will keep on incrementing and one day it will be overflow ..and we know this wont happened they got some technique---> – Saqy Gee Oct 28 '11 at 13:27
  • --->to handle this problem my question was how they handle such a problem cauz integer dosnt allowed you unlimited increments they must have a solution to it ...whats that solution i am not a Database master thats why i am asking you profs..besides this other two questions are crystal clear to me ..thanx for that i hope i have cleared my question enough... – Saqy Gee Oct 28 '11 at 13:31
  • @SaqyGee: OK, I understand what you mean now. I would say this is maybe where facebook uses a GUID ID (or something similar) if you look at the URL for a facebook message you will see it is not a typical int value, my best guess would be they use a string for ID (this may also help them matching up posts across there many database servers), I wouldn't worry though, if you ever get as big as Facebook, you can pay someone else to fix the problem ;) - besides, I doubt facebook works anything like it did when it was first release, you start with the basics and build as required – musefan Oct 28 '11 at 13:39
  • hahaha i like ur style "big as facebook and pay them money to fix the problem" :) by discussing with u i got a way around it i just want to share i will logically delete the row by making delete column which will use bit data type so whenever user deletes the comments i will simply set bit to 1 and when any other user makes a comment i will simply update those rows one by one which having value deleted=1 ..so from this aproch there will no gap in PK's what do u say about this i think it will work triggers are the jiggos they will help me out :) so waddaya think buddy :) – Saqy Gee Oct 28 '11 at 18:21
  • I guess it could work, but check out [this link](http://stackoverflow.com/questions/261815/upper-limit-for-autoincrement-primary-key-in-sql-server) there are some interesting thoughts, like the one about reseeding if you do hit the limit – musefan Oct 28 '11 at 20:59
  • that was a scary post but helped me alot in understanding of basic concept overflow is after all a nightmare but i will try to find a way like facebook handled the data PK's may be i find out prof.s way i will share with u when i will find... – Saqy Gee Oct 29 '11 at 08:00
  • yeah i found out they are using simple int for their profile management or it could be big int which cant be overflow for sure ;p i incremented their profile id its integer 1,2,3 are unavilable but 4 was mark zakberger the owner of fb... – Saqy Gee Oct 29 '11 at 11:05