Difference between Select and set in sql

Difference between Select and set in sql

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Select the top 1 into a variable

Declare @Column int

SELECT TOP 1 @Column = Column1 

FROM tbl_Test 
WHERE Column2 = 'test' 

In above query Column1, Column2 are two columns of table 'tbl_Test'. 

Compare the date

Compare the date

Convert(datetime,convert(varchar,Column,105),103) < Convert(datetime,convert(varchar,GETDATE(),105),103)

Best Programming Techniques

Format SQL Code. Make it readable. Wrap it.

Use Column name in ORDER BY clause instead of numbers.

Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).

Join tables in order that they always perform the most restrictive search first to filter out the maximum number of rows in the early phases of a multiple table join.

Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.

Do not use temp tables use CTE or Derived tables instead.

Always take backup of all the data.

Never ever work on production server

Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.

Reduces the use of nullable columns.

NULL columns consumes an extra byte on each column used as well as adds overhead in queries. Also NULL is not good for logic development for programmers.

Use User Defined Functions sparsely, use Stored Procedures instead.

Stored Procedure can achieve all the tasks UDF can do. SP provides much more features than UDFs.

Do not prefix stored procedure with SP_ prefix. As they are first searched in master database, before it is searched in any other database.

SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)