|
Download SQL
Server interview questions and answers pdf
Test your SQL Server Skills!
Sql server part 1 (40 questions)
Sql server part 2 (28 questions)
ASP.NET (41 questions)
Database
concepts (20 questions)
Sql Server (25 questions) - New
Latest answer: Buffer Cache: Buffer cache is a memory pool in
which data pages are read. It performance of the buffer cache is indicated as
follows:.............
Read
answer
Latest answer: Temporary setting of specific server
characteristics is done by trace tags. DBCC TRACEON is the command to set the
trace flags. Once activated, trace flag will be in effect until the server is
restarted...............
Read answer
Difference between control flow and data flow?, If you want to send some data
from Access database to SQL server database. What are different component of
SSIS will you use?, Explain why variables called the most powerful component of
SSIS?..................
Read answer
Latest answer: MS SQL Server supports the connection to
different OLE DB on an ad hoc basis. This persistent connection is referred as
Linked Server..............
Read answer
Latest answer: SQL Server has a feature for sending mail.
Stored procedures can also be used for sending mail on demand. With SQL Server
2005, MAPI client is not needed for sending mails................
Read
answer
Latest answer: The following is the process to make a remote
connection in database: - Use SQL Server Surface Area Configuration Tool for
enabling the remote connection in database...................
Read
answer
Latest answer: Cross Join : No join conditions are specified.
Results in pairs of rows. Results in Cartesian product of two
tables...............
Read
answer
Latest answer: OPENXML
parses the XML data in SQL Server in an efficient manner. It’s primary ability
is to insert XML data to the RDB. It is also possible to query the data by
using OpenXML................
Read
answer
Latest answer: The following is the order of executing SQL
query: The query goes to the shared pool that has information like parse tree
and execution plan for the corresponding statement...............
Read
answer
Latest answer: Create a column as type ‘blob’ in a table. Read
the content of the file and save in ‘blob’ type column in a
table...............
Read answer
Latest answer: Microsoft SQL server is a relational database
management system. It uses MS- SQL as the query language. SQL Server offers a
high level of security, reliability and scalability depending on the business
needs..............
Read answer
SQL Server interview questions for freshers and experienced
Explain inline variable assignment in sql server 2008 with an example.
What is Compound Operators in sql server 2008? Explain with an example
SQL Server 2008 introduces automatic auditing. Explain its
benefits.............
Read answer
Latest answer: WITH ENCRYPTION Indicates that SQL Server will
convert the original text of the CREATE PROCEDURE statement to an encrypted
format. Users that have no access to system................
Read answer
Latest answer: A linked server allows remote access. Using
this, we can issue distributed queries, update, commands, and transactions
across different data sources................
Read answer
Latest answer: Analysis service provides a combined view of the
data used in OLAP or Data mining. Services here refer to OLAP, Data mining.
Analysis services assists in creating, designing...........
Read answer
Latest answer: Each server running analysis service has a
repository to store objects of the computer running Analysis Services an
Analysis service repository stores the information about the.............
Read answer
Latest answer: SQL
service broker provides asynchronous queuing functionality to SQL server. Once
message is sent to the SQL server................
Read
answer
Latest answer: User defined datatypes is created by using base
SQL Server data type by providing a descriptive name.................
Read
answer
Latest answer: Bit datatype is used to store boolean
information................
Read answer
Latest answer: Lock escalation from SQL Server 7.0 onwards, is
dynamically managed by SQL Server. It is..........
Read answer
Latest answer: Blocking happens when one connection from an
application holds a lock and a second............
Read answer
Latest answer: Every database has a public role which holds all
the default permissions for the users in a database.................
Read answer
Latest answer: SQL server login is used to connect to SQL
server. This used when login in through the windows login credentials is not
existent.............
Read answer
Latest answer: The built in Administrator Account is basically
used during some setup to join some machine in the domain............
Read answer
Latest answer: Failover clustering is mainly used for data
availability. Typically in a failover cluster, there are two machines. One
machine provides the basic services and the second is available to
run..................
Read answer
Latest answer: SQL server can return XML document using FOR XML
clause.................
Read answer
Latest answer: SQL Server has efficient ways to enhance
scalability of the database system...............
Read answer
Latest answer: SQL Server English Query helps to build
applications that can accept query.............
Read answer
Latest answer: SQL Profiler captures SQL Server events from a
server. The events are saved.................
Read answer
Latest answer: SQL Server uses different ways to execute SQL
statements which are listed below................
Read answer
Latest answer: SQL Server supports searches on character string
columns using Full-Text Query...............
Read answer
Latest answer: The several phases a transaction has to go
through are listed here. Database..............
Read answer
Latest answer: XPath is a language defined by the W3C, used to
select nodes from XML documents..............
Read answer
Latest answer: A file or file group can only be used by one
database. For example, the files abc.mdf and abc.ndf contains.................
Read answer
Latest answer: SQL Server supports two security
(authentication) modes................
Read answer
Latest answer: Data definition language is used to define and
manage all attributes and properties of a database..............
Read answer
Latest answer: SQL Server uses the following steps to process a
single SELECT statement............
Read answer
Latest answer: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE,
CREATE TRIGGER, and CREATE VIEW statements..............
Read
answer
Latest answer: GO Command is used to signal the end of a
batch...............
Read answer
Latest answer: Null means no entry has been made. It implies
that the value is either unknown or undefined............
Read answer
Latest answer: UNION command selects distinct and related
information from two tables. On the other hand..............
Read answer
Latest answer: Database Consistency Checker Commands give
details in form of statistics about the SQL Server..............
Read answer
Latest answer: UNION command selects distinct and related
information from two tables. On the other hand.............
Read answer
Latest answer: A local temporary table lives until the
connection is valid or until the duration of a compound statement.........
Read
answer
Latest answer: STUFF function is used to insert a string into
another string by deleting some characters specified.............
Read
answer
Sql Server interview - May 7, 2011 by Swati Parakh
Explain various data region available in SSRS with their use.
Data regions are report items used to display data from a single dataset. You
can perform grouping, sorting and various aggregate functions on data in data
region. In SSRS 2005, there were 4 data regions:-
1. Table
2. Matrix
3. List
4. Chart
While in SSRS 2008, there are one additional data region namely Gauge.
Let’s explain each one of them:
1. Table - Table Data region has fixed tabular structure i.e. fixed number of
columns. It is useful for displaying data grouped by row. You can have maximum
of 1 report item per cell. The size of table depends on number of rows dataset
fetches i.e., if number of rows returned by dataset is more; it can expand to
multiple pages.
2. Matrix – A matrix data region display data in pivot table format, hence also
popularly known as pivot table or crosstab report. It has minimum of one row
group and one column group. The size of matrix data region depends on columns
and rows fetched.
3. List - A list data region is free layout. It is useful for complex reporting
resign. The list can be used to display multiple table and matrix. Each getting
data from different dataset.
4. Chart – This data region is for displays the data graphically i.e., in form
of chart. A various chart types are available in SSRS 2008 namely line, pie
chart, columns etc.
5. Gauge - This can be used in a table or matrix to show the relative value of a
field in a range of values in the data region. You can also add a gauge to the
design surface to show a single relative value.
What are various ways to enhance the SSRS report? Explain.
There are various ways in which you can enhance your report:
1. Display your data in graphic format using Chart Region.
2. Use sorting.
3. If couple of reports are related, you can make them interactive using
connect them using bookmark link, hyper link or drill through report link.
4. Adding sub-report. Sub-report is a stand-alone report which can be link to
another report based on its content using parameter.
5. Add custom fields. Custom fields provide with same functionality as alias
columns provide in SQL server query. It is the timing of the operation that
differs from the alias columns. The calculation is performed on dataset by
report server.
6. Using expression.
7. Using custom code. SSRS allows including custom code written in VB.Net. 8.
Add document map (navigational links to report item once report is rendered) to
report.
Sql Server interview - July 7, 2011 by Swati Parakh
What are various aggregate functions that are available?
The following are various aggregate functions available:-
1. SUM
2. AVG
3. COUNT
4. COUNTDISTINCT
5. MAX
6. MIN
7. STDEV
8. STDEVP
9. VAR
10. VARP
By default, SUM is the aggregate function used for numeric data type.
How do you integrate the SSRS reports in your application?
There are 3 ways in which you can integrate reports into your application:-
1. Navigating to URL i.e. https:\\servername\reportservername\reportname – This
is simplest and most popular way. A separate login might be required since we
are directly calling the report from report server. Address of report server
gets expose to user.
2. Using IFrame, Browser control or Report Viewer Control – In this approach,
we embed the URL of report server in our application, hence address of
reportserver is not exposed. No separate window opens. A user does not come to
know that he has moved to different server.
3. Programmatically sending a web request using SOAP to report server.
Explain use of Expression builder.
Expressions provide us with flexibility to customize our report. It is written
in Visual basic and is used throughout the report to to retrieve, calculate,
display, group, sort, filter, parameterize, and format the data in a report.
They start with equal sign (=).
| S.No.
|
Functionality |
Property, Context and Dialog Box |
Expression |
| 1 |
Format data in a text box depending on value |
Colour for a placeholder inside of a text box in the
details row for a Tablix |
=IIF(Fields!TotalDue.Value < 10000,"Red","Black") |
| 2 |
Dynamic page header or footer content. |
Value for a placeholder inside of a text box that is
placed in the page header or footer. |
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
|
| 3 |
Specify page breaks for every 20 rows in a Tablix with no
other groups. |
Group expression for a group in a Tablix. |
=Ceiling(RowNumber(Nothing)/20) |
| 4 |
Shows the user ID of the person running the report |
Value |
=User!UserID |
| 5 |
To get first day of the month |
Value |
=DateSerial(Year(Today()),Month(Today()),1) |
| 6 |
To get the current date |
Value |
=Today() |
| 7 |
To get last day of the month |
Value |
=DateAdd("d",-1,DateSerial(Year(Today()),Month(Today())+1,1)) |
Sql Server interview - July 10, 2011 by Swati Parakh
Difference between drill down and drill through report.
Both the drill down and drill through report provide interactive functionality
to the SSRS report. The differences are as follows:-
| Trait |
Drill Down
|
Drill Through |
| Retrieves Data |
Data retrieved at the same time as main report |
Data retrieved one click on link of drill through report |
| Is processed and rendered when |
With the main report |
When link is clicked |
| Performance |
Slower since retrieves all data with main report |
Faster (but does not retrieve all data with main report) |
| Is displayed |
Within main report |
Separately either in separate window or tab |
What’s the use of custom fields in report?
Custom fields can be defined as alias column of the report since the operation
is performed on report server rather than on database server. The custom field
is very useful for the data manipulation like adding some fields whose value
can be calculated based on expression, text e.g. instead of CName fetched from
database, I want the dataset to display Customer Name etc.
We can add custom fields as right click on dataset, select add in Dataset
window. The New field dialog box will open, we can add name of custom field and
also mention whether it is database field or calculated one. If it is
calculated, then we can mention the computation in this window.
Can we use custom code in SSRS? If so, explain how we can do.
Yes, we can. SSRS allows developer to add custom code in your report. You can
write the code directly into embedded VB.Net and call it using property
expression or you can write a custom class library and refer it in report
server. The advantage of first method is that it is simple and easy to use but
disadvantage is that it is available for that report only. While the second
method has advantage of being available for multiple reports but it has much of
configuration overhead.
To write custom code, right click on Report Designer outside report body and
select Properties and go to Code tab and you can write custom code here.
To add custom class library, right click on Report Designer outside report body
and select Properties and go to Reference tab and add the reference by browsing
to the assembly of your class library. Note that you need to create class
library and then compile it before referencing it in your SSRS report.
Sql Server interview - July 16, 2011 by Swati Parakh
Difference between report and query parameter. Why do we need different type of
parameter?
|
Query Parameter |
Report Parameter |
| Defined At |
Database Level |
Report Level |
| Created |
Automatically if database query or stored procedure has a
parameter |
Automatically if report has some query parameter and is mapped to query
parameter |
| processed |
On Database Server |
On Report Server |
| Use |
Filtering of Data, Security of Data |
Manipulate data, interconnect reports, filtering data |
| Processing Output |
Number of records returned is based on query parameter |
Number of records presented is based on query. Note- Records processed on
report parameter would be same as records returned based on query parameter. |
| Filtering data based on them |
Performance is good |
Full set of records is retrieved then filtered. Hence, performance is low |
How does your SSRS maintain security?
Reporting services maintain role based security. When a user logs into reporting
services, a Report Manager (whose duty is to maintain security of Reporting
Services) first checks the identity of user and then determine what rights he
have to perform on report.
Report Manager manages the security at 2 levels –
1. System-level – Administer the report server globally
2. Item-level – Security at report and dataset level
System-level roles are:-
1. System Administrator – can manage report server and report manager security
2. Site User - view basic information like report properties and schedules.
Item-level roles – User can use any of predefined item-level roles or create
their own roles by using combination of predefined item-level roles.
Pre-defined Item-level roles are:-
1. Browser – can navigate to report and run them.
2. My Reports – these users’ rights is restricted to reports present in their
MyReports folder. However, they can create, view and manage reports in their
folder.
3. Publisher – As name suggest, publisher user has rights to publish reports to
Reporting Server database.
4. Content Manager – has all permission at item-level.
Also read
|
SQL Profiler
What are the tasks performed by SQL Profiler? How can you use the SQL Profiler
to ensure database security? How can you reduce the amount of data collected by
a trace? What is SQL Profiler? When do you use SQL Profiler?............
|
Sql Server DBA interview
What are the steps to take to improve performance of a poor performing query?
What is a deadlock and what is a live lock? How will you go about resolving
deadlocks? What is blocking and how would you troubleshoot it?.............
|
|
Sql Server database maintenance
Explain the concepts of faster differential backups. Explain the concepts of
Parallel Database consistency check (DBCC). Define Indexed view.
Define Distributed partitioned views............
|
SQL Server backup devices and methods
Database backup methods - Full Backups, Differential Backups, Transaction Log
Architecture Backups, File and Filegroup Backups...............
|
|
RAID (Redundant Array of Inexpensive disks)
RAID is a mechanism of storing the same data in different locations. Since the
same data is stored, it is termed as redundant............
|
SQL Server 2005 XML support
Explain the concepts and capabilities of SOAP. Explain the purpose of
Native XML mode in SQL Server 2005. Native XML Access vs. SQLXML.............
|
|
XML Data type implementation in SQL
2005
What is Untyped XML? Provide examples for : Create a table with a untype XML
column, Insert into an untyped XML data type column. What is typed XML? The XML
data type comes with five methods. Explain them Differentiate between Untyped
XML and Typed XML...................
|
Querying &
modifying XML data in SQL 2005
What is XQuery language? Explain the syntax rule of XQuery language.
XQuery expression contains two parts: the Prolog and the Body. Explain them
Explain PATH expression in XQuery with an example.....................
|
|
Indexing XML data in SQL Server
2005
Explain the concepts of indexing XML data in SQL Server 2005. Provide basic
syntax for creating index on XML data type column. What is content
indexing/full text indexing? Explain the reason to index XML data type column.
What are the guidelines to be adhered when creating a XML
index?...................
|
Indexing XML data in SQL Server
2005
Explain the concepts of indexing XML data in SQL Server 2005. Provide basic
syntax for creating index on XML data type column. What is content
indexing/full text indexing? Explain the reason to index XML data type column.
What are the guidelines to be adhered when creating a XML index?...............
|
|
XML Schemas in SQL Server 2005
Syntax and example for creating and dropping XML schema collection. Limitations
of the XML Schema collections. Discuss about XML schema collection
permission...................
|
FOR XML in SQL Server
Discuss the TYPE directive of FOR XML. Explain the RAW mode of FOR XML. Explain
the AUTO mode of FOR XML. Explain the EXPLICIT mode of FOR XML..............
|
|
CLR support for SQL Server 2005
Overview of integration of CLR with SQL Server.
Advantages of CLR integration...............
|
XSLT in SQL Server 2005
Overview of XSLT and the components that make up an XSLT style sheet. What is
XSLCompiledTransform class of the .NET Framework? What is XSLTSetting class of
the .NET Framework?...................
|
|
SOAP support in SQL Server 2005
Syntax and example for creating and dropping XML schema collection. Limitations
of the XML Schema collections. Discuss about XML schema collection
permission..............
|
Transferring a
View from Oracle 10G XE to an SQL Server 2005 Database
Here you will learn how to copy a table on an Oracle 10G XE database to a
database on the SQL Server 2005. You will also learn how to install an Oracle
10G XE server and work with its database objects...........
|
|
SQL
Server 2005 Analysis Services
What is SQL Server 2005 Analysis Services (SSAS)? What are the new features
with SQL Server 2005 Analysis Services (SSAS)? What are SQL Server Analysis
Services cubes? Explain the purpose of synchronization feature provided
in Analysis Services 2005. Explain the new features of SQL Server 2005 Analysis
Services (SSAS). [Hint - Unified Dimensional Model, Data Source View, new
aggregation functions and querying tools]....................
|
|
|
