Archive

Archive for the ‘Alter’ 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…

 

Compound Operators in SQL Server

July 5, 2011 Leave a 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…

 

 

Changing SQL database to single user mode and back to Multi User mode

October 26, 2010 1 comment

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users.

It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode.

ALTER  DATABASE <<Database Name>>  SET SINGLE_USER  WITH NO_WAIT

The NO_WAIT clause will set it to single user mode as soon as you execute the query.  An alternate to this is by using the system stored procedure sp_dboption

Continue Reading ….

 

 

 

Remove Not Null Constraint on a column using Alter Table statement

October 8, 2010 3 comments

Many times , it may be necessary to remove Not Null constraint used for a column in  a table. It is usually necessary to overcome unexpected integrity constraints ( rarely occurs, if designed well) against a table. We can remove the Not Null constaint by using  ALTER TABLE …. ALTER COLUMN … option.

A sample query to is as below

Continue Reading …

Search for a pattern in sql server using patindex() and Charindex()

October 8, 2010 2 comments

Although these functions are not commonly used, these are really powerful in searching a pattern and returning the location of text/ string/ pattern. By understanding the functionality of both the string functions, we will be able to understand the difference as well.

PATINDEX()

The function searches for a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similar to the LIKE operator in any query.

The general syntax is

PATINDEX ( ‘%pattern%’ , expression )

You might have already noticed the ‘%’ symbols used in the pattern. These are important, as I mentioned earlier, use the same syntax as used for searching by  LIKE operator.

Continue Reading …

GUID (Globally Unique Identifier) in SQL Server

October 7, 2010 1 comment

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Continue Reading …