november 2013 havi bejegyzések

SELECT…INTO és a NOT NULL

Régi problémám volt, hogy amikor egy új táblát készítettem SELECT … INTO-val, akkor nem tudtam növekvő számsoros primary key-t létrehozni.
Mutatom a(z ex)problémát:

use tempdb
go

if object_id('new_table') is not null
    drop table new_table
go

select top 100000
    row_number() over(order by (select null)) as id,
    newid() as name
into new_table
from sys.columns c
cross join sys.columns c2
go

alter table new_table add constraint PK_new_table primary key clustered (id)

A hibaüzenet pedig:

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘new_table’.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Mivel az „id” mező megengedi  a NULL értéket, ezért nem lehet primary key-nek megtenni, de ellenőrzzük a következő lekérdezéssel:

select
    TABLE_NAME, COLUMN_NAME, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.columns
where TABLE_NAME = 'new_table'

Eredmény:
select_into_01

Aztán történt egyszer, hogy nagyon benne voltam a google-zásban egy másik dolog kapcsán, és belefutottam az identity függvénybe, ami már SQL Server 2000-től létezik. Gyorsan utána néztem, hogy mit tud. Ez lett a megoldás a problémára.

if object_id('new_table') is not null
	drop table new_table
go

select top 100000
	identity(int, 1, 1) as id,
	newid() as name
into new_table
from sys.columns c
cross join sys.columns c2
go

alter table new_table add constraint PK_new_table primary key clustered (id)

Eredmény:
select_into_02

A feladat teljesítve, de miért volt ez olyan fontos?
Mert vannak esetek amikor a SELECT…INTO sokkal gyorsabb, mint egy INSERT…SELECT.
Vessük össze 10.000.000 sorral a két scriptet, itt a másik.

if object_id('new_table') is not null
	drop table new_table
go

create table new_table (
    id int not null primary key clustered identity(1,1),
    name uniqueidentifier null)
go

insert into new_table (name)
select top 10000000
	newid() as name
from sys.columns c
cross join sys.columns c2
cross join sys.columns c3
go

És ha már így benne vagyok, még egy dolgot megosztanék. A name mező a SELECT…INTO-val  NULL-osként jön létre, lásd az első resultset képe feljebb. Ha azt is NOT NULL-al szeretnénk deffiniálni, akkor SQL 2012-től van rá biztos módszer, a concat függvénybe kell tenni az értéket.

if object_id('new_table') is not null
	drop table new_table
go

select top 100000
	identity(int, 1, 1) as id,
	concat(newid(),'') as name
into new_table
from sys.columns c
cross join sys.columns c2
cross join sys.columns c3
go

Eredmény:
select_into_03

Így már a name mező is NOT NULL tulajdonsággal bír, viszont előkerült egy másik probléma:
A name mező hossza 41, holott egy uniqueidentifier stabilan 36 hosszú. Ez soronként 5 byte plusz a táblában, ami sok sornál sok hely. Ez is orvosolható:

if object_id('new_table') is not null
	drop table new_table
go

select top 100
	identity(int, 1, 1) as id,
	concat(cast(newid() as varchar(36)),null) as name
into new_table
from sys.columns c
cross join sys.columns c2
cross join sys.columns c3
go

Eredmény:
select_into_04

Voila, készen is vagyunk.