SQL Interview Questions and Answers set 3

 SQL Interview Questions and Answers set 3

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port

number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?

Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL

Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.

Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the

Security page.

Where are SQL server users names and passwords are stored in sql server?

They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating



What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It

is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the

implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to

schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves.

Recursion can be defined as a method of problem solving wherein the solution is arrived at by

repetitively applying it to subsets of the problem. A common application of recursive logic is to perform

numeric computations that lend themselves to repetitive evaluation by the same processing steps.

Stored procedures are nested when one stored procedure calls another or executes managed code by

referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code

references up to 32 levels.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there

was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement,

it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR

doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR

automatic variable, and optionally writes the message to the SQL Server error log and the NT

application event log.

What is log shipping?

Log shipping is the process of automating the backup of database and transaction log files on a

production SQL server, and then restoring them onto a standby server. Enterprise Editions only

supports log shipping. In log shipping the transactional log file from one server is automatically updated

into the backup database on the other server. If one server fails, the other server will have the same db

can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will

automatically backup transaction logs throughout the day and automatically restore them on the

standby server at defined interval.

What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound

statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given

connection. When connection are closed, the data in the global temporary table disappears. However,

the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?

sp_renamedb ‘oldname’ , ‘newname’

If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using

sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user


What is sp_configure commands and set commands?

Use sp_configure to display or change server-level settings. To change database-level settings, use

ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

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


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.

What are the OS services that the SQL Server installation adds?

MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?


Post a Comment