SQL Server FAQ on COMPUTE,STUFF,REPLACE,ISNULL,COLLEASE and temporary Tables

SQL Server FAQ 2:

1.      What are COMPUTE and COMPUTE BY  in SQL Server

COMPUTE can be used to generate an addition summary column at the end of result set followed by aggregate functions like SUM, AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, and VARP.

Syntax:

COMPUTE    { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }  ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]  

     Example:

select a.cust_id,a.amount from dbo.tblPaymentDetails a where cust_id='1751'
compute SUM(a.amount).

    

     COMPUTE BY create groups of result set based on Column in BY clause

select a.cust_id,a.amount from dbo.tblPaymentDetails a order by cust_id  compute SUM(a.amount) by a.cust_id

2.      Where vs. Having clause

Where is used with search condition upon rows whereas having is used with search condition for group or aggregate.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name HAVING searchcondition;

Having can be used without group by but your query should have aggregate functions. You cannot be able to select any column without aggregation function

SELECT aggregate_function(column_name) FROM tablename HAVING  aggregate_function(column_name)=value

Example:

SELECT SUM(qty)FROM dbo.Orders HAVING SUM(salesprice) > 10
SELECT  * FROM dbo.Orders Where salesprice > 10

3.      STUFF vs. REPLACE

Replace function replace all occurrences of a specified string value with another string value.

REPLACE ( string_expression , string_pattern , string_replacement )

Example:

SELECT  REPLACE('queryingsql','q','A')

Result:  AueryingsAl.

q is replaced by A in whole expression for each occurrence. Now if you want to replace a part of string expression. then you need to use STUFF.

STUFF ( character_expression , start , length , replaceWith_expression )

STUFF deletes all character (specified in length parameter) from specified start position and insert replaceWith_expression string.

SELECT STUFF('queryingsql',9, 5,' SQL SERVER')

Result:  querying SQL SERVER

And on other hand it is length and location based so it not replaces all occurrence of specified pattern.

4.      Local Temporary table vs. Global Temporary table

Local Temporary table: It is created with single ‘#’ as prefix of table name and is available for the connection for which it was created. It is automatically dropped when this connection closed or user drop explicitly by using drop query.

CREATE TABLE #temptest
( ID int NOT Null ,Name varchar(250))

Global Temporary table: It is created with ‘##’ as prefix of table name and is available for all connections or any connection created after. These tables dropped when all connections that are referencing the table disconnected from the instance of SQL Server.

CREATE TABLE ##gobaltest
( ID int NOT Null ,Name varchar(250)) 

5.      COLLEASE vs. ISNULL

·         COALESCE and ISNULL   both can be used in NULL handling. But there are some differences in both of them.

·         COALESCE allows multiple parameters while ISNULL allow only two parameters.      

SELECT ISNULL(null,0) –0
 SELECT COALESCE(null,null,0) --0

·         COALESCE returns type of value with high precedence from list of parameters. While ISNULL return first parameter type.

DECLARE @intval INT, @floatval FLOAT
SELECT  @floatval= 8.25, @intval = 10
SELECT COALESCE(@floatval, @intval) AS Value

Result: 8.25

Since float have higher precedence then int.You can see data type precedence here http://msdn.microsoft.com/en-us/library/ms190309.aspx.

·         COALESCE is ANSI SQL Standard but ISNULL is T-SQL function.