Finding another job can be so cumbersome that it can turn into a job itself. If you are expertise in data base concepts like SQL, administrator roles then prepare well for the job interviews to get your dream job.SQL Server 2005 features a number of enhancements, such as multiple installations and distributed partitions, which will make your admin chores easier. Here's our recommendation on the important things to need to prepare for the job interview to achieve your career goals in an easy way. SQL Server 2005 is an Embedded Edition of SQL Server where the database is accessed by few. Using SQL, queries are written to function on DB. Follow Wisdomjobs page for SQL Server 2005 job interview questions and answers page to get through your job interview successfully in first attempt.
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.
Answer :
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:
Question 19. What Does The On Delete Cascade Option Do?
Answer :
ON DELETE CASCADE:
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.
Answer :
Unique + NOT NULL.
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.
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.
Answer :
Yes we can. Alter Table Modify Column.
Question 35. What Does The Update Command Do?
Answer :
Update command will modify the existing record.
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.
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:
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:
Question 44. Will Count(column) Include Columns With Null Values In Its Count?
Answer :
Yes, it will include the null column in count.
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.
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.
Answer :
A request for information from a database. There are three general methods for posing queries:
SQL Server 2005 Related Tutorials |
|
---|---|
SQL Server 2008 Tutorial | Microsoft Entity Framework Tutorial |
LINQ Tutorial |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.