AVG() function in sql server

It will return the average value of a numeric column .

Syntax:

SELECT AVG(column_name)

FROM table_name

Example:

SELECT AVG(totalMarks)

FROM Students

IIF function in SQL Server

It is a SQL Server function in which you specify an expression and two possible result values,

So if the condition satisfies then it returns the first value if not then it returns the second.

You can also use it instead of CASE , in fact it is a shorthand for CASE . The query execution

plan Is also the same it just increase the readability so you might call it as  Syntactic sugar

for your code. It is familiar to ? keyword  used in c# .

Syntax:

IIF ( Boolean_expression, True_value, False_value )  

Examples:

SELECT IIF( 33 > 3,  'true' , 'false')

-- Result :  true

SELECT IIF( 2 > 3,  'true' , 'false')

-- Result :  false 


DECLARE  @passingMarks INT = 33 , @achievedMarks INT = 70

SELECT IIF(@achievedMarks >= @ passingMarks ,  'Congratulations! You have passed'  ,  'Sorry! You ditn’t make it')

-- Result :  Congratulations! You have passed

How to get definition of Stored Procedure in Sql Server

If you have a stored procedure name "GetStudents" and you want to get it's defintion then
you need to write:

sp_helptext  'GetStudents' 

so syntax is : sp_helptext <your stored procedure name>

Paging of Large Datasets in Sql Server

When you are fetching large "datasets" using ROW_NUMBER(), you might experience a long

delays in getting result and sometime it timeout expired, To overcome this situation you 

need a better and efficient approach, Fortunately there is an efficient solution for that

teasy situation but question is what .

"Table Variables" are light weight because they does not allow explicit addition of indexes 

after it's declaration only implicit indexes can be created using primary key or unique key 

and also scope of the table variable is the Batch or Stored Procedure in which it is declared. 

And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes. 

So in a situation like this it can be useful in a way that create a table variable and insert
fetched data in it along with an auto incremented id then fetch the records from that
temp table with paging filter applied then you would get you expected result.

Here is a small demonstration :

CREATE PROCEDURE GetEmployee

       @PageSize BIGINT = 10,

       @PageNo BIGINT = 1

AS

BEGIN

       SET QUOTED_IDENTIFIER OFF

       DECLARE @TempItems TABLE (

               Rowid BIGINT IDENTITY

              ,EmployeeID BIGINT

              ,EmployeeName VARCHAR(155)

              )

       DECLARE @maxRow BIGINT    

       SET @maxRow = (@PageNo * @PageSize) + @PageSize + 1

       SET ROWCOUNT @maxRow

       INSERT INTO @TempItems (

               EmployeeID

              ,EmployeeName

              )

       SELECT *

       FROM Employee

       SET ROWCOUNT @PageSize

       DECLARE @minimumRange BIGINT, @maximumRange BIGINT

       SET @minimumRange = (@PageNo * @PageSize) - @PageSize

       SET @maximumRange = (@PageNo * @PageSize + 1)

       SELECT *

       FROM @TempItems t

       WHERE Rowid BETWEEN @minimumRange AND @maximumRange

       SET ROWCOUNT 0

END

Select Day from Date in Sql Server

SELECT Day('2015-03-05 23:15:36.710')

-- Result: 5 

Select Month from Date in Sql Server

select MONTH('2015-03-05 23:15:36.710')

-- Result: 3 

Select Year from Date in Sql Server

SELECT YEAR('2015-03-05 23:15:36.710')

-- Result: 2015

Get PI value in Sql Server

SELECT PI()

-- Result 3.14159265358979

Round values to N number of Decimal points in sql server

Round (expression , length)

SELECT ROUND(3.1415 , 2)        -- 3.14

SELECT ROUND(33.234234234 , 1)  -- 33.2

SELECT ROUND(55.6345345 , 0)    -- 56

Find Length of string with LEN function in sql server

It returns the number of characters of string expression but with excluding trailing blanks.

SELECT LEN('Sql Server Database')

-- Result : 19

Get List of all tables of a database in sql server

SELECT * from sys.tables

Get List of all stored procedures of a database in sql server

SELECT * from sys.procedures

Find Age from Date of Birth in Sql Server

declare @dob datetime = '1952-08-14 21:11:19.300'

select CAST( DATEDIFF(Y , @dob , getdate() )/365.25 as int)

-- 365 are the Number of Average days in 4 years

Select Top N rows from a table in Sql Server

Suppose if N=10 then ,

Select Top 10 * from Person

IF you want to Select only particular columns then,

Select Top 10 Name , City , Age from Person

Get current system date in Sql Server

SELECT GETDATE()

-- Result : 2014-03-23 19:57:21.630

Find Version of Sql Server through query

select @@Version

Result from My Computer:

--Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

--Feb 10 2012 19:39:15

--Copyright (c) Microsoft Corporation

--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Reverse values with Reverse function in Sql Server

select REVERSE(45)  -- Result 54

select REVERSE('abc') -- Result cba

Select Last Date of Current year in Sql Server

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()) + 1, -1) AS LastDateOfYear

Select First Date of Current year in Sql Server

SELECT DATEADD( yy, DATEDIFF( yy, 0, getdate() ), 0) AS FirstDateOfYear

Dynamic Query in Sql Server

It is used commonly when you are Selecting the Result set on the base of some parameter or condition ,  or to optimize the Sql to generate the accurate query at Runtime according to the Requirements. .

suppose we have a Stored Procedure which takes a single Parameters , which is a table name and generates the query according to it.

Here is a simple Example below:

Create procedure DynamicQuery

(

@tableName varchar(33)

)

as

BEGIN

Declare @SelectQuery Varchar(25)

If (@tableName = 'Teacher')

set @SelectQuery = 'select * from Teacher'

exec (@SelectQuery)

If (@tableName = 'Student')

set @SelectQuery = 'select * from Student'

exec (@SelectQuery)

END