70-461 – notes from ch. 2 – identity, newId, newSequentialId and sequences

imagep027_identity_ is set up as ‘int identity‘ in the create table

newid() is functionally similar but you get a 36 character field

newsequentialid is like newid but you can see a sequntial increase

sequences are like in Oracle

Identity

To use the automatic identity thing:

drop table aa.tax_dodgers;
create table aa.tax_dodgers
  (id1 int identity,
   name varchar(10));

insert into aa.tax_dodgers values ('Google'), ('Amazon'),
                                  ('Facebook'),('Vodafone'),
                                  ('Boots')

This gives

select * from aa.tax_dodgers

1   Google
2   Amazon
3   Facebook
4   Vodafone
5   Boots

If you ‘forget’ to include a datatype ((I didn’t actually forget…being an Oracle person, the identity thing is new to me)), as follows:

drop table aa.tax_dodgers;
create table aa.tax_dodgers
  (id1 identity,
   name varchar(10));

…then you get this

Msg 173, Level 15, State 1, Line 3
The definition for column 'id' must include a data type.

More:

Newid

Using newid() instead of the IDENTITY column can be done as follows:

drop table aa.tax_dodgers;
CREATE TABLE aa.tax_dodgers
(
  id1 UNIQUEIDENTIFIER not null
      DEFAULT NEWID (),
  name varchar(10)
);

insert into aa.tax_dodgers (name) values ('Google'), ('Amazon'), ('Facebook'),('Vodafone'), ('Boots')

….which looks like this:

947E043E-3D6B-4109-AE82-5F3FED0FCBEF    Google
3F679903-AAD5-45E5-B4D3-B27FC0B18162    Amazon
F81177F6-E8F8-48EB-A53A-5BD49B11665A    Facebook
52F7F3A4-DBA8-4F3F-BA55-0385ED672475    Vodafone
C33DF212-2A14-4146-A240-7A93E48C6C15    Boots

You can also just use newid() stand-alone, although I can’t think why you’d want to at the minute:

select NEWID()

01160CC4-58AE-46BF-82D1-839047F565FF

Note that you can’t do this:

insert into aa.tax_dodgers values ('Google'), ('Amazon'), ('Facebook'),('Vodafone'), ('Boots')

…when you’re using the newid, because you get

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

…although this seemed to work ok when using the identity column in the previous example

NewSequentialId()

The newSEQUENTIALid is syntactically much the same:

drop table aa.tax_dodgers;
CREATE TABLE aa.tax_dodgers
(
  id1 UNIQUEIDENTIFIER not null
      DEFAULT NEWsequentialID (),
  name varchar(10)
);

insert into aa.tax_dodgers (name) values ('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon')

select * from aa.tax_dodgers

….which gives

FAD37894-8796-E411-AAAA-000C29299BE8    Google
FBD37894-8796-E411-AAAA-000C29299BE8    Amazon
FCD37894-8796-E411-AAAA-000C29299BE8    Facebook
FDD37894-8796-E411-AAAA-000C29299BE8    Google
FED37894-8796-E411-AAAA-000C29299BE8    Amazon

It took me a few seconds to see what had changed here – it’s the second digit. It would be nice at some time to delve into how this is sequential…but I’m sure it is!

Sequences

As an Oracle-inflected chap, this is much more like it!


drop table aa.tax_dodgers; CREATE TABLE aa.tax_dodgers ( id1 int primary key, name varchar(10) ); create sequence aa.tax_dodgers_seq START WITH 100 INCREMENT BY 1 ; insert into aa.tax_dodgers (id1, name) values (NEXT VALUE FOR aa.tax_dodgers_seq, 'Google'), (NEXT VALUE FOR aa.tax_dodgers_seq, 'Amazon'), (NEXT VALUE FOR aa.tax_dodgers_seq, 'Facebook'), (NEXT VALUE FOR aa.tax_dodgers_seq, 'Google'), (NEXT VALUE FOR aa.tax_dodgers_seq, 'Amazon') select * from aa.tax_dodgers

…. gives

100 Google
101 Amazon
102 Facebook
103 Google
104 Amazon

You can also do this:

drop table aa.tax_dodgers;
CREATE TABLE aa.tax_dodgers
(
  id1 int primary key default NEXT VALUE FOR aa.tax_dodgers_seq,
  name varchar(10)
);

…and tbh I’m not sure whether you can do the same thing in Oracle or not. If you can, why wouldn’t you?

Advertisements