CURSOR - Current set of Records

"CURSOR"  stands for  CURrent Set Of Records.

An interesting way of creating a name. I think I like the name, Cursor.

IDENTITY (Function) in SQL Server

I found  IDENTITY (Function) very useful today.

Task & Issue
- I had to load a big number of contact information records into a table.
- The contact records were all missing ContactNo, which is primary key. The source table has ContactNo column, but the field were not filled in at all. Thus, the P.K. field needs to be populated.
- I got a range of integer numbers that I can use as P.K for this set of contract records
   (The number begins from 109578, and it should increment by 1)
- How can I assign the P.K. numbers(ContactNo) to the records and load them into the target table?  (without using a cursor; doing a set-based operation)

Solution
- Identity( dataType, startNumber, incrementBy )
This function populates an integer(dataType) number for each records. The integer number begins with the startNumber and increments by incrementBy.  This function allowed me to assign a unique contactNo to each record without using a cursor and looping.
- I just used a temp table instead of attempting to load the data directly into the target table. I am sure that someone can find more efficient way of accomplishing this kind of task.


Step 1

   SELECT   IDENTITY ( int,  109578 , 1 ) AS contactID,  *
         INTO   #temp_Contact_Load
         FROM   Source_DB.dbo.CONTACT
 
Step 2 

   INSERT INTO  Target_DB.dbo.CONTACT
     ( CONTACTNO
      ,FIRSTNAME
      ,LASTNAME
      ,EMAILADDRESS
      ,PHONE
       ...
   )
    SELECT   
          contactID        -- select contactID field instead of ContactNo field from the Temp Table..
         ,FIRSTNAME
         ,LASTNAME
         ,EMAILADDRESS
         ,PHONE
         ...
      FROM  #temp_Contact_Load


-------------------------------------------
Reference :  http://msdn.microsoft.com/en-us/library/aa933208%28v=sql.80%29.aspx

Set-based operation Versus Cursor-based operation

We develop a query to retrieve/process data. We could have our processing operation done in row level(one row after another row) or in the level of the whole data set (the whole set at once). We could use a cursor to process each row at a time (Cursor-based operation). The following examples* could help us understand the difference between set-based operation  and  cursor-based operation.

> SET-based operation

UPDATE s                       -- update the entire set of data at once.

   SET StatusCode = 'ACTIVE', ModDate = dbo.DateTrunc('day', GETDATE())

   FROM dbo.Site s

        INNER JOIN dbo.Contact c WITH (NOLOCK) ON s.SiteNo = c.SiteNo

> Cursor-based operation (row-based/serial) 

DECLARE @ls_SiteNo      CHAR(10)

DECLARE crsModule1 CURSOR LOCAL STATIC FORWARD_ONLY FOR

   SELECT s.SiteNo

   FROM dbo.Site s

   INNER JOIN dbo.Contact c WITH (NOLOCK) ON s.SiteNo = c.SiteNo

OPEN crsModule1

FETCH NEXT FROM crsModule1 INTO @ls_SiteNo

WHILE (@@fetch_status = 0 ) BEGIN

     UPDATE dbo.Site

     SET StatusCode = 'ACTIVE', ModDate = dbo.DateTrunc('day', GETDATE())

     WHERE SiteNo = @ls_SiteNo     -- update each row at a time.              

     FETCH NEXT FROM crsModule1 INTO @ls_SiteNo

END

CLOSE crsModule1

DEALLOCATE crsModule1

1) Database engines are optimized for set-based operation. However, there are some cases that a serial operation is the only or better option. Generally speaking, if we can avoid using a cursor, we would be better off.

2) When we deal with a smaller set of data, the difference between the two operations might be very minimal or we wouldn't even notice any difference in performance. But as the volume of data grows bigger, the performance difference will probably become more obvious.

3) Personally speaking, I believe that a set-based operation would be processed still serially inside of the database engine in the end. However, experts still recommend that we use a set-based operation if possible. It seems to me that it is a better idea to let the database engine take care of the serial data operation. It is probably not a good idea for us to manually figure out how to serially process data because the database engines normally know better than we do about the data operation. Just a thought...

-----------------------------------------------------------------

* Thank to Mr. Gord Gray who kindly provided me with these examples to help me understand on this subject.
 

Data Pivoting - how to create a pivot query

1.  Pivoting data
Itzik Ben-Gan describes, "pivoting data is a technique that rotates data from a state of rows to a state of columns" in his article, 'Pivoting Data' (SQL Server Magazine, January 2011). Why do we ever have to rotate data then?  Data pivoting allows us to summarize(aggregate) a given data set and re-organize(pivot) the structure of the original data set for a better report purposes.

2.  Creating a pivot query   
Let's say we have a table, called 'SalesOrderHeader.'  And we need to summarize the data and horizontally show SUM of TotalDue of each YEAR for all the TerritoryIDs.

 SELECT     TerritoryID
                   , TotalDue
                  , YEAR(OrderDate) OrderYear
  FROM      Sales.SalesOrderHeader

The report is supposed to summarize the data as following

3. A few things to identify before writing a PIVOT query.
- The data is to be grouped by TerritoryID (Grouping element)
- TotalDue column is to be aggregated (Aggregation element)
- The distinct values of YEAR(OrderDate) are used as the column headings of the pivoted result (Pivoted element. To be displayed horizontally.). And we need to know the distinct values of YEAR(OrderDate) in advance in order to write our Pivot query.  i.e) 2005, 2006, 2006, 2007

4. Writing a pivot query in SQL Server
4.1) SQL Server with CASE expression.

 SELECT     TerritoryID,
SUM (CASE WHEN YEAR(OrderDate) = 2005  THEN TotalDue ELSE 0 END) as [2005],
SUM (CASE WHEN YEAR(OrderDate) = 2006  THEN TotalDue ELSE 0 END) as [2006],
SUM (CASE WHEN YEAR(OrderDate) = 2007  THEN TotalDue ELSE 0 END) as [2007],
SUM (CASE WHEN YEAR(OrderDate) = 2008  THEN TotalDue ELSE 0 END) as [2008]
       FROM     Sales.SalesOrderHeader
       GROUP BY    TerritoryID
       ORDER BY    TerritoryID;

4.2) SQL Server with PIVOT operator

WITH BaseTable AS
 (
    SELECT     TerritoryID
                      , TotalDue
                      , YEAR(OrderDate) OrderYear
     FROM      Sales.SalesOrderHeader
  )
  SELECT    TerritoryID
    , [2005], [2006], [2007], [2008]
FROM    BaseTable
PIVOT  ( SUM(TotalDue)  FOR  OrderYear  IN ([2005],[2006],[2007],[2008]))  as PvtResult ;

5. Things to remember
- When using PIVOT operator, creating a base table using CTE is preferred. Include the columns needed only: a grouping column, pivoted column and aggregated column. It is because any column that is not pivoted or aggregated will be used as a grouping element.
-  IN list restricts the rows that are pivoted and supplies the pivoted column names. So if you omit [2007] from the above query, summation on TotalDue for year 2007 won't be done even if there are some data for year 2007.
- IN list is NOT DYNAMIC. We can not use a sub-query to populate this IN list. It should be hard-coded.
  (If the IN list is unknown, we can create a dynamic SQL statement. Dynamic pivoting is beyond the scope of this post. )
---------------------------------------------------
Reference: 
-  Querying Microsoft SQL Server 2012 Training Kit by Itzik Ben-Gan, Dejan Sarka, Ron Talmage     http://technet.microsoft.com
-  http://sqlmag.com/t-sql/create-pivoted-tables-3-steps  "Create Pivoted Tables in 3 Steps" by Kathi Kellenberger

NCHAR, NVARCHAR, NTEXT / NCLOB - supporting Unicode characters

As more companies deploy their database globally, their database needs to be able to handle Unicode. Unicode enables us to represent all the characters that are expressed in most of human written languages.

The following datatypes allow us to handle unicode characters.
   -  NCHAR, NVARCHAR,  NTEXT, NVARCHAR(max), NCLOB

And the following example shows how we can use these datatypes.

CREATE TABLE  unicodeDataTable
  (  text_id       number           Primary KEY,
     uni_text      nvarchar(20)

   );

INSERT INTO unicodeDataTable(text_id, uni_text) VALUES (1,  N'이동훈' );
INSERT INTO unicodeDataTable(text_id, uni_text) VALUES (2,  N'こんにちは' );

SELECT * FROM  unicodeDataTable;

   text_id       uni_text

   1               이동훈
   2               こんにちは

--------------------------------------------------------- 

NTEXT :  (SQL Server)  NText is going to be deprecated. Microsoft recommends that we use NVARCHAr(max).

NCLOB & CLOB  : (Oracle) store up to 8 to 128 terabytes of character data (11g) 

Table Variables vs. Temp Tables in SQL Server

SQL Server has 'table variables' that doesn't exist in Oracle. In Oracle, we may use a cursor to get the same task done.  In addition, it seems worth taking a note on the difference between Table Variables and Temp Tables in SQL Server.

(1) How to create and use a Table variable :  Primary Key, Unique Key and Not Null allowed.

     DECALRE @tableVariableName TABLE
      (
         Column1    INT   IDENTITY(1,1),
         Column2    VARCHAR(10) NOT NULL,
         Column3    MONEY,
         ...
      )

      INSERT INTO  @tableVariableName
      SELECT customerID, name, salary
      FROM  customers


      SELECT *   FROM @tableVariableName

(2) Differences between Temp tables and Table Variables

•  DDL operation - Temp tables can be altered with a DDL operation while Table variables cannot be.
• Statistics - SQL Server collects statistices for temp tables but not for table variables. 
• Data access - SQL Server uses various strategies to access temp tables with the table statistices collected (index density, distribution, selectivity, etc). But it accesses table variables through a table scan only.
•  Performance - many people say that we get slightly better performance benefit with a table variable when working on a small data set (< 100,000 rows). But for a bigger data sets, a temp table seems to be a better choice.

How to view the structure of a table in SQL Server

EXEC  sp_columns  table_name


* sp_columns : one of the catalog stored procedures that retrieve information from the system tables.
                       They are created by installmaster at installation and located in the sybsystemprocs database.
                       They are owned by the System Administrator.


* We would use DESC / DESCRIBE command in Oracle & MySQL.


----------------------------------------------------------------------------
Reference:   http://msdn.microsoft.com/en-us/library/aa259626(v=sql.80).aspx