sql-faq-4

41. How can I enforce to use particular index?
You can use index hint (index=index_name) after the table name.
SELECT au_lname
FROM authors (index=aunmind)

42. What is sorting and what is the difference between sorting and clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.

43. What are the differences between UNION and JOINS?
A JOIN selects columns from 2 or more tables. A UNION selects rows.
The UNION operator allows you to combine the results of two or more SELECT statements into a single result set.

44. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

45. What is the row size in SQL Server 2000?
8060 bytes.

46. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. eg: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

47. What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

48. What is the use of SCOPE_IDENTITY() function?
Returns the most recently created identity value for the tables in the current execution scope.