Archive

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

 

 

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 …

 

Changing Identity Seed value of a SQL server table using DBCC

September 16, 2009 Leave a comment

Whew !! this is my third post in two days on identity fields of SQL server. The more I investigate, more is the information I get.

Most of us , would have faced the problem during deletion of records from a table containing identity column. Deleting records will not reset the value of identity seed. This results in sequential gap between values. Even deleting all the records will not result in identity seed being set to “1”.

You can reset the seed value to your desired value by using DBCC.  Create a table with identity column and insert some records, Now run the query below to get the current Identity Value.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

Now execute the DBCC CHECKIDENT command to reset the Seed value for the table as

DBCC CHECKIDENT('TestTable', RESEED, 33)

check the current Identity Value.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

You will be able to notice a change in the identity value. Be careful while using this, as it can result in duplicates, if your identity column is a non unique column( or PK column).

However , if you are truncating the table ( using TRUNCATE TABLE command) the seed will be automatically set to “1”.

Categories: DBCC, SQL Queries, SQL Server