DATEDIFF function in SQL server ?

To return the count of Specifed Datepart between the startdate and enddate in SQL server,we can use the DATEDIFF function.

SYNTAX

DATEDIFF (DATEPART,STARTDATE,ENDDATE)

You can find the datepart and its abbreviations below.

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

STARTDATE - Refers to start date

ENDDATE - Refers to end date

Its return type is INT.

EXAMPLE

You can refer the examples in the following screenshot.

You can also get the (milli/micro/nano) seconds difference between two dates.

Note : If the return value exceeds the INT maximum value, then the error will be returned.

Error :

Msg 535, Level 16, State 0, Line 3

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

ROW_NUMBER function in SQL server ?

To return the Sequential number of row within the partition of a result set we can use the Row_Number function.

 For each partition the starting row number will be 1.

Its return type is BIGINT.

SYNTAX

ROW_NUMBER() OVER ( [PARTITION BY VALUE_EXPRESSION] ORDER BY CLAUSE)

EXAMPLE

In below example I have created Rownumber based on the UID column.

In below example I have created the department-wise Rownumber using Partition by clause.

Row_Number order will not exactly generated unless it satisfies the following condition.

• Values of ORDER BY columns are Unique
• Values of Partitioned column are Unique
• Values of both ORDER BY and Partitioned columns are Unique.

CAST & CONVERT function in SQL server ?

To convert from one datatype to another we can use cast and convert function in SQL server.

SYNTAX

CAST (EXPRESSION AS DATATYPE[LENGTH])

EXAMPLE

SYNTAX

CONVERT (DATATYPE[LENGTH],EXPRESSION,[STYLE])

EXAMPLE

you can use the various style format (such as 101,120,103) in convert function to get different types of date formats as shown below.

You can refer the below screenshot to get idea about possible conversion within the SQL data types.

How to find the dependency of objects in SQL server ?

In SQL server we can find the dependency of objects using system stored procedure.

Syntax

SP_DEPENDS [DATABASE NAME].[SCHEMA NAME].OBJECTNAME

Example

SP_DEPENDS 'dbo.StudentDetail'

It will display the list of objects which depend on the studentdetail table.

Lets say we have use this object name [StudentDetail] in stored procedure or function or trigger.

If you want to find the list of objects which uses this table and those details will be displayed.

If there is no object dependency then the below message will appear.

In below screenshot I have created new stored procedure with studentdetail table.So if you run the object dependency of the table then the following stored procedure will appear.

Similarly I have created the function using the studentdetail table.

 Similarly I have created the trigger using the studentdetail table.

You can also pass the stored procedure name in Sp_Depends stored procedure.It will give list of objects and its columns depends on the stored procedure.

You can also pass the function name in Sp_Depends stored procedure.

You can also pass the trigger name in Sp_Depends stored procedure.

Use of @@RowCount function in SQL server ?

To get the total No of Rows affected by the last statement, we can use the system function @@Rowcount in SQL server.

Syntax

@@ROWCOUNT

Example

In the above example, we are assigning the value of @@Rowcount to the int variable.

Based on the value obtained in the variable we are printing the No of Rows.

Instead of assigning the value we can directly check the value using @@Rowcount (e.g If (@@Rowcount > 0))

If the no of rows are more than 2 Billion  then use RowCount_Big. function.

Set NoCount ON statement will not impact the @@Rowcount function.

Note : This @@Rowcount function will return only the no of rows affected by Previous statement.(i.e the Statement before the @@Rowcount function)

Use of NOCOUNT statement in SQL server ?

To stop the message that shows the no of rows affected by the SQL statement or stored Procedures,

we can use the NOCOUNT statement in T-SQL statement as well as in stored procedures.

Syntax

NOCOUNT ON | OFF

Example

Before using NOCOUNT Statement.

In the message tab it will show the no of rows affected by the above SQL statement.

After using NOCOUNT statement

After using the NOCOUNT statement the no of rows affected by the above SQL statement is not returned.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

To improve the performance of the query we can use the NOCOUNT ON statement in SQL query.

Don't forget to OFF the NOCOUNT Statement at the end of the T-SQL statement. 

Use of CHARINDEX Function in SQL Server ?

CHARINDEX function is used to find the position of the expression in a given character expression.

Syntax:

CHARINDEX (ExpressionToFind,CharacterExpression,[Starting_Position])

Example:

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER')

Output:

4

Explanation:

The above example will return the position of the 'SQL' in the character expression.The expression 'SQL' is found in the 4th position of the character expression.

So it returns the output value as 4.

Starting_Position in CHARINDEX function is optional.

Let's see another example which uses the optional parameter.

If you want to return the position of the 'SQL' after the 4th position the you can use the following syntax.

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER',5)

Now the output value will be 28.

Now the function will skip the value from 1st to 4th character , then it will search for the next occurrence of the word in the character expression.

And finally it found the value at the 28th position in the character expression.So it returns the value as 28.

Note : If you want to find the position of the character from the starting position itself then no need to use the starting_position parameter if not so then you can go head with 3 parameters.

How to create the CLUSTERED INDEX in SQL server ?

To create the Clustered index in SQL server , we can follow up the two ways.

• Using T-SQL Query
• Using SQL Management studio

Using T-SQL Query

Syntax:

CREATE CLUSTERED INDEX [INDEX NAME]

ON [TABLE NAME](COLUMN NAME)

Example:

CREATE CLUSTERED INDEX IX_UID

ON Student (UID)

IX_UID --> Refers the Index name

UID --> Refers the index column name

Now the Index will be created on the UID column of Student table.

Using SQL Management studio

Connect to the SQL instance and select the database.

Expand the database and right click the Indexes folder and select New Index --> Clustered Index as shown below.

After selection the below screen will appear, there you can see the index name (highlighted in Red colour) as shown below.

You can also change the index name to a meaningful one to easily identify the index and then Click the add button to select the index column.

Select the required column to create the index and click OK button.(You can also choose the multiple columns to create the clustered index(also known as Cover indexes)) as shown below.

Then click the OK button in New index pop up window as shown below.

Finally the clustered index will be created in the table as shown below.

You can notice that the index will be mentioned as Clustered in bracket to identify the clustered index.