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

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

What is an Index?

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What command do we use to rename a db?

sp_renamedb ‘oldname’ , ‘newname’

What do you mean by COLLATION?

Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

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

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

Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping 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 and we can use this as the DR (disaster recovery) plan.

Where do you think the users names and passwords will be stored in sql server?

They get stored in master db in the sysxlogins table.

What is a deadlock?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process

what is ACID?

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Above four rules are very important for any developers dealing with databases

What Is DTS?

DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.

What is Log Shipping?

In Microsoft SQL Server, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.

What are sequence diagrams? What you will get out of this sequence diagrams?

Sequence diagrams document the interactions between classes to achieve a result, such as a use case. Because UML is designed for object-oriented programming, these communications between classes are known as messages. The sequence diagram lists objects horizontally, and time vertically, and models these messages over time.

What is a tuple?

A tuple is an instance of data within a relational database.

What is the purpose of UPDATE STATISTICS?

Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance

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

Grant, Deny and Revoke

What is DTC?

The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction

How to get GUID in sql server?

select newid().
This will return you GUID

What is Check Constraint?

Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.

Can we create a Foreign Key with out Primary Key?

Yes. If the table has Unique Key then it is posible to create a Foreign key constraint

Difference between varchar and char:

varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.

Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.)

Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc).