Top 100+ Sql Server 2005 Interview Questions And Answers
Question 1. How Do You Troubleshoot Errors In A Sql Server Agent Job?
Answer :
Inside SSMS, in Object explorer underneath SQL Server Agent look for Job Activity Monitor. The activity hobby screen presentations the contemporary status of all the jobs on the example. Choose the particular job which failed, right click on and pick out view records from the drop down menu. The execution records of the task is displayed and you could select the execution time (if the activity failed multiple instances during the same day). There might records such as the time it took to execute that Job and information about the mistake passed off.
Question 2. What Is The Default Port No On Which Sql Server Listens?
Answer :
1433.
SQL Server 2000 Interview Questions
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 comprise a maximum of 32,767 documents.
There are Primarily 2 forms of information files Primary facts record and Secondary statistics file(s). There can be simplest one Primary facts record and a couple of secondary records documents as long as the entire # of files is much less than 32,767 documents.
Question four. What Is Dcl?
Answer :
DCL stands for Data Control Language.
SQL Server 2008 Tutorial
Question five. What Is Fill Factor?
Answer :
Fill Factor is a placing that is relevant to Indexes in SQL Server. The fill thing value determines how a lot facts is written to an index page when it's far created / rebuilt.
MSBI Interview Questions
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.
Microsoft Entity Framework Tutorial SQL Server 2008 Interview Questions
Question eight. What Stored By The Tempdb ?
Answer :
Row versions, cursor, temp items.
Question 9. What Stored By The Model?
Answer :
Templates of latest database gadgets, like tables and column.
SSIS(SQL Server Integration Services) Interview Questions
Question 10. What Stored By The Master?
Answer :
Server's configurations and logins.
LINQ Tutorial
Question eleven. What Stored By The Msdb?
Answer :
Scheduled jobs, Backup/Restore and DTA data.
SSRS(SQL Server Reporting Services) Interview Questions
Question 12. Can We Perform Backup Restore Operation On Tempdb?
Answer :
NO.
SQL Server 2000 Interview Questions
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 idea in SQL Server with the aid of which we can add other SQL Server to a Group and question both the SQL Server databases the use of T-SQL Statements.
Question 15. What Are The Dmvs?
Answer :
Dynamic Management Views (DMV) go back server nation data that may be used to reveal the fitness of a server instance, diagnose troubles, and tune performance.
Microsoft Entity Framework Interview Questions
Question sixteen. 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 movement facts type, Activity Monitor, Resource governor, records compression, compressed backup.
LINQ Interview Questions
Question 18. What Does The On Update No Action Do?
Answer :
ON UPDATE NO ACTION:
Specifies that if an attempt is made to replace a key fee in a row whose key is referenced by means of foreign keys in present rows in other tables, an errors is raised and the UPDATE is rolled again.
Specifies that if an strive is made to delete a row with a key referenced by using overseas keys in existing rows in different tables, an errors is raised and the DELETE is rolled returned.
MSBI Interview Questions
Question 19. What Does The On Delete Cascade Option Do?
Answer :
ON DELETE CASCADE:
Specifies that if an try is made to delete a row with a key referenced via foreign keys in existing rows in other tables, all rows containing those foreign keys also are deleted. If cascading referential moves have also been described at the goal tables, the specified cascading moves also are taken for the rows deleted from the ones tables.
Specifies that if an try is made to update a key cost in a row, wherein the key value is referenced by overseas keys in present rows in other tables, all the foreign key values also are updated to the brand new cost specified for the key. If cascading referential moves have also been defined on the goal tables, the desired cascading moves also are taken for the key values updated in the ones 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 commonly enforced by way of the combination of a number one key or candidate key (exchange key) and a overseas key. For referential integrity to hold, any area in a desk this is declared a overseas key can contain handiest values from a parent desk’s number one key or a candidate key. For instance, deleting a document that includes a price cited through a overseas key in some other desk could destroy referential integrity. The relational database management machine (RDBMS) enforces referential integrity, generally either by deleting the foreign key rows as properly to keep integrity, or with the aid of returning an blunders and not acting the delete. Which technique is used might be decided by way of the referential integrity constraint, as described inside the information dictionary.
SQL Server Analysis Services (SSAS) Interview Questions
Question 21. What Is A Concatenated Primary Key?
Answer :
Each table has one and handiest one number one key, that may include one or many columns. A concatenated number one key accommodates or more columns. In a single table, you may discover several columns, or corporations of columns, that would serve as a number one key and are referred to as candidate keys. A desk may have a couple of candidate key, but simplest one candidate key can become the primary key for that desk.
Question 22. How Are The Unique And Primary Key Constraints Different?
Answer :
A UNIQUE constraint is much like PRIMARY key, however you may have multiple UNIQUE constraint in keeping with table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to hurry up the process of searching for duplicates. In this example the index defaults to NONCLUSTERED index, because you can have handiest one CLUSTERED index in step with desk.
The number of UNIQUE constraints consistent with table is constrained through the wide variety of indexes on the table i.E 249 NONCLUSTERED index and one possible CLUSTERED index.
Contrary to PRIMARY key UNIQUE constraints can take delivery of NULL however just once. If the constraint is described in a aggregate of fields, then each discipline can accept NULL and may have some values on them, so long as the aggregate values is particular.
Question 23. When A Primary Key Constraint Is Included In A Table, What Other Constraints Does This Imply?
Answer :
Unique + NOT NULL.
Sql Server Dba Interview Questions
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.
SQL Server 2008 Interview Questions
Question 25. What Does The Not Null Constraint Do?
Answer :
Constrain will now not allow NULL values inside 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.
SSIS(SQL Server Integration Services) Interview Questions
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 period columns only use a very small amount of area to keep a NULL so VARCHAR datatype is the best alternative for null values.
Question 31. Does Server Sql Treat Char As A Variable-length Or Fixed-length Column?
Answer :
SQL Server treats CHAR as constant duration 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 lets in.
SSRS(SQL Server Reporting Services) Interview Questions
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 are able to. Alter Table Modify Column.
Question 35. What Does The Update Command Do?
Answer :
Update command will adjust 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 statistics in to desk from one-of-a-kind tables or circumstance based totally insert.
INSERT INTO .. VALUES you have to specify the insert values.
Microsoft Entity Framework Interview Questions
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 unmarried-component call that may replace a , three or 4-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs may be created for the subsequent items:
Table.
View.
Assembly (CLR) Stored Procedure.
Assembly (CLR) Table-valued Function.
Assembly (CLR) Scalar Function.
Assembly Aggregate (CLR) Aggregate Functions.
Replication-filter-system.
Extended Stored Procedure.
SQL Scalar Function.
SQL Table-valued Function.
SQL Inline-table-valued Function.
SQL Stored Procedure.
LINQ Interview Questions
Question forty one. Do Comments Need To Go In A Special Place In Sql Server 2005?
Answer :
No its not essential.
Question forty two. Are Semicolons Required At The End Of Sql Statements In Sql Server 2005?
Answer :
No it isn't always required.
SQL Server Analysis Services (SSAS) Interview Questions
Question forty three. What Are Table Aliases?
Answer :
Aliases can make it simpler to work with desk names. Using aliases is useful while:
You need to make the announcement in the SQL Pane shorter and less complicated to study.
You talk to the desk call often to your query — inclusive of in qualifying column names — and need to be sure you live inside a specific individual-length restriction on your question. (Some databases impose a maximum period for queries).
You are running with a couple of times of the identical table (such as in a self-be part of) and want a manner to consult one instance or the other.
Question 44. Will Count(column) Include Columns With Null Values In Its Count?
Answer :
Yes, it will consist of the null column in matter.
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 way nothing. We can’t don't forget space as NULL cost.
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 forty seven. What Is The Default Order Of An Order By Clause?
Answer :
Ascending Order.
Question forty eight. Does Order By Actually Change The Order Of The Data In The Tables Or Does It Just Change The Output?
Answer :
Order By clause alternate most effective the output of the facts.
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 fifty one. What Is Usually The First Word In A Sql Query?
Answer :
SELECT.
Question fifty two. 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 fifty four. What Is The Purpose Of The Use Command?
Answer :
Use command is used for to pick out the database. For i.E Use Database Name.
Question 55. What Is The Purpose Of The Tempdb Database?
Answer :
Tempdb database continues the records about the transient items (#TableName, #Procedure). Also the sorting, DBCC operations are finished within the TempDB.
Question fifty six. What Is The Purpose Of The Master Database?
Answer :
Master database continues the information approximately sq. Server configuration, databases users and so forth.
Question fifty seven. 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 discover the relationships between tables.
A foreign key in one table factors to a candidate key in every other desk. Foreign keys save you movements that might go away rows with overseas key values whilst there are no candidate keys with that fee. In the subsequent sample, the order_part desk establishes a overseas key referencing the part_sample desk defined earlier. Usually, order_part would actually have a overseas key towards an order table, however this is a easy instance.
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 records from a database. There are 3 standard strategies for posing queries:
Choosing parameters from a menu: In this technique, the database machine presents a list of parameters from which you can select. This is possibly the very best manner to pose a query because the menus guide you, but it's also the least bendy.
Query by means of instance (QBE): In this approach, the gadget offers a clean file and helps you to specify the fields and values that outline the question.
Query language: Many database structures require you to make requests for information in the form of a stylized query that have to be written in a special question language. This is the maximum complicated approach because it forces you to study a specialized language, however it's also the most effective.
