This section will contain SQL Server Interview Questions and Answers.
1. How to optimize stored procedures in SQL Server?
Ans: I. Use where clause
II. Select only required fields
III. Do join on indexed key fields
2. What is the difference between Stored procedure and User defined functions?
3. Why should we go for Stored Procedures? Why not direct queries?
Ans: SP are precompiled and contain an execution plan with it. Hence, they are faster.
4. How many NULL values we can have in a Unique key field in SQL Server?
Ans: Only one. In case of Oracle, we can have multiple NULL values in a Unique key field.
5. What is correlated subquery?
6. What is an index. What are the types?
Indexes in databases are very much similar to Indexes in Books. Indexes help in searching data faster.
Types: Clustered Index
Non-Clustered Index
7. What is the difference between a clustered index and a non-clustered index?
Clustered:
1 Only one clustered index allowed per table
2 Physically rearranges the data
3 For use on columns that are frequently searched for range of data
Non-clustered:
1 Upto 249 non-clustered index allowed per table
2 Doesn’t rearrange the data. Keeps a pointer to the data.
3 For use on columns that are searched for single value.
8. What is a join? What are the types of joins?
Joins are used to retrieve data from multiple tables.
Following are the types of joins:
Inner join
Left outer join
Right outer join
Full outer join
Cross join
9. What is a transaction?
A SQL transaction is a sequence of operations performed as a
single unit of work. If all the tasks are completed successfully, then the transaction is committed (saved). If a single task fails, the transaction is rolled back (discarded).
10. What is ACID property of transaction?
A SQL transaction must exhibit ACID property, i.e Atomicity, Consistency, Isolation, and Durability.
Atomicity: A transaction is always treated as a single unit of work, i.e. either all the tasks are performed or none of them, no intermediate stage.
Consistency: When a transaction is completed, it must leave all datain a consistent state. Isolation: Modifications made by a transaction must be isolated from the modifications made by other transactions. Durability: After a transaction is completed, it’s effects are permanently in place in the system.
11. What is SET NOCOUNT ON?
When we perform a SELECT, INSERT, UPDATE or DELETE query, it returns a COUNT (number of rows affected) when SET NOCOUNT OFF. If SET NOCOUNT ON, it doesn’t return the COUNT.
12. How to delete exactly duplicate records from a table?
There are many ways. Simplest answer is:
i. Let the table tab1 contains duplicate records.
ii. Insert distinct records from tab1 in a temporary table #temp
INSERT INTO #temp
SELECT DISTINCT * FROM tab1
iii. Delete all rows from original table tab1
DELETE FROM tab1
iv. Insert from temporary table
INSERT INTO tab1
SELECT * FROM #temp
Try other solutions yourself.
13. How to get nth highest salary from employee table.
The query below demonstrates how to find the 5th highest salary. Replace 5 with any integer to get nth salary.
SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 5 SALARY
FROM EMPLOYEE ORDER BY SALARY DESC) a
ORDER BY SALARY ASC
Please post your comments here. Also, post your questions to get an answer.
What is the use of indexes and what are the types of indexes available in SQL Server?
What is the use of indexes and what are the types of indexes available in SQL Server?
Indexes are used to find data quickly when a query is processed in any relational database. Indexes improve performance by 10 to 500 times.
Index can improve the performance in following operations:
* Find the records matching with WHERE clause
UPDATE Books SET Availability = 1 WHERE SubjectId =12
DELETE FROM Books WHERE Price <10
SELECT * FROM Books WHERE Price BETWEEN 50 AND 80
* Sorting the result with ORDER BY
SELECT * FROM Books ORDER BY Price DESC
* Grouping records and aggregate values
SELECT Count(*) as Units, Price FROM Books GROUP BY Price
There are two types of indexes available in SQL Server: clustered and non-clustered
Clustered index
Clustered index physically reorders the records of a table. Therefore a table can have only one clustered index. Usually a clustered index will be created on the primary key of a table.
Non – Clustered Index
Non – Clustered index are stored in the order of the index key values, but the information in the table is stored in a different order. Means logical sorting of data not Physical. In SQl Server 2005 a table can have 249 non-clustered indexes.
Composite Indexes
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. If you have composite index on Price and BookName then take can take advantage of it like this:
SELECT BookName, Price FROM Products ORDER BY UnitPrice BookName, Price DESC
Indexes are used to find data quickly when a query is processed in any relational database. Indexes improve performance by 10 to 500 times.
Index can improve the performance in following operations:
* Find the records matching with WHERE clause
UPDATE Books SET Availability = 1 WHERE SubjectId =12
DELETE FROM Books WHERE Price <10
SELECT * FROM Books WHERE Price BETWEEN 50 AND 80
* Sorting the result with ORDER BY
SELECT * FROM Books ORDER BY Price DESC
* Grouping records and aggregate values
SELECT Count(*) as Units, Price FROM Books GROUP BY Price
There are two types of indexes available in SQL Server: clustered and non-clustered
Clustered index
Clustered index physically reorders the records of a table. Therefore a table can have only one clustered index. Usually a clustered index will be created on the primary key of a table.
Non – Clustered Index
Non – Clustered index are stored in the order of the index key values, but the information in the table is stored in a different order. Means logical sorting of data not Physical. In SQl Server 2005 a table can have 249 non-clustered indexes.
Composite Indexes
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. If you have composite index on Price and BookName then take can take advantage of it like this:
SELECT BookName, Price FROM Products ORDER BY UnitPrice BookName, Price DESC
What is static member of class?
What is static member of class?
A static member belongs to the class rather than to the instances of the class. In C# data fields, member functions, properties and events can be declared static. When any instances of the class are created, they cannot be used to access the static member.
To access a static class member, use the name of the class instead of an instance variable
Static methods and Static properties can only access static fields and static events.
Like: int i = Car.GetWheels;
Here Car is class name and GetWheels is static property.
Static members are often used to represent data or calculations that do not change in response to object state.
A static member belongs to the class rather than to the instances of the class. In C# data fields, member functions, properties and events can be declared static. When any instances of the class are created, they cannot be used to access the static member.
To access a static class member, use the name of the class instead of an instance variable
Static methods and Static properties can only access static fields and static events.
Like: int i = Car.GetWheels;
Here Car is class name and GetWheels is static property.
Static members are often used to represent data or calculations that do not change in response to object state.
What is a static class?
What is a static class?
We can declare a static class. We use static class when there is no data or behavior in the class that depends on object identity. A static class can have only static members. We can not create instances of a static class using the new keyword. .NET Framework Common Language Runtime (CLR) loads Static classes automatically when the program or namespace containing the class is loaded.
Here are some more features of static class
* Static classes only contain static members.
* Static classes can not be instantiated. They cannot contain Instance Constructors
* Static classes are sealed.
We can declare a static class. We use static class when there is no data or behavior in the class that depends on object identity. A static class can have only static members. We can not create instances of a static class using the new keyword. .NET Framework Common Language Runtime (CLR) loads Static classes automatically when the program or namespace containing the class is loaded.
Here are some more features of static class
* Static classes only contain static members.
* Static classes can not be instantiated. They cannot contain Instance Constructors
* Static classes are sealed.
What is the basic functionality of Garbage Collector in Microsft.Net?
The Common Language Runtime (CLR) requires that you create objects in the managed heap, but you do not have to bother with cleaning up the memory once the object goes out of the scope or is no longer needed. The Microsoft .NET Framework Garbage Collector provides memory management capabilities for managed resources. The Garbage Collector frees objects that are not referenced and reclaims their memory. You should set your references to Nothing(null) as soon as you are done with them to ensure your objects are eligible for collection as soon as possible.
Here are the list of some tasks performed by the Garbage collector:
* Garbage collector reserves a piece of memory as the application starts for the managed heap.
* Garbage collector controls the managed heap memory currently used and available to an application.
* Garbage collector allocates memory for new objects within the application.
* The Garbage Collector attempts to reclaim the memory of objects that are not referenced.
Here are the list of some tasks performed by the Garbage collector:
* Garbage collector reserves a piece of memory as the application starts for the managed heap.
* Garbage collector controls the managed heap memory currently used and available to an application.
* Garbage collector allocates memory for new objects within the application.
* The Garbage Collector attempts to reclaim the memory of objects that are not referenced.
Explain ACID properties of the database?
All Database systems which include transaction support implement ACID properties to ensure the integrity of the database. ACID stands for Atomicity, Consistency, Isolation and Durability
* Atomicity: Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. Modifications on the data in the database either fail or succeed.
* Consistency: This property ensures that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
* Isolation: It requires that multiple transactions occurring at the same time not impact each other’s execution.
* Durability: It ensures that any transaction committed to the database will not be lost.
* Atomicity: Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. Modifications on the data in the database either fail or succeed.
* Consistency: This property ensures that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
* Isolation: It requires that multiple transactions occurring at the same time not impact each other’s execution.
* Durability: It ensures that any transaction committed to the database will not be lost.
What is the use of XSLT?
What is the use of XSLT?
XSLT stands for Extensible Stylesheet Language Transformations. This language used in XSL style sheets to transform XML documents into other XML documents.
XSLT is based on template rules which specify how XML documents should be processed. An XSLT processor reads both an XML document and an XSLT style sheet. Based on the instructions the processor finds in the XSLT style sheet, it produce a new XML document. With XSLT we can also produce HTML or XHTML from XML document. With XSLT we can add/remove elements and attributes, rearrange and sort elements, hide and display elements from the output file. Converting XML to HTML for display is probably the most common application of XSLT today.
XSLT stands for Extensible Stylesheet Language Transformations. This language used in XSL style sheets to transform XML documents into other XML documents.
XSLT is based on template rules which specify how XML documents should be processed. An XSLT processor reads both an XML document and an XSLT style sheet. Based on the instructions the processor finds in the XSLT style sheet, it produce a new XML document. With XSLT we can also produce HTML or XHTML from XML document. With XSLT we can add/remove elements and attributes, rearrange and sort elements, hide and display elements from the output file. Converting XML to HTML for display is probably the most common application of XSLT today.
What is a Strong Name in Microsoft Visual Studio.Net?
What is a Strong Name in Microsoft Visual Studio.Net?
In Microsoft Visual Studio.Net a strong name consists of the assembly's identity. The strong name guarantees the integrity of the assembly. Strong Name includes the name of the .net assembly, version number, culture identity, and a public key token. It is generated from an assembly file using the corresponding private key.
Steps to create strong named assembly:
To create a strong named assembly you need to have a key pair (public key and a private key) file.
Use sn -k KeyFile.snk
Open the dot net project to be complied as a strong named assembly. Open AssembyInfo.cs/
AssembyInfo.vb file. Add the following lines to AssemblyInfo file.
[Assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("..\\..\\KeyFile.snk")]
Compile the application, we have created a strong named assembly.
In Microsoft Visual Studio.Net a strong name consists of the assembly's identity. The strong name guarantees the integrity of the assembly. Strong Name includes the name of the .net assembly, version number, culture identity, and a public key token. It is generated from an assembly file using the corresponding private key.
Steps to create strong named assembly:
To create a strong named assembly you need to have a key pair (public key and a private key) file.
Use sn -k KeyFile.snk
Open the dot net project to be complied as a strong named assembly. Open AssembyInfo.cs/
AssembyInfo.vb file. Add the following lines to AssemblyInfo file.
[Assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("..\\..\\KeyFile.snk")]
Compile the application, we have created a strong named assembly.
What is the difference between classes and structs in Microsoft.Net?
What is the difference between classes and structs in Microsoft.Net?
# A struct is a value type, while a class is a reference type.
# When we instantiate a class, memory will be allocated on the heap. When struct gets initiated, it gets memory on the stack.
# Classes can have explicit parameter less constructors. But structs cannot have this.
# Classes support inheritance. But there is no inheritance for structs. A struct cannot inherit from another struct or class, and it cannot be the base of a class. Like classes, structures can implement interfaces.
# We can assign null variable to class. But we cannot assign null to a struct variable, since structs are value type.
# We can declare a destructor in class but can not in struct.
# A struct is a value type, while a class is a reference type.
# When we instantiate a class, memory will be allocated on the heap. When struct gets initiated, it gets memory on the stack.
# Classes can have explicit parameter less constructors. But structs cannot have this.
# Classes support inheritance. But there is no inheritance for structs. A struct cannot inherit from another struct or class, and it cannot be the base of a class. Like classes, structures can implement interfaces.
# We can assign null variable to class. But we cannot assign null to a struct variable, since structs are value type.
# We can declare a destructor in class but can not in struct.
What is the purpose of Connection Pooling in ADO.NET?
What is the purpose of Connection Pooling in ADO.NET?
Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.
By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.
When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released. But be sure than your connections use the same connection string each time. Here is the Syntax
conn.ConnectionString = "integrated Security=SSPI; SERVER=192.168.0.123; DATABASE=MY_DB; Min Pool Size=4;Max Pool Size=40;Connect Timeout=14;";
Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.
By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.
When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released. But be sure than your connections use the same connection string each time. Here is the Syntax
conn.ConnectionString = "integrated Security=SSPI; SERVER=192.168.0.123; DATABASE=MY_DB; Min Pool Size=4;Max Pool Size=40;Connect Timeout=14;";
Explain the Delegates in C#.
Delegates in C# are objects which points towards a function which matches its signature. Delegates are reference type used to encapsulate a method with a specific signature. Delegates are similar to function pointers in C++; however, delegates are type-safe and secure.
Here are some features of delegates:
* A delegate represents a class.
* A delegate is type-safe.
* We can use delegates both for static and instance methods
* We can combine multiple delegates into a single delegate.
* Delegates are often used in event-based programming, such as publish/subscribe.
* We can use delegates in asynchronous-style programming.
* We can define delegates inside or outside of classes.
Syntax of using delegates
//Declaring delegate
delegate void SampleDelegate(string message);
// declare method with same signature:
static void SampleDelegateMethod(string message) { Console.WriteLine(message); }
// create delegate object
SampleDelegate d1 = SampleDelegateMethod;
// Invoke method with delegate
d1("my program");
Value types VS Reference types C#
What is difference between Value types and Reference types in VB.NET or C# (Value types VS Reference types)
C# provides two types—class and structure, class is a reference type while the other is a value type. Here is difference between Value types and Reference types.
Value types directly contain their data which are either allocated on the stack or allocated in-line in a structure. Reference types store a reference to the value's memory address, and are allocated on the heap. With reference types, an object is created in memory, and then handled through a separate reference—rather like a pointer.
Reference types can be self-describing types, pointer types, or interface types. Primitive types such as int, float, bool and char are value types.
Variables that are value types each have their own copy of the data, and therefore operations on one variable do not affect other variables. Variables that are reference types can refer to the same object; therefore, operations on one variable can affect the same object referred to by another variable.
Value types have a default implied constructor that initializes the default value. Reference types default to a null reference in memory.
Value types derive from System.ValueType. Reference types derive from System.Object.
Value types cannot derive a new type from an existing value type, except for structs. Reference types can derive a new type from an existing reference type as well as being able to implement interfaces.
C# provides two types—class and structure, class is a reference type while the other is a value type. Here is difference between Value types and Reference types.
Value types directly contain their data which are either allocated on the stack or allocated in-line in a structure. Reference types store a reference to the value's memory address, and are allocated on the heap. With reference types, an object is created in memory, and then handled through a separate reference—rather like a pointer.
Reference types can be self-describing types, pointer types, or interface types. Primitive types such as int, float, bool and char are value types.
Variables that are value types each have their own copy of the data, and therefore operations on one variable do not affect other variables. Variables that are reference types can refer to the same object; therefore, operations on one variable can affect the same object referred to by another variable.
Value types have a default implied constructor that initializes the default value. Reference types default to a null reference in memory.
Value types derive from System.ValueType. Reference types derive from System.Object.
Value types cannot derive a new type from an existing value type, except for structs. Reference types can derive a new type from an existing reference type as well as being able to implement interfaces.
SQL Server 2008 advantages for Developers
Microsoft has released a new version of its popular Database management system (SQL Server). It is Microsoft SQL Server 2008. So what are the benefits of SQL SERVER 2008 for Developers & Programmers.
SQL Management Studio improvements including IntelliSense.
New Data Types for just dates or times (no more storing time when you only need the date).
New Hierarchical data support .IsDescendent(), that can automatically pull a hierarchical view of data (no more custom recursive queries).
New Grouping Sets statement which enables you to automatically group dimensions in a query for easy aggregation and reporting.
New Merge statement which provides automatic insert/update semantics for keeping multiple data sources in sync.
New data types and support for Geodata and geometry.
New support for optimizing "empty" or row/column tables using the sparse keyword.
New FileStream attribute that enables you to include files that are stored in the server file system, but can be managed by SQL Server.
SQL Management Studio improvements including IntelliSense.
New Data Types for just dates or times (no more storing time when you only need the date).
New Hierarchical data support .IsDescendent(), that can automatically pull a hierarchical view of data (no more custom recursive queries).
New Grouping Sets statement which enables you to automatically group dimensions in a query for easy aggregation and reporting.
New Merge statement which provides automatic insert/update semantics for keeping multiple data sources in sync.
New data types and support for Geodata and geometry.
New support for optimizing "empty" or row/column tables using the sparse keyword.
New FileStream attribute that enables you to include files that are stored in the server file system, but can be managed by SQL Server.
SQL Server Basic Databases
SQL Server Interview Question. Name the Basic or Default Databases of SQL SERVER and What are their functions and use.?
Microsoft SQL SERVER Provides 4 default databases
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
Microsoft SQL SERVER Provides 4 default databases
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
Aggregate Functions in SQL SERVER 2008
What are Aggregate functions? Explain Aggregate functions in SQL SERVER 2008 with example.
Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value.
SQL SERVER 2008 / Transact-SQL supports following aggregate functions:
AVG: Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
MAX and MIN: Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values.
SUM: Calculates the total of all data values in a column. The column must contain numeric values.
COUNT: Calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
COUNT_BIG: New and Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.
Aggregate function Example:
SELECT ProjectName, SUM(budget) TotalBudget FROM Project_Tbl GROUP BY ProjectName;
Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value.
SQL SERVER 2008 / Transact-SQL supports following aggregate functions:
AVG: Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
MAX and MIN: Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values.
SUM: Calculates the total of all data values in a column. The column must contain numeric values.
COUNT: Calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
COUNT_BIG: New and Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.
Aggregate function Example:
SELECT ProjectName, SUM(budget) TotalBudget FROM Project_Tbl GROUP BY ProjectName;
Subscribe to:
Posts (Atom)