Questions from http://www.sqlserverquest.com
i. What is DBMS?
ii. What is RDBMS?
iii. What are the ACID properties?
iv. What is Normalization?
v. What is D-normalization and when do we need it?
vi. What are the different Normal Forms?
b. Table Design
i. What is Table? *
ii. What is Data type? *
iii. What are the different types of data types in SQL Server? *
iv. What is the difference between Float, BIG Int and Integer? *
v. Explain the Decimal Data types and its uses. *
vi. What is the use of different types of Integer data types? *
vii. What is the difference between bit, Char and Varchar? *
viii. What is Unicode? **
ix. What is the difference between Varchar and Nvarchar? **
x. What are different date time data types? *
xi. What are unique identifiers in SQL Server Table? **
xii. What is check constraint in SQL Server? **
xiii. What is default constraint? *
xiv. What is Null ability? *
xv. Explain table relationship? *
xvi. What is Primary Key? *
xvii. What is Foreign Key? *
xviii. What is composite key? *
xix. What are Primary Key and Foreign Key Constraint? *
xx. How to get the list of primary key and foreign key of the table? ****
xxi. Can we insert null value in primary column? *
xxii. No we can’t insert null value in a single column primary key while in composite primary key only one column can be null
xxiii. What is difference between Primary Key and Unique Key? **
xxiv. We can insert one null value in unique key but not in primary key.
xxv. What is Identity Column in Table? *
xxvi. Syntax to check current Identity of the table? *
xxvii. What is the difference between Scope of Identity on @@identity? ***
xxviii. Can we change identity key values for a table or reset the identity key value. ***
xxix. What is function Ident_INCR? ***
xxx. What is times stamp data type is SQL Server? **
xxxi. What is the alternative of timestamp? **
c. JOINS AND SELECT
i. What is select statement in TSQL? *
ii. What is the Join in SQL Server? *
iii. What are the different types of join? *
iv. Difference between Left join and Outer Join? *
v. What is full outer join? *
vi. What is inner join? *
vii. What is cross join? *
viii. Write one example for self-join? **
ix. What is where clause? *
x. What are the sub queries? *
xi. What are the nested queries in SQL Server? **
xii. What is inline query? **
xiii. What are the aggregate functions in SQL Server? *
xiv. What is Group By? *
xv. What is Having clause? **
xvi. What is difference between where and having clause? **
xvii. What is the table variable? **
xviii. What is the temporary table? **
xix. What is the global temporary table? **
xx. Difference between temporary table and table variable? **
xxi. What is the union all clause? **
xxii. What is union Clause? **
xxiii. Difference between union all and union clause? **
xxiv. What is the Ranking function in SQL Server? ****
xxv. Difference between rank and dense rank? ****
xxvi. What is top operator in SQL Server? **
xxvii. What is table sample in SQL Server? ***
xxviii. What is the delete command? *
xxix. What is truncate command? **
xxx. Difference between delete and truncate? **
xxxi. What are dynamic queries? ***
xxxii. What is distinct clause? How it works? **
xxxiii. How to use Order by in Sub Queries? **
xxxiv. What is the Limit of small date time function? **
xxxv. What is the @@error? **
xxxvi. What is the @@raise error? **
xxxvii. What is the @@row count? **
xxxviii. What is Date Diff function? **
xxxix. What is Date Add Function? **
xl. What is date part function? ***
xli. What is coalesce function in SQL Server? ***
xlii. What is difference between stuff and Replace function? ***
xliii. What are the sparse column and when we use it? ****
xliv. How to Convert Integer to String in SQL Server?
i. What is the view in SQL Server? **
ii. What are the updated views? ***
iii. What are the materialized views or Indexed Views in SQL Server? ****
iv. What is view with check option? ****
v. Write the Create syntax for Views. **
vi. What is common table expression (CTE)? ***
vii. What is difference between CTE and View? ****
viii. Stored procedure
ix. What is stored procedure in SQL Server? **
x. How to pass record set in stored procedure? ***
xi. Is nesting possible in stored procedure? If yes How many number of times? ***
xii. Write the create syntax for the procedure. **
xiii. How to return output parameter from stored procedure? ***
xiv. How to perform error handling in Stored procedure? ****
i. What is the User defined functions? **
ii. What are the difference types of UDF? **
iii. What are difference between UDF and stored procedure? ***
iv. Can we create a table in function? ****
i. What is a trigger in SQL Server? **
ii. What is the nesting level of triggers? **
iii. What are the different types of triggers in SQL Server? ***
i. What is the cursor in SQL server? **
ii. What are the different types of cursor in SQL Server? ***
iii. When to use Cursor in SQL Server? ***
i. What is Index in SQL Server? ***
ii. Explain different types of index in SQL Server? ***
iii. What is filter index in SQL Server? ****
iv. What is Covering Index? ****
v. What is index scan and index seek? ****
vi. What is B+ tree index? ****
vii. Explain the architecture of the index? ****
viii. Explain the performance impact of index? ****
ix. Can we create clustered index on null value column? ****
x. What are indexes in SQL Server? ****
xi. How many clustered index can be created on the table? ****
xii. How to enforce index on a query? ****
xiii. What are the different index configurations a table can have?
i. Performance tuning
i. What is SQL Profiler?
ii. What is Database Tuning Advisor?
iii. What is Execution Plan?
iv. How to Read Execution Plan?
v. What are the statistics and the impact on SQL Server?
vi. What are the table hint and how we can provide it on table?
vii. How to read execution plan?
viii. What are the algorithms does SQL Server used to fetch data?
ix. What is bookmark lookup?
x. How do you tell SQL Server to preserve the join order in the FROM clause during optimization?
xi. You can use OPTION (FORCE ORDER)
i. What is Transaction Server Implicit?
ii. What is Transaction Server Auto commit?
iii. What is Transaction Server Explicit transaction?
iv. What is Transaction Server Consistency?
v. What is Transaction Server Isolation?
vi. Explain Atomicity?
vii. What are the different types of transaction errors?
viii. What is No lock?
ix. What happens if a transaction is failed in nested transaction, then other outer transactions will execute or not?
x. Explain Atomicity, Consistency, Isolation, and Durability?
xi. What is nested transaction?
xii. What is distributed transaction?
xiii. What is isolation level in SQL Server?
xiv. What are different types of isolation level in SQL Server?
xv. What is default isolation Level for SQL Server?
i. How Left Join with count (*) works.
ii. Write a query to find the nth minimum and maximum?
iii. Write a query to get the last record of the table?
iv. Write a query to get the no rows of a table without using count or any other clause?
v. How many columns can be used with roll up or with cube command?
vi. How many columns can be selected in a select statement?
vii. How many columns can be selected in an insert statement?
viii. What level of nesting is possible for sub Queries?
ix. What level of nesting is possible for Triggers?
x. How many non-clustered indexes can be created on a table?
xi. How many parameters can be provided to a stored procedure?
xii. How many parameters can be provided to a UDF?
xiii. How many columns can be taken in update statement?
xiv. What is NO COUNT used for?
xv. What is SET ANSI NULL ON used for?
xvi. What is arithmetic abort used for?
xvii. What is SET ROW COUNT used for?
xviii. What is SET ANSI padding?
xix. What is SET ANSI WARNING on?
xx. What is SET NO EXEC?
xxi. Which command using Query Analyser will give you the version of SQL server and operating system?
xxii. How to delete duplicate records from table using query?
xxiii. What are different types of Collation Sensitivity?
1. What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
2. What are the properties of the Relational tables?
Relational tables have six properties:
- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column must have a unique name.
3. What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
4. What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
5. What are different normalization forms?
- 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
- 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table.
- 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
- BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
- 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related.
- 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.
- ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
- DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
6. What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.e.g. sp_helpdb, sp_renamedb, sp_depends etc.
7. What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
8. What is Nested Trigger?
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
9. What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
10. What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
11. What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
12. What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by- row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.In order to work with a cursor we need to perform some steps in the following order:
- Declare cursor
- Open cursor
- Fetch row from the cursor
- Process fetched row
- Close cursor
- Deallocate cursor
13. What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.
14. What is Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
15. What is sub-query? Explain properties of sub-query?
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.
16. What are different Types of Join?
- Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
- Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
- Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
- Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
- Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
- Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
- Self Join This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.
17. What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.
18. What is User Defined Functions? What kind of User-Defined Functions can be created?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User-Defined Functions created are:
- Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
- Inline Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
- Multi-statement Table-Value User-Defined Function A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
19. What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
20. What is DataWarehousing?
- Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
- Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
- Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
- Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.
SQL Server database developer interview questions and answers
Development hiring managers and potential interviewees may find these open-ended SQL Server proficiency interview Q&As useful.
The first installment of this TechRepublic series focused on C# developer interview questions
. It generated a lively discussion on the merits of such questions and the different approaches to measuring technical aptitude during an interview process.
This week we take a look at SQL Server
. These questions (which all apply to SQL Server 2008) are not meant to be the only way to ascertain a candidate’s technical expertise during an interview, but rather one piece of the puzzle that may spawn more thorough discussions. The depth of the questions may vary according to the level of the open position; for instance, you would expect more from a senior level candidate than from a junior level candidate.
What are DMVs?
Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:
SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like the sp_who2 command:
SELECT * FROM sys.dm_exec_sessions;
What are temp tables? What is the difference between global and local temp tables?
Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.
CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )
Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.
CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )
Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.
How are transactions used?
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.
What is the difference between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.
What are DBCC commands?
Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:
DBCC CHECKALLOC — Check disk allocation consistency.
DBCC OPENTRAN — Display information about recent transactions.
DBCC HELP — Display Help for DBCC commands.
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.
What does the NOLOCK query hint do?
Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.
SELECT * FROM table_name (NOLOCK)
What is a CTE?
A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:
WITH ExampleCTE (id, fname, lname)
SELECT id, firstname, lastname FROM table
SELECT * FROM ExampleCTE
A CTE can be used in place of a view in some instances.
What is a view? What is the WITH CHECK OPTION clause for a view?
A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.
The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.
What is a query execution plan?
SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.
What does the SQL Server Agent Windows service do?
SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.
What is the default port number for SQL Server?
If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.
The list of possible questions is endless. I am sure these questions will spawn debate and discussion.
I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31
In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating. I’d like to discuss the pros and cons of a Question and Answer format here.
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31
- What is RDBMS?
- What are the Properties of the Relational Tables?
- What is Normalization?
- What is De-normalization?
- How is ACID property related to Database?
- What are the Different Normalization Forms?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31
- What is a Stored Procedure?
- What is a Trigger?
- What are the Different Types of Triggers?
- What is a View?
- What is an Index?
- What is a Linked Server?
- What is a Cursor?
- What is Collation?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31
- What is the Difference between a Function and a Stored Procedure?
- What is subquery? Explain the Properties of a Subquery?
- What are Different Types of Join?
- What are Primary Keys and Foreign Keys?
- What is User-defined Functions? What are the types of User-defined Functions that can be created?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31
- What is an Identity?
- What is DataWarehousing?
- What languages BI uses to achieve the goal?
- What is Standby Servers? Explain Types of Standby Servers.
- What is Dirty Read?
- Why can’t I use Outer Join in an Indexed View?
- What is the Correct Order of the Logical Query Processing Phases?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31
- Which TCP/IP port does the SQL Server run on? How can it be Changed?
- What are the Difference between Clustered and a Non-clustered Index?
- What are the Different Index Configurations a Table can have?
- What are Different Types of Collation Sensitivity?
- What is OLTP (Online Transaction Processing)?
- What’s the Difference between a Primary Key and a Unique Key?
- What is Difference between DELETE and TRUNCATE Commands?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31
- What are Different Types of Locks?
- What are Pessimistic Lock and Optimistic Lock?
- When is the use of UPDATE_STATISTICS command?
- What is the Difference between a HAVING clause and a WHERE clause?
- What is Connection Pooling and why it is Used?
- What are the Properties and Different Types of Sub-Queries?
- What are the Authentication Modes in SQL Server? How can it be Changed?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31
- Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
- What is an SQL Server Agent?
- Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
- What is Log Shipping?
- Name 3 ways to get an Accurate Count of the Number of Records in a Table?
- What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
- What is the Difference between a Local and a Global Temporary Table?
- What is the STUFF Function and How Does it Differ from the REPLACE Function?
- What is PRIMARY KEY?
- What is UNIQUE KEY Constraint?
- What is FOREIGN KEY?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31
- What is CHECK Constraint?
- What is NOT NULL Constraint?
- What is the difference between UNION and UNION ALL?
- What is B-Tree?
- How to get @@ERROR and @@ROWCOUNT at the Same Time?
- What is a Scheduled Job or What is a Scheduled Task?
- What are the Advantages of Using Stored Procedures?
- What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
- Can SQL Servers Linked to other Servers like Oracle?
- What is BCP? When is it Used?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31
- What Command do we Use to Rename a db, a Table and a Column?
- What are sp_configure Commands and SET Commands?
- How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
- What is Difference between Commit and Rollback when Used in Transactions?
- What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31
- What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
- What is the difference between CHAR and VARCHAR Datatypes?
- What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
- What is the Difference between VARCHAR and NVARCHAR datatypes?
- Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
- How to Optimize Stored Procedure Optimization?
- What is SQL Injection? How to Protect Against SQL Injection Attack?
- How to Find Out the List Schema Name and Table Name for the Database?
- What is CHECKPOINT Process in the SQL Server?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31
- How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
- How to Find the List of Fixed Hard Drive and Free Space on Server?
- Why can there be only one Clustered Index and not more than one?
- What is Difference between Line Feed (\n) and Carriage Return (\r)?
- Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
- What is a Hint?
- How to Delete Duplicate Rows?
- Why the Trigger Fires Multiple Times in Single Login?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31
- What is Aggregate Functions?
- What is Use of @@ SPID in SQL Server?
- What is the Difference between Index Seek vs. Index Scan?
- What is the Maximum Size per Database for SQL Server Express?
- How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
- What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
- How to Create Primary Key with Specific Name while Creating a Table?
- What is T-SQL Script to Take Database Offline – Take Database Online
- How to Enable/Disable Indexes?
- Can we Insert Data if Clustered Index is Disabled?
- How to Recompile Stored Procedure at Run Time?
- Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
- What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
- Does the Order of Columns in UPDATE statements Matter?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31
- What are the basic functions for master, msdb, model, tempdb and resource databases?
- What is the Maximum Number of Index per Table?
- Explain Few of the New Features of SQL Server 2008 Management Studio
- Explain IntelliSense for Query Editing
- Explain MultiServer Query
- Explain Query Editor Regions
- Explain Object Explorer Enhancements
- Explain Activity Monitors
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31
- What is Service Broker?
- Where are SQL server Usernames and Passwords Stored in the SQL server?
- What is Policy Management?
- What is Database Mirroring?
- What are Sparse Columns?
- What does TOP Operator Do?
- What is CTE?
- What is MERGE Statement?
- What is Filtered Index?
- Which are the New Data Types Introduced in SQL SERVER 2008?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31
- What are the Advantages of Using CTE?
- How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
- What is CLR?
- What are Synonyms?
- What is LINQ?
- What are Isolation Levels?
- What is Use of EXCEPT Clause?
- What is XPath?
- What is NOLOCK?
- What is the Difference between Update Lock and Exclusive Lock?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31
- How will you Handle Error in SQL SERVER 2008?
- What is RAISEERROR? What is RAISEERROR?
- How to Rebuild the Master Database?
- What is the XML Datatype?
- What is Data Compression?
- What is Use of DBCC Commands?
- How to Copy the Tables, Schema and Views from one SQL Server to Another?
- How to Find Tables without Indexes?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31
- How to Copy Data from One Table to Another Table?
- What is Catalog Views?
- What is PIVOT and UNPIVOT?
- What is a Filestream?
- What is SQLCMD?
- What do you mean by TABLESAMPLE?
- What is ROW_NUMBER()?
- What are Ranking Functions?
- What is Change Data Capture (CDC) in SQL Server 2008?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31
- How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
- What is the CPU Pressure?
- How can I Get Data from a Database on Another Server?
- What is the Bookmark Lookup and RID Lookup?
- What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
- What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
- How can I Check that whether Automatic Statistic Update is Enabled or not?
- How to Find Index Size for Each Index on Table?
- What is the Difference between Seek Predicate and Predicate?
- What are Basics of Policy Management?
- What are the Advantages of Policy Management?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31
- What are Policy Management Terms?
- What is the ‘FILLFACTOR’?
- Where in MS SQL Server is ’100’ equal to ‘0’?
- What are Points to Remember while Using the FILLFACTOR Argument?
- What is a ROLLUP Clause?
- What are Various Limitations of the Views?
- What is a Covered index?
- When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
- What are Wait Types?
- How to Stop Log File Growing too Big?
- If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 21 of 31
- What is Data Warehousing?
- What is Business Intelligence (BI)?
- What is a Dimension Table?
- What is Dimensional Modeling?
- What is a Fact Table?
- What are the Fundamental Stages of Data Warehousing?
- What are the Different Methods of Loading Dimension tables?
- Describes the Foreign Key Columns in Fact Table and Dimension Table?
- What is Data Mining?
- What is the Difference between a View and a Materialized View?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31
- What is OLTP?
- What is OLAP?
- What is the Difference between OLTP and OLAP?
- What is ODS?
- What is ER Diagram?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31
- What is ETL?
- What is VLDB?
- Is OLTP Database is Design Optimal for Data Warehouse?
- If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
- What are Lookup Tables?
- What are Aggregate Tables?
- What is Real-Time Data-Warehousing?
- What are Conformed Dimensions?
- What is a Conformed Fact?
- How do you Load the Time Dimension?
- What is a Level of Granularity of a Fact Table?
- What are Non-Additive Facts?
- What is a Factless Facts Table?
- What are Slowly Changing Dimensions (SCD)?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 24 of 31
- What is Hybrid Slowly Changing Dimension?
- What is BUS Schema?
- What is a Star Schema?
- What Snow Flake Schema?
- Differences between the Star and Snowflake Schema?
- What is Difference between ER Modeling and Dimensional Modeling?
- What is Degenerate Dimension Table?
- Why is Data Modeling Important?
- What is a Surrogate Key?
- What is Junk Dimension?
- What is a Data Mart?
- What is the Difference between OLAP and Data Warehouse?
- What is a Cube and Linked Cube with Reference to Data Warehouse?
- What is Snapshot with Reference to Data Warehouse?
- What is Active Data Warehousing?
- What is the Difference between Data Warehousing and Business Intelligence?
- What is MDS?
- Explain the Paradigm of Bill Inmon and Ralph Kimball.
SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31
Paras Doshi has submitted 21 interesting question and answers for SQL Azure.
1.What is SQL Azure?
2.What is cloud computing?
3.How is SQL Azure different than SQL server?
4.How many replicas are maintained for each SQL Azure database?
5.How can we migrate from SQL server to SQL Azure?
6.Which tools are available to manage SQL Azure databases and servers?
7.Tell me something about security and SQL Azure.
8.What is SQL Azure Firewall?
9.What is the difference between web edition and business edition?
10.How do we synchronize On Premise SQL server with SQL Azure?
11.How do we Backup SQL Azure Data?
12.What is the current pricing model of SQL Azure?
13.What is the current limitation of the size of SQL Azure DB?
14.How do you handle datasets larger than 50 GB?
15.What happens when the SQL Azure database reaches Max Size?
16.How many databases can we create in a single server?
17.How many servers can we create in a single subscription?
18.How do you improve the performance of a SQL Azure Database?
19.What is code near application topology?
20.What were the latest updates to SQL Azure service?
21.When does a workload on SQL Azure get throttled?