Admin: 1 версия

2010-05-26T10:24:57Z

1 версия

Новая страница

== Altering the Table to Add an Identity Column==



<source lang="sql">
7> CREATE TABLE Dupes(
8> ID int NOT NULL,
9> Txt char (10) NOT NULL
10> )
11> GO
1> INSERT Dupes (ID, Txt) VALUES (1, "x")
2> INSERT Dupes (ID, Txt) VALUES (1, "a")
3> INSERT Dupes (ID, Txt) VALUES (1, "x")
4> INSERT Dupes (ID, Txt) VALUES (1, "x")
5> INSERT Dupes (ID, Txt) VALUES (2, "b")
6> INSERT Dupes (ID, Txt) VALUES (2, "x")
7> INSERT Dupes (ID, Txt) VALUES (2, "b")
8> INSERT Dupes (ID, Txt) VALUES (3, "c")
9> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> ALTER TABLE Dupes
4> ADD
5> Ident int NOT NULL IDENTITY (1, 1)
6> GO
1>
2>
3> --Deleting Duplicates with a Correlated Subquery
4> DELETE D1
5> FROM
6> Dupes AS D1
7> WHERE
8> D1.Ident >
9> (
10> SELECT
11> MIN (D2.Ident)
12> FROM
13> Dupes AS D2
14> WHERE
15> D2.ID = D1.ID
16> AND
17> D2.Txt = D1.Txt
18> )
19>
20>
21> drop table dupes;
22> GO
(3 rows affected)
1></source>

== A table could also be created so that the seed value was negative and the increment was positive:==



<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL
5> ,MyDescription NVarChar(50) NOT NULL)
6> GO
1>
2>
3> drop table MyTable;
4> GO
1>
By default, once a column has been assigned the IDENTITY property,
SQL Server does not allow explicit values to be inserted into it.</source>

== Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.==



<source lang="sql">
4> CREATE TABLE T (
5> int1 int IDENTITY PRIMARY KEY,
6> bit1 bit NOT NULL DEFAULT 0
7> )
8> GO
1>
2> INSERT T (bit1) VALUES (1)
3> INSERT T (bit1) VALUES (0)
4> INSERT T DEFAULT VALUES
5> INSERT T (int1, bit1) VALUES (4,1)
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
Msg 544, Level 16, State 1, Server J\SQLEXPRESS, Line 5
Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.
1>
2> select * from t;
3> GO
int1 bit1
----------- ----
1 1
2 0
3 0
(3 rows affected)
1>
2> drop table t;
3> GO</source>

== Check @@IDENTITY value==



<source lang="sql">
3>
4> CREATE TABLE OrderDetails (
5> OrderID int NOT NULL ,
6> ProductID int NOT NULL ,
7> UnitPrice money NOT NULL DEFAULT (0),
8> Quantity smallint NOT NULL DEFAULT (1),
9> Discount real NOT NULL DEFAULT (0)
10> )
11> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Orders (
4> OrderID int IDENTITY (1, 1) NOT NULL ,
5> CustomerID nchar (5) NULL ,
6> EmployeeID int NULL ,
7> OrderDate datetime NULL ,
8> RequiredDate datetime NULL ,
9> ShippedDate datetime NULL ,
10> ShipVia int NULL ,
11> Freight money NULL DEFAULT (0),
12> ShipName nvarchar (40) NULL ,
13> ShipAddress nvarchar (60) NULL ,
14> ShipCity nvarchar (15) NULL ,
15> ShipRegion nvarchar (15) NULL ,
16> ShipPostalCode nvarchar (10) NULL ,
17> ShipCountry nvarchar (15) NULL
18> )
19> GO
1>
2>
3> DECLARE @Ident int
4>
5> INSERT INTO Orders
6> (CustomerID,OrderDate)
7> VALUES
8> ("ALFKI", DATEADD(day,-1,GETDATE()))
9>
10> SELECT @Ident = @@IDENTITY
11>
12> INSERT INTO OrderDetails
13> (OrderID, ProductID, UnitPrice, Quantity)
14> VALUES
15> (@Ident, 1, 50, 25)
16>
17> SELECT "The OrderID of the INSERTed row is " + CONVERT(varchar(8),@Ident)
18> GO
(1 rows affected)
(1 rows affected)
-------------------------------------------
The OrderID of the INSERTed row is 1
(1 rows affected)
1>
2> drop table orders;
3> drop table OrderDetails;
4> GO</source>

== Create table with IDENTITY column==



<source lang="sql">
The basic syntax for creating a table is as follows:
CREATE TABLE [database_name].[owner].table_name
(column_name data_type [length] [IDENTITY(seed, increment)] [NOT NULL])
9> CREATE TABLE Groups (
10> Id int IDENTITY (1, 1) NOT NULL
11> )
12> GO
1>
2> drop table Groups
3> GO</source>

== Generating IDENTITY values==



<source lang="sql">
4> IF EXISTS (SELECT name
5> FROM sysobjects
6> WHERE name = N"Players"
7> AND type = "U"
8> )
9> DROP TABLE Players
10> GO
1>
2> create table Players (
3> Id int IDENTITY (1, 1) NOT NULL
4> )
5> GO
1>
2> drop table Players
3> GO
1></source>

== IDENTITY(==



<source lang="sql">
2> CREATE TABLE customer
3> (
4> cust_id smallint IDENTITY(100, 20) NOT NULL,
5> cust_name varchar(50) NOT NULL
6> )
7> GO
1>
2> SELECT IDENT_CURRENT("customer")
3> GO
----------------------------------------
100
(1 rows affected)
1>
2> drop table customer;
3> GO
1></source>

== Identity: A numerical value automatically assigned to a row any time a new row is added.==



<source lang="sql">
4>
5> CREATE TABLE MyTable (
6> MyID Int IDENTITY(1, 1) NOT NULL
7> , MyDescription nVarChar(50) NOT NULL)
8> GO
1>
2> drop table MyTable;
3> GO
1>
The actual syntax for the IDENTITY property is as follows:
IDENTITY [ (seed , increment ) ]
4></source>

== Identity column==



<source lang="sql">
3> CREATE TABLE customer
4> (
5> cust_id int IDENTITY NOT NULL,
6> cust_name varchar(30) NOT NULL,
7> CONSTRAINT customer_PK PRIMARY KEY (cust_id)
8> )
9> GO
1>
2> EXEC sp_helpconstraint customer
3> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer

constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys

-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
PRIMARY KEY (clustered) customer_PK
(n/a) (n/a) (n/a) (n/a) cust_id

No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1> drop table customer;
2> GO</source>

== IDENTITY/NOT NULL/PRIMARY KEY==



<source lang="sql">
12> CREATE TABLE customer
13> (
14> cust_id int IDENTITY NOT NULL PRIMARY KEY,
15> cust_name varchar(30) NOT NULL
16> )
17> GO
1>
2> EXEC sp_helpconstraint customer
3> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer

constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys

-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
PRIMARY KEY (clustered) PK__customer__383021B8
(n/a) (n/a) (n/a) (n/a) cust_id

No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1>
2> drop table customer;
3> GO</source>

== Query a view with four table join==



<source lang="sql">
4> CREATE TABLE Customers (
5> CustomerID nchar (5) NOT NULL ,
6> CompanyName nvarchar (40) NOT NULL ,
7> ContactName nvarchar (30) NULL ,
8> ContactTitle nvarchar (30) NULL ,
9> Address nvarchar (60) NULL ,
10> City nvarchar (15) NULL ,
11> Region nvarchar (15) NULL ,
12> PostalCode nvarchar (10) NULL ,
13> Country nvarchar (15) NULL ,
14> Phone nvarchar (24) NULL ,
15> Fax nvarchar (24) NULL
16> )
17> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Orders (
4> OrderID int IDENTITY (1, 1) NOT NULL ,
5> CustomerID nchar (5) NULL ,
6> EmployeeID int NULL ,
7> OrderDate datetime NULL ,
8> RequiredDate datetime NULL ,
9> ShippedDate datetime NULL ,
10> ShipVia int NULL ,
11> Freight money NULL DEFAULT (0),
12> ShipName nvarchar (40) NULL ,
13> ShipAddress nvarchar (60) NULL ,
14> ShipCity nvarchar (15) NULL ,
15> ShipRegion nvarchar (15) NULL ,
16> ShipPostalCode nvarchar (10) NULL ,
17> ShipCountry nvarchar (15) NULL
18> )
19> GO
1>
2> CREATE TABLE Products (
3> ProductID int NOT NULL ,
4> ProductName nvarchar (40) NOT NULL ,
5> SupplierID int NULL ,
6> CategoryID int NULL ,
7> QuantityPerUnit nvarchar (20) NULL ,
8> UnitPrice money NULL,
9> UnitsInStock smallint NULL,
10> UnitsOnOrder smallint NULL,
11> ReorderLevel smallint NULL,
12> Discontinued bit NOT NULL
13> )
14> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE OrderDetails (
4> OrderID int NOT NULL ,
5> ProductID int NOT NULL ,
6> UnitPrice money NOT NULL DEFAULT (0),
7> Quantity smallint NOT NULL DEFAULT (1),
8> Discount real NOT NULL DEFAULT (0)
9> )
10> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE VIEW CustomerOrders_vw
4> AS
5> SELECT cu.rupanyName,
6> o.OrderID,
7> o.OrderDate,
8> od.ProductID,
9> p.ProductName,
10> od.Quantity,
11> od.UnitPrice,
12> od.Quantity * Od.UnitPrice AS ExtendedPrice
13> FROM Customers AS cu
14> INNER JOIN Orders AS o
15> ON cu.CustomerID = o.CustomerID
16> INNER JOIN OrderDetails AS od
17> ON o.OrderID = od.OrderID
18> INNER JOIN Products AS p
19> ON od.ProductID = p.ProductID
20> GO
1> SELECT CompanyName, ExtendedPrice
2> FROM CustomerOrders_vw
3> WHERE OrderDate = "9/3/1996"
4> GO
CompanyName ExtendedPrice
---------------------------------------- ---------------------
(0 rows affected)
1>
2> drop VIEW CustomerOrders_vw;
3> GO
1> drop table Customers;
2> drop table orders;
3> drop table orderdetails;
4> drop table Products;
5> GO</source>

== Query that Returns Result Row Numbers, Starting with 1 and Incrementing by 3==



<source lang="sql">
4>
5> CREATE TABLE MyTable (
6> key_col int NOT NULL IDENTITY (1,1),
7> abc char(1) NOT NULL
8> )
9> INSERT INTO MyTable VALUES ("a")
10> INSERT INTO MyTable VALUES ("b")
11> INSERT INTO MyTable VALUES ("c")
12> SELECT * FROM MyTable ORDER BY key_col
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1>
2> SELECT
3> 1 +
4> 3 *
5> (SELECT
6> count(*)
7> FROM
8> MyTable AS T2
9> WHERE
10> T2.abc < T1.abc) AS rownum,
11> abc
12> FROM
13> MyTable AS T1
14> ORDER BY
15> abc
16>
17> drop table MyTable
18> GO
rownum abc
----------- ---
1 a
4 b
7 c
(3 rows affected)
1></source>

== Retrieving the Maximum Value of key_col from MyTable==



<source lang="sql">
5> CREATE TABLE MyTable (
6> key_col int NOT NULL IDENTITY (1,1),
7> abc char(1) NOT NULL
8> )
9> INSERT INTO MyTable VALUES ("a")
10> INSERT INTO MyTable VALUES ("b")
11> INSERT INTO MyTable VALUES ("c")
12> SELECT * FROM MyTable ORDER BY key_col
13>
14>
15> SELECT
16> MAX (key_col) AS max_key_col
17> FROM
18> MyTable
19>
20> drop table MyTable
21> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
max_key_col
-----------
3
(1 rows affected)
1></source>

== Saving the @@IDENTITY Value in a Variable==



<source lang="sql">
4>
5> CREATE TABLE MyTable (
6> key_col int NOT NULL IDENTITY (1,1),
7> abc char(1) NOT NULL
8> )
9> INSERT INTO MyTable VALUES ("a")
10> INSERT INTO MyTable VALUES ("b")
11> INSERT INTO MyTable VALUES ("c")
12> SELECT * FROM MyTable ORDER BY key_col
13>
14>
15> DECLARE @mylastident AS int
16> SET @mylastident = @@IDENTITY
17> PRINT @mylastident
18>
19> drop table MyTable
20> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
3
1></source>

== SELECT Statement and IDENTITY Property==



<source lang="sql">
The IDENTITYCOL variable can be used instead of the name of the column with the IDENTITY property.
8> CREATE TABLE product
9> (product_no INTEGER IDENTITY(10000,1) NOT NULL,
10> product_name CHAR(30) NOT NULL,
11> price MONEY)
12> GO
1>
2> SELECT IDENTITYCOL
3> FROM product
4> WHERE product_name = "Soap"
5> GO
product_no
-----------
(0 rows affected)
1> drop table product;
2> GO</source>

== SQL Server provides the @@IDENTITY global variable to hold that value.==



<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL
5> , MyDescription nVarChar(50) NOT NULL)
6>
7> INSERT MyTable (MyDescription) VALUES ("Auto Record 1")
8> INSERT MyTable (MyDescription) VALUES ("Auto Record 2")
9>
10> SET IDENTITY_INSERT MyTable ON
11>
12> INSERT MyTable (MyID, MyDescription) VALUES (5, "Manual Record 1")
13>
14> SET IDENTITY_INSERT MyTable OFF
15>
16> INSERT MyTable (MyDescription) VALUES ("Auto Record 3")
17>
18> SELECT * FROM MyTable
19> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
MyID MyDescription
----------- --------------------------------------------------
1 Auto Record 1
11 Auto Record 2
5 Manual Record 1
21 Auto Record 3
(4 rows affected)
1>
2> SELECT @@IDENTITY AS LastIdentity
3> GO
LastIdentity
----------------------------------------
21
(1 rows affected)
1> drop table MyTable;
2> GO</source>

== SQL Server will choose the highest number as its current seed for a positive increment value or the lowest for a negative increment value.==



<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL
5> , MyDescription nVarChar(50) NOT NULL)
6>
7> INSERT MyTable (MyDescription) VALUES ("Auto Record 1")
8> INSERT MyTable (MyDescription) VALUES ("Auto Record 2")
9>
10> SET IDENTITY_INSERT MyTable ON
11>
12> INSERT MyTable (MyID, MyDescription) VALUES (5, "Manual Record 1")
13>
14> SET IDENTITY_INSERT MyTable OFF
15>
16> INSERT MyTable (MyDescription) VALUES ("Auto Record 3")
17>
18> SELECT * FROM MyTable
19> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
MyID MyDescription
----------- --------------------------------------------------
1 Auto Record 1
11 Auto Record 2
5 Manual Record 1
21 Auto Record 3
(4 rows affected)
1>
2> drop table MyTable;
3> GO</source>

== Storing the Results in a Temporary Table Using the IDENTITY() Function==



<source lang="sql">
4>
5> CREATE TABLE MyTable (
6> key_col int NOT NULL IDENTITY (1,1),
7> abc char(1) NOT NULL
8> )
9> INSERT INTO MyTable VALUES ("a")
10> INSERT INTO MyTable VALUES ("b")
11> INSERT INTO MyTable VALUES ("c")
12> SELECT * FROM MyTable ORDER BY key_col
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1> SELECT
2> IDENTITY (int , 1, 1) AS rownum,
3> abc
4> INTO
5> #temp
6> FROM
7> MyTable
8> ORDER BY
9> abc
10>
11> --Retrieving the Results of Using the IDENTITY() Function from the Temporary Table
12> SELECT
13> *
14> FROM
15> #temp
16> ORDER BY
17> Abc
18>
19> drop table MyTable
20> GO
(3 rows affected)
rownum abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1></source>

== Supported data types for the IDENTITY property are TinyInt, SmallInt Int, BigInt, Decimal, and Numeric.==



<source lang="sql">
8>
9> CREATE TABLE MyTable (
10> MyID Int IDENTITY(1000000, -100) NOT NULL
11> ,MyDescription NVarChar(50) NOT NULL )
12> GO
1>
2> drop table MyTable;
3> GO</source>

== Syntax for Using the IDENTITY() Function==



<source lang="sql">
The IDENTITY() function is used in a SELECT INTO statement to generate a result column with automatically generated values.
The IDENTITY() function works in the same way that the IDENTITY property generates them when you use it in a table.

SELECT
IDENTITY(<data_type> [, <seed>, <increment>]) AS column_name,
<other_columns>
INTO
<new_table_name>
FROM
<table_name>
WHERE
<search_criteria>

Query that Returns Result Row Numbers, Starting with 1 and Incremented by 1
22>
23> CREATE TABLE MyTable (
24> key_col int NOT NULL IDENTITY (1,1),
25> abc char(1) NOT NULL
26> )
27> INSERT INTO MyTable VALUES ("a")
28> INSERT INTO MyTable VALUES ("b")
29> INSERT INTO MyTable VALUES ("c")
30> SELECT * FROM MyTable ORDER BY key_col
31> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1>
2> SELECT
3> (SELECT
4> COUNT (*)
5> FROM
6> MyTable AS T2
7> WHERE
8> T2.abc <= T1.abc) AS rownum,
9> abc
10> FROM
11> MyTable AS T1
12> ORDER BY
13> abc
14>
15> drop table MyTable
16> GO
rownum abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
1></source>

== The IDENTITY property must be temporarily turned off when inserting a specific value.==



<source lang="sql">
9> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL
10> ,MyDescription NVarChar(50) NOT NULL)
11> GO
1>
2> SET IDENTITY_INSERT MyTable ON
3>
4> INSERT MyTable (MyID, MyDescription)
5> VALUES (5, "This will work")
6>
7> SET IDENTITY_INSERT MyTable OFF
8>
9> select * from MyTable;
10> GO
(1 rows affected)
MyID MyDescription
----------- --------------------------------------------------
5 This will work
(1 rows affected)
1>
2> drop table MyTable;
3> GO</source>

== Using the IDENTITY Property and the IDENTITY() Function==



<source lang="sql">
The IDENTITY property enables you to assign an auto-number to a numeric column.
Values for a column with an IDENTITY property are generated automatically.
The IDENTITY column must be of datatype int, bigint, smallint, tinyint, decimal, or numeric with a scale of 0.
It must be non-NULLable.</source>

SQL Server/T-SQL Tutorial/Sequence Indentity/Identity - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)