SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5
- by pinaldave
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 | Indiaplaza
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.
SQL Joes 2 Pros Development Series – All about SQL Statistics
SQL Joes 2 Pros Development Series – Introduction to Page Split
SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding
SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth
SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data
SQL Joes 2 Pros Development Series – System and Time Data Types
SQL Joes 2 Pros Development Series – Data Row Space Usage and NULL Storage
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)
Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology