SQL Server 2005 Interview Questions & Answers

Question 1. How Do You Troubleshoot Errors In A Sql Server Agent Job?

Answer :

Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

Question 2. What Is The Default Port No On Which Sql Server Listens?

Answer :

1433.

Question 3. How Many Files Can A Database Contain In Sql Server?how Many Types Of Data Files Exists In Sql Server? How Many Of Those Files Can Exist For A Single Database?

Answer :

•  A Database can contain a maximum of 32,767 files.
•  There are Primarily 2 types of data files Primary data file and Secondary data file(s). There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

Question 4. What Is Dcl?

Answer :

DCL stands for Data Control Language.

Question 5. What Is Fill Factor?

Answer :

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

Question 6. What Are The Commands Used In Dcl?

Answer :

GRANT, DENY and REVOKE.

Question 7. What Are System Databases Into Sql Server (2005/2008)?

Answer :

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

Question 8. What Stored By The Tempdb ?

Answer :

Row versions, cursor, temp objects.

Question 9. What Stored By The Model?

Answer :

Templates of new database objects, like tables and column.

Question 10. What Stored By The Master?

Answer :

Server's configurations and logins.

Question 11. What Stored By The Msdb?

Answer :

Scheduled jobs, Backup/Restore and DTA information.

Question 12. Can We Perform Backup Restore Operation On Tempdb?

Answer :

NO.

Question 13. Where The Sql Logs Gets Stored?

Answer :

It's stored into root folder SQL server, LOG folder.

Question 14. What Is A Linked Server?

Answer :

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 databases using T-SQL Statements.

Question 15. What Are The Dmvs?

Answer :

Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Question 16. What Is The Syntax To Execute The Sys.dm_db_missing_index_details?

Answer :

Select * from sys.dm_db_missing_index_details.

Question 17. What Is The New In Sql Server 2008?

Answer :

File stream data type, Activity Monitor, Resource governor, data compression, compressed backup.

Question 18. What Does The On Update No Action Do?

Answer :

ON UPDATE NO ACTION:

• Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back.
• Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.

Question 19. What Does The On Delete Cascade Option Do?

Answer :

ON DELETE CASCADE:

• Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.
• Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

Question 20. What Is A Referential Integrity Constraint? What Two Keys Does The Referential Integrity Constraint Usually Include?

Answer :

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

Question 21. What Is A Concatenated Primary Key?

Answer :

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table.

Question 22. How Are The Unique And Primary Key Constraints Different?

Answer :

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

•  The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.
• Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

Question 23. When A Primary Key Constraint Is Included In A Table, What Other Constraints Does This Imply?

Answer :

Unique + NOT NULL.

Question 24. What Command Must You Use To Include The Not Null Constraint After A Table Has Already Been Created?

Answer :

DEFAULT, WITH CHECK or WITH NOCHECK.

Question 25. What Does The Not Null Constraint Do?

Answer :

Constrain will not allow NULL values in the column.

Question 26. What Command Would You Use To Create An Index?

Answer :

CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME).

Question 27. Does An Index Slow Down Updates On Indexed Columns?

Answer :

Yes.

Question 28. What Command Would You Use To Add A Column To A Table In Sql Server?

Answer :

ALTER TABLE tablename ADD column_name DATATYPE.

Question 29. When Columns Are Added To Existing Tables, What Do They Initially Contain?

Answer :

The column initially contains the NULL values.

Question 30. If You Are Going To Have Too Many Nulls In A Column, What Would Be The Best Data Type To Use?

Answer :

Variable length columns only use a very small amount of space to store a NULL so VARCHAR datatype is the good option for null values.

Question 31. Does Server Sql Treat Char As A Variable-length Or Fixed-length Column?

Answer :

SQL Server treats CHAR as fixed length column.

Question 32. What Is The Default Value Of An Integer Data Type In Sql Server 2005?

Answer :

NULL.

Question 33. Will Sql Server 2005 Allow You To Reduce The Size Of A Column?

Answer :

Yes it allows.

Question 34. Can You Change The Data Type Of A Column In A Table After The Table Has Been Created? If So, Which Command Would You Use?

Answer :

Yes we can. Alter Table Modify Column.

Question 35. What Does The Update Command Do?

Answer :

Update command will modify the existing record.

Question 36. When Would You Use An Insert Into .. Select Option Versus An Insert Into .. Values Option? Give An Example Of Each?

Answer :

INSERT INTO .. SELECT is used insert data in to table from different tables or condition based insert.
INSERT INTO .. VALUES you have to specify the insert values.

Question 37. While You Are Inserting Values Into A Table With The Insert Into .. Values Option, Does The Order Of The Columns In The Insert Statement Have To Be The Same As The Order Of The Columns In The Table?

Answer :

Not Necessary.

Question 38. Can You Type More Than One Query In The Query Editor Screen At The Same Time?

Answer :

Yes we can.

Question 39. Can A Synonym Name Of A Table Be Used Instead Of A Table Name In A Select Statement?

Answer :

Yes.

Question 40. What Is A Synonym? Why Would You Want To Create A Synonym?

Answer :

SYNONYM is a single-part name that can replace a two, three or four-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:

• Table.
•  View.
• Assembly (CLR) Stored Procedure.
• Assembly (CLR) Table-valued Function.
• Assembly (CLR) Scalar Function.
• Assembly Aggregate (CLR) Aggregate Functions.
• Replication-filter-procedure.
• Extended Stored Procedure.
• SQL Scalar Function.
• SQL Table-valued Function.
• SQL Inline-table-valued Function.
• SQL Stored Procedure.

Question 41. Do Comments Need To Go In A Special Place In Sql Server 2005?

Answer :

No its not necessary.

Question 42. Are Semicolons Required At The End Of Sql Statements In Sql Server 2005?

Answer :

No it is not required.

Question 43. What Are Table Aliases?

Answer :

Aliases can make it easier to work with table names. Using aliases is helpful when:

•  You want to make the statement in the SQL Pane shorter and easier to read.
• You refer to the table name often in your query — such as in qualifying column names — and want to be sure you stay within a specific character-length limit for your query. (Some databases impose a maximum length for queries).
•  You are working with multiple instances of the same table (such as in a self-join) and need a way to refer to one instance or the other.

Question 44. Will Count(column) Include Columns With Null Values In Its Count?

Answer :

Yes, it will include the null column in count.

Question 45. Is A Null Value Equal To Anything? Can A Space In A Column Be Considered A Null Value? Why Or Why Not?

Answer :

No NULL value means nothing. We can’t consider space as NULL value.

Question 46. What Are Four Major Operators That Can Be Used To Combine Conditions On A Where Clause?

Answer :

OR, AND, IN and BETWEEN.

Question 47. What Is The Default Order Of An Order By Clause?

Answer :

Ascending Order.

Question 48. Does Order By Actually Change The Order Of The Data In The Tables Or Does It Just Change The Output?

Answer :

Order By clause change only the output of the data.

Question 49. What Is The Order By Used For?

Answer :

Order By clause is used for sorting records in Ascending or Descending order.

Question 50. Does A Sql Server 2005 Select Statement Require A From?

Answer :

NO.

Question 51. What Is Usually The First Word In A Sql Query?

Answer :

SELECT.

Question 52. What Is The Parse Query Button Used For?

Answer :

Parse query button is used to check the SQL Query Syntax.

Question 53. If You Delete A Table In The Database, Will The Data In The Table Be Deleted Too?

Answer :

Yes.

Question 54. What Is The Purpose Of The Use Command?

Answer :

Use command is used for to select the database. For i.e Use Database Name.

Question 55. What Is The Purpose Of The Tempdb Database?

Answer :

Tempdb database keeps the information about the temporary objects (#TableName, #Procedure). Also the sorting, DBCC operations are performed in the TempDB.

Question 56. What Is The Purpose Of The Master Database?

Answer :

Master database keeps the information about sql server configuration, databases users etc.

Question 57. What Is The Purpose Of The Model Database?

Answer :

It works as Template Database for the Create Database Syntax.

Question 58. Can You Use The On Delete And On Update In The Same Constraint?

Answer :

Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO.

Question 59. What Is A Foreign Key?

Answer :

FOREIGN KEY constraints identify the relationships between tables.
A foreign key in one table points to a candidate key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Usually, order_part would also have a foreign key against an order table, but this is a simple example.
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO.

Question 60. What Is A Query?

Answer :

A request for information from a database. There are three general methods for posing queries:

• Choosing parameters from a menu: In this method, the database system presents a list of parameters from which you can choose. This is perhaps the easiest way to pose a query because the menus guide you, but it is also the least flexible.
• Query by example (QBE): In this method, the system presents a blank record and lets you specify the fields and values that define the query.
• Query language: Many database systems require you to make requests for information in the form of a stylized query that must be written in a special query language. This is the most complex method because it forces you to learn a specialized language, but it is also the most powerful.