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.
- TIMEFROMPARTS () which returns output in TIME format.
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
- SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- DATETIMEFROMPARTS() which returns output in DATETIME format.
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
- DATETIME2FROMPARTS() which returns output in DATETIME2 format.
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
- 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
- SQL Server: How to Avoid Data Loss During Migration From MS Excel
- SQL Server: How to Migrate Data From Top Speed Database Files
TSQL Tips and Tricks
- SQL Server: A Simple Method to Get Specific Part of String
- SQL Server: 20 Most Commonly Used DateTime Formats
- SQL Server: How to Transfer All Objects of a Schema
- SQL Server: How to Delete or Update Rows with JOIN Clause
- TSQL Challenge 49: Identify overlapping time sheet entries of field technicians
Performance Tuning
- SQL Server: Indexes for GROUP BY Columns, a Good Choice
- SQL Server: When We Should Use Cross or Outer Apply Instead of Simple Joins
- SQL Server: Mind Your Search Conditions In WHERE Clause
- SQL Server: Data Type DATETIME or Only DATE
- SQL Server Performance: Not All Fast Queries Are Innocent
- SQL Server: When We Should Use Read Only Cursors
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
