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.

Remove duplicate records from a table in SQL server

Here I create a table and inserted some duplicate records for testing purpose.

create table temp(cust_id varchar(50))

insert into temp(cust_id)

values

('oms1'),

('oms2'),

('oms1'),

('oms2'),

('oms3')

select *from temp

Method 1[Using ROWCOUNT]: 
You can simply use ROWCOUNT to delete duplicate record.

      --deleting 1 by 1

set ROWCOUNT 1

DELETE from temp where cust_id='oms1'

SET ROWCOUNT 0

select *from temp

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

          

insert into temp (cust_id) values('oms1')

Deleting all duplicate rows 

SET NOCOUNT ON

SET ROWCOUNT 1

WHILE 1 = 1

   BEGIN

        DELETE   FROM temp WHERE cust_id IN (SELECT  cust_id FROM temp GROUP BY          cust_id HAVING  COUNT(*) > 1)

   IF @@Rowcount = 0

         BREAK ;

   END

SET ROWCOUNT 0   

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 2[Using TOP Clause]: 

--Remove 1 by 1

delete top(1) from temp where cust_id='oms1'   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

                 

Method 3[With the help of Another Table]: 

If your data is small then you can use this way.Here I created a table with distinct records.Drop first one and rename second one.

SELECT DISTINCT cust_id

INTO    temp1

FROM    temp

GO

DROP TABLE temp

exec sp_rename 'temp1', 'temp'

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 4[Using ROW_NUMBER]:
It is very efficient way to achieve the same.]  

WITH  TEST

          AS (
             SELECT   cust_id

             , row_number() OVER ( PARTITION BY cust_id ORDER BY cust_id )                     AS rowcnt

             FROM  temp

             )

          DELETE  FROM TEST

          WHERE  rowcnt  > 1

   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms3

Querying Microsoft SQL Server : Basics of Triggers in SQL Server

Querying Microsoft SQL Server : Basics of Triggers in SQL Server: SQL Triggers:  A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, de...

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see a book, in last of book an index is provided that has some titles and page numbers. You don’t need ...

Querying Microsoft SQL Server : Control Statements in T-SQL

Querying Microsoft SQL Server : Control Statements in T-SQL: Control statements are used to control execution flow within a T-SQL.Here I provides some example on control statements. ·            ...

Querying Microsoft SQL Server : Defining Variables

Querying Microsoft SQL Server : Defining Variables: Defining Variables in SQL Server: Like other programming languages T-SQL allows to defining your variables. A variable is known as lo...

Find Transaction Log information

Transaction Log in SQL server records all operation on database.This recoring depends on Recovery Model selected for database.You can find Recovery Model for each database by following Query

SELECT

    name as DataBaseName,

    log_reuse_wait_desc,

    recovery_model_desc

    FROM sys.databases      

Output:

There are three Recovery Model as

1. Simple: In simple Recovery Model,SQL server truncates the log ate every Transaction Checkpoint.It can not be used for disaster recovery purpose.So you can choose this one on test Server.
2. Full: As  name suggested,In Full recovery model SQL server truncates transaction log every time you backup it under Full recovery Model.With this you can recover database up to a certain point in time.
3. Bulk-Logged: Performs logging of bulk operations.In this, Transaction log can be backed up quicker than with the FULL recovery model.
   
   
   

   How to see Transaction Log Size

   you can use DBCC command for same as
   DBCC SQLPERF(LOGSPACE);

   
   

   Output:

   

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   You see if you choose recovery model of your database FULL,then transaction size will increase frequently.So you need to truncate transaction log on regular basis to keep it from filling up.You can schedule a job for backup and truncation transaction log or you can create maintenance plan.
   
   You can shrink your Transaction Log File in below simple steps\

USE your_db

GO

ALTER DATABASE your_db_name SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(N'your_db_log_name', 1)

ALTER DATABASE your_db_name SET RECOVERY FULL WITH NO_WAIT

GO