Sql Server Interview Questions and Answers (1758) - Page 20

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

When will you go for SQL Server Authentication instead of Windows Authentication ?

If the server is in different domain from our users. So, The two domains don't trust each other. So, We have to use SQL Server authentication.

When will SQL Server throw an Error: "MSSQLSERVER ERROR 576" ?

The record-size limit for tables that use SPARSE columns is 8018 bytes. When the converted data plus existing record data exceeds 8,018 bytes, The Error will be thrown "MSSQLSERVER ERROR 576".

When columns are converted between SPARSE and NONSPARSE types, Database Engine keeps a copy of the current record data. This temporarily doubles the storage that is required for the record, So the Error triggered.

What is Instance-aware and Instance-unaware Services in SQL Server ?

Instance-aware services are associated with a specific instance of SQL Server, and have their own registry hives. We can install multiple copies of instance-aware services by running SQL Server Setup for each component or service.
ie:
1.SQL Server
2.SQL Server Agent
3.Analysis Services
4.Reporting Services
5.Full-text search

Instance-unaware services are shared among all installed SQL Server instances. They are not associated with a specific instance, are installed only once, and cannot be installed side-by-side.
ie:
1.Integration Services
2.SQL Server Browser
3.SQL Server Active Directory Helper
4.SQL Writer

What is the difference between following two statements ? 1. SELECT 'DotnetFunda' 'SQL Server' 2. SELECT 'DotnetFunda' = 'SQL Server'

Difference between the two statements given below

1. SELECT 'DotnetFunda' 'SQL Server'

2. SELECT 'DotnetFunda' = 'SQL Server'

The value 'DotnetFunda' uses 'SQL Server' as an Alias name. Alias will be the column Name.

SELECT 'DotnetFunda' 'SQL Server'

Go

SQL Server
DotnetFunda

The value 'SQL Server' assignes to 'DotnetFunda'. Target will be the column Name.

SELECT 'DotnetFunda' = 'SQL Server'

Go

DotnetFunda
SQL Server

What is "Locking" concept in DBMS?

Locking is the most common type of concurrency control mechanism. In this approach, any data is retrieved by an active user for updating, must be locked or denied to other users until updating is not complete.

Locking is of three types,
1> Shared Lock(S)
2> Exclusive Lock(X)
3> Dead Lock(D)

What is a "dead lock"?

A "Dead Lock" happens when two or more transaction is locked from a common resource. In this case each must have to wait for the other to unlock that resource.

What is "Functional Dependency"?

Suppose there are two sets of attributes, X and Y. Then Y is said to be functionally dependent to X if a given value for each attribute in X uniquely determines the value of the attributes in Y.

Here X is called the determinant of the functional dependency and the functional dependency is denoted as X--->Y

Difference between Database Migration and Upgradation ?

What is Migration ?
Migrating from one Product type to another Product type.
ie: MSAccess to SQL Server, Oracle to SQL Server

What is Upgradation ?
Upgrading from one Edition/Version to another Edition/Version within the same Product type.
ie:SQL Server 2005 to SQL Server 2008

How will you validate whether the linked server properly configured / not ?

Scenario:
1.You are in Server1
2.You have configured a linked server to access the Remote server(Server2)
3.How will you validate whether the configured linked server to the Server2 is properly configured or not ?

Solution:
1.Login into Server1
2.Execute the script given below to validate the remote server accessibility.

EXEC sp_testlinkedserver N'Server2'

Result:
The result should be : Command(s) completed successfully.

If it throws an Err like : "Server 'Server2' is not configured for DATA ACCESS" then, You have to give Data Access server option.

Use Master

Go

EXEC sp_serveroption @server=N'Server2', @optname=N'data access', @optvalue=N'true'

Now, Try to validate again

EXEC sp_testlinkedserver N'Server2'

Result:
The result should be : Command(s) completed successfully.

SQL Server connection is terminated after the query completes. Why ?

Scenario:
I run a simple query, The connection also Disconnected once the query completed. Why ? and How to fix it ?

Solution:
Query Menu --> Query Options... --> Select Advanced node under the Execution root node.
Un-Select the check box "Disconnect after the query executes" and Click OK.

What is constant folding ?

SQL Server evaluates some constant expressions early to improve the query performance. This is referred to as "constant folding"

What is Forced Service ?

Database mirroring provides forcing service (with possible data loss) as a disaster recovery method to allow you to use a mirror server as a warm standby server.

Forcing service is possible only if the principal server is disconnected from the mirror server in a mirroring session. Because forcing service risks possible data loss, it should be used cautiously.

Which objects can not be specified "FORCESEEK" table hint from the following list ?

Normally we can define hint on Tables, Views, Indexed Views, Table-valued function, CTE, DMV, Table variable, Named Subquery, Openrowset.

But, we can not define FORCESEEK table hint on Table-valued function, Table variable and Openrowset objects/statements.

A Foreign Key constraint can be defined to reference the columns of Non Primary key column ?

Yes. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

A FOREIGN KEY constraint can contain null values.

What is Self-referencing table ?

FOREIGN KEY constraint can reference columns in the same table is called self-referencing tables.

Can a FOREIGN KEY constraint reference the column in tables in the different database ?

A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table.

The following error occurred when we try to reference the different database
"The object name contains more than the maximum number of prefixes. The maximum is 2."

How to identify the "Full-Text search Installed or Not" on your current instance of SQL Server ?

SELECT CASE [Full-Text Search] WHEN 1 THEN 'Full-Text Search Installed' ELSE 'Full-Text Search Not Installed' END 'Status' FROM 

(

SELECT SERVERPROPERTY('IsFullTextInstalled') 'Full-Text Search'

) AS X

What are the key points to be followed when implementing Full-Text Search on View ?

When we implement a Full-Text Search on View, We have to follow the key rules

1. View should be 'WITH SCHEMABINDING', Otherwise the following Err will be thrown
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VM_DataTypes' because the view is not schema bound.

2. All tables should be used with schema name (Two part-naming convension, "SchemaName.TableName"), Otherwise the following Err will be thrown
Msg 4512, Level 16, State 3, Procedure VM_DataTypes, Line 2
Cannot schema bind view 'VM_DataTypes' because name 'TB_Category' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

3. Table hint should not be used inside the view(WITH NOLOCK), Otherwise the following Err will be thrown
Msg 10140, Level 16, State 1, Line 1
Cannot create index on view 'DotNetFunda.dbo.VM_DataTypes' because the view contains a table hint. Consider removing the hint.

4. OUTER Join can not be used Inside the View

5. Non-Deterministic & Windows function cannot be used inside the View

6. The Unique Index should be created on single column, Not with composite, Non-Nullable

7. The Index size should not exceed 900 Bytes.

How to Enable / Disable the Full-Text Index created on a View / Table ?

To Enable the Full-Text search Index already created on a View/Table :
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].Table or View Name', @action=N'activate'

To Disable the Full-Text search Index already created on a View/Table :
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].Table or View Name', @action=N'deactivate'

What is Delegation in SQL Server ?

SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as delegation.