Q. When you upgrade a SQL Server, the upgrade wizard stops responding and fails. Why?
Answer: During the conversion process, if any applications or services have ODBC connections to SQL Server 2005, it may prevent SQL Server from shutting down and proceeding to the next step.
Q. What is the terminology used for Replication?
Answer:
- Publisher server
- Distributor (Optional)
- Subscriber server
Q. How to roll back the upgrade?
Answer: If the legacy SQL Server instance is substituted by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complicated and time-consuming. If a rollback becomes necessary, the legacy instance will still be available during a side-by-side upgrade.
Q. What is log shipping?
Answer: Transaction Log backup jobs copy the source data to the destination. It is a database-level technology.
Q. What is SSIS?
Answer: SQL Server Integration Services (SSIS).
Q. What is the usage of SSIS?
Answer: It is used to carry out ETL processes, which stands for Extraction, Transform, and Load of data.
Q. How to speed up the DBCC CHECKDB execution process?
Answer: The command DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK enforces an exclusive lock on the database, which makes the process faster.
Q. What is the usage of the Analysis service?
Answer: Analysis services analyze vast amounts of data and apply insights to business decisions.
Q. What is the terminology used in log shipping?
Answer:
- Primary server
- Secondary server
- Monitor server (Optional)
Q. What is mirroring?
Answer: Mirroring involves copying the primary data to a secondary location via the network. It is a database-level technology.
Q. What is PHYSICAL_ONLY in DBCC CHECKDB?
Answer: The PHYSICAL_ONLY command restricts checks to the integrity of the physical layout of a page and record headers. It can detect torn pages, checksum failures, and common hardware failures. This option may reduce the run-time for DBCC CHECKDB on extensive databases and is recommended for frequent use on production systems. Specifying the PHYSICAL_ONLY option makes DBCC CHECKDB skip all checks of the FILESTREAM data—for example, DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, PHYSICAL_ONLY.
Q. What is the terminology used for mirroring?
Answer:
- Principal server
- Mirror server
- Witness server (Optional)
Q. How to check data purity using DBCC CHECKDB?
Answer: The command DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, DATA_PURITY checks the database for column values that aren’t valid or out of range. With this command, DBCC CHECKDB sees columns with date and time values either larger or smaller than the acceptable range for the DATETIME data type. It also restricts checking the integrity of the physical structure of the page and record.
Q. How can all records, including data types, be cleared in a table?
Answer: Use the command Truncate table Tablename.
Q. What is clustering?
Answer: Clustering involves storing data in a shared location accessed by primary and secondary servers based on server availability. It operates at the instance level.
Q. How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels during a read operation, supposing row-level locking?
Answer: Locks are held for the entire transaction in REPEATABLE_READ and SERIALIZABLE isolation levels, while they are more transient in READ_COMMITTED isolation levels.
Q. What is the terminology used for clustering?
Answer:
- Active node
- Passive node
Q. Which two isolation levels support optimistic/row-version-based concurrency control?
Answer: One is the READ_COMMITTED isolation level, which supports both pessimistic (locking-based) and optimistic (version-based) concurrency control models. The other is the SNAPSHOT isolation level, which supports only an optimistic concurrency control model.
Q. What is Always ON Availability Groups?
Answer: Always ON Availability Groups involve copying primary data to secondary locations via the network. It’s a group of database-level technologies.
Q. What database options must be set to allow an optimistic concurrency control model?
Answer: The READ_COMMITTED_SNAPSHOT option is required for the READ_COMMITTED optimistic model, and the ALLOW_SNAPSHOT_ISOLATION option is needed for the SNAPSHOT isolation level.
Q. What is the terminology used for Always ON Availability Groups?
Answer:
- Primary (Source server)
- Secondary (Destination server)
Q. Explain the purpose of Intent locks.
Answer: The database engine utilizes intent locks to protect shared (S) and exclusive (X) locks on a lower resource in the lock hierarchy. They’re termed “intent” locks because they’re acquired before a lock at the lower level, signaling the intention to place locks at that lower level. Their primary functions are to stop different transactions from modifying a higher-level resource in a way that would conflict with a lock at the lower level and to enhance the efficiency of the database engine in catching lock conflicts at a more granular level.
Q. What is the usage of reporting services?
Answer: Reporting services are used to create and publish various reports.
Q. What is the command to see the execution plan by a user?
Answer: The command is SHOWPLAN.
Q. What is the syntax to get the top 60 records in a table?
Answer: The syntax is selecting the top 60 * from the table.
Q. How to read the graphical execution plan?
Answer: The graphical execution plan should be read from right to left. Some aspects to consider include:
- Table Scan: Indicates a missing index.
- Index Scan: Implies proper indexes aren’t being used.
- Bookmark Lookup: Look into limiting the number of columns in the select list.
- Filter: Consider removing functions from the WHERE clause and adding more indexes.
- Sort: Ascertain if the data needs sorting or if an index can avoid sorting. Consider if sorting can be done on the client side more efficiently.
- DataFlow Arrow (high density): Sometimes, a query might produce a few output rows, but the arrow density suggests that many rows were processed.
- Cost: Helps in pinpointing which table or operation is time-consuming.
Q. What is the syntax to insert data into a table?
Answer: The syntax is:
Insert into table(Column1, column2, etc) Values(‘value1’, ‘value2’, etc)
Q. What are the environments used for reports?
Answer: The environments used for reports are BIDS (Business Intelligence Development Studio) and SSDT (SQL Server Data Tools).
Q. What is an execution plan?
Answer: An execution plan details how the SQL Server query optimizer would process a given SQL statement. It provides insights into the steps, operations, statistics, and processor tree involved. Essentially, it shows how SQL Server would execute a query.
Q. How do you delete records from a table from the current day and have a timestamp column?
Answer: The command is:
Delete from table where datediff(day, timestamp, getdate()) = 0
Q. What are the types of query estimation plans out there?
Answer: There are two main types of query estimation plans:
- Estimated
- Actual
Q. How many formats of execution plans are available?
Answer: There are three formats of execution plans:
- Graphical
- Text
- XML
Q. What permissions are required to view the execution plan?
Answer: A user must be mapped to one of the roles: sysadmin, db_owner, or db_creator. Alternatively, the user can be granted specific permission using:
GRANT SHOWPLAN TO [username]
Q. How do indexes improve query performance in SQL Server?
Answer: Indexes enhance query performance by allowing the database engine to find the location of data without scanning every row in a table. They can be thought of as a “table of contents” that the database can reference rather than reading the entire “book” (table). There are primarily two types of indexes: clustered and non-clustered.
Q. What is the distinction between a Clustered and Non-Clustered Index?
Answer: A Clustered Index determines a table’s physical order of data. There can only be one Clustered Index per table. A Non-Clustered Index is a separate structure from the table that holds a pointer to the data rows and sorts it differently from the physical data. A table can have multiple non-clustered indexes.
Q. What is the impact of frequent updates on an indexed column?
Answer: Frequent updates on an indexed column can lead to index fragmentation. This fragmentation can degrade query performance and require periodic index maintenance (like reorganization or rebuilding).
Q. Explain the concept of a Covering Index.
Answer: A Covering Index is a non-clustered index that includes all columns referenced by a query. Since all the necessary data can be found within the index itself, it eliminates the need for the database engine to perform additional lookups in the base table.
Q. What is a Stored Procedure?
Answer: A Stored Procedure is a precompiled set of one or more SQL statements stored under a name and can be executed as a unit, providing a mechanism to modularize and encapsulate a series of operations.
Q. What’s the distinction between a Function and a Stored Procedure?
Answer: The main differences are:
- A function can return a value, but a stored procedure cannot.
- Functions can be utilized in the SELECT statement, but stored procedures cannot.
- Stored Procedures can have output parameters, while functions cannot.
- Transactions can be utilized in stored procedures but not in functions.
Q. What is a Trigger?
Answer: A Trigger is a stored procedure that gets executed automatically in response to specific events in the database, like data modifications (INSERT, UPDATE, DELETE).
Q. What’s the difference between INNER JOIN and LEFT JOIN?
Answer: An INNER JOIN returns rows from both tables that satisfy the given condition. In contrast, a LEFT JOIN (or LEFT OUTER JOIN) yields all rows from the left table and the matching rows from the right table. If no matches exist, the result is NULL on the right side.
Q. What are SQL Server Constraints?
Answer: Constraints are rules enforced on columns in a table to preserve data integrity. Typical constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT.
Q. Describe the difference between UNION and UNION ALL.
Answer: UNION and UNION ALL combine the result of two or more SELECT statements. However, UNION removes duplicate rows from the development, while UNION ALL includes duplicates. UNION ALL is generally faster as it doesn’t need to check for duplicates.
Q. What is Normalization?
Answer: Normalization organizes data in a database to reduce redundancy and improve data integrity. It typically involves dividing larger tables into smaller, related tables and linking them using relationships.
Q. Can you explain the different Normal Forms?
Answer: There are several Normal Forms, including:
- 1NF: Each column contains atomic (indivisible) values with no repeating groups or arrays.
- 2NF: Everything in 1NF and all non-key attributes are fully functional and dependent on the primary key.
- 3NF: Everything in 2NF and all attributes are functionally dependent only on the primary key.
- BCNF (Boyce-Codd Normal Form): Everything in 3NF and for every non-trivial functional dependency X -> Y, X is a super key.
- 4NF: Everything in BCNF and no multi-valued dependencies.
- 5NF (or PJNF – Project-Join Normal Form): The superkeys imply every non-trivial join dependency in the table.
Q. What are SQL Server Views?
Answer: A view in SQL Server is a virtual table based on the result set of an SQL statement. It contains rows and columns similar to a real table. The fields in a view are fields from one or more real tables. Views do not store data themselves but retrieve it from the underlying tables.
Q. What is SQL Server Profiler?
Answer: SQL Server Profiler is a tool that captures SQL Server events from a server. It monitors, analyzes, and troubleshoots database performance and activities.
Q. How is ACID related to SQL Server transactions?
Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. It’s a set of properties ensuring reliable processing of transactions. In SQL Server, these principles ensure that all transactions are completed successfully, or no transactions are processed at all, maintain database consistency, provide isolation between transactions, and ensure the durability and reliability of transactions even in the event of failures.
Q. What is Deadlock in SQL Server?
Answer: A deadlock happens when two or more tasks permanently block each other by holding a lock on a resource the other tasks try to lock. SQL Server detects these deadlocks and ends one user process to alleviate the situation.
Q. Describe a SQL Server Cursor.
Answer: A cursor in SQL Server is a database object that allows traversal over the rows of a result set. It behaves much like a record set and provides row-by-row processing. However, they can be resource-intensive and may slow down the database performance, so they should be used judiciously.
Q. What is SQL Server Index Fragmentation?
Answer: Index fragmentation happens when the logical order of index pages does not match the physical order in the data file. This can degrade performance. There are two types of fragmentation: internal (free space within pages) and external (disordered pages). Regular maintenance, like reorganizing or rebuilding indexes, can help manage fragmentation.
Q. What is a SQL Server Schema?
Answer: In SQL Server, a schema is a designated container for database objects such as tables, views, stored procedures, and more. It provides a logical grouping, can simplify object naming, and offers a layer of security.
Q. How is DELETE different from TRUNCATE?
Answer: While both commands remove rows from a table, there are distinct differences:
- DELETE is a logged operation, logging each row deletion, which can be slower. It can also have a WHERE clause to specify which rows to delete.
- TRUNCATE is minimally logged, usually faster, and removes all rows from the table. It cannot delete specific rows based on a condition.
Q. Explain Parameterized Queries.
Answer: Parameterized queries allow placeholders instead of directly writing the values into SQL statements. They help prevent SQL injection attacks, as the parameters are treated as literal values and not executable code.
Q. What is the distinction between a temporary table and a table variable in SQL Server?
Answer:
- Temporary Tables: Are defined using the #TableName syntax and are stored in tempdb. They support indexes and statistics and behave like regular tables but are temporary.
- Table Variables: Are declared using the DECLARE statement and are used like regular variables. They are also stored in tempdb but have limitations compared to temporary tables, like no statistics creation and no explicit transaction boundary.
Q. Explain the HAVING clause in SQL Server.
Answer: The HAVING clause filters the results of the GROUP BY clause in SQL statements. While the WHERE clause filters rows before they are grouped, HAVING filters them after.
Q. Describe SQL Server Authentication modes.
Answer: SQL Server provides two authentication modes:
- Windows Authentication: Uses the AD (Active Directory) user account or groups to authenticate against SQL Server. It’s more secure than SQL Server authentication.
- SQL Server Authentication: Utilizes a username and password stored in SQL Server.
Q. What is SQL Server Agent?
Answer: SQL Server Agent is a component of SQL Server used to automate and schedule tasks like backups, database maintenance, ETL processes, and more. It uses jobs defined by the user to carry out these tasks at scheduled times.
Q. Describe SQL Server Partitioning.
Answer: Partitioning in SQL Server allows tables, indexes, or indexed views to be subdivided into smaller pieces, allowing operations to be performed on a subset of the data. This can optimize large tables by enabling more efficient data access and maintenance operations.
Q. What are Common Language Runtime (CLR) functions in SQL Server?
Answer: CLR integration in SQL Server allows for the creation and execution of database objects (like functions and triggers) using .NET languages. These CLR functions can achieve tasks that might be cumbersome or inefficient using traditional T-SQL.
Q. Explain the concept of Data Warehousing.
Answer: A Data Warehouse is an extensive, centralized database for analytical processing. Unlike operational databases that focus on CRUD operations, data warehouses support business intelligence activities, like querying, reporting, and data mining, by consolidating data from various sources and organizing it optimally for querying.
Q. How is a JOIN different from a SUBQUERY?
Answer: A JOIN allows retrieving columns from multiple tables in a single SELECT statement based on common column values. A SUBQUERY, on the other hand, involves executing one query inside another, often retrieving a single value or set of values to be used by the outer query. Both can combine data, but they operate in fundamentally different ways.