Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah

Download SQL Server 2008 and SQL Server 2005 Sample Database

SQL Server 2008 Sample Database for SQL Server 2008 February CTP

Following is the link to the CodePlex (CodePlex is Microsoft’s open source project hosting web site) site from where you can download the examples

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=10901

SQL Server 2005 Sample Database - For SQL Server 2005 SP2a

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

Microsoft is Positioned Leader in Gartner’s Business Intelligence Top Ranking

Microsoft Is Positioned in Leaders Quadrant of Latest Magic Quadrant for Data Warehousing.

This is indeed a great news for all of us. The Magic Quadrant is an esteemed evaluative model established by Gartner. It positions vendors according to their ability to execute and completeness of vision. For more information please visit the following links.

Microsoft Is Positioned in Leaders Quadrant of Latest Magic Quadrant for Data Warehousing
http://www.microsoft.com/presspass/press/2007/oct07/10-12DWMQPR.mspx

Magic Quadrant for Business Intelligence Platforms, 1Q07
http://mediaproducts.gartner.com/reprints/oracle/145507.html

TOP 10 Microsoft Business Intelligence Benefits

1. Connect people to information efficiently and effectively.
2. Empower employees.
3. Simplify collaboration and sharing.
4. Analyze and gain insight.
5. Improve alignment.
6. Harness the power of the 2007 Microsoft Office system.
7. Deliver business intelligence to your entire organization.
8. Reduce training needs
9. Deliver sophisticated analysis and reporting.
10. Deliver enterprise-grade business intelligence.

(Source : office.microsoft.com)

SQL Server: How to change SQL Server Authentication Mode

To change the SQL Server Authentication mode please follow the below steps

1. Logon to SQL Server and Right Click on the instance name (see in the below image) and select Properties.

2. You will see the Server Properties for the selected instance, Here you select Security page from the left side pane. Once you select the Security page you will get the option to change the Server Authentication mode.

By Harsh Shah (Sr. Consultant)

SQL Server: How to check the current authentication mode by code in SQL Server

EXEC xp_loginconfig ‘login mode’

Result:

SQL SERVER: How to identify the SQL Server Version No?

To know the version of the SQL Server on your machine type follwoing statement in the SQL Server Management Studio

SELECT @@VERSION

Following is the result:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server:SET STATISTICS TIME ON

SET STATISTICS TIME ON

Displays the number of milliseconds required to parse, compile and execute SQL statements.

EXAMPLE

USE Adventureworks

SET STATISTICS TIME ON

SELECT * FROM Production.Product

SET STATISTICS TIME OFF

Results: (You can view the results in the Message Pane i.e. next to Results Pane)

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 50 ms.

(504 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 668 ms