STUFF FUNCTION

This function inserts a string into another string, it deletes a specified lenght of the characters at the starting position in the expression and inserts the second string at the starting position.

syntax : STUFF ( character_expression , start , length , replaceWith_expression )

start : Integer value it specifies the position to start the insertion.

length : Integer value it specifies the number of characters to delete from start position.

Reference : MSDN_STUFF



Difference between STUFF and REPLACE Functions :

STUFF function deletes a specified length of string from start position and inserts the second string in the expression.

REPLACE function replaces all occurences of the string with the second string in the expression.

Check this code

select stuff('krishna',1,0,'MR. ')

output is
MR. krishna

select replace('krishna', 'k','satya k')

output is
satya krishna

GOTO

Alters the flow of execution to a label. The statements that follow GOTO are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.
Reference : MSDN_GOTO

Difference between Len() and DataLength() functions

Len() function gives the length of the characters in a string by excluding the trailing spaces, but datalength() function will give the length of characters in a string by including trailing spaces also because it returns the amount of storage required to represent a column.

Datalength() function works on nvarchar and nchar accurately by considering 2 bytes per character where len() function does not.

Datalength() function works on text and ntext where len() function does not works.

Check This code

create table test(val1 char(10), val2 nchar(10))


insert into test values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test

Output is
3 3   10   20

create table test_v(val1 varchar(10), val2 nvarchar(10))


insert into test_v values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test_v

Output is
3   3   3   6


create table test_c(val1 text, val2 ntext)


insert into test_c values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from  test_c

Above query gives the below error, because len() function not support text data types
Argument data type text is invalid for argument 1 of len function.

select datalength(val1),datalength(val2) from  test_c

Output is
3 6


Reference : MSDN_Len
                   MSDN_DataLength

ROW Functions

@@Rowcount : Returns the number of rows affected by the last statement.
Reference : MSDN_ROWCOUNT   


ROWCOUNT_BIG( ) : This function is also working same as @@rowcount, if the table having more than 2 billion rows then this one will useful.
Reference : MSDN_ROWCOUNT_BIG


SET ROWCOUNT : MSSQL Server stops the query processing after the specified number of rows are returned.
By using the below code we can eliminates the restriction on number of rows processing
SET ROWCOUNT 0

Reference : MSDN_SET_ROWCOUNT

TOP : Limits the rows returned in a query result set to a specified number of rows or percentage of rows.
Reference : MSDN_TOP


ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Reference : MSDN_ROWNUMBER

RANK : Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row.
Reference : MSDN_RANK

DENSE_RANK : Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Reference : MSDN_DENSERANK

NTILE : Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Reference : MSDN_NTILE

Difference between CAST and CONVERT functions

Cast and convert functions are used to convert one data type to another datatype, but CAST is ANSI standard function and CONVERT is specific to MSSQL Server and by using CONVERT we can get the specified format particularly for datetime datatype fields.

Reference : MSDN_CAST_CONVERT


Performance wise both the functions will perform equally.

Reference : Good Post

Null Functions

ISNULL : It validates the expression and replaces to a specified value if it is null, other wise it will returns the expression.

ISNULL(expression,specifiedvalue)

NULLIF : It compaares the expressions and returns NULL if both the expressions are same, otherwise it will returns first explression.

NULLIF(expression1,expression2)

COALESCE : It will return the first Non Null expression from the specified expressions, if all expressions are NULL then it will returns NULL.

COALESCE(expression1,expression2,expression3.....,expressionN)


GETANSINULL : It returns the default nullability of the specified database.

GETANSINULL('databasename')

When the nullability of the specified database allows for null values and the column or data type nullability is not explicitly defined, GETANSINULL returns 1. This is the ANSI NULL default.

Reference : GETANSINULL


check this code

SET ANSI_NULL_DFLT_ON off
select getansinull('master')
create table m(id int,val char)
insert m values(2,null)

This query returns the below error,

Cannot insert the value NULL into column 'val', table 'master.dbo.m'; column does not allow nulls. INSERT fails.
The statement has been terminated.


SET ANSI_NULL_DFLT_ON on
select getansinull('master')
create table p(id int,val char)
insert p values(2,null)

This query working fine.

Note : Twist in ISNULL Function

NULL

1 NULL can be defined as absence of value, undefined, or the value which is unknown.

2 All datatypes can be defined with NULL constraint

3 Direct usage of arithmetic or logical operations on NULL will not work as expected

4 The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL

5 NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED

6 The only datatypes that will interpret NULL differently are rowversion and timestamp

Run this code and see the output

declare @rv rowversion , @ts timestamp
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp

Output is
rowversion         timestamp
------------------ ------------------
0x                 0x

7 NULL=NULL will never be true unless SET ANSI_NULLS is OFF

While the first query returns nothing, the second will return 6
--Query 1
set ansi_nulls on
select 6 where null=null
--Query 2
set ansi_nulls off
select 6 where null=null
The condition WHERE col IS NULL will not be affected by the above setting

8 The default datatype of NULL is INT. Refer Default datatype of NULL for more information

9 Column with UNIQUE constraint will allow only one NULL value in SQL Server

10  NULL will make SQL Server to use short circuit logic in some cases

Consider this example
select 6/0/null
select null/6/0
While the first query throws an error the second query returns NULL

11 The value NULL is not equal to string value 'NULL'

12 By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order

13 If you dont use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false

select sum(col) as col from ( select 45 as col ) as t where 1=0
The above returns NULL

14 NULL values are by default omitted in all aggregate functions

Consider the following example

select sum(col) as col_cum,count(col) as col_count,avg(col*1.0) as col_avg from ( select 1 as col union all select null as col union all select 2 as col union all select 3 as col ) as t

The output is
col_cum     col_count   col_avg
----------- ----------- -----------
6           3           2.000000

15 Aggregate functions cannot be directly applied over NULL value

This code

select sum(null) as null_sum
returns the following error
Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

16. If sub-query returns NULL value when NOT IN used to filter the records, main query returns no records.

This code

create table test(id int)
insert into test select 1 union all select 2 union all select null union all select 3
select * from test where id in (1,2,3,null)
select * from test where id not in (1,2,3,null)

where first query returns 3 values and second query returns no records

Reference : About NULL