Archive

Archive for the ‘Joins’ Category

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

Advertisements

Compound Operators in SQL Server

July 5, 2011 1 comment

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

 

 

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…

 

 

The string function SOUNDEX() and its purpose in SQL.

April 25, 2011 Leave a comment

The Soundex code was developed to help negate the effects of all the spelling variations that can occur for similar sounding names ( Smith, Smithe, Smythe, etc.).  This way, users can index records based on a Soundex code based on the sound  and “not” by how it is spelled.

The Soundex system is not infallible – the surnames Gough and Goff sound the same (Goff), but a different code is formed for these two names.  Soundex can however be used to a great extent to get a desired result ( atleast closer to a desired result) This is a commonly used algorithm by many researchers.

In SQL a string function is available for this and is usually comes handy.  The function is

Continue Reading …

.

what does NULLIF() do ? how is it different from ISNULL()?

October 7, 2010 3 comments

NULLIF( ) : Returns a null value if the two expressions specified in the function are equal.

The syntax used is

NULLIF ( expression1 ,expression2 )

If expression1 = expression2, then the function will return NULL.

ISNULL( ): The function replaces a null expressions with a specified value.

The syntax used is

Continue Reading …

Script to List Linked Servers

October 7, 2010 Leave a comment

In a large server , it is very common to create high number of linked server connections. it might be required ( not very often) to use this list in the SPs or other objetcs created. Below is a simple query / script that can provide the list of linked servers used.

Continue Reading ..

Get the consolidated size of all tables in a database.

January 18, 2010 Leave a comment

The first thing that goes through your mind might be ” why cant we SP_SPACEUSED instead ? “. You are right. However , it will result in a row by row operation rather than a set operation. This will be a major headache in the usual production environment with large number of tabls and huge data. The below query can give you the result in few seconds. The step 2 can be avoided by careful coding . I have included update statement for easier understanding of the code. 

SELECT OBJECT_ID
 , SUM (RESERVED_PAGE_COUNT) RESERVEDPAGES
 , SUM (USED_PAGE_COUNT) USEDPAGES
 , SUM ( CASE WHEN (INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT) ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT END )PAGES, SUM ( CASE WHEN (INDEX_ID < 2) THEN ROW_COUNT ELSE 0 END ) AS [ROWCOUNT]  
 INTO   #SIZE  
 FROM  SYS.DM_DB_PARTITION_STATS 
 GROUP BY OBJECT_ID     

UPDATE #SIZE  
SET RESERVEDPAGES= (RESERVEDPAGES + RESERVED_PAGE_COUNT)

 ,USEDPAGES = (USEDPAGES + USED_PAGE_COUNT)  
 FROM
#SIZE T
 ,SYS.DM_DB_PARTITION_STATS P
 ,SYS.INTERNAL_TABLES IT  
WHERE T.OBJECT_ID=IT.PARENT_ID AND IT.INTERNAL_TYPE IN (202,204) AND P.OBJECT_ID = IT.OBJECT_ID   
 
 
SELECT  DATASIZEINKB= SUM(PAGES) * 8 , INDEXSIZEINKB = SUM((CASE WHEN USEDPAGES > PAGES THEN (USEDPAGES PAGES) ELSE 0 END)) * 8, UNUSEDSIZEINKB = SUM((CASE WHEN RESERVEDPAGES > USEDPAGES THEN (RESERVEDPAGES USEDPAGES) ELSE 0 END)) * 8
FROM #SIZE  
 

DROP TABLE #SIZE 

 

 pardon me for the allignments, as i could not get it right :).