SQL Server Interview Questions.


Few important question for SQL Server asked to ASP.Net Developer. Part- 1

Primary key is used to uniquely identify a record. A table can not have more than one primary key.
If a table has userid, email, passport no. and userid is the primary key then how to maintain uniqueness of other columns such as email and passport no.

That is possible with Unique key. We can have multiple unique key in a table.

Primary key doesn’t allow nulls, unique key allows one null.



Wild Characters

_ is to specify exactly one character.

Select * from table1 email like ‘_@_.com’

Will result all email id like a@a.com, x@x.com , y@y.com

[] Any character within the bracket.

Ex:- select * from table where name like ‘[KAS]%’

Will result all name which starts with either K, or A or S.


[^] Any character within the bracket.

Ex:- select * from table where name like ‘[^KAS]%’

Will result all name which does not starts with either K, or A or S.

Where vs Having in SQL

From the table only those record retrieved where gender is Male and then grouped.
Select gender, sum(population) as population from counting
Where gender = ‘Male’
Group by gender


All records are retrieved, they are grouped by gender and then shown records where gender is Male

Select gender, sum(population) as population from counting Group by gender
Having gender = ‘Male’

Inner Join – Returns only the matching rows in both the tables.


Left outer Join or Left Join-
Returns all the rows from left table.




Right Join
Returns all record from right table.


Full Join
All matching and non matching records from left and right table.


Cross Join-
Results no. of records into table A into no. of records in table B.

Self Join-

EmployeeID
Name
ManagerID
1
Mak
2
2
Tom
3
3
Shane
NULL

Here self join require as for Employee ID 1 , Manager is Tom which Employee ID is 2.

ISNULL()-

ISNULL('LastName','No Last Name')


Here is LastName returns null then "No Last Name" will be your result.

Union vs UnionAll-

Both are used to combine more than one select queries.

Union removes duplicate records, but Union All doesn't remove duplicate records.

Union is slower than Union All as Union needs to perform distinct sort.

Union vs Join -

Union combine rows from 2 or more tables, but JOIN combine columns from 2 or more tables.

Cast vs Convert- Use to convert one data type to another.

CAST (expression AS data_type [(length)])

CONVERT (data_type [(length)], expression[style])


Mathematical Functions in SQL Server:


ABS(-80.8) returns 80.8

CEILING(20.3) returns 21

FLOOR(15.2) returns 15

POWER(2,3) returns 8

SQRT(81) returns 9


User Define Functions in SQL Server:

  • Scalar function 
  • Inline table valued function 
  • Multi statement table valued function 

Scalar function may or may not have parameter and returns a value.


CREATE FUNCTION myfunction(@parameter1 datatype, @parameter2 datatype)

RETURNS return_datatype

AS

BEGIN

--WRITE FUNCTION CODE HERE

RETURN return value

END



For eg age calculation from DOB can be a scalar function.


Inline Table Valued Function

It returns Table.

CREATE FUNCTION myfunction(@city nvarchar(10))

RETURNS TABLE

AS

Return (Select id, name from Table1 where city = @city)



Above function will return result in a table based on passed parameter.


So in sql query we can utilize this function like this:


select * from myfunction('New Delhi')


Multi Statement Table Valued Function:

It can have BEGIN and END, whereas inline table valued function can't have.


Temporary Table

Create Table #Table(id int, name varchar(50), city varchar(50))


Temporary table get created in TempDB and are automatically deleted when they are no longer used.


2 types of Temporary table:


Local and Global



Local temporary table is available for the connection that has created, and dropped when the connection get closed.


Global Temporary Table:


Create Table ##Table1 (id int, name varchar(50))

Global temporary tables are available for all connection.

Comments

Popular

C# 6.0 New Features

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX

What is Cloud Computing - A guide for beginners