A Simple Example on SQL Trigger

Introduction

A trigger is a special kind of stored procedure, which is attached with a database table and gets executed automatically in response to certain action on the table like insertion, deletion or updation.

You can not invoke/call it manually, the only way to execute it is to do the required action on the table.

There are two types of Triggers

• After Triggers (For Triggers)
• Instead Of Triggers 

 

After Triggers 

These Triggers are fired just after insertion, deletion and updation of a table.

 

Instead Of Triggers 

These Triggers are fired when a user tries to do something like inserting/deleting/updating the table and performs what you have written inside it instead of what the user tries to do.

Example: If you have written an Instead Of Trigger for an Insertion operation on Sales table and someone is trying to insert into the Sales table then the code inside the Trigger gets executed instead of the Insertion query. Basically with the Instead Of Trigger you are overloading the actual functionality of the operators like INSERT, UPDATE, DELETE.

For more details on Triggers visit this link.

Behind The Scene

Here in this article we will see how to create a Trigger and how it works in real world.

Before we proceed we may need some basic knowledge on SQL.

Lets create a Table named Sales in our database.

CREATE TABLE Sales
  (
     id         INT IDENTITY(1, 1) PRIMARY KEY,
     totalsales VARCHAR(255) NOT NULL,
     solddate   VARCHAR(15)
  ) 

Now create another Table named TrigerResult

CREATE TABLE TrigerResult
  (
     id                 INT IDENTITY(1, 1) PRIMARY KEY,
     totalsalesoftheday INT NOT NULL,
     solddate           VARCHAR(15)
  ) 

Ok, below is what our aim to achieve with a Trigger.

• When any row is inserted into the Sales table check TriggerResult table 
• If it has a row with the same solddate column  value then update the row's value by adding the newly inserted totalsales column value of Sales table.
• If no row exists with the same solddate column  value then insert a new row into the TriggerResult table.

Here is the Trigger which will do our job perfectly.

CREATE TRIGGER UpdateTriggerResult
ON Sales  
AFTER INSERT
AS
  BEGIN
      DECLARE @currentDate VARCHAR(19)
      DECLARE @CurrentSoldAmmount INT

      SET @currentDate = CONVERT(VARCHAR(19), (SELECT Cast(Getdate() AS DATE)))
      SET @CurrentSoldAmmount = (SELECT TOP 1 totalsales
                                 FROM   Sales
                                 ORDER  BY id DESC)

      IF( (SELECT TOP 1 solddate
           FROM   TrigerResult
           ORDER  BY id DESC) = @currentDate )
        BEGIN
            UPDATE TrigerResult
            SET    totalsalesoftheday = totalsalesoftheday + @CurrentSoldAmmount
            WHERE  solddate = @currentDate
        END
      ELSE
        BEGIN
            INSERT INTO TrigerResult
                        (totalsalesoftheday,
                         solddate)
            VALUES     (@CurrentSoldAmmount,
                        CONVERT(VARCHAR(19), (SELECT Cast(Getdate() AS DATE))))
        END
  END
GO

This trigger will perform as the above said constraints. And inserts/updates the TrigerResult table in response of the INSERT statement on Sales table.

Now insert into Sales table by the below statement and see what happens.

INSERT INTO sales
            (totalsales,
             solddate)
VALUES      (100,
             CONVERT(VARCHAR(19), (SELECT Cast(Getdate() + 1 AS DATE)))) 

This will give you the below result.

One row inserted in both tables.

Now run the same insert statement again and you will find the below result.

One row inserted in Sales table and TriggerResult table is updated.

Now you got to know how this Trigger actually works. You can continue with the other types for Update and Delete operations.

Find out the video tutorial on this.

Happy Coding...

Read More

Simple Example On SQL CASE Expression

Introduction

If you are working with SQL SERVER then you are definitely going to need this article. Let me explain a bit briefly. 

You have one table named [User] where, you are storing 3 type of user's data such as

• Clients data
• Employee data
• Admin data

Consider the case if you are asked to write a stored procedure that needs to fetch the respective user data. What will you do ? I can say most of the people will go for a If-Else condition to fetch the data.

But the Case expression will solve your problem in just simple way. Find it below....

   

Behind The Scene

First see if you want to fetch the client data, employee data and admin data in If-Else condition.

-- AUTHOR: Tapan kumar
-- CREATE DATE: --                                                  
-- PROJECT:          
-- CREATED BY: Tapan kumar
-- MODIFIED BY:   
-- MODIFICATION DATE: 28DEC2013  
-- Last MODIFICATIONS :                                                
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type. 
-- UNIT TESTING :  [Getuseranddetailstosendexpirynotification] 
-------------------------------------------------------
CREATE PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
  @FilterType INT
AS
  BEGIN
      -- @FilterType = 1 (will select clients only)
      -- @FilterType = 2 (will select employees only)
      -- @FilterType = 3 (will select admins only)
      
      SET nocount ON;

      IF @FilterType = 1 -- For selecting client data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isclient = 1
                   AND .isdeleted = 0
        END
      ELSE IF @FilterType = 2 -- For selecting employee data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isemployee = 1
                   AND .isdeleted = 0
        END
      ELSE IF @FilterType = 3 -- For selecting admin data
        BEGIN
            SELECT id,
                   firstname,
                   lastname,
                   salary
            FROM   [user]
            WHERE  isadmin = 1
                   AND .isdeleted = 0
        END
  END 

This is how your stored procedure will look like. You need to write the select query 3 times.

But here comes the use of CASE expression to save your time. See the below stored procedure to do the same thing..

-- AUTHOR: Tapan kumar
-- CREATE DATE: --                                                  
-- PROJECT:          
-- CREATED BY: Tapan kumar
-- MODIFIED BY:   
-- MODIFICATION DATE: 28DEC2013  
-- Last MODIFICATIONS :                                                
-- DESCRIPTION/PURPOSE: Gets all user data according to filter type. 
-- UNIT TESTING :  [Getuseranddetailstosendexpirynotification] 
-----------------------------------------------
ALTER PROCEDURE [dbo].[Getuseranddetailstosendexpirynotification] --1
  @FilterType INT
AS
  BEGIN
      -- @FilterType = 1 (will select clients only)
      -- @FilterType = 2 (will select employees only)
      -- @FilterType = 3 (will select admins only)
      
      SET nocount ON;

      SELECT id,
             firstname,
             lastname,
             salary
      FROM   [user]
      WHERE  1 = CASE
                   WHEN @FilterType = 1
                        AND isclient = 1
                        AND .isdeleted = 0 THEN 1
                   WHEN @FilterType = 2
                        AND isemployee = 1
                        AND .isdeleted = 0 THEN 1
                   WHEN @FilterType = 3
                        AND isadmin = 1
                        AND .isdeleted = 0 THEN 1
                   ELSE 0
                 END
  END 

In the above procedure you can see how I have written a single select query that behave differently depending on the @filterType .

Here is how it behaves

• @filterType = 1, it will select the client data. 
• @filterType = 2, it will select the employee data. 
• @filterType = 3, it will select the admin data.

Hope this will be helpful to you.

Happy Coding...

Read More

How And Where To Use Sql Command ExecuteReader

Introduction

In my previous two articles I have explained about ExecuteNonQuery and ExecuteScalar. Where and how to use them in ADO.NET and now here I will explain about ExecuteReader.

When your stored procedure returns a set of rows then you need to hold the returned values in an object. Here you need DataSet or DataTable or a List to hold the returned values.

In this particular case its advisable to use ExecuteReader.

Behind The Scene

When you have written a select query in your procedure or function in sql and it returns a number of rows then you need to hold those list of data in a .net object. 

Basically what happened in the background this ExecuteReader keeps the connection to the database opened until you closes it. And each time this reader reads a different row and returns it back. Here you need to hold those values in a .net data structure lets say DataTabe/DataSet. 

You keep on adding one by one row in the DataTable/DataSet and once done dispose the Reader object.

Here is a simple example how I am getting the data from the database and binding those to a list using DataReader.

  internal SalesClaimList GetUserPointsForSalesClaim(Int32 userId)
  {
      Database db;
      try
      {
          SalesClaimList salesClaimList = null;
          SalesClaim salesClaim = null;

          salesClaimList = new SalesClaimList();
          salesClaimList.ClaimItems = new List();

          db = DatabaseFactory.CreateDatabase();
          DbCommand dbCommand =     db.GetStoredProcCommand(Constants.StoredProcedures.GetUserPointsForSalesClaim);
          db.AddInParameter(dbCommand, Constants.StoredProcedures.GetUserPointsForSalesClaimPameters.UserId, DbType.Int32, userId);
          
          using (IDataReader dataReader = db.ExecuteReader(dbCommand))
          {
              while (dataReader.Read())
              {
                  salesClaim = new SalesClaim();

                  salesClaim.UserTotalPoints = Convert.ToInt32(dataReader["ProgramPoints"]);
                  salesClaim.UserAvailablePoints = Convert.ToInt32(dataReader["PointChange"]);
                  salesClaim.UserSpentPoints = salesClaim.UserTotalPoints - salesClaim.UserAvailablePoints;
                  salesClaimList.ClaimItems.Add(salesClaim);
              }

              dataReader.Close();
          }

          return salesClaimList;
      }
      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          db = null;
      }
  }

Why to use  ExecuteNonQuery  instead of using ExecuteReader and ExecuteScalar.

ExecuteReader needs a DataSet or a List to hold the return values, so it is unnecessary to declare a DataSet for updating/inserting/deleting something in the database. So its best practice to use ExecuteNonQuery  instead that need no DataSet to instantiate.

ExecuteScalar holds a single value that is returned from the stored procedure. This can be used in case of ExecutenonQuery where you are passing some return value from database by a select statement in the same procedure.

** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...

Read More

Where To Use Sql Command ExecuteScalar

Introduction

In my previous article I have explained a bit about how and where to use ExecuteNonQuery. In this article we will see how and where to use the Sql Command ExecuteScalar in ADO.NET.

 

Behind The Scene

When your query returns a single value, that is an aggregate value from the database then its better to use ExecuteScalar.

This needs a minimum of one select query in your procedure that returns a single value. The returned value may be of any type so you need to typecast the ExecuteScalar with the DataType you want.

Lets say your stored procedure is written to select a particular user name from the database according to the user id, so in this case you don't need a DataSet to hold the returned record. Instead you need only a string variable to hold the user name  that is retrieved from the database.

Below is a simple example of this that retrieves the user name from the database according to the user id.

 public static string GetUserNameByUserID(string userID, string connString)
 {
     string userName = string.Empty();
     string sql =
         "SELECT UserName FROM [User] WHERE UserId = "+ userID;
     using (SqlConnection conn = new SqlConnection(connString))
     {
         SqlCommand cmd = new SqlCommand(sql, conn);
         try
         {
             conn.Open();
             userName = (string)cmd.ExecuteScalar();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
         }
     }
     return userName ;
 }

** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...

Read More

How And Where To Use SQL Command ExecuteNonQuey

Introduction

Its a very simple and common thing if you are well known to ADO.NET.  But if you are new to ADO.NET then this will help you a lot.

There are mostly 3 types of return types you can expect from a stored procedure like;

•  A set of rows ( DataSet/Datatable )
•  A single value ( may be integer, string )
•  No return value ( only number of rows affected  ie. row count)

So, My point is, its not required to hold the data a stored procedure returns in a DataSet/DataTable always.

ADO.NET provide 3 types of SQL Commands for achieving the above said purposes.

Behind The Scene

Here in this article we will see the use of the SQL Command ExecuteNonQuery.

When your stored procedure is used to update, insert or delete records in the database then its better to use ExecuteNonQuery. although it returns nothing, you can use a return parameter to return values.

While using ExecuteNonQuery  for Insert, Update and Delete statements, it returns an integer value that hold the number of rows affected by the procedure.

If your procedure successfully updated or  deleted or inserted some data in the database then it returns the number of rows affected by it. If nothing happens in the database or a rollback occurs or an exception occurs then it returns -1. 

 private static void CreateCommand(string queryString, string connectionString)
 {
     using (SqlConnection connection = new SqlConnection(
                connectionString))
     {
         SqlCommand command = new SqlCommand(queryString, connection);
         command.Connection.Open();
         command.ExecuteNonQuery();
     }
 }

Why to use  ExecuteNonQuery  instead of using ExecuteReader and ExecuteScalar.

ExecuteReader needs a DataSet or a List to hold the return values, so it is unnecessary to declare a DataSet for updating/inserting/deleting something in the database. So its best practice to use ExecuteNonQuery  instead that need no DataSet to instantiate.

ExecuteScalar holds a single value that is returned from the stored procedure. This can be used in case of ExecutenonQuery where you are passing some return value from database by a select statement in the same procedure.

** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...

Read More

How To Get Day Name From Date Time In SQL Server

Introduction

 

While working with the SQL Server you may need sometime to retrieve the day name from a date time stamp. If you want to find it out then you are in the right place.

Here is how to get the day name from a date time in SQL Server 2005/2008.

Behind The Scene

 

Just execute the below query in SQL Server and you will find the current day name.

 SELECT Datename(dw, Getdate()) AS 'Day Name' 

The above select statement is taking the input as "GETDATE()" and retrieves the day name from it. If you want to use it in any procedure or user defined functions then you can replace the "GETDATE()" wiht the time stamp column name. See the below query.

 SELECT datename(dw,StartDate) AS 'Day Name' FROM MyTimeSheet

The above query selects only the day names from the table "MyTimeSheet" which has a column named "StartDate".

Happy Coding...

Read More

Difference Between Function And Procedure In SQL

Introduction

We are very familiar with this two things if we are familiar with the database thing. procedures are called from our web application or window applications and procedures are too. Both are meant to serve almost the same purpose, but there is a difference between them. 

Here in this article I will explain that with some examples.

Behind The Scene

Stored Procedure:

         Stored procedure is a program that is physically present inside the database and those are executed by the database engine itself ( automatically ) in response to a call from the user.A stored procedure may return anything or not.

User Defined Function:

  
        A user defined function is a block of logic that is written for performing a specific task. Those can not be executed directly by the database engine in response to the user call. But a function must return something.

There are three types of User Defined Functions as below;

• Scalar Valued Function : Can return a scalar value like an integer, TimeStamp, string. So this function can be used as a column name in a query.

• Inline Function : Can contain a single select statement.

• Table Valued Function : Can contain multiple row sets, that means it can return a table with the help of multiple statements.

Difference Between Procedure and Function :

Procedure :

1. It can contain any update, delete, alter, open statements.
2. Returns integer integer value always by default.
3. Can have input/output parameters.
4. Procedures can not be called from a user defined function.
5. Exception can be handled by using try - catch.
6. We can use transactions inside a procedures.
7. Can not be called as an inline query ( can not be called from a select statement )

Function :

1. A function can contain a insert, update delete statements but that function can not be called from a SQL query.
2. Returns one value which is mandatory.
3. Can have only input parameters on output parameters.
4. Function can be called from a procedure.
5. No Exception handling can be done by using try - catch block.
6. Can not use transaction inside a function.
7. Can be called in a SQL query ( that behaves as a column in a select statement ; only scalar valued function )

That's all for the differentiation between User Defined Functions and Stored Procedure.
Happy Coding...

Read More

Non Clustered Index Scan Vs Non Clustered Index Seek

Introductions 

The concept of non clustered index is like the index page in the behind of a book. Consider your C# book, lets say you are trying to search a topic named "delegates" then what you will do ?

You will go to the index page and search there for this " Delegates" value and then you will move to the corresponding page.

Consider if the index page was not there then you have to scan all the pages of the book in order to find the particular page.

Something exactly like the above scenario happens in the database. If you create an index page ( here it is called as Non Clustered Index ) in the table then it will minimize the table scans hence providing you better an execution plan.

Behind The Scene 

Before we proceed you need the AdventureWorks2008R2 Database installed in your SQL Server 2008 R2. Download the database here from Codeplex Download

And download the query presenting all the things I am going to explain here from the below download link.

   Download Source Code Here

Now, Just create a new table with the bellow query, upon which we will experiment through out this article.

-- CREATE A TABLE TO EXPERIMENT ON THE NON CLUSTERED INDEX SCAN AND SEEK
 
SELECT * INTO My_DemoSalesOrderDetails
FROM [Sales].[SalesOrderDetail]

This query will create a new table named My_DemoSalesOrderDetails in the AdventureWorks Database. Now just add the primary ke constraint to the newly created table by executing th bellow query.

-- ADD THE PRIMARY KEY CONSTRAINT TO THE NEWLY CREATED TABLE.

ALTER TABLE My_DemoSalesOrderDetails
ADD CONSTRAINT [PK_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED
(
  [SalesOrderID] ASC,
  [SalesOrderDetailID] ASC
)

Now before moving forward for any further demonstration we will set the statistics IO on so that we can look into some important parameters. and also select the execution plan ( CTRL + M ) .

-- SET THE STATICS IO ON

SET STATISTICS IO ON
GO

Fine, we are doing well. Here is the time to add the "Non Clustered index" to the table In order to add the index just run the below query, here we are adding 2 columns named [OrderQty] and the [ProductID] as the non clustered index.

-- CREATE INDEX WITH OrderQty and ProductID

CREATE NONCLUSTERED INDEX [ IX_MySalesOrderDetail_OrderQty_ProductID]
ON My_DemoSalesOrderDetails
([OrderQty], [ProductID])
GO

Non Clustered Index Scan:

      Good, now just try to select * form the tabe now.

-- SELECT * FORM THE TABLE

SELECT SalesOrderID, SalesOrderDetailID,ProductID, OrderQty
FROM My_DemoSalesOrderDetails 

you will see in the message tab that a logical read of 257 has occurred, and in the Execution plan tab you can find an Index Scan has encountered.

Non Clustered Index Seek:

Now just add a where cluse to the select statement and see the message tab and the execution plan tab.

-- SELECT * FORM THE TABLE WITH A WHERE CLAUSE
SELECT SalesOrderID, SalesOrderDetailID,ProductID, OrderQty 
FROM My_DemoSalesOrderDetails 
   WHERE OrderQty = 1

In the message tab you will find a logical read of 160, and in the execution plan tab you will be able to see that an Index Seek operation has occurred.

Up to now, I hope you got the idea behind the non-clustered index scan and the non-clustered
seek.

But one thing I want to say here if you slightly change the where clause by adding productID over there instead of OrderQty, then you will see a logical read of around 1496 occurred in the message tab.

So in order to decrease the logical reads you can add another non-clustered index to the table with productID first.

-- CREATE INDEX WITH ProductID and OrderQty 

CREATE NONCLUSTERED INDEX [ IX_MySalesOrderDetail_ProductID_OrderQty]
ON My_DemoSalesOrderDetails
([ProductID],[OrderQty])
GO

now, if you execute the previous query then you will find a logical read of just 9. That's it, From the above we can conclude that a table can have multiple non clustered indices.

Note :

While using SQL Server we should think about the query optimization, I mean the efficiency of the SQL query in terms of CPU cost, IO cost and execution time.

So It is a better idea to implement the clustered indexing ( Primary Key ) in the tables, so that in each case a request for selecting any document goes to the server, it does not need to scan all the records present instead the qualifying ones.

Happy Coding...

Read More

Clustered Index Scan vs Clustered Index Seek in SQL Server

Introductions 

Writing a select statement in the SQL Server may do 2 types of operations

•     Table Scan
•     Index Scan

If the table is a small one, you are writing the select query upon, then table scan may not be a problem. But when your select query is operating upon a table that contain some millions of records then it might be a problem as each time you are trying to select something then the table scan is performed in the background.

Just consider a simple case of GMAIL. There are approx. 50 millions of user ( lets say ) of Google, so in each and every time when a user tries to log in to his/her account then you think what will be happening in the background ? If we assume 50,000 users try to log in to their webmail at a single time then what will be the count that the [User] table will be executed ??  Is it be 50 millions X 50,000 ?

Here is the problem with table scan, so we need to decrease the scan count and for which the Indexing functionality comes to action.

Here in this article I will explain the difference between Clustered Index Scan and Clustered Index Seek. And their usefulness depending on their execution plan and execution cost.

Before we proceed you need the AdventureWorks2008R2 Database installed in your SQL Server 2008 R2. Download the database here from Codeplex Download

And download the query presenting all the things I am going to explain here from the below download link.

Download Source Code Here

Behind The Scene 

Clustered Index Scan:

      As scan means touching through all the records present, hence while you are performing any clustered index scan then it touches all the rows present in the table. 

      Here the cost is  proportional to the total no of rows present in the table. Let you have written a select statement

---------------------------------
-- Culstered Index Scan and Culstered Index Seek
---------------------------------
-- CTRL + M
-- Build Sample Example

SET STATISTICS IO ON 
GO 
SELECT * FROM MySalesOrdreDetails
-- logical reads 1495
GO

This select statement will select all the rows present in the MySalesOrderDetails table. and to see the execution plan please press CTRL + M while executing the above query. Here I have written the STATISTICS IO ON  in order to see the IO cost.

While executing the above query I found a logical read of 1495.

Consider another case ;

 Now, just change the above query and add a WHERE clause there

---------------------------------
-- Culstered Index Scan and Culstered Index Seek
---------------------------------
-- CTRL + M
-- Build Sample Example

SET STATISTICS IO ON 
GO 
SELECT * FROM MySalesOrdreDetails
WHERE SalesOrderID = 60726 AND SalesOrderDetailID = 74616
-- logical reads 1495
GO

Here if you look into the message tab after executing this then also you will find that the logical read is the same 1495. Now the question arises why after giving a condition why the logical read comes to 1495.
   Well, the answer is that in both cases if you look into the execution plan then you will find in both cases a "Table Scan" opertaion is running in the back end. that's why the logical read counts remains the same in both cases.

Clustered Index Seek

  As seek means selecting a particular( qualifying ) record form a group of records. Hence, when you perform a clustered index seek in sql server the compiler will touch only the qualifying rows.

      The clustered index seek happens in a table when the table got a primary key and upon which the table is re structured in to a B-Tree while execution, returning a lower logical reads.

Now lets add the clustered key to the table you have created before ( MySalesOrderDetails )

---------------------------------
-- Culstered Index Scan and Culstered Index Seek
---------------------------------
-- CTRL + M
-- Build Sample Example
-- Create clustered Index
ALTER TABLE MySalesOrdreDetails
ADD CONSTRAINT [PK_MySalesOrderDetail_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
)

 After altering the table now go ahead and do the same thing we did previously, Run the First Script
 ( select * from MySalesOrdreDetails )  here you will see the logical reads as 1501. A bit more than the previous one the more 6 steps are added during the creation of the initial node and the transition between the nodes of the B-Tree.

If you notice the execution plan over there then you will find that there is no table scan is done here instead a clustered index scan is done here.

Now run the second script ( with where cluse )  you will find a logical read of 3 only. and if you mark in the execution plan then  there will find this as a clustered index seek operation.

Note :

 

While using SQL Server we should think about the query optimization, I mean the efficiency of the SQL query in terms of CPU cost, IO cost and execution time.

So It is a better idea to implement the clustered indexing ( Primary Key ) in the tables, so that in each case a request for selecting any document goes to the server, it does not need to scan all the records present instead the qualifying ones.

Happy Coding...

Read More

Convert Table To Comma Delimited String In SQL Server 2008

Introduction

Its very often to convert a database table to comma delimited string for various purposes while working in SQL Server. Here I will give an example to convert a database table to a comma delimited string.

Download Source Code Here

Behind The Scene

Before going to use the script that I will provide here you need to have some basic idea about the keyword STUFF ( Transaction SQL ).

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF ( character_expression , start , length , replaceWith_expression )

character_expression

    Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start

    Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

length

    Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

replaceWith_expression

    Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

Here use the query to convert the table to comma separated string value. Here the table used is 'YourtableName' replace it with the required table name, and the column name is 'Name'.

 SELECT ComaDelimitedString = STUFF(( SELECT ',' + CAST(Name AS NVARCHAR(max))  
                  FROM YourTableName
                                    FOR XML PATH ('')  
                                     ),1,1,'')

Happy Coding...

Read More

How To Remove Duplicate Rows From SQL Server 2008

Introduction

While working with SQL Server you might come across some situation where you need to remove the duplicate rows from the table. Here is a simple query that will do the job for you very easily.

Download Source Code Here

Behind The Scene

Here I am taking a table named 'YourTableName' that contains only one column named 'value'. This column contains some duplicate data, those I need to remove by a script. The script is bellow. Just update the table name in the place of  'YourTableName'  and the column name in place of  'value'.

DELETE FROM  YourTableName
    WHERE YourTableName.%%physloc%%
    NOT IN(SELECT MIN(b.%%physloc%%)
             FROM   YourTableName b GROUP BY b.value);

Now you have removed the duplicate names from the table, to check this just run the below query.

SELECT o.value
FROM YourTableName o
INNER JOIN (
    SELECT value, COUNT(*) AS dupeCount
    FROM YourTableName 
    GROUP BY value
    HAVING COUNT(*) > 1
) oc on o.value = oc.value

Happy Coding...

Read More