7.26.2012

SQL Server Difference FAQs-3


1.Difference between Identity and Sequence in SQL Server 2012
S.No Identity Sequence
1 Dependant on table. Independent from table.
2
Identity is a property in a table.

Example :

CREATE TABLE Table
test_Identity

(

[ID] int Identity (1,1),

[Product Name] varchar(50)

)


Sequence is an object.

Example :

CREATE SEQUENCE [dbo].[Sequence_ID]

AS [int]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 1000

NO CYCLE

NO CACHE
3
If we need a new ID from an identity column we need to
insert and then get new ID.

Example :

Insert into [test_Identity] Values (‘SQL Server’)

GO

SELECT @@IDENTITY AS ‘Identity’

OR

Select SCOPE_IDENTITY() AS ‘Identity’
In the sequence, we do not need to insert new ID, we can view the new ID directly.

Example :

SELECT NEXT VALUE
FOR dbo.[Sequence_ID]
4
We cannot perform a cycle in identity column. Meaning, we cannot restart the counter after a
particular interval.
In the sequence, we can simply add one property to make it a cycle.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CYCLE;
5 We cannot cache Identity column property.
Sequence can be easily cached by just setting cache property of
sequence. It also improves the performance.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CACHE 3;
6 We cannot remove the identity column from the table directly.
The sequence is not table dependent so we can easily remove it

Example :

Create table dbo.[test_Sequence]

(

[ID] int,

[Product Name] varchar(50)

)

GO

First Insert With Sequence object

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008′)

GO

Second Insert without Sequence

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012′)


7
We cannot define the maximum value in identity column it is
based on the data type limit.
Here we can set up its maximum value.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

MAXVALUE 2000;
8
We can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (test_Identity, RESEED, 4)



We can reseed as well as change the step size.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

RESTART WITH 7

INCREMENT BY 2;
9 We cannot generate range from identity.
We can generate a range of sequence
values from a sequence object with the help of sp_sequence_get_range.


2.Difference between Temp table and Table variable
S.No Temp table Table variable
1 A Temp table is easy to create and back up data. But the table variable involves the effort when we usually create the normal tables.
2 Temp table result can be used by multiple users. But the table variable can be used by the current user only. 
3 Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist. But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.
4 Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
5 Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. But the table variable can be used up to that program. (Stored procedure)
6 Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. But we cannot do it for table variable.
7 Functions cannot use the temp variable. More over we cannot do the DML operation in the functions . But the function allows us to use the table variable. But using the table variable we can do that.
8 The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Whereas the table variable won't do like that.

Another Good Reference:


3.Difference between RAISERROR and THROW statements

S.No RAISERROR Statement THROW Statement
1 If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages. The error_number parameter does not have to be defined in sys.messages.
2 The msg_str parameter can contain printf formatting styles. The message parameter does not accept printf style formatting.
3 The severity parameter specifies the severity of the exception. There is no severity parameter. The exception severity is always set to 16.

4.Difference between Local temporary table and Global temporary table

S.No Local temporary table Global temporary table
1
Denoted by # symbol.
Denoted by ## symbol.
2
Valid for the current connection only. They are cleared as soon as the current connection closes.
Available to all the connections once created. They are deleted when all users referencing the table disconnect from SQL Server .
3
Cannot be shared between multiple users.
Can be shared between multiple users.

No comments:

Post a Comment