SQL Interview Questions for experienced professionals

<<Previous Page                                                                          Next Page>>

                                                                        

Question 46: How to calculate last date of the current month using SQL Server?
Answer -  Find the below SQL query -

Select DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)) 

More detail...

Question 47: How to drop and recreate constraint in  SQL Server table?
Answer -  It would be quite simple query -

Drop constraint

Alter table <Table_Name> Drop constraint <Constraint Name>

Recreate constraint

Alter table <Table_Name> Add constraint <Constraint Name> foreign key

(<Child Column Name>) References <Master Table Name>(<Column Name>)

Question 48: How to drop and recreate constraint in  SQL Server table?
Answer -  It would be quite simple query -

Drop constraint

Alter table <Table_Name> Drop constraint <Constraint Name>

Recreate constraint

Alter table <Table_Name> Add constraint <Constraint Name> foreign key

(<Child Column Name>) References <Master Table Name>(<Column Name>)

Question 49: How to find all constraint from a table in SQL Server table?
Answer -  It would be quite simple query -

SELECT * FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT%' and parent_object_id=object_Id('TableA')

Question 50: How to free the procedure cache in SQL server?
Answer -  DBCC FREEPROCCACHE

Question 51: how to find the occurrence of a character in a string in sql server?
Answer -  Here's another way of determining the number of times a certain character occur in a given string without the use of a loop.

select LEN('sachan')-LEN(replace('sachan','a',''))

Question 52: How to enable of disable the clustered index on a table in sql server?
Answer -  how to enable a disabled index in SQL Server 2014 by using SQL Query -

Enable the index - alter index ClusteredIndex on Temp rebuild

Disable the index - alter index ClusteredIndex on Temp disable

Question 53: Will SELECT *FROM TABLE will work if we disable the clustered index?
Answer -  No, It will not work. It will through the error message that Query processor is unable to process the plan.

Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index 'ClusteredIndex' on table or view '#Temp' is disabled.

Question 54: Assume the employee table, which has six rows. What will be the output for the following set of queries -

(i.) Select count(*) from employee
(ii.) Select count(1) from employee
(iii.) Select count(2) from employee
(iv.) Select count(NULL) from employee


Answer -  Count function will work normally with '*' or any number like 1,2,3 ..
But when we pass NULL to the count function, it will through an error. Find the below results respectively -

(i.)    6
(ii.)   6
(iii.)  6
(iv.) Error message will be -Msg 8117, Level 16, State 1, Line 1

Operand data type void type is invalid for count operator.

Question 55: Can we create a view which contains a Order by clause.
Answer - No, we can not create the view in which where clause is involved unless Top or For XML is also specified.

Error message - Msg 1033, Level 15, State 1, Procedure vw_Destination, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. 

Question 56: Can we apply join between two or multiple views?
Answer - No, we can not apply join between two or more views.

Error message - Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "sample.vw_SampleId" could not be bound.

Question 57: How to use '_' UnderScore character in the like Query of SQL server?
Answer - Close the UnderScore character into brackets like '%[_]text%'. It will provide the expected result.

Example - Select *from <Table_Name> where name like '%[_]Sachan%'

Question 58: How to generate the delimited data  in the SQL server?
Answer - We can easily generate the delimited data using SQL server, follow the below sample query -
Example - SELECT 
  STUFF((SELECT distinct ' ''' + JobId
         FROM #Temp p1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') 

Question 59: How to get current RowVersion value for a database in SQL server?
Answer - Use this at SQL Query analyzer - Select @@DBTS

Question 60: How to get minimum RowVersion value for a database in SQL server?
Answer - Use this at SQL Query analyzer - Select min_active_rowversion()