Question asked by one of my DBA friends.

 

 

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?

 

Answer: 

 

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

                    SELECT column_list

                    FROM   table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)

                    -- Use This

                    SELECT column_list

                    FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)

 

d.    Avoided joining between two types of columns like "INT" and "FLOAT" and  

      don't try to use CONVERT or CAST.

e.   Try to avoid dynamic SQL

f.    Try to avoid Temporary table

g.   Don't use IN or NOT IN clause in SQL statement, instead use EXISTS or

      NOT EXISTS

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

n.   Last but most important, solid idea related to Index. If necessary use

      guided index in your SQL join operations.

 

What is the difference between 'SET' and 'SELECT' in SQL?

 

Answer:

 

DECLARE @i INT

 

--Type1

SELECT @i=Roll FROM MyTab WHERE Name='RAJA'

--Type2

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.

3.     BY SELECT we can assigned multiple value to multiple variable within a single statements. Like this.

DECLARE @i INT,

                           @k VARCHAR(MAX)  

 

          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?

 

Answer:

 

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