SQL Server Interview FAQ 1

Interview FAQs Part 1

•  Primary Key vs. Unique Key

Basically we say that Primary key is combination of Unique key and NOT NULL Constraint.

Primary Key=Unique Key +Not NULL

Primary key creates clustered index and does not allow null values. Unique key creates non-clustered index and allow one null value for which column it is defined.

•  What is use of CHECK Constraints:

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. It checks a condition on entered record, if it false record is not entered in to table.

CREATE TABLE EMP(

       EMPID   INT IDENTITY(1,1) PRIMARY KEY,

       EMPNAME VARCHAR (20)     NOT NULL,

       AGE  INT     NOT NULL CHECK (AGE >= 18),

       SALARY   DECIMAL (18, 2),      

   

);

Now if you insert a record as

insert into EMP (EMPNAME,AGE,SALARY) values('ABC',10,20000)

Message appears in OUTPUT window

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CK__EMP__AGE__1367E606". The conflict occurred in database "DBDB", table "dbo.EMP", column 'AGE'.

The statement has been terminated.

• Cluster Index vs. Non-Cluster Index

Only one cluster index can be created per table. When creating cluster index, SQL server reads the column on which index created and form a binary tree that decreases number of comparisons at time of searching data.

Multiple non-cluster indexes are allowed per table. Non-cluster indexes are more useful for columns that have repeated values. A non- clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.

Clustered Index automatically created on creating primary key on table. On creating UNIQUE Key ,Non-clustered index created.

• Function vs. Stored Procedure

Function	Stored Procedure
In SQL Server, functions are subroutines that encapsulate a group of T-SQL statements for reuse.	It is group of T-SQL statements compiled into single execution plan.T-SQL code inside stored procedure is stored on SQL Server in precompiled format.
Functions can have only input parameters.	Procedures can have input/output parameters
Functions can be called from procedure	Procedures cannot be called from function.
Try-catch not allowed inside functions	Exception can be handled by try-catch block in a procedure
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT as Table, View.	Stored Procedure cannot.

• Difference Between Group By and Order By

Group By is especially used for aggregating while order by used for ordering of values in ascending or descending.

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer.

SELECT * FROM Persons

ORDER BY LastName

•  What is trigger?

A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, delete, and update) occurs on table, view or database.

CREATE TRIGGER trigger_name
 ON    table_name | view_name
      FOR | AFTER | INSTEAD OF   [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
    AS    BEGIN
         --INSERT INTO dbo.CustomeractivityHistory.......
          --Your action statements.........
    END

•    Write a trigger to prevent drop database.

CREATE TRIGGER trg_preventionDrop
ON all SERVER FOR DROP_DATABASE
ASprint 'Drop Database not permitted'
ROLLBACK tran
 go CREATE TRIGGER trg_preventDropTable
ON all SERVER FOR DROP_TABLE
asprint 'Drop table not allowed'
ROLLBACK tran

•   What is LEN ()?

LEN () returns the number of characters in a string, excluding trailing spaces (both those included explicitly during the assignment and the ones added by the padding of data types char and nchar), but not leading spaces.

•   What will be the output

Declare @str1 char(5), @str2 char(5), @str3 char(5)

set @str1 = 'abcde'

set @str2 = ' ab'

set @str3 = 'ab '

select LEN(@str1), LEN(@str2), LEN(@str3)

Output:

5 3 2

• Union vs. Join

Union Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. Basic rules of Union are

·         The number and the order of the columns must be the same in all queries.

·         The data types must be compatible.

SELECT col1,col2,col3 FROM table_name1

UNION

SELECT col1,col2,col3  FROM table_name2

Join is used to fetch data from two or more tables, based on a relationship between certain columns in these tables.

SELECT t1.col1,t1.col2,t2.col3,t2.col4

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.col1=t2.col2

•  Union vs. Union ALL

Union:  It selects only distinct rows.

Union ALL: It selects all values without eliminating duplicate values.

• Can we create multiple identity column per table

     NO,Only one identity column is allowed per table.