SQL SERVER – Contest – Summary of 5 Day and Additional Information

 

I am overwhelmed with the response of our contest ran earlier this week. Every day we are giving away USD 198 worth give aways to readers in USA and India. If you have not participated so far, I encourage you to participate today itself. Here are links to our 5 day contest. The winner of the contest will be announced on August 20th.

Here are a few important notes related to the contest.

A few people asked me what should they do as they have forgotten to mention their country in the response. Please resubmit with correct data, we will only consider latest entry from one person.

What if you are not from the USA or India?
Participate in the Bonus Quiz. Leave a comment for each of the questions above with your favorite article and you may be eligible for winning something cool.

What if I am winner of two contests out of 5 contests?
Well, in that case, we will send you one set of Combo Kit and Amazon Gift Card of USD 100 for another contest which you won.

Can I exchange my kit with other stuff?
No, if you do not want kit, give it to someone who needs it.

Btw, I strongly suggest that you participate in the Bonus Quiz. There is something cool for everyone!

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

 

 

 

 

SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5

combos SQL SERVER   Understanding XML   Contest Win Joes 2 Pros Combo (USD 198)   Day 5 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:

Is following XML a well formed XML Document?

<?xml version=”1.0″?>
<address>
<firstname>Pinal</firstname>
<lastname>Dave</lastname>
<title>Founder</title>
<company>SQLAuthority.com</company>
</address>

a) Yes
b) No
c) I do not know

Query Hints:

BIG HINT POST

A common observation by people seeing an XML file for the first time is that it looks like just a bunch of data inside a text file. XML files are text-based documents, which makes them easy to read.  All of the data is literally spelled out in the document and relies on a just a few characters (<, >, =) to convey relationships and structure of the data.  XML files can be used by any commonly available text editor, like Notepad.

Much like a book’s Table of Contents, your first glance at well-formed XML will tell you the subject matter of the data and its general structure. Hints appearing within the data help you to quickly identify the main theme (similar to book’s subject), its headers (similar to chapter titles or sections of a book), data elements (similar to a book’s characters or chief topics), and so forth. We’ll learn to recognize and use the structural “hints,” which are XML’s markup components (e.g., XML tags, root elements).

The XML Raw and Auto modes are great for displaying data as all attributes or all elements – but not both at once. If you want your XML stream to have some of its data shown in attributes and some shown as elements, then you can use the XML Path mode. If you are using an XML Path stream, then by default all values will be shown as elements. However, it is possible to pick one or more elements to be shown with an attribute(s) as well.

Additional Hints:

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

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)

SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5

combos SQL SERVER   Expanding Views   Contest Win Joes 2 Pros Combo (USD 198)   Day 4 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 key word will force the query to use indexes created on views?

a) ENCRYPTION
b) SCHEMABINDING
c) NOEXPAND
d) CHECK OPTION

Query Hints:

BIG HINT POST

Usually, the assumption is that Index on the table will use Index on the table and Index on view will be used by view. However, that is the misconception. It does not happen this way. In fact, if you notice the image, you will find the both of them (table and view) use both the index created on the table. The index created on the view is not used. The reason for the same as listed in BOL.

The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don’t initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn’t reference the view. If they are close, this may give you the confidence that the decision of whether or not to use the indexed view doesn’t matter.

Additional Hints:

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

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)

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

combos 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)

SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5

combos SQL SERVER   Identity Fields   Contest Win Joes 2 Pros Combo (USD 198)   Day 2 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 statement is incorrect?

a) Identity value can be negative.
b) Identity value can have negative interval.
c) Identity value can be of datatype VARCHAR
d) Identity value can have increment interval larger than 1

Query Hints:

BIG HINT POST

A simple way to determine if a table contains an identity field is to use the SSMS Object Explorer Design Interface. Navigate to the table, then right-click it and choose Design from the pop-up window. When your design tab opens, select the first field in the table to view its list of properties in the lower pane of the tab (In this case the field is ProductID). Look to see if the Identity Specification property in the lower pane is set to either yes or no. SQL Server will allow you to utilize IDENTITY_INSERT with just one table at a time. After you’ve completed the needed work, it’s very important to reset the IDENTITY_INSERT back to OFF.

Additional Hints:

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

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)

SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5

combos SQL SERVER   Query Hint   Contest Win Joes 2 Pros Combo (USD 198)   Day 1 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 queries will return dirty data?

a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)

Query Hints:

BIG HINT POST

Most SQL people know what a “Dirty Record” is. You might also call that an “Intermediate record”. In case this is new to you here is a very quick explanation. The simplest way to describe the steps of a transaction is to use an example of updating an existing record into a table. When the insert runs, SQL Server gets the data from storage, such as a hard drive, and loads it into memory and your CPU. The data in memory is changed and then saved to the storage device. Finally, a message is sent confirming the rows that were affected.

For a very short period of time the update takes the data and puts it into memory (an intermediate state), not a permanent state. For every data change to a table there is a brief moment where the change is made in the intermediate state, but is not committed. During this time, any other DML statement needing that data waits until the lock is released. This is a safety feature so that SQL Server evaluates only official data.

For every data change to a table there is a brief moment where the change is made in this intermediate state, but is not committed. During this time, any other DML statement (SELECT, INSERT, DELETE, UPDATE) needing that data must wait until the lock is released. This is a safety feature put in place so that SQL Server evaluates only official data.

Additional Hints:

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

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)

SQL SERVER – Quiz and Video – Introduction to Discovering XML Data Type Methods

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer. In the article we discussed various basics terminology of the XML. The article further covers following important concepts of XML.

  • What are XML Data Type Methods
  • The query() Method
  • The value() Method
  • The exist() Method
  • The modify() Method

Above five are the most important concepts related to XML and SQL Server. There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1.) Which method returns an XML fragment from the source XML?

  1. query( )
  2. value( )
  3. exist( )
  4. modify( )
  5. All of them
  6. Only query( ) and value( )

2.) Which XML data type method returns a “1” if found and “0” if the specified XPath is not found in the source XML?

  1. query( )
  2. value( )
  3. exist( )
  4. modify( )
  5. All of them
  6. Only query( ) and value( )

3.) Which XML data type method allows you to pick the data type of the value that is returned from the source XML?

  1. query( )
  2. value( )
  3. exist( )
  4. modify( )
  5. All of them
  6. Only query( ) and value( )

4.) Which method will not work with a SQL SELECT statement?

  1. query( )
  2. value( )
  3. exist( )
  4. modify( )
  5. All of them
  6. Only query( ) and value( )

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 1

2) 3

3) 2

4) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

combos SQL SERVER   Quiz and Video   Introduction to Discovering XML Data Type Methods

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

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

SQL SERVER – Quiz and Video – Introduction to SQL Error Actions

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to SQL Error Actions – A Primer. In the article we discussed various basics terminology of the error handling. The article further covers following important concepts of error handling.

  • Introduction to SQL Error Actions
  • Statement Termination
  • Scope Abortion
  • Batch Termination

Above three are the most important concepts related to error handling and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1.) Which SQL Server error action happens for errors with a severity of 11-16 when you set the XACT_ABORT setting to ON?

  1. You will get Statement Termination.
  2. You will get Scope Abortion.
  3. You will get Batch Abortion.
  4. You will get Connection Termination.
  5. SQL Server will pick the error action.

2.) Which SQL Server error action happens for errors with a severity of 11-16 when you set the XACT_ABORT setting to OFF?

  1. You will get Statement Termination
  2. You will get Scope Abortion
  3. You will get Batch Abortion
  4. You will get Connection Termination
  5. SQL Server will pick the error action

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 5

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

combos SQL SERVER   Quiz and Video   Introduction to SQL Error Actions

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

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

SQL SERVER – Quiz and Video – Introduction to Basics of a Query Hint

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Basics of a Query Hint – A Primer. In the article we discussed various basics terminology of the query hints. The article further covers following important concepts of query hints.

  • Expecting Seek and getting a Scan
  • Creating an index for improved optimization
  • Implementing the query hint

Above three are the most important concepts related to query hint and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have the following query:

DECLARE @UlaChoice TinyInt
SET @Type = 1
SELECT *
FROM LegalActivity
WHERE UlaChoice = @UlaChoice

You have a nonclustered index named IX_Legal_Ula on the UlaChoice field. The Primary key is on the ID field and called PK_Legal_ID 99% of the time the value of the @UlaChoice is set to ‘YP101’. What query will achieve the best optimization for this query?

  1. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    WITH(INDEX(X_Legal_Ula))
  2. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    WITH(INDEX(PK_Legal_ID))
  3. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    OPTION (Optimize FOR(@UlaChoice = ‘YP101’))

2) You have the following query:

SELECT *
FROM CurrentProducts
WHERE ShortName = ‘Yoga Trip’

You have a nonclustered index on the ShortName field and the query runs an efficient index seek. You change your query to use a variable for ShortName and now you are using a slow index scan.

What query hint can you use to get the same execution time as before?

  1. WITH
  2. LOCK
  3. FAST
  4. OPTIMIZE FOR
  5. MAXDOP
  6. READONLY

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

combos SQL SERVER   Quiz and Video   Introduction to Basics of a Query Hint

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

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

SQL SERVER – Quiz and Video – Introduction to Hierarchical Query using a Recursive CTE

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basics terminology of the CTE. The article further covers following important concepts of common table expression.

  • What is a Common Table Expression (CTE)
  • Building a Recursive CTE
  • Identify the Anchor and Recursive Query
  • Add the Anchor and Recursive query to a CTE
  • Add an expression to track hierarchical level
  • Add a self-referencing INNER JOIN statement

Above six are the most important concepts related to CTE and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have an employee table with the following data.

EmpID FirstName LastName MgrID
1 David Kennson 11
2 Eric Bender 11
3 Lisa Kendall 4
4 David Lonning 11
5 John Marshbank 4
6 James Newton 3
7 Sally Smith NULL

You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result?

  1. WITH EmpList AS
    (SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID,
    1 AS Lvl
    FROM Employee AS Boss WHERE Boss.MgrID IS NULL
    UNION ALL
    SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1
    FROM Employee AS E INNER JOIN EmpList
    ON E.MgrID = EmpList.EmpID)
    SELECT * FROM EmpList
  2. WITH EmpListAS
    (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
    FROM Employee WHERE MgrID IS NULL
    UNION ALL
    SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl )
    SELECT * FROM BossList
  3. WITH EmpList AS
    (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
    FROM Employee WHERE MgrID is NOT NULL
    UNION
    SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1
    FROM Employee INNER JOIN EmpList BossList
    ON Employee.MgrID = BossList.EmpID)
    SELECT * FROM EmpList

2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment:

WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
AS (
–PICK ANSWER CODE HERE
)

  1. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
    FROM Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level]
    FROM Employee emp JOIN Employee mgr
    ON emp.ManagerID = mgr.EmployeeId
  2. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
    FROM Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
    FROM EmployeeList mgr JOIN Employee emp
    ON emp.ManagerID = mgr.EmployeeId

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 1

2) 2

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

combos SQL SERVER   Quiz and Video   Introduction to Hierarchical Query using a Recursive CTE

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

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