SQL Server Questions

Hi,

Today we will take up some general questions.

Question. What are the SQL Server system databases and can you outline the general functionality of each database?

Ans:
Master - Database responsible for SQL Server instance related data. 
Resource - Database responsible for SQL Server system objects. This database was introduced in SQL Server 2005 and is intended to ease the upgrade and rollback of SQL Server system objects.
Model - Template database for the creation of new user defined databases.
MSDB - Database responsible for SQL Server Agent related data such as Jobs, Alerts, Operators, Backup, restore, database diagrams etc.
TempDB - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Distribution - Database responsible for managing replicated data. This database could reside on the publisher or subscriber.

Question. Is it a good idea to create objects in the system databases?

Ans:
Logically objects should not be created in the system databases. It is a best practice to create a separate database for user defined objects that would be used instance wide or create the objects in each of the needed databases. From a DBA perspective, it is a common practice to create a dedicated DBA database in order to perform custom administrative tasks.
If objects are needed for future user defined databases those can be created in the Model database.

Question. Does Reporting Services create system databases during the installation process?

Ans.
Yes. It does create following databases:

• ReportServer - Stores the reports related data.
• ReportServerTempDB - Temporary database storage for Reporting Services.

Question. What are the typical objects that are created in the TempDB database?

Ans.
following objects use TempDB database:

• Temporary tables (#temptable or ##temptale)
• Table variables
• Cursors
• Work tables
• Row versioning
• Create or rebuild indexes sorted in TempDB

Question. Does size of the TempDB database really make a difference and how should TempDB be sized?

Ans.
In situations where temporary objects are created in T-SQL code (i.e. temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.) the TempDB database makes a significant difference in overall performance. In these situations the database should be sized appropriately and moved to a separate disk drive in order to support the I/O requests. If not, the default location and size may be appriopriate.

Question. Explain relationship between logins and users in the system and user databases.

Ans.
Logins - All logins reside in the master database
Users - All users reside in the master database, other system databases and in the user defined databases.

Question. What's the differences in restoring the Master database versus a user defined database?

Ans.
In order to restore the Master database, SQL Server instance must be in single user mode. After the Master database is restored, the SQL Server instance restarts.

When a user defined database is restored, it has no impact on other databases.

Question. What's Resource database?

Ans.
The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. The Resource database was introduced in SQL Server 2005.

Question. What are some of the tools that you use to troubleshoot any SQL Server issue?

Ans.

• SQL Server Error Log
• SQL Server Agent Error Log
• Windows Application Event Log
• SQL Server Integration Services Logs
• Custom Application\Process Logs

Question.How can you help to prevent a SQL Injection attack?

Ans.

• Review firewall and web server logs for potential threats.
• Prevent particular strings from passing through the firewall.
• Prevent IP traffic from particular web sites.
• Implement technology to scan your public facing web sites for potential issues.

Question. What is an identity? What is the value? How can you capture the last identity value per column?

Ans.
An identity is a property of a column where we specify seed and increment values. The seed is the starting value for the column and increment is the value by which the identity grows. In most cases seed and increment values are 1 wherein the initial value is 1 and the identity grows by 1.

The value of the identity column is managed by relational engine. SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
@@IDENTITY
SCOPE_IDENTITY()
IDENT_CURRENT(‘tablename’)

Question. What are the different indexing options available and what columns do we typically index? What is the value of indexing columns?

Ans.

SQL Server offers two types of indexes, clustered and non-clustered. A clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap.

The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. In the query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
Indexes are typically created on these columns:

• Primary keys
• Foreign keys
• Columns in WHERE, GROUP BY, ORDER BY, etc.
• Single value, range of values, etc.

Question.How you will tuning a SQL Server query? How to identify the query causing the issue?

Ans.
Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical Query Plan or sys.dm_exec_query_stats.

Review the individual query components to determine which components of the query have the highest cost.
Outline options to improve the query such as moving from cursor based logic to set based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY clause, adding indexes, removing indexes, creating covering indexes, updating statistics, removing use of functions in where clause etc.
Test the options to determine the associated performance improvement.

Question. What are the options in SQL Server to rebuild indexes?

Ans.

• CREATE INDEX with DROP_EXISTING
• DROP INDEX and CREATE INDEX
• ALTER INDEX

Question.What happens when we add a column in the middle of a table (Employee) in Management Studio?

Ans.
Management Studio creates a temporary table called dbo.Tmp_Employee with the new structure.
If there is data in the original table dbo.Employee this data is inserted into the new temp table dbo.Tmp_Employee (now you have two sets of the same data).
The original table dbo.Employee is dropped.
The new table dbo.Tmp_Employee is renamed to dbo.Employee.
If the table has indexes all of the indexes are recreated.

Question.How do you backup Analysis Services databases?

Ans.
Create XML statement to backup the Analysis Services databases, then create a SQL Server Agent Job to perform the task on a daily basis.

Question.What is a common use case for the OPENROWSET function?

Ans.
Import from Excel to SQL Server or vice-versa.

Question.What is the difference between a deterministic and non deterministic function?

Ans.
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.
Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

Question.What is the Guest user account? What login is it mapped to? Can we drop it?

Ans.
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB, although sufficient testing should be conducted to validate applications will not break with this security restriction.

Question.What do synonyms do and when could you make the case for using them?

Ans.
Synonyms were released with SQL Server 2005. Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary. It is an alias, means to simplify migrations and application testing without the need to make any dependent coding changes.
Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code. This could be useful during a re-architecture or upgrade project.

Question.With SQL Server 2005, what is the technique to manage errors in Integration Services?

Ans.
Event Handlers can be setup to capture errors and perform operation such as writing the errors to a table. Additionally, custom logging can be setup at the Control Flow level and perform custom logging as well.

Question.Explain the difference between the INTERSECT and EXCEPT operators? What is the value of these commands over other techniques?

Ans.
INTERSECT - gives final result set where values in both of the tables match.
EXCEPT - gives final result set where data exists in the first dataset and not in the second dataset.

The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.

Question.Explain service oriented architecture (SOA) technology introduced with SQL Server 2005 and what are some of the components of the technology?

Ans.
Service Broker is the service oriented architecture (SOA) technology introduced with SQL Server 2005. It includes:
Endpoint - Communication point in the database for Service Broker. In addition, ability to specify the authentication, encryption and message forwarding. A single Service Broker endpoint can be created for each database, however the HTTP and Database Mirroring can be created as well.
Message Type - Format for the message that is being sent and received. The format could be simply well formed XML or be bound to a schema.
Contract - Which message types are sent by either the initiator or the target.
Route - For the specific Service Broker Service which SQL Server instance and database the messages will be routed to during the Conversation.
Queue - This is the logical container in the Service Broker application. The queue serve as a storage mechanism in Service Broker. All data is RECEIVED from the queue for processing. Although the queues are all named differently, they are actually all of the same format with both relational and binary columns that can be converted to an XML format.
Service - Service correlates the queue and the contract.
Remote Binding Service - Defines the Service Broker user to with the Service.

Question.What is the OUTPUT clause and what sorts of options does it provide?

Ans.
The OUTPUT clause is introduced in SQL Server 2005 and has the ability to access the INSERTED and DELETED tables as is the case with a trigger. The OUTPUT command can be added to your stored procedures or T-SQL scripts in order to write the data out to an auditing table or return the data back to the front end client.
The OUTPUT clause has the ability to mimic and/or replace some of the functionality typically addressed by triggers. In SQL Server 2008, it can also be used with MERGE command to access INSERTED, UPDATED or DELETED values

Question. What is database mirroring?

Ans.
Database mirroring was introduced in SQL Server 2005. Database Mirroring consists of two mandatory (Principal and Mirror) roles and an optional third (Witness) role. These roles would be installed on the Enterprise and Standard editions of SQL Server 2005. The Witness role can be installed on an any version of SQL Server including Express Edition.

In terms of data protection, Database Mirroring has three different options. First is High Availability Operating Mode. High Availability Operating Mode provides durable, synchronous transfer of data between the principal and mirror instances including automatic failure detection and failover. Second is High Performance Operating Mode. With the High Performance Operating Mode the overall architecture acts as a warm standby and does not support automatic failure detection or failover. Third is High Protection Operating Mode. The High Protection Operating Mode operates very similar to the High Availability Mode except the failover and promotion (mirror to principal) process is manual.

Question. How does the database recovery model impact database backups?

Ans.
The database recovery model is responsible for the retention of the transaction log entries. So based on the setting it determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.
Simple - Committed transactions are removed from the log when the check point process occurs.
Bulk Logged - Committed transactions are only removed when the transaction log backup process occurs.
Full - Committed transactions are only removed when the transaction log backup process occurs.

Question.What are some common post restore processes?

Ans.

• Sync the logins and users
• Validate the data is accurate
• Notify the team\user community
• Cleanse the data to remove sensitive data i.e. SSN's, credit card information, customer names, personal information, etc.
• Change database properties i.e. recovery model, read-only, etc.

Question.For differential backups, how is the data determined for those backups?

Ans.
As data is changed in the extent, the extent is marked as changed and the entire extent is backed up.

Question.How is a point in time recovery performed?

Ans.
It depends on which backup types are issued. In this example let's assume its full, differential and transaction log backups are issued.

• Restore the most recent full backup with the NORECOVERY clause
• Restore the most recent differential backup with the NORECOVERY clause
• Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
• Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied.