[ ABOUT THE AUTHOR ]

Since the both the organized sessions were unexpectedly cancelled at the last SQL Server Sri Lanka User Group meeting that was scheduled for April 2008, I had to prepare something for the audience to cover up the planned sessions. I had no time to prepare a comprehensive presentation that speaks about a specific subject, hence made a small questionnaire and discussed with the audience. That went nicely and become worthwhile to all the attendees. I decided to add them as an article to the site, rather than just a presentation; here is part 1 of the questionnaire.

Below questions are explained with this article:

• Question related to CROSS JOINs.

• What is the different between INTERSECT and INNER JOIN?

• How many ranking functions available with SQL Server 2005?

• What is the different between TABLESAMPLE and TOP clauses?

• What are the three transaction modes available with SQL Server?

Question 1: If the "dbo.Customers" table contains 10 records, how many records will be returned by the below queries?

Since the CROSS JOIN has been used for the first query, it returns 100 records. The CROSS JOIN makes a result set by taking each row from first table together with all the rows from second table, hence the number of rows in the result set comes by number of rows in the first table multiplied by number of records in the second table (10 x 10 = 100).

Since no JOIN type has not been used with the second query, it behaves like a CROSS JOIN, hence resulting in 100 rows.

The behavior of the CROSS JOIN changes when a WHERE clause that matches two columns in either table is added to the query. In a query, such as the one below, it behaves just like an INNER JOIN:

The third query is the same as the second one with an additional WHERE clause, which makes it function like an INNER JOIN, hence resulting in 10 rows.

Question II: What is the difference between INTERSECT and INNER JOIN?

An INNER JOIN returns rows matching the join columns from both the joined tables. INTERSECT results in a fashion similar to a standard INNER JOIN, applying two more rules:

• It applies same rules as the UNION set operator, hence results in distinct values.

• Two NULL values are compared equally.

Here is an example for an INNER JOIN:

Here is an example for an INTERSECT, and how it results

Question III: How many ranking functions are available with SQL Server 2005?

There are four ranking functions available with SQL Server 2005. They are;

• ROW_NUMBER - Allows us to add a sequential number to the result set as an additional column, over a column. The sequence can be reset by issuing PARTITION BY clause. Here are some examples;
  
  

  -- this returns an additional column with sequential number
  -- that is set over the descending order of "OderDate" column.
  SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNumber,
     SalesOrderID, OrderDate
  FROM Sales.SalesOrderHeader
  ORDER BY SalesOrderID DESC
  
  -- this returns an additional column with sequential number
  -- that is set over the descending order of "OderDate" column.
  -- The sequence is reset at each year of the order date
  SELECT ROW_NUMBER() OVER (PARTITION BY YEAR(OrderDate)
                                                                     ORDER BY OrderDate DESC) AS RowNumber,
  SalesOrderID, OrderDate
  FROM Sales.SalesOrderHeader
  ORDER BY OrderDate DESC

• RANK - Allows us to add a rank to each row over a column. Same rank is repeated if the values of the rows of the column that is set for the rank are tied. The rank is produced by adding one to the number of ranks that come before the row. PARTITION BY clause can be used with this too and it behaves similar to ROW_NUMBER.

  -- this ranks each row over the "OrderDate".
  -- same rank is repeated if two or more contains same
  -- value for "OrderDate".
  SELECT RANK() OVER (ORDER BY OrderDate DESC) AS RowNumber,
               SalesOrderID, OrderDate
  FROM Sales.SalesOrderHeader
  ORDER BY OrderDate DESC

• DENSE_RANK - Allows to add a rank to each row over a column. The different between this and RANK is, the rank is produced by adding one to the number of distinct ranks that come before the row. PARTITION BY clause can be used with this too and it behaves similar to ROW_NUMBER. Here is an example;
  

  SELECT DENSE_RANK() OVER (ORDER BY OrderDate DESC) AS RowNumber,
              SalesOrderID, OrderDate
  FROM Sales.SalesOrderHeader
  ORDER BY OrderDate DESC

• NTILE - Allows to group the result set over a column and number them, starting with one. The result can be partitioned too by adding the PARTITION BY clause.
  

  -- this groups the result set into 3, numbering from 1.
  SELECT NTILE(3) OVER (ORDER BY OrderDate DESC) AS RowNumber,
               SalesOrderID, OrderDate
  FROM Sales.SalesOrderHeader
  ORDER BY OrderDate DESC

Question IV: What is the difference between TABLESAMPLE and TOP clauses?

When TABLESAMPLE is specified, it picks records randomly and returns, whereas TOP always returns the first (or top) set of records from the table. When TABLESAMPLE is used, it may or may not return records. If same set of records need to be retrieved again and again with TABLESAMPLE, REPEATABLE has to be used with same repeat_seed value.

Question V: What are the three transaction modes available with SQL Server?

• Autocommit Mode - This is the default mode of SQL Server. SQL Server sets the Autocommit mode when a statement is executed without a transaction being explicitly specified. Each T-SQL statement is either committed or rolled back when it finishes the statement. Since the default mode is applied to each statement, an explicit mode transaction is not required for a process that has only one statement, unless it needs to be run as a nested transaction.
  

  -- BEGIN TRAN
  UPDATE Sales.SalesOrderHeader
         SET OnlineOrderFlag = 1
  WHERE SalesOrderID = 500
  -- COMMIT TRAN

• Explicit Mode - This is the mode we use to explicitly specify the start and the end of the transaction. Simply BEGIN TRAN is used for indicating the starting point and COMMIT TRAN is used for indicating the end point of the transaction. ROLLBACK TRAN is used for rolling back the transaction in case of failure. SQL Server supports nested transactions. The global variable @@TRANCOUNT gives the active number of transactions in the session. This variable gets incremented by 1 at each BEGIN TRAN statement and gets decreased by 1 at each COMMIT TRAN statement. If the transaction is a nested transaction, though it decreases the @@TRANCOUNT at inner COMMIT TRAN, it does not commit the nested transaction until the top transaction is committed.
  
  If you want to roll back part of a transaction and continue the transaction, savepoint can be used for it. If a savepoint is set in the middle of the transaction, it rolls back updates from the savepoint to rollback statement, without affecting updates above the savepoint. Though the top section remains unchanged, it requires another ROLL BACK or COMMIT at the end of the code to complete the process.
  

  BEGIN TRAN order_update
  
          -- this update will be commited
          UPDATE Sales.SalesOrderHeader
          SET OnlineOrderFlag = 1
          WHERE CurrencyRateID IS NOT NULL
  
          SAVE TRAN sales_order
  
          -- this update will be rolled back by below ROLLBACK statement
          UPDATE Purchasing.Vendor
          SET ActiveFlag = 0
          WHERE CreditRating = 3
  
          -- roll back the above statement
          ROLLBACK TRAN sales_order
  
          -- this update will be commited
          UPDATE Purchasing.Vendor
          SET ActiveFlag = 0
          WHERE CreditRating = 5
  
   -- completing the transaction
  COMMIT TRAN order_update

• Implicit Mode - When this mode is set, SQL Server automatically starts a transaction when a certain statement is about to be executed. The SET IMPLICIT_TRANSACTIONS ON enables the implicit mode and below statements start the transaction automatically;

  • SELECT

  • UPDATE

  • DELETE

  • INSERT

  • CREATE

  • DROP

  • ALTER TABLE

  • TRUNCATE TABLE

  • OPEN

  • FETCH

  • GRANT

  • REVOKE

This transaction must be ended up with either COMMIT TRAN or ROLL BACK. Once either is issued, the next transaction is automatically started when it encounters one of above mentioned types statement again. This behavior continues until implicit mode is disabled. If the connection is closed without committing or rolling back, the transaction will be automatically rolled back. See the code below to understand the implicit mode.