Category Archives: T-SQL Cursors

SQL Server Denali: Get Date/Time from Parts

SQL Server Denali has madedevelopers life quite easy by introducing new useful functions. We have alreadydiscussed EOMONTH() in anearly post.
DATEFROMPARTS() is a new function introduced in SQL Server Denali,which will help us to avoid lengthy code to get date from year, month and dayinputs. DATEFROMPARTS() takes three input parameters YEAR, MONTH and DAYand returns value in DATE format.
DATEFROMPARTS ( year, month, day )

DATEFROMPARTS() is not onlyfunction to get DATETIME values from parts but 5 other functions are alsointroduced.

  1. TIMEFROMPARTS () which returns output in TIME format.
    TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
    1. SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.
    SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
    1. DATETIMEFROMPARTS() which returns output in DATETIME format.
    DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
    1. DATETIME2FROMPARTS() which returns output in DATETIME2 format.
    DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
    1. DATETIMEOFFSETFROMPARTS() which returns output in datetimeoffset format.
    DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
    Advertisements

    >SQL Server: What I have Learned in April 2011

    >

    Dedicated to Unbeaten Hero of SQL Server Community Jacob Sebastian
    TSQL Tips & Tricks
    SQL Server Management:
    Performance Tuning:
    SQL Server Community:

    >SQL Server: What I Have Learned In February 2011

    >

    Dedicated to Tara Kizer., who is on this planet, just to help others.
    February was a tough month as I switched my company and also because of my final term exams. Here is a list of topics which I have learned and shared in February 2011.   
    Data Migration
    TSQL Tips and Tricks
    Performance Tuning
    Database Design
    SQL Server Management Studio
     

    >SQL Server: When We Should Use Read Only Cursors

    >

    Everyone of us knows that processing of data through cursors, is a worst choice, because SQL Server are designed to work best with sets of data and not one row at a time.
    Still processing through cursors can be made faster by making little changes. Like, if we need to use cursor to process one row at a time and we don’t need to update base table through this cursor, we MUST use read only cursor. As read only cursors are non-updateable so no locks are required on the base table. Only shared locks are held. And due to this phenomenon read only type of cursor are considered FASTER and SAFER.
    Syntax is almost same to ordinary cursor and only keyword of READ_ONLY is added.
    DECLARE YourCursorNameHere CURSOR READ_ONLY
          FOR SELECT columnNameHere
                FROM  tableNameHere
                WHERE filterConditionHere
    OPEN YourCursorNameHere
    FETCH NEXT FROM YourCursorNameHere INTO @parameterName
    WHILE @@FETCH_STATUS = 0
          BEGIN
                YourImplementationHere
                .
                .
                .
          FETCH NEXT FROM YourCursorNameHERE INTO @parameterName
          END
    CLOSE YourCursorNameHere
    DEALLOCATE YourCursorNameHere