Interview Questions for Microsoft SQL Server

We do not guarantee the correctness of all answers. It is user’s responsibility to make sure that the answers are right. Good luck.

1. What is .NET?

    The .NET Framework is a development and execution environment. At its core is a virtual machine that turns intermediate language (IL) into machine code. High-level language compilers for C#, VB.NET and C++ are provided to turn source code into IL.

2. Which are the languages supported by .NET Framework ?

    APL,Fortran,Pascal,C++,Haskell,Perl,C#,Java,Language,Python,COBOL,Microsoft,JScript®,RPG,Component,Pascal,Mercury,Scheme,Curriculum,Mondrian,SmallTalk,Eiffel,Oberon,Standard,ML,Forth,Oz,Microsoft,Visual,Basic

3. Which All of the following are methods of System.Object EXCEPT
Equals()
GetHashCode()
ToString()
ToInt()
GetType ()

    ToInt()

4. Which is the root class in .NET Frame work?

    System.Object.

5. What is the diffrence between logical read and physical read?

    Microsoft® SQL Server™ 2000 allocates much of its virtual memory to a buffer cache and uses the cache to reduce physical I/O. Each instance of SQL Server 2000 has its own buffer cache. Data is read from the database disk files into the buffer cache.Multiple logical reads of the data can be satisfied without requiring that the data be physically read again. The data remains in the cache until it has not been referenced for some time and the database needs the buffer area to read in more data. Data is written back to disk only if it is modified. Data can be changed multiple times by logical writes before a physical write transfers the new data back to disk. A physical read is when SQL Server accesses the disk to read data pages into the data cache before it can execute the query. SQL Server only operates against data in the data cache. A logical read is the number of pages SQL Server reads from the data cache to produce the results specified by a query.

6. What is a Transactions?

    SQLServer ensures that every single transaction operation is either completed (committed) or rolled back. However, you may wish to involve more than one operation in a transaction. In such situations you may explicitly declare a transaction and determine whether or not the transaction should be committed or rolled back.

Begin Transaction tr1
UPDATE tblrssfeed SET fldpriority = 1 WHERE fldpriority = 20
IF @@ERROR <> 0
BEGIN
PRINT 'An error occurred loading the new author information'
Rollback Transaction tr1
END
Else
BEGIN
PRINT 'No error occurred loading the new author information'
Commit Transaction tr1
END

7. SQL Server Views ?

    In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (sometimes even update data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users. Views can also aggregate data, and help partition data.

8. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

    One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book

9. What's the difference between a primary key and a unique key?

    Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but nique key allows one NULL only.

10. What are user defined datatypes and when you should go for them?

    User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

11. What is bit datatype and what's the information that can be stored inside a bit column?

    Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

12. Define candidate key, alternate key, composite key.

    A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.

13. What are defaults? Is there a column to which a default can't be bound?

    A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.

14. What is a transaction and what are ACID properties?

    A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

15. Explain different isolation levels

    An isolation level determines the degree of isolation of data between concurrent transactions. The efault SQL Server solation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed,Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

16. What's the maximum size of a row?

    8060 bytes.

17. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

    DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

18. What are constraints? Explain different types of constraints.

    Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

19. Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table.

    Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

20. What is a join and explain different types of joins.

    Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

21. What are triggers?

    Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

22. What is CLR?

    CLR is the most important part of .NET frame work, Which provides the environment in which the program is executed. The CLR includes a virtual machine, like in Java. At a high level CLR activates objects, perform security checks, lays them in memory, execute them, and garbage collect them.

Tags: Interview Questions for Microsoft SQL Server
Updated on: March 2024