SQL Server interview question Part 6

1. Ques: What is DBCC commands in SQL server ? How many types of DBCC commands ?
Answer: 
DBCC commands used for Maintenance of database, index, or filegroup. It also used to perform validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

Commonly use for database maintenance?

• DBCC CHECKDB, 
• DBCC CHECKTABLE, 
• DBCC CHECKCATALOG, 
• DBCC CHECKALLOC, 
• DBCC SHOWCONTIG, 
• DBCC SHRINKDATABASE, 
• DBCC SHRINKFILE 

Some list of  DBCC commands given bellow

DBCC CHECKDB

Check the database. This is the default.

DBCC CHECKCATALOG

It is used to perform consistency check for a table/catalog for a particular database.

DBCC CHECKCONSTRAINTS

It is used to check integrity of a particular constraint or all constraints on a particular table for a database.

DBCC CHECKALLOC

It is used to check page usage and allocation of a database.

DBCC CHECKTABLE(tablename)

It is used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

DBCC SQLPERF(logspace)

Displays T-log size and space used % for all databases.

DBCC SHOWFILESTATS

Display Extent information for a database.

DBCC CHECKIDENT(tablename)

It is used to check identity information or to return current identity value for a particular table.

DBCC TRACEOFF

It is used to disable tracing

DBCC TRACEON

It is used to enable tracing.

DBCC TRACESTATUS

It displays the trace status.

DBCC USEROPTIONS

It displays the active SET options for a particular database


2. Ques: What are the difference between “Where” and “Having” clause in Sql Server?
Answer: 

• Where is used to filter records before any groupings take place.
• Having is used to filter values after they have  been groups.  

• Where clause can be used with - Select, Insert, and Update statements, 
• Having clause can only be used with the Select statement.

• Where filters rows before aggregation (GROUPING) 
• Having filters groups, after the aggregations are performed.

3. Ques: What is difference between “@@ROWCOUNT” and “@@ERROR” in Sql Server?
Answer: 

• @@ROWCOUNT: It is used to return the number of rows affected in the table due to last statement. 
• @@ERROR –It is used to return the error code which is occurred due to last SQL statement. ‘0’ means there are no errors.

4. Ques:  What is difference between Union and UnionAll in Sql Server?
Answer: 

• Union eliminates duplicate rows from the result set.It always returns distinct rows.
• UnionAll will not eliminate duplicate rows, instead it just pulls all rows from all tables.

• Union cannot work with a column that has a TEXT data type.
• UnionALL can work with all data type columns.

5. Ques: What is differences between ISNULL() and COALESCE() in Sql Server?

Answer: 

ISNULL(): It is used to replace the given value in case of NULL value. 

COALESCE():This function will return the first non-null expression given in the list of expressions.


6. Ques:  What is Log shipping?
Answer:
It is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.

If 1 server fails, the other 1 will have the same db and can be used as the Disaster Recovery plan.
The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set.


7. Ques:  What are the step to Create an Effective Index?
Answer:

• Keep clustered index keys as small as possible.
• Consider range data for clustered indexes.
• Create an index on all foreign keys.
• Create highly selective indexes.
• Consider a covering index for often-used, high-impact queries.
• Use multiple narrow indexes rather than a few wide indexes.
• Create composite indexes with the most restrictive column first.
• Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
• Remove unused indexes.
• Use the Index Tuning Wizard.

8. Ques: What are different Types of SQL Server Backups?

Answer:

Different Types of SQL Server Backups which you can perform are:-

Full Backups:

full database backup is taken then all data files, file groups, and transaction logs are backed up.

ex:

BACKUP DATABASE EmployeeDB

TO DISK = ‘C:\Backup\EmployeeDBfull.bak’

GO

Differential Backups:

Differential backup is related to the last full backup and contains all changes that have been made since the last full backup.

Ex:

BACKUP DATABASE EmployeeDB TO DISK = 'diff.bak' WITH DIFFERENTIAL

Transactional Log Backup:

A transaction log backup contains all log records that have not been included in the last transaction log backup. 

Ex:

BACKUP LOG EmployeeDBTO DISK = 'log.bak'

Copy-Only Backups

Use Copy-Only option if you need to make an additional full or transaction log backups which will occur beyond the regular sequence of SQL Server backups.

Ex:

BACKUP DATABASE EmployeeDB TO DISK = 'EmployeeDBfull.bak' WITH COPY_ONLY

File & FileGroup Backups

These backup types allow you to backup one or more database files or filegroups. 

Ex:

BACKUP DATABASE EmployeeDB 

FILE = 'File' 

TO DISK = 'File.bck' 

Partial Backups

Partial backups are used in simple recovery model to make backups of very large databases that have one or mode read-only filegroups. 

Ex

BACKUP DATABASE EmployeeDB READ_WRITE_FILEGROUPS 

TO DISK = 'EmployeeDB_backup.bak'