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 →
Category: Sql Server
Datetime2,Datetime and Date Datatype differences
larger range of values better Accuracy smaller storage space (if optional user-specified precision is specified) 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 →
Sql query To find database information
///Sql server query EXEC sp_helpdb @dbname= ‘db1’; 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 expression.it 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 →