Sqlserver Written Test Questions 2 years of experince

REQUEST:- IF ANYONE OF THE USERS HAS ANY VALID INTERVIEW PREP MATERIAL WHICH HE WOULD LIKE TO SHARE WITH OTHERS, WE WOULD REQUEST YOU TO SEND THAT TO US ON n.subhash26@gmail.com WTIH SUBJECT LINE AS "INTERVIEW CONTRIBUTIONS" AND WE WOULD PUBLISH IT TO HELP OTHERS ALSO CRACK THE INTERVIEWS !!

************************************************************************

1. Execute an Operating System Command From Within SQL Server.

Answer :

The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server.
Example:

EXEC Master..xp_cmdshell ‘Dir c:\’

2. What is SQL*Loader?

Answer :

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility.

3. What is a SQL * NET?

Answer :

SQL *NET is ORACLE’s mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.

4. What is a Recovery Model?

Answer :

A recovery model is a database property that controls the basic behavior of the backup and restores operations for a database. Recovery models determine how transactions are logged, which logs require backups and the kinds of restore operations available to the DBA. A new database inherits its recovery model from the model database. Recovery models simplify recovery planning, backup and recovery procedures, clarify tradeoff among system operational requirements and clarify tradeoffs among availability and recovery requirements.

5. What are the different types of JOIN operations?

Answer :

Equi Join: This is the most common type of join which involves only equality comparisions. The disadvantage in this type of join is that there

6. Does PL/SQL support “overloading”?

Answer :

The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

7. Explain ACID rule of thumb for transactions.

Answer :

A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.

8. What is the wildcard character in SQL?

Answer :

Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

9. What is Startup and Shutdown ?

Answer :

STARTUP is the process of starting an instance, presumably with the intent of mounting and opening a database in order to make a database system available for use.

To SHUTDOWN is to disconnect an instance from the database and terminate the instance.

10. How to Run a Query on a Remote SQL Server?

Answer :

Use the OpenRowSet Function to Run a Query on a Remote SQL Server

You can use the OPENROWSET( ) function to run a query on a remote SQL server by using the following syntax:

SELECT * FROM OPENROWSET(’SQLOLEDB’, ‘remote_server_name’; ’sa’; ‘password’,'SQL statement’)

Here replace ‘remote_server_name’ with the name of the remote server on which you want to run the query. If necessary, replace ’sa’ and ‘password’ with the name and password of a SQL login ID you want to use to log in to the remote server. Finally, replace ‘SQL statement’ with the SQL statement you want to run on the remote server

11. What is Except Operator?

Answer :

To find rows in one set that do not exist in another set, use the except operator (as defined in SQL-92 and SQL-99). For example, here’s how you find column1 from Table1 that does not exist in column2 of Table2:

Select column1 from Table1 Except Select column2 from Table2; The except operator will remove duplicates, and a single null value will be returned in the case of multiple null values. To return duplicates, use except all. Keep in mind, of course, that other proprietary implementations (such as Minus in Oracle) exist.

12. If my SQL Server has the following specs: 6.05.02 SQL-DMO 6.50.252 DB-Library Which version do I have? And which service pack version do I have?

Answer :

If you want to query the version of SQL Server that’s currently running you can use the @@version variable:

SELECT @@version

This returns the version, processor, build and service pack information for the currently installed SQL Server. This information is stored in the system tables, and you can retrieve more details by calling the extended stored procedure xp_msver. Be sure to call it from the master database.

13. I would like to create a stored procedure that runs a query and outputs the results to a text file and allows me to add extra delimeters and static field info.

Answer :

SQL Server has no native command for outputting query results to a file. You can use the extended

stored procedure xp_cmdshell and call isql (command-line SQL) with your query and output the results to a file. Any delimiters would need to be part of the SELECT string:

DECLARE @isqlString varchar(255)

SELECT @isqlString = ‘isql -Q “SELECT DateCol FROM NorthwindTest.dbo.Test” -E -o C:\Results.txt’

EXEC master..xp_cmdshell @isqlString

14. How do I give a user the option of importing Excel and a delimited text file into a SQL Server Database without manually using SQL DTS?

Answer :

You can use the DTS object model to programmatically create, modify and run DTS packages.

You can do this many ways, but essentially this object model has objects for anything you can do in DTS.

If you already have the package created and saved as a file, add a reference to the DTS Library.

Then you can call it like this:

Dim Package As New DTS.Package

Package.LoadFromStorageFile App.Path “\DTSPackage.dts”

Package.Execute