Saturday, September 8, 2007

SQL SET ROWCOUNT

SQL SET ROWCOUNT statement gives administrators some control over the execution of "runaway" queries issued by naive users of SQL. This statement stops the execution of a query as soon as a specified number of rows has been retrieved. In this way, system resources are not wasted, but users can see at least a partial result set. An informational message is displayed after the result set, indicating that SET ROWCOUNT is in effect.
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.

Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.

After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)

SQL SET ROWCOUNT

Thursday, September 6, 2007

SQL Split Function

This SQL Split Function is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.

Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END


Result
- SQL
- TUTORIALS

* Copy blue color Sql split function with change @String variable to test the result in your query analyzer.

SQL Split Function