Introduction to Sequences in SQL 2012

SQLServer 2012 introduces Sequence as new schema object which has been requested by the SQL community  and due for release since many years.

  • It is similar to sequence object of  oracle database
  • Unlike identity column sequence is not attached with table. It is  separately created
  • A sequence once created can be used with any table.
  • Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
  • Sequences, unlike identity columns can generate numeric values in an ascending or descending order at a defined interval and can be configured to restart when ever needed.
  • Sequence can also have a Data which is mostly any integer type but by default it is bigint.

How to create sequence?

CREATE SEQUENCE Test.CountBy1 START WITH n INCREMENT BY m;

GO

Where n,m can be any positive of negative integer

OR

CREATE SEQUENCE Temp.NumDesc   AS decimal(3,0)    START WITH 125

INCREMENT BY 25   MINVALUE 100  MAXVALUE 200  CYCLE;

 

How to use sequence?

To activate the sequence or to fetch  the first value

SELECT NEXT VALUE FOR Temp.NumDesc

 

To fetch current value

SELECT current_value FROM Temp.NumDesc

To insert value in a column of a  table using sequence

INSERT  Test.TestTable(CounterColumn,Name) VALUES(NEXT VALUE FOR Test.CountBy1,’Samuel’);

           Identity Sequence
Defines as part of a table It is not tied to the table
Auto generates values on insert Value needs to be generated Explicitly
Can not restart the value Can be restarted  all over again
Value can not be  Cached Can be Cached
Same value cannot be used again Same value can be used several times without regenerating new value

All SQL date formats

Hi,

In this Post I will list all the SQL date formats which we generally use while writing the SQL queries,

The most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. So here is a list of different date formats that come standard in SQL Server as part of the CONVERT function.

 

Type Code Format Code

100

Apr 16 2013 11:01AM SELECT convert(varchar,getdate(),100)

101

4/16/2013

SELECT convert(varchar,getdate(),101)

102

2013.04.16 SELECT convert(varchar,getdate(),102)

103

16/04/2013 SELECT convert(varchar,getdate(),103)

104

16.04.2013 SELECT convert(varchar,getdate(),104)

105

16-04-2013 SELECT convert(varchar,getdate(),105)

106

16-Apr-13

SELECT convert(varchar,getdate(),106)

107

16-Apr-13

SELECT convert(varchar,getdate(),107)

108

11:01:54

SELECT convert(varchar,getdate(),108)

109

Apr 16 2013 11:01:54:223AM SELECT convert(varchar,getdate(),109)

110

4/16/2013

SELECT convert(varchar,getdate(),110)

111

4/16/2013

SELECT convert(varchar,getdate(),111)

112

20130416

SELECT convert(varchar,getdate(),112)

113

16 Apr 2013 11:01:54:223 SELECT convert(varchar,getdate(),113)

114

11:01:54:223 SELECT convert(varchar,getdate(),114)

 

Please copy the below code and execute in SQL management student you will get the above information

select ’100′ TypeCode,convert(varchar,getdate(),100) Format, ‘SELECT convert(varchar,getdate(),100)’ Code UNION

select ’101′ TypeCode,convert(varchar,getdate(),101) Format, ‘SELECT convert(varchar,getdate(),101)’ Code UNION

select ’102′ TypeCode,convert(varchar,getdate(),102) Format, ‘SELECT convert(varchar,getdate(),102)’ Code UNION

select ’103′ TypeCode,convert(varchar,getdate(),103) Format, ‘SELECT convert(varchar,getdate(),103)’ Code UNION

select ’104′ TypeCode,convert(varchar,getdate(),104) Format, ‘SELECT convert(varchar,getdate(),104)’ Code UNION

select ’105′ TypeCode,convert(varchar,getdate(),105) Format, ‘SELECT convert(varchar,getdate(),105)’ Code UNION

select ’106′ TypeCode,convert(varchar,getdate(),106) Format, ‘SELECT convert(varchar,getdate(),106)’ Code UNION

select ’107′ TypeCode,convert(varchar,getdate(),107) Format, ‘SELECT convert(varchar,getdate(),107)’ Code UNION

select ’108′ TypeCode,convert(varchar,getdate(),108) Format, ‘SELECT convert(varchar,getdate(),108)’ Code UNION

select ’109′ TypeCode,convert(varchar,getdate(),109) Format, ‘SELECT convert(varchar,getdate(),109)’ Code UNION

select ’110′ TypeCode,convert(varchar,getdate(),110) Format, ‘SELECT convert(varchar,getdate(),110)’ Code UNION

select ’111′ TypeCode,convert(varchar,getdate(),111) Format, ‘SELECT convert(varchar,getdate(),111)’ Code UNION

select ’112′ TypeCode,convert(varchar,getdate(),112) Format, ‘SELECT convert(varchar,getdate(),112)’ Code UNION

select ’113′ TypeCode,convert(varchar,getdate(),113) Format, ‘SELECT convert(varchar,getdate(),113)’ Code UNION

select ’114′ TypeCode,convert(varchar,getdate(),114) Format, ‘SELECT convert(varchar,getdate(),114)’ Code

Please do Comment to the posts..

Thank You….