Top 50 Sql Server 2005 Interview Questions You Must Prepare 19.Apr.2024

No it is not required.

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.

SQL Server treats CHAR as fixed length column.

Yes, it will include the null column in count.

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.

Yes it allows.

Select * from sys.dm_db_missing_index_details.

OR, AND, IN and BETWEEN.

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.

Constrain will not allow NULL values in the column.

NO.

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.

1433.

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

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

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

No its not necessary.

NULL.

It works as Template Database for the Create Database Syntax.

Server's configurations and logins.

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.

GRANT, DENY and REVOKE.

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.

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.

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

NO.

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.

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.

ALTER TABLE tablename ADD column_name DATATYPE.

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

Ascending Order.

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

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.

Order By clause change only the output of the data.

Yes we can. Alter Table Modify Column.

SELECT.

Not Necessary.

The column initially contains the NULL values.

Update command will modify the existing record.

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

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

Templates of new database objects, like tables and column.

•  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.

Yes we can.

Scheduled jobs, Backup/Restore and DTA information.

DEFAULT, WITH CHECK or WITH NOCHECK.

Yes.

Yes.

Unique + NOT NULL.

CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME).