Sql Server Interview Questions and Answers (1758) - Page 19

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

Can we have option to use configuration file in dts package?

No, we don't have any option of configuration file in dts package.

Can we use Configuration file in SSIS Packages?

Yes, we can use configuration file in SSIS package with extension dtsConfig (XML File).

Name of the task that is used to execute batch file in SSIS package?

Execute Process Task is used to execute batch files.

Which Task in SSIS is used to perform operation on a file or directory?

File System task is used to perform operation like move, delete, create file opr directories.

What is the result of "SELECT 2/2/4" in SQL ?

NOTE: This is objective type question, Please click question title for correct answer.

Difference between Table Variable (@tbl) and temporary table (#temp or ##temp )

1. Transaction logs are not recorded for the table variables.

2. A procedure having temporary table is not pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance.

3. Table variables exist only in the same scope as variables.
Differencing to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

Can we execute "BCP" command directly in the SQL Server Management studio?

No, we can't not. BCP utility is a command line utility. To execute it within SQL Server Management studio, one has to enable the xp_cmdshell. We can execute bcp command through xp_cmdshell only. xp_cmdshell by default is false. We need to enable it.

Use below script to enable BCP

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

Can we restore the database forcefully when it is in use via SQL script?

When you try to restore the database (RESTORE DATABASE) through command and if it's in use then SQL SERVER will not allow you to do so. It will throw an error.

But there is a way to restore database forcefully. But there are chances of data loss.

To restore database forcefully, one need to bring it in SINGLE USER mode. and then restore it and again put the database in MULTI USER mode. Make sure, you are always using MASTER database for this operation.

See below script.

--To Restore forcefully to an existing database which is in use.

Use Master;

ALTER DATABASE virendra SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

RESTORE DATABASE virendra FROM DISK='D:\virendra.bak' WITH REPLACE 

ALTER DATABASE virendra SET MULTI_USER

Thanks,
Virendra Dugar

Is it possible to find out .mdf and .ldf file name from the backup(.bak) file? If yes, then how?

YES. It is possible to find out the name of .ldf and .mdf file from the backup file.

Use below script to find out the details:

RESTORE FILELISTONLY FROM DISK='D:\virendra.bak'

Select * from table1,table2

NOTE: This is objective type question, Please click question title for correct answer.

How we can specify the first day of the week?

Using Set DateFirst we can specify the first day of week. synatx for this is as:

SET DATEFIRST 1

it means monday is the first day of the week.

Thanks
Lakhan Pal Garg

How to get the value of first day of the week?

using SELECT @@DATEFIRST we can get the value of first day of the week.
if it will return 1 then it means Monday is the first day but if it returns 7 then that means Sunday is the first day.

What is Tabular Value Function ?

A Tabular Value function is one which provides a table like structure as an output from the function, it is like a view which is non updateable

What is two-phase commit?

This is a feature in transaction processing systems that enables all databases to be synchronized or in the same state in case some error occurs.

This strategy works in the principal that either all databases are updated or none of them are updated.

DB changes required by the transactions are initially stored temporarily by each database, then a pre-commit command is issued to each databases. The transaction monitor waits for the acknowledgment of the pre-commit command, if received then commit command is sent to each database, that results to make the database change permanent.

In case, the acknowledgment is not received, the rollback command is issued to all the databases.

How many Evaluation Modes are there in "SQL Server 2008 - Policy Based Management" ? Which mode is manual ?

1. In "SQL Server 2008 - Policy Based Management", FOUR evaluation modes are there.
- On Demand
- On Change: Prevent
- On Change: Log Only
- On Schedule

2. "On Demand" mode is manual. Remaining three are Automated.

What is the Product code (Internal Code) for "SQL Server 2005", "SQL Server 2008" and "SQL Server 2008 R2" ?

"Yukon" for SQL Server 2005
"Katmai" for SQL Server 2008
"Kilimanjaro" for SQL Server 2008 R2

What is the difference between Host_Name() and ServerProperty('MachineName') Functions ?

The Host_Name() and ServerProperty('MachineName') Function will return the System Name(Machine Name).

But the difference is ...

Host_Name() will return the Client Machine name.

ServerProperty('MachineName') will return Server Machine name.

ie: CLIENT SQL Server tools only installed on "Computer1" (SQL Server Management Studio) - Connectivity tools only

SERVER SQL Server tools installed on "Computer2" (Databases, SQL Server Services, SQL Server Management Studio, Profiler,...Etc.,)

Now You are the "Client machine (Computer1)", You want to access the Database from the "Server Machine (Computer2)".

Client Machine (Computer1):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer1, Computer2

Server Machine (Computer2):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer2, Computer2

How will you Enable / Disable Resource Governor Using T-SQL in SQL Server 2008 ?

To Enable the Resource Governor :

Use DatabaseName

Go

ALTER RESOURCE GOVERNOR RECONFIGURE

To Disable the Resource Governor :

Use DatabaseName

Go

ALTER RESOURCE GOVERNOR DISABLE

What is the use of "SET NOCOUNT ON;" in SQL Server?

This statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the stored procedure or directly SQL Statement. You can view this message in the Management Studio in the Message tab of the result pan.

When it is ON - the number of affected rows will not be returned
When it is OFF - the number of affected rows will be returned

More on this ....

@@ROWCOUNT is used to get the number of rows affected. Note that either the SET NONCOUNT is ON or OFF, @@ROWCOUNT is always updated with the number of rows affected.

For more detailed information on this, visit http://msdn.microsoft.com/en-us/library/ms189837.aspx

How will you calculate maximum range of "INT" Data Type ?

The Formula Is:
2 ^ (N-1)

here, "N " is nothing but size of the Data type."^ " means Power of the value.

1. We just want to get the Maximum length of the Data type "INT".

Select (max_length * 8) 'Bit(s)' from sys.types Where [name] = 'Int'

The answer is : 32 Bit(s)

2. Now, we can apply the formula for Range

Select Power(Cast(2 as Varchar),(max_length * 8) -1) from sys.types Where [name] = 'Int'

The Result is : 2147483648

The maximum range of "INT" data type is -2147483648 to 2147483647

More SQL SERVER Exclusive Interview Questions & Answers here

Found this useful, bookmark this page to the blog or social networking websites.

Bookmark It

Interview Questions and Answers Categories

Data Structures & Algorithms

Design Pattern & Practices

DotNetFunda.Com

Entity Framework

Error and Solution

Function Points (FPA)

MSBI (SSIS, SSRS, SSAS)

Web Services, Remoting