| SQL Server Interview Questions and Answers |
| Print Book Available (207 Pages) | Sample Chapters |
UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.
What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.
What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.
Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
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 system?
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
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.
Complete Series of SQL Server Interview Questions and Answers
SQL Server Interview Questions and Answers – Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download
Reference : Pinal Dave (http://blog.SQLAuthority.com)
what is query execution plan
srisailam,
A query execution plan outlines how the SQL Server query optimizer actually ran a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.
Thank you,
Pinal Dave
(http://www.SQLAuthority.com)
Hi Pinal ,
We are trying to print some logs from an MS SQL function.
Since it trows error for print, tried inserting to a table declared within the function. I think the scope of the table that is used to create logs ends when the functions stops.
Would you have any idea what we could do to print the logs?
Vikram,
I always used log to restore database in point of time, I never tried to print them. You may search for third party tool to do that.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
excellent reply
Excellent interview questions.
Hi Dear
Im using sql server 2005 in my network .
But I have a problem with it .
Sql server 2005 is too slow on windows authentication mode .
For Example , my administrator account connect to server by 15 second’s delay .
Did You know any thing about this problem ?
I tried by mix mode( sql authentication ), all things is Ok .
Thanks .
Hi Pinal,
I am tryng to write a trigger in SQL server 2000 to capture the NT usernames updating a databasev table .Can you please let me know how can I do that ?
Thanks
Surj
Excellent Answers
Thanx
Rahul
Hello Pinal.
I have SQL server 2000 with SQL Authentication. Can I test user’s login and IP when he is connecting to the server. Our network doesn’t use Domain. Maybe you know some intresting links where I’ll find the answer.
All what I want is just – “User can logging only from his computer”.
Thanks
hi Pin,
Very good work ! I wish you to do more!
may GOD bless you
by
IGNATIUS
HII Mr. Dave,
Sir i have a question which was asked to me in an interview.
plz telll me the solution :
if i fire a set of queries or a single query in database, and after that want to know the status of database means, how much rows are affected.how much aredeleted how much are updated and how much are untouched.and how much are inserted.
I think thr is a property in sqlserver by which we can know that staus of database.
Sir Plz help me.
Thank you
Sharad Agarwal
I need to know whether we can create single user for two different database in sql 2005
I read all the question-answers. It was great to have this kind of knowledge. However, I got confused with the following question-answer.
I do not know whether you made a typo in the question. Even if you made a typo, I am not satisfied with the answer of the question about the temp tables. Please let all know about the real answer.
Thanks.
Pratap.
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.
UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.
Please continue with your questions and answers at new location.
[...] Server Interview Questions and Answers – Part 2 SQL Server Interview Questions and Answers – Part 3 SQL Server Interview Questions and Answers – Part 4 SQL Server Interview Questions and Answers – Part 5 SQL Server Interview Questions and Answers – [...]
[...] Interview Questions and Answers – Part 2 SQL Server Interview Questions and Answers – Part 3 SQL Server Interview Questions and Answers – Part 4 SQL Server Interview Questions and Answers – Part 5 SQL Server Interview Questions and Answers [...]