One of my DBA friends asks me some SQL related Question. Here I am trying to explain it in this article
What are necessary steps should be taken for query execution time faster, suppose a Query when it's executing taking time 3min now I want to execute this query within 30secs how?
There are lots of factors related to make quay execution faster. My strong suggestion is to understand the execution plan to execute your query in better ways.
Some common factors that we all knows, related to SQL query executions are mentioned bellow.
a. Don't use "SELECT * " in a SQL query. That means use the proper columns name that you needed not overload the query by using *. That added extra expenses to data retrieval.
b. Don't use extra table join that you don't needed in your SQL statement.
c. Don't use COUNT(*) in Sub query, instead use EXIST or NOT EXIST Clause.
-- Do not Use
FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)
-- Use This
FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)
e. Try to avoid dynamic SQL
f. Try to avoid Temporary table
h. Avoid LIKE instead you can use full text search.
i. Try to use UNION or UNION ALL to implement OR operators.
j. Don't calls any function in SELET statements, I mean try to avoid.
k. Try to avoid correlated sub query
l. Try to use stored procedure to execute all your T-SQL statement.
m. Use VIEWs but always use WITH SCHEMA BINDING options
What is the difference between 'SET' and 'SELECT' in SQL?
DECLARE @i INT
SELECT @i=Roll FROM MyTab WHERE Name='RAJA'
SET @i=(SELECT Roll FROM MyTab WHERE Name='RAJA')
Consider the above two SQL statements, the variable @i have the same value. So, what is the difference between two statements?
1. From SQL server 7.0 Microsoft recommends to use the SET statement only, in such kind of above operations.
2. SET is ANSI standard way to assign the value of a variable.
DECLARE @i INT,
SELECT @i=Roll, @k=SName FROM MyTab WHERE SName='RAJA'
4. SELECT has some difficulties like, if the above statements return more than one rows it not return any error. But if you use SET in the above example it give you error like "Sub query returns more than one values".
So at the conclusions, I recommended you to use SET and not to go at SELECT when assigning variables.
What are the basic differences between SSMS 2005 & SSMS 2008?
As per me, there are no differences; the difference is the database engine Limitations of SQL 2005 and SQL 2008. SQL 2008 gives you the better functionality and performance then SQL 2005.
Posted by: MR. JOYDEEP DAS