SQL Server Interview Questions -- Part 7

61. How do you check the version number of the SQL Server you are currently running ?
Ans: select @@version

62. What is use of sp_who ?
Ans: Currently running users and process in SQL Server can be found by running sp_who.

63. What is sp_who2 ?
Ans: It shows little more that sp_who with locking, blocking, what the users logged in are doing and so on.

64. What is a linked server in SQL Server ?
Ans: From your current SQL server management stuio/querey analyzer, if you want to execute commands against OLE DB data sources on remote servers, you use linked server.

65. Write a query to list all the user defined tables in SQL Server 2008 ?
Ans: select * from sys.objects where type = 'U'

66. What is instead of trigger in SQL Server ?
Ans: Instead of triggers -- are triggers attached to table in which, the code inside them is executed in place of the original insert/delete/update statement

67. What are DBCC commands in SQL Server 2008 ?
Ans: Database Console Commands -- DBCC -- are used for Maintenance, Information, Validation and Miscellaneous -- tasks in SQL Server

68. What is DBCC DBREINDEX in SQL Server 2008 ?
Ans: DBCC DBREINDEX is used to rebuilds indexes for a table. Please note that it is deprecated in SQL SERVER 2008 and will be removed in the next version. It will be replaced by REBUILD option in Alter Index.

69. What are the default databases in SQL Server 2008 ?
Ans: tempdb, model, msdb, master

70. What is the NOLOCK in SQL Server 2008 ?
Ans: NOLOCK command asks the SQL Server to ignore the locking. For example,
SELECT * from TableA WITH NOLOCK
JOIN TABLEB WITH NOLOCK ON TABLEA.ID = TABLEB.ID