S.No Identity Sequence 1 Dependant on table. Independent from table. 2Identity is a property in a table.Example :CREATE TABLE Tabletest_Identity([ID] int Identity (1,1),[Product Name] varchar(50))
Sequence is an object.Example :CREATE SEQUENCE [dbo].[Sequence_ID]AS [int]START WITH 1INCREMENT BY 1MINVALUE 1MAXVALUE 1000NO CYCLE
3If we need a new ID from an identity column we need toinsert and then get new ID.Example :Insert into [test_Identity] Values (‘SQL Server’)GOSELECT @@IDENTITY AS ‘Identity’–ORSelect 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 VALUEFOR dbo.[Sequence_ID] 4We cannot perform a cycle in identity column. Meaning, we cannot restart the counter after aparticular interval.In the sequence, we can simply add one property to make it a cycle.Example :ALTER SEQUENCE [dbo].[Sequence_ID] 5 We cannot cache Identity column property.Sequence can be easily cached by just setting cache property ofsequence. 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 itExample :Create table dbo.[test_Sequence]([ID] int,[Product Name] varchar(50))GO–First Insert With Sequence objectINSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008′)GO–Second Insert without SequenceINSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012′)
7We cannot define the maximum value in identity column it isbased on the data type limit.Here we can set up its maximum value.Example :ALTER SEQUENCE [dbo].[Sequence_ID] 8We 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
9 We cannot generate range from identity.We can generate a range of sequencevalues from a sequence object with the help of sp_sequence_get_range.
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.
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.
S.No Local temporary table Global temporary table 1Denoted by # symbol.Denoted by ## symbol. 2Valid 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 . 3Cannot be shared between multiple users.Can be shared between multiple users.