Category: Sql Server

SQL Server Data Type Mappings

  SQL Server Database Engine type .NET Framework type SqlDbType enumeration SqlDataReader SqlTypes typed accessor DbType enumeration SqlDataReader DbType typed accessor bigint Int64 BigInt GetSqlInt64 Int64 GetInt64 binary Byte[] VarBinary GetSqlBinary Binary GetBytes bit Boolean Bit GetSqlBoolean Boolean GetBoolean char String Char[] Char GetSqlString AnsiStringFixedLength, String GetString GetChars date (SQL Server 2008 and later) DateTime Date GetSqlDateTime Date GetDateTime datetime… Read more →

sql query updated

–update table with swap between two columns update mmm_1 set id=phone,phone=id; –delete duplicate value from data table and select not duplicate also with x as( select empid,lname,deptno,rn=ROW_NUMBER() over (partition by deptno order by deptno),rn1=dense_rank() over (partition by deptno order by deptno), rn2=Rank() over ( order by deptno) from employees) delete x where x.rn>1; –delete all duplicate values delete employees where… Read more →

Recursive Queries Using Common Table Expressions

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained. A query is referred to as a recursive query when it references a recursive CTE.… Read more →

Updated sql questions

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like: @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ. Compare @@IDENTITY It returns… Read more →

Pivot table and Find DateDifference in fomat of Number of year,Month and Days in sql server

—-Use of datedifference Declare @PreviousDate datetime=‘2001-11-08’; Declare @CurrentDate date=CONVERT(date,GETDATE(),110); Declare @CurrentDate1 date=CONVERT(varchar(10),GETDATE(),110); declare @datediff int=DATEDIFF(DAY,@PreviousDate,@CurrentDate); Declare @NumLeapYear INT=DATEDIFF(YEAR,@PreviousDate,@CurrentDate)/4 –for Count of Leap Year but may be some extend to be need.; with FindDate as(Select @datediff as TotalDays, (   @datediff % 365)as NumMonth,–for calculate of MonthCount @CurrentDate as TodayDate,@CurrentDate1 as TodayDate1 ) Select   TotalDays/365 asYear, NumMonth /31 asMonth, (((NumMonth/31)%31)+@NumLeapYear)asdays,TotalDays… Read more →

Table variable , CTE and Temporary table scope

When to Use CTE: It stands for Common table was introduced on sql server 2005. Below is details for using a CTE: This is used to store result of a complex sub query for further use. It is  also used to create a recursive query. When to use Temporary Table: It is created at Run-time and behave same as… Read more →