SET ANSI_NULLS in sql server

Problem ::

                  some time we are try to select record from table where column is = null or column is not equal to null (<>) but we get zero records but in table a record is exist where column is = null or col -umn is not equal to null (<>),becouse somewhere in the query or proc we can add this statement
" SET ANSI_NULLS ON"
Click here to know about set-quotedidentifier-in-sql

Discription::

                                     When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name. When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value
and that are not NULL.

Check the following query for better understanding.

Create table #Temp(id int,Names Varchar(100))
Insert into #Temp(id,Names)values(1,'VITA')
Insert into #Temp(id,Names)values (2,NULL)
Insert into #Temp(id,Names)values (3,'ADITYA')
Insert into #Temp(id,Names)values (4,'PRANAV')
Insert into #Temp(id,Names)values (5,NULL)
Insert into #Temp(id,Names)values (6,'SANDIP')
Insert into #Temp(id,Names)values (7,'GAIKWAD')

Check the effect of "SET ANSI_NULLS OFF" on following query

SET ANSI_NULLS OFF
select * from #Temp where Names =NULL

Check the effect of "SET ANSI_NULLS ON" on following query

SET ANSI_NULLS ON
select * from #Temp where Names =NULL
select * from #Temp where Names =is NULL

SET QUOTED_IDENTIFIER in sql server

   Hi,
              In this article we will see a use of SET QUOTED_IDENTIFIER options in sql server. This options specifies  the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

Check my previous articles
Send mail by using store procedure
Dynamic query in sql server
initial in upper case
Example of cursor
delete duplicate records from table

For example ::

1) Try to create a table with name "select"

                                CREATE TABLE  SELECT (id INT)

2) Try to create a proc with name "delete"

                                CREATE PROCEDURE DELETE
                                AS
                                BEGIN
                                      PRINT'Impossible'
                                END

*)Now check the effect of "SET QUOTED_IDENTIFIER On" in sql server

1) we will again try to create table with name select by using below statement.

                            SET QUOTED_IDENTIFIER On
                            CREATE TABLE "SELECT"(id INT)

2)we will also try to create procedure with name delete by using below statement.

                            SET QUOTED_IDENTIFIER On
                            CREATE PROCEDURE "DELETE"
                            AS 
                            BEGIN
                                    PRINT  'possible'
                           END

example of Cursor in sql server

Simple example of Cursor.

Hi ,
          In this article we will see a how to use a cursor with some simple example. check a cursor indetails in my post Cursor Indetails

firstly we will see what is syntax of cursor in sql server.

Systax of Cursor::

step1: declaration
          declare @Cursor_name Cursor
step2: set set  
          set @Cursor_name = Cursor for
         Select  id From table_name
         --( You use your query statement)
step3: open cursor 
         open @Cursor_name
step4: Fetch Cursor
         fetch next
         From @Cursor_name into @ID
step5: process cursor 
          while @@FETCH_STATUS = 0
          begin
          print '@@FETCH_STATUS'+convert(varchar,@@FETCH_STATUS)
          -- you will write your statements here
          fetch next
          From @Cursor_name into @ID
          end
step6: Close cursor
          close @Cursor_name
step7: Deallocate Cursor
         deallocate @Cursor_name

Example::            
         for your better understanding we will see a example of cursor you create a temp table and fill some data in it.by using below statement.

        CREATE TABLE#Employee (ID int,emp_name varchar(50))
       INSERT INTO #Employee (ID,emp_name) VALUES(1,'anukrushna')
       INSERT INTO #Employee (ID,emp_name) VALUES(2,'krushna')
       INSERT INTO #Employee (ID,emp_name) VALUES(3,'pranav')
       INSERT INTO #Employee (ID,emp_name) VALUES(4,'aditya')
       INSERT INTO #Employee (ID,emp_name) VALUES(5,'harshal')
       INSERT INTO #Employee (ID,emp_name) VALUES(6,'sandip')
       INSERT INTO #Employee (ID,emp_name) VALUES(7,'ajit')

       Create one more table.

               
                CREATE TABLE#Employee_bk (ID int,emp_name varchar(50))

           Now we can write a cursor for fetch records from a #Employee table and fill it in #Employee back up table. Check a below query for achive this .

       declare @ID int
       declare @emp_name varchar(50)
       declare @getEmployee CURSOR
       set @getEmployee = CURSOR for
       select ID,emp_name
       from #Employee
       open @getEmployee
       fetch next
       from @getEmployee into @ID,@emp_name
       while @@FETCH_STATUS = 0
       begin
       begin try
       insert into #Employee_bk (ID,emp_name)values (@ID,@emp_name+' backup')
       print Convert(varchar, @ID) +' Inserted'
       print @emp_name+' Inserted'
       end try
       begin catch
       print 'Error in insert' +Convert(varchar, @ID)
       print 'Error in insert' +@emp_name
       end catch
       fetch next
       from @getEmployee into @ID,@emp_name
       end
       close @getEmployee
       deallocate @getEmployee
       select * from #Employee
       select * from #Employee_bk

SUMMERY ::

        In this article I try to explain how use cursor in sql server. I hope it is helpfull to you.I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Cursor in sql server

Cursor in sql.

     Hi,
          In this article we will discuss about a cursor in sql server,what is cursor ,use and disadvantages
of a cursor,types of cursor etc.

Click here For example of Cursor in sql server
What is Cursor object in sql server ?

                  A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This  temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

 Difference between Cursor and sql statement ? or when we use a cursor?

Generally our sql statement or sql query process a records in bench and a Cursor is worked with data or records on row by row basis. so if we need to do something on on row by row basis then you can  use Corsor.
E.g::     
  Some time we need to Update only one column of the all record in table,that time
we can use cursor.

There are two types of cursors in PL/SQL:

1) Implicit cursors ::

               We can use a Implicit cursor when DML statement like Insert,Update ,Delete are executed, a Implicit Cursor also used with select statement that return just one row is executed.

1) Explicit cursors ::

                        We can use a Explicit cursors with SELECT statement that returns more than one row.
Even though the cursor stores multiple records, only one record can be processed at a time,which is
called as current row. When you fetch a row the current row position moves to next row.

Difference between Implicit cursors and Explicit cursors ?

              Both implicit and explicit cursors have the same functionality, but they differ in the way they
are accessed.A Implicit cursor is hold only single record ,but in case of Explicit cursor it hold a multiple
records but it process a only one record at a time

disadvantages of cursor?

                 A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs. You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Subtypes of Cursors

1.Static Cursors

         A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable. You can't update, delete data using static cursor. It is not sensitive to any changes to the original data source.

2.Dynamic Cursors

      A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.

3.Forward Only Cursors

                  A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling.You can  update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source. There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the datato the cursor lifetime. It is not sensitive to any changes to the data source. A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source
.

4.Keyset Driven Cursors::  

   A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset.
The keyset depends on all the rows that qualified the SELECT statement at the time of cursor was opened.A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations.
By default keyset driven cursors are scrollable.

SUMMERY ::

        In this article I try to explain the what is Cursor , types of Cursor in SQL Server,and what is drowbacks or disadvantage of cursor. I hope after reading this article you will be able to understand different types of cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

View In Sql Query

Sql server:Basic About View.

1)What is View?
                              VIEW is a virtual table, defined by a query, that does not exist until it is invoked by name in an SQL statement.It means only defination of view is stored on database not a data It is used to implements the security mechanism in the Sql Server.
for example in login_master table have info of user and also have login credentials of User in that case a admin doesnot want to give you full access of a login_master that time we can create a view to get a access of other field to users.

2) The Standard SQL syntax for the VIEW definition is

Create View

     Create View Viewname As Select Column1, Column2 From Tablename Where (Condition)Group by (Grouping Condition) having (having Condition)

Alter View

    Alter View Viewname As Select Column1, Column2 From Tablename Where (Condition)Group by (Grouping Condition) having (having Condition)

How to Use View In Sql Query

   Select *from View Where (Condition)Group by (Grouping Condition) having (having Condition)

We will see a example of Views

Create a following tables and some data for a better understanding.

       create tabledbo.User_Login_Details
   (
           User_Id intnot null,
           User_Name varchar(100) null,
           User_Passowrd varchar(50) null,
           User_Count int null,
           User_Emp_Code varchar(50) null
   )

     create table Emp_mater
   (
         Emp_code int
        ,Emp_name varchar(100)
        ,emp_city varchar(100)
   )

Add Some Data in both table .

Create view on single table.

       Create View ViewSingleTable as select * fromdbo.User_Login_Details

alter view on single table.

       Alter View ViewSingleTable as selectUser_Name,User_Countfromdbo.User_Login_Details

create view on multiple table.

      Create View ViewSingleTable as select * fromdbo.User_Login_Details inner join Emp_mater on User_Emp_Code=Emp_code

How to use a view in query.

       select *  from  ViewMultiTable
    select *   from  ViewSingleTable

Sql server- send sql table data via email using store procedure

 Hi All, 
     In this article we will see how to send a sql table data via the mail body by using
sql store procedure.

Problem:

         Some time we need to send a sql table records via a mailbody in html format.
e.g- Send daily activity of jr person to Sr.person etc

Solution::

                  We will see a step by step solution of this problem below.

step1 :
           I start by creating a temporary table and populating it with some sample data

      Create table #Temp
     (
     [Id] [int],
      [Emp_Name] [varchar](128),
     [salary] [int],
     [department] [varchar](128)
     )

     Insert into #Temp
     select 1,'SA',12390,'INDIA'
     union all
     select 2,'SA',7965,'INDIA'
     union all
     select 3,'SANDIP',7880,'INDIA'
     union all
     select 4,'GAIKWAD',12390,'INDIA'
     union all
     select 5,'SAGRIKA',7965,'INDIA'
     union all
     select 6,'SAVITA',7880,'INDIA'

step2 : by bellow statement we will select a data from a table in Html format.

     declare @xmlnvarchar(MAX)
     declare @body nvarchar(MAX)
     set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
     [salary] AS 'td','', department AS 'td'
     from #Temp order by ID
     for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
     set @body ='<html><body><H3>Employee Info</H3>
      <table border = 1>      
      <tr bgcolor="#FF0000">
      <th> ID <th> Emp_Name </th> <th> salary </th>       <th> department
      </th></tr> '
     set @body = @body + @xml +'</table></body></html>'
     print @body

                     Once the HTML has been formatted I send the email using the system
stored procedure "sp_send_dbmail" found in the msdb database and finally I drop the
temporary table. A email sending part is bellow

exec msdb.dbo.sp_send_dbmail 'IMRAN' , @recipients = 'sandip.gaikwad@bbraun.com', @copy_recipients = '', @blind_copy_recipients='', @subject = 'DATA IN TABULAR FORMAT....' , @body = @body , @body_format = 'HTML'
drop table #Temp

final query:
                In order to use the code below, against your database table, you will have
        to replace the table name and column names of your table wherever necessary.
       Also, you will need to change the mail profile name and email address that you
       want to use.
Here is the sample code.

      Create table #Temp
     (
     [Id] [int],
      [Emp_Name] [varchar](128),
     [salary] [int],
     [department] [varchar](128)
     )

     Insert into #Temp
     select 1,'SA',12390,'INDIA'
     union all
     select 2,'SA',7965,'INDIA'
     union all
     select 3,'SANDIP',7880,'INDIA'
     union all
     select 4,'GAIKWAD',12390,'INDIA'
     union all
     select 5,'SAGRIKA',7965,'INDIA'
     union all
     select 6,'SAVITA',7880,'INDIA'

     declare @xmlnvarchar(MAX)
     declare @body nvarchar(MAX)
     set @xml = cast(( select [ID] AS 'td','',[Emp_Name] AS 'td','',
     [salary] AS 'td','', department AS 'td'
     from #Temp order by ID
     for XML PATH('tr'), ELEMENTS ) ASnvarchar(MAX))
     set @body ='<html><body><H3>Employee Info</H3>
      <table border = 1>      
      <tr bgcolor="#FF0000">
      <th> ID <th> Emp_Name </th> <th> salary </th>       <th> department
      </th></tr> '
     set @body = @body + @xml +'</table></body></html>'
exec msdb.dbo.sp_send_dbmail 'IMRAN' , @recipients = 'sandip.gaikwad@bbraun.com', @copy_recipients = '', @blind_copy_recipients='', @subject = 'DATA IN TABULAR FORMAT....' , @body = @body , @body_format = 'HTML'
drop table #Temp

Sql server:How to use Store Procedure inside a store procedure

Sql server:How to use Store Procedure inside a store procedure

        Hi,
                      In this article we will see how use store procedures inside a another store procedures. for your  better understanding we will see the following example.

Example::

create table #color and add some data into #color

   Create table #color (c_ID int ,COLOR varchar(100))

   Insert into #color (c_ID ,COLOR )values(1,'RED')
   Insert into #color (c_ID ,COLOR )values(2,'white')
   Insert into #color (c_ID ,COLOR )values(3,'blue')
   Insert into #color (c_ID ,COLOR )values(4,'green')

Create inner store procedure to select a data from a table.

      Create PROC INNER_PROC
           @ID   INT,
           @COMID   INT
     AS
    BEGIN
             SELECT * FROM DBO.COLOR WHERE ID=@ID AND COMID=@COMID
    END

Below is example of use Procedure inside a procedure.

    
    Create PROC OUTER_PROC
       @ID      INT,
       @COMID    INT
       AS
       BEGIN
               CREATE TABLE #TEMP(COMID INT,ID INT,NAMES VARCHAR(50),FRZ BIT)
               INSERT INTO #TEMP EXEC('INNER_PROC '+@ID+','+@COMID)
               SELECT NAMES FROM #TEMP
       END

       EXEC INNER_PROC 1,1
       EXEC OUTER_PROC 1,1

Sql server::String with initial letter in uppercase.

Sql server::String with initial letter in uppercase.

    Hi, 
        Some time we need to select a string with first later is Uppercase,I mean Initial letter is Uppercase.
in that case a following function helf to you.

             Create function [dbo].[InitCap] ( @InputString varchar(4000) )
            returns varchar (4000)
            as
            begin
            declare @Index INT
            declare @Char CHAR(1)
            declare @PrevChar CHAR(1)
            declare @OutputString varchar(255)
            set @OutputString = lower (@InputString)
            set @Index = 1
            while @Index <= len (@InputString)
            begin
            set @Char =substring (@InputString, @Index, 1)
            set @PrevChar = case when @Index = 1 then ' '
            else substring (@InputString, @Index - 1, 1)
            end
            if @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
            begin
            if @PrevChar != '''' or upper (@Char) != 'S'
            set @OutputString = stuff (@OutputString, @Index, 1, upper(@Char))
            end
            set @Index = @Index + 1
            end
            erturn @OutputString
            end

SELECT    [dbo].[InitCap] ('SANDIP GAIKWAD AITAE DFFDDF FSDFSFS')

Joins in Sql server

Joins in Sql server

Description ::

            In this article I can explain all type of join in sql server,with example . for your better understanding
I have created a following tables on which we will perform a join queres.

step 1: Create table

     create table #Name (id int , names varchar(50) )
     create table #NickName (id int , NickName varchar(50))
      create table #selfMaster(id int ,emp_name varchar(50),ManagerId int)

step 2: insert data

         insert into #Name (id,names)values (1,'sandip' ) 
         insert into #Name (id,names)values (2,'aditya' )
         insert into #Name (id,names)values (3,'pranav' )
         insert into #Name (id,names)values (4,'savita' )

         insert into #NickName (id,NickName)values (2,'babu' )
         insert into #NickName (id,NickName)values (3,'chandu' )
         insert into #NickName (id,NickName)values (4,'dhara' )
         insert into #NickName (id,NickName)values (5,'dhara' )

         insert into #selfMaster (id,emp_name,ManagerId) values (1,'sandip' ,2)
         insert into #selfMaster (id,emp_name,ManagerId) values (2,'aditya' ,3)
         insert into #selfMaster (id,emp_name,ManagerId) values (3,'pranav' ,4)
         insert into #selfMaster (id,emp_name,ManagerId) values (4,'savita' ,1)

s

step 3: inner join

An inner join essentially combines the records from two tables (A and B) based on a given join-predicate,
in simple words by using this join we can select a record from both table where a condition is matched .

Example::Inner Join Query

               
              select a.id,names,nickname from #name a
                 inner join #nickname b on a.id=b.id

step 4: Outer Join

In sql server there are three types of outer join used.

Types of Outer joins

Join 1: Left Outer join

     An left outer join is gives you all data ( Records) from left side table and matched data ( Records) from
right side table.

Example::Left Outer join Query

                   select a.id,names,nickname from #name a
              left outer join #nickname b on a.id=b.id

Join 2: Right Outer join

     An Right Outer join is gives you all data ( Records) from Right side table and matched data ( Records)
from  Left side table.

Example::Right Outer join Query

             select a.id,names,nickname from #name a
           right outer join #nickname b on a.id=b.id

Join 3: Full Outer join

          An Full outerjoin is gives you all rows from both tables, regardless of whether or not the other table
has a matching value.

Example::Full Outer join Query

                      select a.id,names,nickname from #name a
           full outer join #nickname b on a.id=b.id

step 5: Self join

               Some time we need to write a join on single table, in short a self join is applicable on two instance
of single table you can check following query for better understanding.

Example:: Self join Query

         select a.id,a.emp_name,b.emp_name Manager_Name from #selfMaster a join   #selfMaster b on b.id=a.ManagerId

Dynamic Query in sql server

Sql server:Dynamic Query

Hi ,
         In this example we will see how to add conditions dynamically in where clause and
joins in sql statement. Lets look at an example that shows how this works.

Assume you have a table called #info,#Post,#city created with the following statement.

      Create table #info (id int,Sname varchar(50))
      Create table #Post (Post_id int, id int,post varchar(50))
      Create table #city (city_id int,id int,city varchar(100))

Add Some data in all tables by using following statements

   insert into #info(id,Sname) values(1,'sandip.')
   insert into #info(id,Sname) values(2,'sandip_SP')
   insert into #info(id,Sname) values(3,'sandip_Ak')
   insert into #info(id,Sname) values(4,'sandip_Sk')
   insert into #info(id,Sname) values(5,'sandipG')

   insert into #Post(Post_id ,id,post) values(1,1,'Software Dev')
   insert into #Post(Post_id ,id,post) values(2,2,'Database admin')
   insert into #Post(Post_id ,id,post) values(3,3,'tester')
   insert into #Post(Post_id ,id,post) values(4,4,'HR')
   insert into #Post(Post_id ,id,post) values(5,5,'Team Lead')

   insert into #city (city_id,id,city) values (1,1,'kolhapur')
   insert into #city (city_id,id,city) values (1,1,'sangli')
   insert into #city (city_id,id,city) values (1,1,'satara')
   insert into #city (city_id,id,city) values (1,1,'karad')
   insert into #city (city_id,id,city) values (1,1,'maharastra')

Here is final query to achive a dynamic conditions and joins in sql statements

    declare @main_str varchar(max)
     declare @query_str varchar(max)
     declare @InnerJoin_str varchar(max)
     declare @Where_str varchar(max)
     set @query_str =' select * from #temp a '
     set @InnerJoin_str =' inner join #post b on a.id=b.id '

     set @Where_str= 'Where a.id=4 '
     set @main_str=''
     set @main_str=@query_str+@InnerJoin_str+@Where_str
     exec(@main_str)

    --Add Dynamacally where clause in sql query

    set @Where_str=@Where_str+ ' or b.post=''Software Dev'''
    set @main_str=''
    set @main_str=@query_str+@InnerJoin_str+@Where_str
    exec(@main_str)

 --Add Dynamacally joins in sql Query

   set @InnerJoin_str=@InnerJoin_str+ ' inner join #city c on a.id=c.id '
   set @main_str=''
   set @main_str=@query_str+@InnerJoin_str+@Where_str
   exec(@main_str)

Sql server:Delete duplicate records from table

Sql server:Delete duplicate records from table

                            Firstly, we will create a table, where we will insert some duplicate rows to
       understand the topic properly. 
     
Create a Temp table called #tbl_MyName by using the following code:::

          Create Table   #tbl_MyName
               (
                      RowID int identity(1,1) not null,
                      MyID int not null,
                      MyName varchar(50) not null
              )

Now insert some data into this table.

Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')
Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')
Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')

SELECT myid,MyName FROM #tbl_MyName

* Now use the following query to resolve the problem of duplicate rows

delete from #tbl_MyName where RowID in( select B.RowIDfrom #tbl_MyName B inner join
(select RowID,MyID,MyName, row_number() (partition by MyName order by MyID)
as duplicate from #tbl_MyName) A ON A.RowID= B.RowID where A.duplicate>1 )
select MyID,MyName from #tbl_MyName order by MyID asc

Now check the data. No duplicate rows exist in the table.

Sql server: DateName function in details

Sql server:DateName function indetail

            This article explains DATENAME function with different examples in sql server.
DATENAME function is used to get string of the specified datepart of the specified date.

Syntax of  DATENAME Function :

DATENAME ( datepart ,date )

Datepart specifies the part of the date to return.Date is an expression that returns a datetime
or smalldatetime value or a character string in a date format.

Return type of DATENAME function is nvarchar .

Examples::

GET YEAR FROM CURRENT DATE

  select DateName(year,getdate());
  select DateName(yy,getdate());
  select DateName(yyyy,getdate());

Get Quarter From current date

  select DateName(quarter,getdate());
  select DateName(qq,getdate());
  select DateName(q,getdate());

Get month name From current date

  select DateName(month,getdate());
  select DateName(mm,getdate());
  select DateName(m,getdate());

Get day number of year From current date

select DateName(dayofyear,getdate());
select DateName(dy,getdate());
select DateName(y,getdate());

Get day in int From current date

  select DateName(day,getdate());
  select DateName(dd,getdate());
  select DateName(d,getdate());

Get date name e.g -monday From current date

  select DateName(weekday,getdate());
  select DateName(dw,getdate());

Get hour From current date

  select DateName(hour,getdate());
  select DateName(hour,getdate());
  select DateName(hh,getdate());
  select DateName(hh,getdate());

Get minute From current date

  select DateName(minute,getdate());
  select DateName(mi,getdate());
  select DateName(n,getdate());

Get second From current date

  select DateName(second,getdate());
  select DateName(ss,getdate());
  select DateName(s,getdate());

Get millisecond From current date

  select DateName(millisecond,getdate());
  select DateName(ms,getdate());

Sql server:Column data in comma seprated string

Sql server:Column data in comma seprated string

A sql sever provide you a inbuild function COALESCE Function
to select a Column data of a table,to a comma seprated string.
By using bellow query we can select column data in comma separated string.

Example::

    create table #temp(id int ,names varchar(50) )

    insert into #temp(id,names)values (1,'SandipG')
    insert into #temp(id,names)values (1,'Aditya')
    insert into #temp(id,names)values (1,'Ajit')
    insert into #temp(id,names)values (1,'Chandu')

A query for select column data in comma separated string::

DECLARE @CommaSeperatedValues VARCHAR (MAX)
SELECT @CommaSeperatedValues = COALESCE (@CommaSeperatedValues+',' , '') + names
FROM #temp
print @CommaSeperatedValues

Take Back up of table using select statement in sql server

Sql server:Take Back up using select statement in sql server

Hi,
        We can see How to take a back up using select statement in sql server.

Example ::

Create a table by using following query

      Create table #info (id int,Sname varchar(50))

Insert data in to above table

   insert into #info(id,Sname) values(1,'sandip.')
   insert into #info(id,Sname) values(2,'sandip_SP')
   insert into #info(id,Sname) values(3,'sandip_Ak')
   insert into #info(id,Sname) values(4,'sandip_Sk')
   insert into #info(id,Sname) values(5,'sandipG')

Check following Query to take backup from our table #info to #backUp

  select * into #backUp from #info

Take a back up of table #info to #backUp with where clause

  select * into #backUp from #info where id=1

Ranks Fuctions in sql server

Ranks Fuctions in sql server

                       Many inbuild functions are provided by sql sever ,I can explain few of those. in this article
I can explain how to use  RANK(), DENSE_RANK(), NTILE (), and row_number() functions in sql server

for your better understanding I explain with examples .

Create table #ExamResult(name varchar(50),Subject varchar(50),Marks int)

Insert into #ExamResult values('Sandip','Maths',70)
Insert into #ExamResult values ('sandip','Science',80)
Insert into #ExamResult values ('sandip','Social',60)

Insert into #ExamResult values('Harshal','Maths',60)
Insert into #ExamResult values ('Harshal','Science',50)
Insert into #ExamResult values ('Harshal','Social',70)

Insert into #ExamResult values('Ram','Maths',90)
Insert into #ExamResult values ('Ram','Science',90)
Insert into #ExamResult values ('Ram','Social',90)
Insert into #ExamResult values ('Ram','Social',80)

1 ) RANK ()

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

1) Syntax :

RANK () OVER ([partition_by_clause] order_by_clause)

A below Query gives you output rank with a Partition by names

select Name,Subject,Marks,RANK() over ( partition by name order by Marks desc )Rank From #ExamResult order by name,subject

A below Query gives you output rank without a Partition

select Name,Subject,Marks, RANK() over ( partition by name order by Marks desc )Rank
From #ExamResult order by name,subject

2) DENSE_RANK()

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

1) Syntax :

DENSE_RANK() OVER ([partition_by_clause] order_by_clause)

A below Query gives you output DENSE_RANK() with a Partition by names

select Name,Subject,Marks,
DENSE_RANK () over (partition by name order by Marks desc )Rank
From #ExamResult
order by name

A below Query gives you output DENSE_RANK() without a Partition

select Name,Subject,Marks,
DENSE_RANK() over ( order by Marks desc )Rank
From #ExamResult
order by name

3) ROW_NUMBER()

1) Syntax :

row_number() OVER ([partition_by_clause] order_by_clause)

below Query gives you output row_number() with a Partition by names

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

select Name,Subject,Marks,
row_number() over (partition by name order by Marks desc ) rowid
From #ExamResult
order by name,subject

A below Query gives you output ROW_NUMBER() without a Partition

Returns the sequential number of a row of a result set

select Name,Subject,Marks,
row_number() over ( order by Marks desc ) rowid
From #ExamResult
order by name,subject

4) NTILE()

Syntax :

NTILE (integer_expression) OVER ([partition_by_clause] order_by_clause)

A below Query gives you output with a Partition by names and integer_expression=2

select Name,Subject,Marks,
NTILE(2) over (partition by name order by Marks desc ) rowid
From #ExamResult
order by name,subject

A below Query gives you output withOut a Partition by names and integer_expression=2

select Name,Subject,Marks,
NTILE(2) over (order by Marks desc ) rowid
From #ExamResult
order by name,subject

A below Query gives you output withOut a Partition by names and integer_expression=3

select Name,Subject,Marks,
NTILE(3) over (partition by name order by Marks desc ) rowid
From #ExamResult
order by name,subject

Get month wise Number of working

Sql server:Get month wise Number of working days in Year.

Hi,

By Using the following query we can get a Month wise number of working days in year.

 
        declare @StartDate datetime ,
                   @EndDate datetime ,
                   @Month int ,
                   @NoOfDays int
                   set @Month = 1
                  set @NoOfDays = 0
-- Temporary Table to store Month Wise No Of Working Days
           declare @tab as Table
      (
                 Mon int ,
                  NoOfWorkingDays int
)
while @Month <= 12
begin
set @StartDate = cast ( cast ( year ( getdate ()) as varchar(4))+'-'+cast(@Month as carchar (2))+'-01' as datetime )
set @NoOfDays = datepart (day, dateadd (s,-1, dateadd (mm, datediff(m,0,@StartDate)+1,0)))
set @EndDate = cast ( cast ( year ( getdate()) as varchar (4))+'-'+ cast (@Month as varchar (2))+'-'+ cast (@NoOfDays as varchar (2)) as datetime )
insert into @tab
selete @Month,
( datedeff(dd, @StartDate, @EndDate) + 1)
-( datedeff(wk, @StartDate, @EndDate))
-( case when datename(dw, @StartDate) = 'Sunday' then 1 else 0 end )
set @Month = @Month + 1
end
-- To Display Month Wise Number Of Working Days for the Current Year
selete case Mon
when 01 then 'JANUARY'
when 02 then 'FEBRUARY'
when 03 then 'MARCH'
when 04 then 'APRIL'
when 05 then 'MAY'
when 06 then 'JUNE'
when 07 then 'JULY'
when 08 then 'AUGUST'
when 09 then 'SEPTEMBER'
when 10 then 'OCTOBER'
when 11 then 'NOVEMBER'
when 12 then 'DECEMBER'
end as 'MONTH' , NoOfWorkingDays
from @tab

If You need to calculate total number of working days in year then just get
 sum(NoOfWorkingDays)

select Column data in Comma Separated string

Column data in Comma Separated string

hi, Check this article for select a column data in comma separated string.

Step1:: Create a table

create table #temp(id int,names varchar(50))

Step2:: Insert A data in table

insert into #temp(id,names)values(1,'w') insert into #temp(id,names)values(2,'c') insert into #temp(id,names)values(3,'a') insert into #temp(id,names)values(4,'m')

Step3:: Execute following query and check a output

DECLARE @CommaSeperatedValues VARCHAR(MAX) SELECT @CommaSeperatedValues = COALESCE(@CommaSeperatedValues+',' , '') + names FROM #temp print @CommaSeperatedValues