1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

MySQL Tutorials

SQL SERVER INTERVIEW QUESTIONS with ANSWERs

SQL SERVER INTERVIEW QUESTIONS
Here are some sql server:
interview questions which is very helpful in interviews. These sql server
interview questions provides a lot of informations. Not at all, here you
will also find interesting query i.e sql server 2000 query like Sql Insert
Query, Delete Sql Query, Update Sql Query and Sql Create Query.

Question: How to get which Process is Blocked in SQL SERVER ?

Answer:- There are two ways to get this sp_who and sp_who2 . You
cannot get any detail about the sp_who2 but its provide more information
then the sp_who . And other option from which we can find which process is
blocked by other process is by using Enterprise Manager or Management
Studio, these two commands work much faster and more efficiently than
these GUI-based front-ends.

Question: What is COMMIT & ROLLBACK statement in SQL ?

Answer: Commit statement helps in termination of the current
transaction and do all the changes that occur in transaction persistent
and this also commits all the changes to the database.COMMIT we can also
use in store procedure. ROLLBACK do the same thing just terminate the
currenct transaction but one another thing is that the changes made to
database are ROLLBACK to the database.

Question:-What is diffrence between OSQL and Query Analyzer ?

Answer:-Both are the same but ther eis little diffrence OSQL is
command line tool whic is execute qery and display the result same a query
analyzer but query analyzer is graphical and OSQL is a command line
tool.OSQL have not ability like query analyzer to analyze queries and show
statics on speed of execution and other usefull thing about OSQL is that
its helps in scheduling.

Question: What is SQL whats its uses and its component ?

Answer: The Structured Query Language (SQL) is foundation for all relational database
systems. Most of the large-scale databases use the SQL to define all user
and administrator interactions. QL is Non-Procedural language . Its allow
the user to concentrate on specifying what data is required rather than
concentrating on the how to get it. The DML component of SQL comprises
four basic statements: * SELECT to get rows from tables * UPDATE to
update the rows of tables * DELETE to remove rows from tables *
INSERT to add new rows to tables

Question: What is DTS in SQL Server ?

Answer: If a organization is
big then it is also there that there is multiple option to store data some
people are using EXCEL some are using ACCESS and some of they are using
SQL SERVER and in some other format also but there a problem is arise that
how to merge that data into one format there is diffrent tool are there
for doing this funtion. One of product of SQL SERVER-2000 DTS helps in
this problem it provides a set of tool from that tool we can customise are
database acording to our need DTSRun is a command-prompt utility used to
execute existing DTS packages.

Question: What is the diffrence between SQL and Pl/Sql ?

Answer: We
can get modify, Retrieve by single command or statement in SQL but PL/SQL
process all SQL statements one at a time. With PL/SQL, an entire block of
statements process in a single command line.sql is structured query
language ,various queries are used to handle the database in a simplified
manner. while pl/sql is procedural language contains various types of
variable,functions and procedures and other major diffrence is Sql as the
name suggest it is just structured query language wheareas PLSQL is a
commbination of Programming language & SQL.

Question: What is the difference between UNION ALL Statement and UNION
?

Answer:- The main difference between UNION ALL statement and UNION
is UNION All statement is much faster than UNION,the reason behind this is
that because UNION ALL statement does not look for duplicate rows, but on
the other hand UNION statement does look for duplicate rows, whether or
not they exist.

Question: Write some disadvantage of Cursor ?

Answer:- Cursor plays
there row quite nicely but although there are some disadvantage of Cursor
. Because we know cursor doing roundtrip it will make network line busy
and also make time consuming methods. First of all select query gernate
output and after that cursor goes one by one so roundtrip happen.Another
disadvange of cursor are ther are too costly because they require lot of
resources and temporary storage so network is quite busy.

Question: What is Log Shipping and its purpose ?

Answer: In Log
Shipping the transactional log file from one server is automatically
updated in backup database on the other server and in the case when one
server fails the other server will have the same DB and we can use this as
the DDR(disaster recovery) plan.

Question: What are the null values in SQL SERVER ?

Answer: Before
understand the null values we have some overview about what the value is.
Value is the actual data stored in a particular field of particular
record. But what is done when there is no values in the field.That value
is something like <null>.Nulls present missing information. We can
also called null propagation.

Question: What is difference between OSQL and Query Analyzer
?
Answer: Both are same for functioning but there is a little
difference OSQL is command line tool which execute query and display the
result same a Query Analyzer do but Query Analyzer is graphical.OSQL have
not ability like Query Analyzer to analyze queries and show statistics on
speed of execution .And other useful thing about OSQL is that its helps in
scheduling which is done in Query Analyzer with the help of JOB. Sql
Server 2000 Query

Question: Write a Role of Sql Server 2005 in XML Web Services?

Answer:- SQL Server 2005 create a standard method for getting the
database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP
requests to SQL Server for executing T-SQL batch statements, stored
procedures, extended stored procedures, and scalar-valued user-defined
functions may be with or without parameters.

Question: What are the different types of Locks ?

Answer: There are
three main types of locks that SQL Server (1)Shared locks are used for
operations that does not allow to change or update data, such as a SELECT
statement. (2)Update locks are used when SQL Server intends to modify a
page, and later promotes the update page lock to an exclusive page lock
before actually making the changes. (3)Exclusive locks are used for the
data modification operations, such as UPDATE, INSERT, or DELETE.

Question: Explain some SQL Server 2000 Query?

Answer: Here are some
sql server 2000 query like Sql Insert Query, Delete Sql Query, Update Sql
Query and Sql Create Query: 1) Sql Insert Query: a) How to encrypt
data by using Sql Insert Query. --: insert into
table_name(Tablecolumn1, tablecolumn2,. . . . .) values ('value1',
pwdencrypt('value'),. . . .)
b) How to copy data from one table to another with the help of Sql
Insert Query. --: insert into table_name(column1,column2,. . . . )
select column1, column2, . . . . from table_name2
c) Sql Insert Query using where clause --: insert into
tablename(column1,column2) select column1,column2 from tablename2 where
id=value.

Question: What is 'Write-ahead log' in Sql Server 2000 ?

Answer:
Before understanding it we must have an idea about the transaction log
files. These files are the files which holds the data for change in
database . Now we explain when we are doing some Sql Server 2000 query
or any Sql query like Sql insert query,delete sql query,update sql query
and change the data in sql server database it cannot change the database
directly to table .Sql server extracts the data that is modified by sql
server 2000 query or by sql query and places it in memory.Once data is
stores in memory user can make changes to that a log file is gernated this
log file is gernated in every five mintues of transaction is done. After
this sql server writes changes to database with the help of transaction
log files. This is called Write-ahead log.

Question: What do u mean by Extents and types of Extends ?
Answer:
An Extent is a collection of 8 sequential pages to hold database from
becoming fregmented. Fragment means these pages relates to same table of
database these also holds in indexing. To avoid for fragmentation Sql
Server assign space to table in extents. So that the Sql Server keep upto
date data in extents. Because these pages are continously one after
another. There are usually two types of extends:-Uniform and Mixed.
Uniform means when extent is own by a single object means all
collection of 8 ages hold by a single extend is called uniform. Mixed
mean when more then one object is comes in extents is known as mixed
extents.

Question: What is different in Rules and Constraints ?

Answer: Rules
and Constraints are similar in functionality but there is a An little
diffrence between them.Rules are used for backward compatibility . One the
most exclusive diffrence is that we an bind rules to a datatypes whereas
constraints are bound only to columns.So we can create our own datatype
with the help of Rules and get the input according to that.

Question: What is defaults in Sql Server and types of Defaults
?
Answer: Defaults are used when a field of columns is allmost common
for all the rows for example in employee table all living in delhi that
value of this field is common for all the row in the table if we set this
field as default the value that is not fill by us automatically fills the
value in the field its also work as intellisense means when user inputing
d it will automatically fill the delhi . There are two types of defaults
object and definations. Object deault:-These defaults are applicable on
a particular columns . These are usually deined at the time of table
designing.When u set the object default field in column state this column
in automatically field when u left this filed blank. Defination
default:-When we bind the datatype with default let we named this as
dotnet .Then every time we create column and named its datatype as dotnet
it will behave the same that we set for dotnet datatype.

Question: What Is Database ?

Answer: A database is similar to a
data file in that it is a storage place for data. Like a data file, a
database does not present information directly to a user; the user runs an
application that accesses data from the database and presents it to the
user in an understandable format.Database systems are more powerful than
data files in that data is more highly organized. In a well-designed
database, there are no duplicate pieces of data that the user or
application must update at the same time. Related pieces of data are
grouped together in a single structure or record, and relationships can be
defined between these structures and records.When working with data files,
an application must be coded to work with the specific structure of each
data file. In contrast, a database contains a catalog that applications
use to determine how data is organized. Generic database applications can
use the catalog to present users with data from different databases
dynamically, without being tied to a specific data format. A database
typically has two main parts: first, the files holding the physical
database and second, the database management system (DBMS) software that
applications use to access data. The DBMS is responsible for enforcing the
database structure, including: · Maintaining relationships between data in
the database. Ensuring that data is stored correctly, and that the rules
defining data relationships are not violated. · Recovering all data to a
point of known consistency in case of system failures.

Question: what is Relational Database ?

Answer: Although there are
different ways to organize data in a database, relational databases are
one of the most effective. Relational database systems are an application
of mathematical set theory to the problem of effectively organizing data.
In a relational database, data is collected into tables (called relations
in relational theory). A table represents some class of objects that are
important to an organization. For example, a company may have a database
with a table for employees, another table for customers, and another for
stores. Each table is built of columns and rows (called attributes and
tuples in relational theory). Each column represents some attribute of the
object represented by the table. For example, an Employee table would
typically have columns for attributes such as first name, last name,
employee ID, department, pay grade, and job title. Each row represents an
instance of the object represented by the table. For example, one row in
the Employee table represents the employee who has employee ID 12345. When
organizing data into tables, you can usually find many different ways to
define tables. Relational database theory defines a process called
normalization, which ensures that the set of tables you define will
organize your data effectively.

Question: What is Data Integrity and it's categories ?

Answer:
Enforcing data integrity ensures the quality of the data in the database.
For example, if an employee is entered with an employee_id value of 123,
the database should not allow another employee to have an ID with the same
value. If you have an employee_rating column intended to have values
ranging from 1 to 5, the database should not accept a value of 6. If the
table has a dept_id column that stores the department number for the
employee, the database should allow only values that are valid for the
department numbers in the company. Two important steps in planning tables
are to identify valid values for a column and to decide how to enforce the
integrity of the data in the column. Data integrity falls into these
categories: 1) Entity integrity 2) Domain integrity 3)
Referential integrity 4) User-defined integrity Entity Integrity:
Entity integrity defines a row as a unique entity for a particular table.
Entity integrity enforces the integrity of the identifier column(s) or the
primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY
constraints, or IDENTITY properties). Domain Integrity: Domain
integrity is the validity of entries for a given column. You can enforce
domain integrity by restricting the type (through data types), the format
(through CHECK constraints and rules), or the range of possible values
(through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions,
NOT NULL definitions, and rules). Referential Integrity: Referential
integrity preserves the defined relationships between tables when records
are entered or deleted. In Microsoft® SQL Server? 2000, referential
integrity is based on relationships between foreign keys and primary keys
or between foreign keys and unique keys (through FOREIGN KEY and CHECK
constraints). Referential integrity ensures that key values are consistent
across tables. Such consistency requires that there be no references to
nonexistent values and that if a key value changes, all references to it
change consistently throughout the database. When you enforce referential
integrity, SQL Server prevents users from: · Adding records to a
related table if there is no associated record in the primary table. ·
Changing values in a primary table that result in orphaned records in a
related table. · Deleting records from a primary table if there are
matching related records. For example, with the sales and titles
tables in the pubs database, referential integrity is based on the
relationship between the foreign key (title_id) in the sales table and the
primary key (title_id) in the titles table. User-Defined: Integrity
User-defined integrity allows you to define specific business rules that
do not fall into one of the other integrity categories. All of the
integrity categories support user-defined integrity (all column- and
table-level constraints in CREATE TABLE, stored procedures, and
triggers).

Question: SQL Server runs on which TCP/IP port and From where can you
change the default port?

Answer: SQL Server runs on port 1433 but we
can also change it for better security and From the network Utility TCP/IP
properties -->Port number.both on client and the server.
Question: What is the use of DBCC commands? Answer: DBCC stands for
database consistency checker. We use these commands to check the
consistency of the databases, i.e., maintenance, validation task and
status checks.DBCC CHECKDB - Ensures that tables in the db and the indexes
are correctly linked.and DBCC CHECKALLOC To check that all pages in a db
are correctly allocated. DBCC SQLPERF - It gives report on current usage
of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables
file group for any damage.


Question: What is the difference between a HAVING CLAUSE and a WHERE
CLAUSE?

Answer: Having Clause is basically used only with the GROUP BY
function in a query. WHERE Clause is applied to each row before they are
part of the GROUP BY function in a query.
Question: When do you use SQL Profiler? Answer: SQL Profiler utility
allows us to basically track Connections to the SQL Server and also
determine activities such as which SQL Scripts are running, failed jobs
etc.


Question: Can you explain the role of each service?

Answer: SQL
SERVER - is for running the databases SQL AGENT - is for automation such
as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to
other SQL Servers.

Question: What is Normalization ?

Answer: The logical design of the
database, including the tables and the relationships between them, is the
core of an optimized relational database. A good logical database design
can lay the foundation for optimal database and application performance. A
poor logical database design can impair the performance of the entire
system.
Normalizing a logical database design involves using formal methods to
separate the data into multiple, related tables. A greater number of
narrow tables (with fewer columns) is characteristic of a normalized
database. A few wide tables (with more columns) is characteristic of an
nonnomalized database. Reasonable normalization often improves
performance. When useful indexes are available, the Microsoft® SQL Server?
2000 query optimizer is efficient at selecting rapid, efficient joins
between tables.
Some of the benefits of normalization include: ·Faster sorting and
index creation. ·A larger number of clustered indexes. For more
information, Narrower and more compact indexes. ·Fewer indexes per
table, which improves the performance of INSERT, UPDATE, and DELETE
statements. ·Fewer null values and less opportunity for inconsistency,
which increase database compactness.
As normalization increases, so do the number and complexity of joins
required to retrieve data. Too many complex relational joins between too
many tables can hinder performance. Reasonable normalization often
includes few regularly executed queries that use joins involving more than
four tables.
Sometimes the logical database design is already fixed and total
redesign is not feasible. Even then, however, it might be possible to
normalize a large table selectively into several smaller tables. If the
database is accessed through stored procedures, this schema change could
take place without affecting applications. If not, it might be possible to
create a view that hides the schema change from the applications.

Question: Can you explain what View is in SQL ?
Answer: View is just
a virtual table nothing else which is based or we can say devlop with SQL
SELECT query.So we can say that its a real database table (it has columns
and rows just like a regular table),but one difference is that real tables
store data,but views can?t. View data is generated dynamically when the
view is referenced.And view can also references one or more existing
database tables or other views. We can say that it is filter of
database. Question: Can you tell me the difference between DELETE
&TRUNCATE commands? Answer: Delete command removes the rows from a
table based on the condition that we provide with a WHERE clause. Truncate
will actually remove all the rows from a table and there will be no data
in the table after we run the truncate command.

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy