SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5

August 2011 we ran a contest where every day we give away one book for an entire month. The contest had extreme success. Lots of people participated and lots of give away. I have received lots of questions if we are doing something similar this month. Absolutely, instead of running a contest a month long we are doing something more interesting. We are giving away USD 198 worth gift every day for this week. We are giving away Joes 2 Pros 5 Volumes (BOOK) SQL 2008 Development Certification Training Kit every day. One copy in India and One in USA. Total 2 of the giveaway (worth USD 198). All the gifts are sponsored from the Koenig Training Solution and Joes 2 Pros.

The books are available here Amazon | Flipkart 

How to Win:

  • Read the Question
  • Read the Hints
  • Answer the Quiz in Contact Form in following format
    • Question
    • Answer
    • Name of the country (The contest is open for USA and India residents only)
  • 2 Winners will be randomly selected announced on August 20th.

Question of the Day:

Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

a) INT
b) BIGINT
c) GUID
d) SMALLINT

Query Hints:

BIG HINT POST

The clustered index is the placement order of a table’s records in memory pages. When you insert new records, then each record will be inserted into the memory page in the order it belongs. In the figure below we see another new record (Major Disarray) being inserted, in sequence, between Jonny and Rick. Since there is no room in this memory page, some records will need to shift around. The page split occurs when Irenes’ record moves to the second page. Page splits are considered very bad for performance, and there are a number of techniques to reduce, or even eliminate, the risk of page splits.

You can create a clustered index on the table on any field you choose. Sometime SQL will create a clustered index for you. Often times the field having the Primary Key makes a great candidate for the clustered index.

Additional Hints:

I have previously discussed various concepts from SQL Server Joes 2 Pros Volume 3.

Next Step:

Answer the Quiz in Contact Form in following format

  • Question
  • Answer
  • Name of the country (The contest is open for USA and India)

Bonus Winner

Leave a comment with your favorite article from the “additional hints” section and you may be eligible for surprise gift. There is no country restriction for this Bonus Contest. Do mention why you liked it any particular blog post and I will announce the winner of the same along with the main contest.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

23 thoughts on “SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5

  1. Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

    c) GUID

    Country: India

    Favorite Article:
    SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding

    Like

  2. GUID is not best option for primary key anf clustered index both.it is better to break GUID-based Primary Clustered Key into two separate key – the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column. In fact it is better to have guid datatype as alternate key.GUID fill ratio for index page is terrible.

    Like

    • Question: Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

      Answer: c) GUID

      Country: INDIA

      Like

  3. Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

    Ans:- c).GUID

    Additional Hints:

    SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth

    This article is related with spatial data i really interested in working with spatial data as i’m GIS developer using Spatial Database Engine (SDE) and the scenario with excellent presentation.

    Like

  4. Clustered Index is very detailed explained in
    SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding.So i would prefer this as my favorite article

    Like

  5. Hi Pinal,
    GUID would not be a good candidate for clustered index since it is a wide data type, clustered indexes are preferred to small,narrow,unique and ever increasing(preferably in order to avoid fragmentation).

    Country: USA
    Name: Ramdas Narayanan

    Like

  6. Question: Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

    Answer: c) GUID

    Country: USA

    Like

  7. Question: Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

    Answer: c) GUID
    Country: INDIA

    Like

    • Question: Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

      Answer: c) GUID
      Country: INDIA

      Favourite article:
      SQL Joes 2 Pros Development Series – Data Row Space Usage and NULL Storage

      Like

  8. Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?
    Answer:- c) GUID
    Country:- India

    Like

  9. Hi Sir,

    SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth

    is one of the best post that I loved trying out.

    The demonstration of the same in the Microsoft TECHED was an eye catcher.
    That Ram was actually right in calculating the shortest distance to lanka… :)

    Like

  10. Pingback: SQL SERVER – Contest – Summary of 5 Day and Additional Information « SQL Server Journey with SQL Authority

  11. Hi Pinal,
    I liked your blog post
    SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth

    In this blog Geography datatype explained very well and it will help me in future if I need to implement some solution using Geography datatype.

    Best Regards,
    S Kumar

    Like

  12. Hi Pinal,

    In this additional HInts section, i am most liked link is below :
    SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth

    Reason : To know the new thing available in SQL, implemeting this to know the accurate reading between different locations. To avoid improper datatypes for this calculation.

    Like

  13. Hello Pinal,
    Answer C: GUID. It is a bad idea to use columns defined as GUID datatype for a Primay key or a clustered index

    Country: USA

    Like

  14. Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

  15. Pingback: Here comes my Free Copy of Joes 2 Pros Combo – Courtesy Pinal Dave « Datta's Ramblings on Business Intelligence 'N' Life

  16. Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

    c) GUID

    Country: India

    Additional Hints:

    SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding

    Like

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s