SQL Server Interview questions

In SQL Server there is a job runs daily, pulls data from flat files, The flat files are published by a third party vendor, we got a situation, one fine day Job didn’t ran, we missed that days data, next day vendor has overwritten flat files, because they will give flat files with same name daily.
We need to have design, if we missed the data of last day also we should be in a position to pull the data.

What are the different ways of pulling data from Flat/Excel files
1. OpenQuery
2. OpenDatasource
3. iSql
4. BulkInsert

How to execute sql statements which is in a text file?
SQLCMD

You have a flat file, how to pull data into a table which is having a Identity column?

What are the different things you will take care while writing a Stored Procedure?

I have below tables of a Student Database.

Student
———-
SID, SNAME

Class
——-
ClsID, CName

Subject
———
SubID, SubName

Marks
——–
SID, ClsID, SUBID, Marks

Write a query to get topper of the student?
Write a query to get student details who are not attended at least one exam?
if we cannot write above query what is the change in the above data model, to make it easy.

What are the different transaction isolation levels?

How did we provided column level security in our project?

What are the different data flow tasks we have in SSIS?

What are the different providers in SSIS to pull the data from different sources?

What is most challenging task you have ever faced? how did you handled it

Which lock will on a table when you put WITH (NOLOCK) in your select statement?

What are the different types of Locks in SQL Server and what is the concept of Lock escalation?

What is the difference between ISNULL, COALESCE and NULLIF?

Finding number of records in all tables of a database with out using sp_MSForEachTable

Below is the script which loop thorough all tables and find out the number of records.

without using sp_MSForEachTable Undocumented Stored procedure
Use YourDatabase
GO
declare @i as int;
declare @n as int;
declare @tablename as varchar(50)
declare @sStr varchar(1000)
 
DECLARE @tables table(id int identity(1, 1) , tablename varchar(50))
DECLARE @tablesCount table(tablename varchar(50), cnt int)
 
insert into @tables(tablename)
select Schema_name(schema_id) + ‘.’ + name as tablename
from sys.objects where type = ‘U’
–select * from @tables
 
set @i = 1;
 
select @n = max(id) from @tables;
set @sStr = ”;
while(@i <= @n)
BEGIN
      select @tablename = tablename from @tables where id = @i;
 
      set @sStr = ”;
      set @sStr = ‘select ”’ + @tablename + ”’, count(1) from ‘ + @tablename
      –print @sStr
      insert into @tablesCount
      exec (@sStr)
      set @i = @i + 1;
END
select * from @tablesCount;
GO

With using sp_MSForEachTable undocumented stored procedure
USE yourdatabase
go
DECLARE @tablesCount table(tablename varchar(50), cnt int)
insert into @tablesCount
EXEC sp_MSForEachTable ‘ select ”?”, count(1) from ?’
select * from @tablesCount
GO

Assign a value to a regular identifier in T-SQL

There are 8 distinct T-SQL constructs that can assign a value to a regular identifier with a leading ‘@’ without using neither SET nor SELECT

Explanation: Probably too much to hope we can avoid a big discussion on ‘distinct’ constructs, but here is how I count them:

1. An Input argument to a Procedure or Function – http://msdn.microsoft.com/en-us/library/ms187926
2. An Output argument to a Procedure – http://msdn.microsoft.com/en-us/library/ms187926
3. EXECUTE a Function: EXEC @return = udfFunct() – http://msdn.microsoft.com/en-us/library/ms188332
4. The Stored Procedure return status: EXEC @status = uspProc (This is very different from a Function return.) – http://msdn.microsoft.com/en-us/library/ms188332
5. The OUTPUT clause: OUTPUT INTO @tablevar (Could count this 4 times but it is really one construct) http://msdn.microsoft.com/en-us/library/ms177564
6. RECEIVE …. FROM INTO @tablevar – http://msdn.microsoft.com/en-us/library/ms186963.aspx
7. FETCH NEXT FROM cursor INTO @varname – http://msdn.microsoft.com/en-us/library/ms180152
8. DECLARE @varname INT = 0; – http://msdn.microsoft.com/en-us/library/ms188927

Ref: Definition Regular Identifiers – http://msdn.microsoft.com/en-us/library/ms175874.aspx

Reseed Identity for all tables

USE DATABASE;
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'
GO

 

The different types of replication

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

Transactional
Snapshot
Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

Remedies for this rainy season

Hello All,

After very hot summer, Hyderabad is enjoying full-fledged rains. But as it is a rainy season we have to be very careful about our health, we have to very cautious.
The precautions we have to take
1. Keep an extra towel/dress with you always.
2. Keep some tablets for Diabetes/Asthma/headache/cold.
3. Don’t forget to carry rain coat/Umbrella.
4. Don’t hesitate to call 108 or local police for help if case of emergency.
5. Best to take bath with warm water after you reach home.
6. Take hot and fresh food.
7. Try to avoid outside food specifically Road side food.

Thanks & Regards
Prasad

Java Script function to show/hide a text box by clicking on a Radio Button

function ShowHide(msg, destinatinid) {
var tmpD;
var phc;
var phcDDL;

tmpD = document.getElementById(destinatinid);
if (msg == ‘No’)
tmpD.style.display = “none”;
if (msg == ‘Yes’)
tmpD.style.display = “”;
}

Adding this function to onclick java script function in C#

this.rbCAPNo.Attributes.Add(“onclick”, “ShowHide(‘No’, ‘” + tbCAPDetails.ClientID + “‘)”);
this.rbCAPYes.Attributes.Add(“onclick”, “ShowHide(‘Yes’, ‘” + tbCAPDetails.ClientID + “‘)”);