sql server-12

Which command using Query Analyzer will give you the version of SQL Server and Operating System?
@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.

How to find the SQL server version from Query Analyser
Answer1
To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:
SELECT SERVERPROPERTY(’productversion’), SERVERPROPERTY (’productlevel’), SERVERPROPERTY (’edition’)
The results are:
• The product version (for example, “9.00.1399.06?)
. • The product level (for example, “RTM”).
• The edition (for example, “Enterprise Edition”).
For example, the result looks similar to:
9.00.1399.06 RTM Enterprise Edition

How to determine which version of SQL Server 2000 is running
To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:
SELECT SERVERPROPERTY(’productversion’), SERVERPROPERTY (’productlevel’), SERVERPROPERTY (’edition’)
The results are:
• The product version (for example, 8.00.534).
• The product level (for example, “RTM” or “SP2?).
• The edition (for example, “Standard Edition”). For example, the result looks similar to
:
8.00.534 RTM Standard Edition

Answer2
One can also use SELECT @@Version where the result would look like
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table.
Answer1.
a. Select count(*) from table1
b. SELECT object_name(id) ,rowcnt FROM sysindexes WHERE indid IN (1,0) AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1
c. exec sp_table_validation @table = ‘authors’

Answer2.
SELECT count( * ) as totalrecords FROM employee
This will display total records under the name totalrecords in the table employee
use COUNT_BIG
Returns the number of items in a group.
@@ROWCOUNT
Returns the number of rows affected by the last statement.
Use this statement after an SQL select * statement, to retrieve the total number of rows in the table

What is the purpose of using COLLATE in a query?
Answer1.
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Answer2.
COLLATE is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”?
Answer1.
Use Storedprocedure for any optimized result, because it is an compiled code.

Answer2.
One of the best ways to increase query performance is to use indexes.

What is an execution plan? When would you use it? How would you view the execution plan?
The Query Analyzer has a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server’s Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.

What is the STUFF function and how does it differ from the REPLACE function?
Answer1:
stuff-> inserts into it without removing any thing. Replace->replace the given text with the new one.

Answer2:
STUFF - it deletes a specified length of characters and inserts another set of characters at a specified starting point. REPLACE -Replaces all occurrences of a specified string value with another string value.