Q. Compare SQL Server and Oracle based on specific criteria.
Answer: SQL Server primarily runs on Windows and Linux platforms, using T-SQL (Transact-SQL) language. It’s simpler in terms of usage with straightforward syntax. On the other hand, Oracle runs on a broader range of platforms, including Windows, Solaris, Linux, and Unix, and uses PL/SQL (Procedural Language/ SQL). It’s more complex but offers powerful functionalities with a more intricate syntax.
Q. How would you hide a SQL Server instance?
Answer: To conceal a SQL Server instance:
- Open SQL Server Configuration Manager.
- Expand ‘SQL Server Network Configuration.’
- Right-click on ‘Protocols for <server instance>’ and select ‘Properties.’
- Navigate to the ‘Flags’ tab and select ‘Yes’ for the ‘HideInstance’ option.
- Save the changes. Remember, the port number must be provided in the connection string for hidden named instances to establish a connection.
Q. Define the basic unit of an SQL Database and its size.
Answer: The fundamental unit of an SQL Database is a “Page,” typically sized at 8KB.
Q. How do you add a CPU to SQL Server?
Answer: CPUs can be physically, logically, or virtually added to SQL Server. SQL Server, starting from the 2008 version, supports CPU Hot Add. Upon adding the CPU, you’d execute the RECONFIGURE command so SQL Server acknowledges the new CPU.
Q. Describe how to check the connection status of a port number on a Server DBA.
Answer: You can use the TELNET command followed by the server name and port number. For instance, to check port 1433 on PAXT3DEVSQL24, the command would be: “TELNET PAXT3DEVSQL24 1433”.
Q. What are the contents of a page header, and how many types of pages are available?
Answer: A page header typically contains an index of data and information about free space. There are nine types of pages available in SQL Server.
Q. Can you explain the Extent of SQL Server?
Answer: Extents are units of space consisting of 8 contiguous pages, or 64KB, allocated for tables and indexes.
Q. What’s the standard method to create a new database in SQL?
Answer: The basic syntax to create a new database is: “CREATE DATABASE <databasename>.”
Q. Describe the steps or precautions to secure an SQL Server.
Answer: SQL Server can be secured by:
- Preferring NT authentication.
- Using roles to control data access.
- Attaching physical database files with NTFS permissions.
- Having a strong SA password and renaming the default Administrator account.
- Deactivating unnecessary accounts like the Guest account.
- Setting up encryption, SSL, and firewalls.
- Isolating SQL Server from public networks.
Q. How do you start SQL Server in different modes?
Answer: SQL Server can be started in different modes like Single-user Mode using the command: “sqlcmd –m –d master –S <ServerName> –c –U sa –P <Password>,” or in DAC using: “sqlcmd –A –d master –S <ServerName> –c –U sa –P <Password>”. Use the command: “ALTER DATABASE <DBName> SET EMERGENCY” for Emergency mode.
Q. Explain Replication in SQL Server.
Answer: Replication in SQL Server refers to copying and maintaining database objects and data from one database to another, facilitating data synchronization and redundancy across multiple SQL Server databases. It uses agents (jobs) to carry out the process. Data is often distributed to different locations and users over various networks, including local and wide area, dial-up, wireless, and the Internet.
Q. Can you detach a SQL Server 2008 database and then attach it to a SQL Server 2005?
Answer: No. Once a SQL Server 2008 database has been created or upgraded, it cannot be downgraded to SQL Server 2005 directly. If you need to move data from SQL Server 2008 to SQL Server 2005, you must utilize methods like Data Transformation Services (Import/Export), SSIS, BCP, or by running a query between linked servers.
Q. Can you explain the primary types of Replication and their purposes?
Answer: Yes, SQL Server offers various types of Replication, each catering to specific use cases:
- Snapshot Replication: It takes a “snapshot” of the entire database and replicates that to subscribers. Best for situations where the data changes infrequently.
- Transactional Replication: As transactions occur in the database (like inserts, updates, and deletes), they get pushed out to subscribers immediately or at defined intervals. Best for cases where data changes frequently.
- Merge Replication: It allows for two-way Replication. Meaning changes the subscriber makes can be synchronized to the publisher and vice versa. Valid for mobile or distributed server applications where data can be updated at the publisher and subscriber and then merged.
Q. Explain the difference between “Push” and “Pull” subscriptions in SQL Server replication.
Answer: In the context of SQL Server replication:
- Push Subscription: The publisher is accountable for updating changes to the subscriber without the subscriber requesting those changes. This is managed by the distribution agent or the merge agent running at the distributor.
- Pull Subscription: The subscriber starts the Replication instead of getting it from the publisher. It means the subscriber requests changes from the publisher. This is managed by the distribution agent or the merge agent running at the subscriber.
Q. Explain the role of the Distributor in SQL Server Replication.
Answer: The Distributor is a critical SQL Server replication framework component. It is a repository to store the replication logs and data until they are moved to subscribers. For snapshot replication, the distributor stores the snapshot. Transactional Replication holds the transactions for distribution to subscribers. Depending on the architecture and performance needs, the distributor can reside on the same server as the publisher or be set up on a separate server.
Q. What do you understand by “Publication” and “Subscription” in SQL Server Replication?
Answer:
- Publication: It’s a collection of database objects and data that you want to replicate. A publisher can have multiple publications.
- Subscription: It refers to the request by a database server to receive a copy of the publication. The subscriber then gets updates as data changes. There are two types of subscriptions: push (where the publisher sends updates) and pull (where the subscriber requests updates).
Q. Explain the role of the Log Reader Agent in Transactional Replication.
Answer: The Log Reader Agent is a crucial component of transactional Replication. It runs at the distributor and monitors the transaction log of the publisher database. It identifies the changes marked for Replication and moves them to the distribution database. From the distribution database, these changes are then sent to the subscribers.
Q. What challenges can you face with SQL Server Replication, and how would you address them?
Answer: Replication can present various challenges:
- Latency: The delay between when a change occurs at the publisher and when it’s reflected at the subscriber. To address this, monitor network traffic, ensure efficient transaction log reading, and consider increasing the frequency of the agents.
- Conflict Resolution: In merge replication, changes can occur between the publisher and subscriber. SQL Server provides conflict resolvers, but you can also implement custom ones.
- Security: Replication can expose more attack vectors. It’s essential to secure channels between publisher, distributor, and subscriber and ensure only necessary agents and processes have access.
- Performance: Replication can put a load on servers. Proper indexing, monitoring, and possibly dedicating a server for distribution can help.
Q. How would you monitor the health and performance of Replication?
Answer: SQL Server provides various tools and mechanisms:
- Replication Monitor: A GUI tool in SQL Server Management Studio that gives insights into the status, performance, and potential issues with Replication.
- System Stored Procedures: Procedures like sp_replcounters provide replication statistics, and sp_replmonitorhelppublication details a given publication’s status.
- Performance Monitor: Can be used to monitor specific replication counters to gauge the performance and health of replication components.
Q. How do you handle situations where Replication needs to be paused or stopped temporarily?
Answer: Depending on the situation, various steps can be taken:
- Stopping Agents: Agents like Log Reader, Distribution Agent, or Merge Agent can be stopped temporarily. This halts Replication but doesn’t remove any configurations.
- Deactivating Subscriptions: Subscriptions can be deactivated, stopping the flow of replicated data to those subscribers.
- Removing Replication: If Replication needs to be stopped permanently, the replication setup can be removed using the sp_removedbreplication stored procedure or SQL Server Management Studio.
Q. What is the difference between a Clustered and a Non-Clustered index in SQL Server?
Answer: In SQL Server, both clustered and non-clustered indexes play vital roles in query optimization, but they work differently:
- Clustered Index: It determines the physical order of data in a table. A table can have only one clustered index since it’s how the rows are physically stored.
- Non-Clustered Index: It’s similar to the index in a book. It holds a pointer to the data in a table where the corresponding rows can be found. A table can have multiple non-clustered indexes.
Q. How do you recover a database in the “Suspect” mode?
Answer: A database might become a ‘Suspect’ for various reasons, such as hardware failures, lack of disk space, or database corruption. Steps to recover include:
- Setting the database to Emergency mode.
- Checking database consistency using DBCC CHECKDB.
- If inconsistencies are found, try to repair the database using DBCC CHECKDB with appropriate repair options.
- Setting the database back to Online mode.
It’s recommended to have recent database backups to restore data if repair actions result in data loss.
Q. What is a Deadlock? How can you prevent or mitigate them in SQL Server?
Answer: A deadlock happens when two or more tasks permanently block each other by having a lock on a resource that the different functions try to lock. SQL Server can detect and resolve deadlocks by killing one of the tasks (victim).
To prevent or mitigate deadlocks:
- Minimize the duration of transactions to reduce lock contention.
- Access resources in a consistent order.
- Use appropriate isolation levels.
- Use the SQL Server Profiler to monitor and identify potential deadlock scenarios.
Q. Explain the difference between CHAR and VARCHAR data types.
Answer: The main differences between CHAR and VARCHAR are:
- CHAR: Fixed length. It reserves the number of characters defined by its size, irrespective of the actual data. The data is padded with spaces if it is shorter than the specified length.
- VARCHAR: Variable length. It reserves only the space for the data stored, plus two bytes for length information. It’s more space-efficient when storing data that varies in size.
Q. What are SQL Server Views, and how are they beneficial?
Answer: Views in SQL Server are virtual tables derived from one or more base tables. They can encapsulate complex queries, making them look simple to end-users. Benefits:
- Simplify complex queries.
- Restrict user access to data by showing only necessary columns.
- Abstract the underlying table structures.
Q. How can you optimize a stored procedure’s performance in SQL Server?
Answer: Optimizing a stored procedure involves several strategies:
- Ensure proper indexing on tables involved.
- Avoid using cursors; opt for set-based operations.
- Use the execution plan to identify bottlenecks.
- Reduce the use of temporary tables.
- Keep transactions short to reduce locks.
- Parameterize stored procedures to leverage cached execution plans.
Q. Explain the concept of Normalization in SQL Server.
Answer: Normalization is organizing data in a database to minimize redundancy and dependency by organizing data into separate tables based on their relationships. The main objective is data integrity and efficient storage. It’s achieved through various standard forms, each with specific rules.
Q. What is the SQL Server Profiler?
Answer: SQL Server Profiler is a graphical tool that lets system administrators monitor events in an instance of Microsoft SQL Server. You can save data about each event to a file or SQL Server table for later analysis. It helps understand the sequence of database events or tracking down problems.
Q. Describe the difference between INNER JOIN and LEFT JOIN in SQL.
Answer: In SQL, JOIN operations merge rows from two or more tables based on corresponding columns.
- The INNER JOIN statement is used to retrieve rows that have matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN)This query retrieves all rows from the left table and only the matching rows from the right table. If there’s no match, the result is NULL from the right table.
Q. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines the results of two or more SELECT statements into a single result set and removes duplicates.
- UNION ALL: Combines the results of two or more SELECT statements into a single result set but does not remove duplicates.
Q. How do you implement transaction control in SQL Server procedures?
Answer: Transaction control in SQL Server is managed using statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK. They ensure that a group of SQL operations are executed as a single unit – all succeed or none.
Q. What is the objective of the COALESCE function in SQL Server?
Answer: COALESCE returns the first non-NULL value from a list of expressions. It’s useful for handling NULL values by providing a default.
Example:
SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');
This would return 'third_value'.
Q. How do you handle errors in SQL Server stored procedures?
Answer: Error handling in SQL Server stored procedures is often done using the TRY…CATCH construct. Inside the TRY block, you place the code that might generate an error. If an error occurs, execution is passed to the corresponding CATCH block.
Q. Explain the difference between Temporary Tables and Table Variables.
Answer:
- Temporary Tables: They can be local (visible only in the session) or global (visible across multiple sessions). Temporary tables are distinguished by a single pound (#) or a double pound (##) prefix.
- Table Variables: Declared using the DECLARE statement, they act similarly to temporary tables but have some limitations, such as being unable to have non-clustered indexes or being used in INSERT EXEC statements.
Q. What is a CTE (Common Table Expression) in SQL Server?
Answer: A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It’s often used for recursive queries and can make queries more readable.
Example:
WITH CTE_Name AS (
SELECT column1, column2 FROM some_table
)
SELECT * FROM CTE_Name;
Q. How is a full-text search different from a regular search in SQL Server?
Answer: Full-text search allows for complex word searches, including inflectional word forms and proximity searches, making it more flexible and powerful than regular pattern matching with LIKE.
Q. What is the difference between a Trigger and a Stored Procedure in SQL Server?
Answer:
- Trigger: A set of actions automatically executed (or fired) in response to a specific event on a table or view. Triggers are used to implement business rules or maintain data integrity.
- Stored Procedure: It’s a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Applications, triggers, or other stored procedures can invoke them.
Q. How can you retrieve the image stored in SQL Server?
Answer: Images in SQL Server are often stored in columns with data types like VARBINARY(MAX) or IMAGE. To retrieve them, you’d perform a regular SELECT query on the column, then process the binary data in your application to display or use the image.
Q. What is the difference between a Function and a Stored Procedure in SQL Server?
Answer:
- Function: It must return a value and cannot modify the data received as parameters. It can be used within a SELECT statement.
- Stored Procedure: It can return zero or more values. It can modify data, use dynamic SQL, and cannot be used in a SELECT statement-like function.
Trending SQL DBA Tutorials
Microsoft Power BI Certification Training Course
Trainer: Microsoft Partner
Subject matter experts curate a Power BI certification course hands-on experience in real-time projects to help you clear the official Microsoft Power BI Data Analyst Exam: PL-300.
AWS Solutions Architect Certification Training Course
Trainer: Microsoft Partner
AWS Training will help you train for the AWS Certified Solutions Architect – Associate exam SAA-C03. With Edureka’s live instructor-led sessions, you can effectively architect, monitor, and deploy secure and robust AWS applications.
DevOps Certification Training Course
Trainer: Microsoft Partner
This course certifies you as a practitioner in Continuous Development, Configuration Management, Continuous Integration, and Continuous Monitoring. You’ll learn to apply these practices for optimal results in software development.