Differences between Clustered and Non-Clustered Indexes

Both Clustered and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.

Below are some characteristics of Clustered Indexes and Nonclustered Indexes in SQL Server.

Clustered Index:

1. The leaf node of a Clustered Index contains data pages of the table on which it is created

2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key

3. If the table does not have Clustered Index it is referred to as a "Heap"

4. A Clustered Index always has Index Id of 1

5. A Table can have ONLY 1 Clustered Index

6. A Primary Key constraint creates a Clustered Index by default *

* A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key

Nonclustered Index:

1. The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row *

* When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created)

2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.

3. A table may not have any Nonclustered Indexes

4. Nonclustered Indexes have Index Id > 1

5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created

6. A Unique Key constraint created a Nonclustered Index by default *

* A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key

Sql Query Max and Min Salary AND EmpName

create table emp1(id int,empname varchar(50),salary int,address varchar(50))

select * from emp1

select empname,salary from emp1 where salary in (select MAX(salary) from emp1)

Difference between Stored procedure and Function

Stored Procedure:

A stored procedure is a pre-compiled group of Transact-SQL statements .We can say astored procedure is a prepared SQL code that we save so that we can reuse the code over and over again.  If a repetitive T-SQL task has to be executed within an application, then thebest way for it is to create stored procedure.

It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.

You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.

Function:

 Function in Sql Server is a Transact-SQL or common language runtime (CLR) routine that takes

parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

Difference between Stored procedure and Function

1.       Function can return only 1 value whereas Stored Procedure can return many values(maximum 1024)

2.       Functions can have only input parameters for it whereas Stored Procedurescanhave input/output parameters.

3.       Function takes one input parameter which is mandatory but Stored Proceduremay take Zero to n input parameters.

4.       Functions can be used in a select statement where as Stored Procedurescannot.

5.       Functions can be called from Stored Procedure whereas Stored Procedurescannotbe called from Function.

6.       Stored procedures are called independently, using the EXEC command,whilefunctions are called from within another SQL statement.

7.       Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

8.       Stored Procedure can be used to read and modify data but function can only read data.

9.       Stored Procedure allows SELECT as well as DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE in it whereas Function allows only SELECT statement in it.

10.   Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

11.   Stored Procedures cannot be used as an inline with a select statement whileFunctions can.

12.   Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called. But Function is compiled and executed every time it is called.

13.   Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

14.   Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

15.   Stored Procedure allows Transaction Management whereas Function doesn’t.

16.   Stored procedures can be used to change server configuration settings (in terms of security-e.g. setting granular permissions of user rights) whereas function can't be used for this

17.   The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in Function you can’t perform use these commands.

18.   Normally the Stored procedures are used to process certain task but theFunctions are used to compute the values i.e. we can pass some value as input and then it perform some task on the passed value and return output.

19.   Stored Procedures can be executed using Execute or Exec command where asFunctions can run as an executable file.

20.   Functions can be used as user defined data types in create table but procedures cannot.

Trigger After Insert

alter TRIGGER AFTER INSERT ON asdf

AFTER INSERT

as

BEGIN

DECLARE @EMPID VARCHAR(50)

DECLARE @ENAME VARCHAR(50)

DECLARE @SALARY VARCHAR(50)

SELECT @EMPID= i.empid FROM inserted i

select @ENAME=i.ename from inserted i

select @SALARY=i.Salary from inserted i

DECLARE @SUBSTRING VARCHAR(50)=SUBSTRING(@EMPID,1,1)+'_'+SUBSTRING(@ENAME,1,1)+'_'+

SUBSTRING(cast(@SALARY asvarchar(50)),1,2)

update asdf set associatID=@SUBSTRING where empid=@EMPID

END

GO

Second highest salary------

Second highest salary------

SELECT*

FROM EmployeeDetails e1

WHERE (1)=(

SELECTCOUNT(DISTINCT(e2.Salary))

FROM EmployeeDetails e2

WHERE e2.Salary > e1.Salary)

selecttop 1 salary from

selectdistincttop 2 salary from EMPLOYEE orderby salary desc)

as salary orderby salary asc

Weekend days SQL

Weekend days

declare @DateFrom DateTime=CONVERT(DATETIME,'01/01/2014', 103)

declare  @DateTo DateTime=CONVERT(DATETIME,'01/03/2014', 103)

;WITH CTE(dt)

AS

(

Select @DateFrom

UnionAll

SelectDATEADD(d,1,dt)FROM CTE

Where dt<@DateTo

)

selectDayName,CONVERT(varchar(50), dt, 103)asDatefrom (select'Saturday'asDayName,dt from CTE

whereDATENAME(dw,dt)In('Saturday')groupby dt

Union

select'Sunday'asDayName,dt from CTE

whereDATENAME(dw,dt)In('Sunday'))as result orderby dt