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.

Where to use SQL Server Cursor

Where to Use Cursor

Cursor can be used when you need to manipulate data in a set on a row-by-row basis, however you can also use T-SQL WHILE loop, CASE expression and some system defined stored procedure like sp_MSforeachdb, sp_MSforeachdb etc.

IN SQL Server the cursor can be implemented by 6 step process as:

1. Declare cursor

DECLARE ins_cursor CURSOR

FOR

    Select statement…………….

2. Open cursor

OPEN ins_cursor

3. Fetch row from the cursor

FETCH NEXT FROM ins_cursor 

INTO @fileno,……….variable list

4. Process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

….

…..

…

FETCH NEXT FROM ins_cursor 

INTO @fileno@fileno,……….variable list

END

5. Close cursor

CLOSE ins_cursor

6. Deallocate cursor

DEALLOCATE ins_cursor;

I have used cursor first time when I need to insert data from one table of one database into tables of another database. So I am explaining same example here

--declare variables to use in logic

DECLARE @RID INT

DECLARE @FILENO VARCHAR(50),@ISSUEDATE DATETIME,@REQDATE DATETIME,@RCN VARCHAR(50),@FILEREMARKS VARCHAR(200)

--decalare cusrsor

DECLARE INS_CURSOR CURSOR

    FOR

    --select statement

    SELECT FILENO,REQCONTROLNO,CONVERT(DATETIME,REPLACE(REQDATE,'-','/'),103),CONVERT(DATETIME,REPLACE(ISSUEDATE,'-','/'),103) ,REMARKS FROM ISSUE WHERE RECEIVEDDATE IS NULL

    --open cursor

OPEN INS_CURSOR

--Fetch row from the cursor

FETCH NEXT FROM INS_CURSOR

INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

--process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

--insert into first table

INSERT INTO FISS1.DBO.FILEREQUEST(REQDATE,REQSTATUS,RCN,CORDSTATUS,FILEREMARKS)

VALUES (@REQDATE,1,@RCN,1,@FILEREMARKS)

----assigning primary key(identity) value

SELECT @RID=@@IDENTITY

--insert into 2^nd table with foreign key @rid

INSERT INTO FISS1.DBO.ISSUE(REQID,FILENO,PRIORITY,PURPOSE,ISSUEDATE,STAUS)

VALUES(@RID,@FILENO,1,'GENERAL',@ISSUEDATE,1)

FETCH NEXT FROM INS_CURSOR INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

END

--close cursor

CLOSE INS_CURSOR;

DEALLOCATE INS_CURSOR;

Cursor Recommendations:

·         It is better to avoid using cursor because they consume memory for execution, so performance is less.

·         If you using cursor, then you should always close cursor after using it.

Further Reading

http://msdn.microsoft.com/en-us/library/ms181441.aspx

Prev---> Handling Null Values in SQL Server

Database connectivity with Oracle,SQL Server and MS Access using Visual Studio

When your project connected with database in visual studio, the properties of Data Connection holds working connection string in Server Explorer in Visual Studio. So to find connection string you only need to connect database through visual studio. Here I will explain database connectivity with three main databases as SQL Server, MS Access database and Oracle Database.

Connect SQL Server with Visual Studio/SQL server Database Connectivity

   1.   Open Visual Studio.

   2.  Go to View menu=> Server Explorer.

   3.   Right click on Data Connections and select Add Connection.

   4.  You will get add connection window.

   5.  Provide Server name. Select Use SQL Server Authentication/Use Window Authentication radio    button. Then submit the username and password.

  6. After validating provided credentials, the database lists is shown in dropdown list .Select relevant database. OR You can attach a .mdf database file also.

   7.  You can test connection and if succeeded Ok.

  8.       A new data connection created in your visual studio Server Explorer.

  9.       Right Click on data connection and go to properties. You can see Connection string here.

10.    Copy connection string and paste it in your web config.

11.    You can use this connection string in your project to open and use connection

public class DbData

{

static SqlConnection cn; //defining in class

    static string cnn; //defining in class

public DbData()

{

}

public static SqlConnection GetConnection()

    {

        cnn = System.Web.Configuration.WebConfigurationManager.AppSettings["con"].ToString();

        cn = new SqlConnection(cnn);

        if (cn.State == ConnectionState.Closed)

        {

            cn.Open();

        }

        else

        {

            cn.Close();

        }

        return cn;

    }

}

Use this method as

SqlCommand cmd = new SqlCommand(query, DbData.GetConnection());

  12.    That is whole thing about getting and using connection string from Visual Studio.

---

Connection String for MS Access database/MS Access database connectivity

1.       Open Visual Studio.

2.       Go to View menu=> Server Explorer.

3.       Right click on Data Connections and select Add Connection.

4.       You will get add connection window.

5.       By default Microsoft SQL Server (sqlclient) selected , click change and select Microsoft Access database.

6.       Browse Access database file and test connection if connection succeeded then OK.

                                                                                          


7.       A new data connection created in your visual studio Server Explorer.

8.       Right Click on data connection and go to properties. You can see Connection string here. Copy and use as above steps for SQL Server.

---

 Connection String for Oracle database/Oracle Database Connectivity

1.       First install oracle client on machine where you want to configure linked server.

2.       Go to [Oracle Client Installation Directory]\product\11.2.0\]dbhome_1\NETWORK\ADMIN

3.       Open tnsnames.ora file.(It is Network configuration file for oracle)

4.       Add TNS setting in this file as Example here

TEST=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=192.168.0.35)

      (PORT=1556)

    )

    (CONNECT_DATA=

      (SERVICE_NAME=TEST)

    )

  )

5.       See more details of tnsnames.ora file.

To Test oracle connection:

6.       Open cmd

7.       Enter SQLPLUS

8.       Enter USERNAME:  username@Test

9.       Enter PASSWORD: password

10.   Type any select query to confirm connection.

Now you can create connection string

11.    Open Visual Studio.

12.    Go to View menu=> Server Explorer.

13.    Right click on Data Connections and select Add Connection.

14.    You will get add connection window.

15.    By default Microsoft SQL Server (sqlclient) selected , click change and select Oracle Database (OracleClient).In TNS Server name is TNS entry name.and Provied user name and password

16.    A new data connection created in your visual studio Server Explorer.

17.    Right Click on data connection and go to properties. You can see Connection string here. Copy and use as above steps for SQL Server.

 Hope you enjoy this post