Märkused: kahjuks MSSQL 2005 natuke tekitas ka pettumust, indeksi max suurus(varchar/char) on 900 märki,nvarchar(450).

Südamest lootsin, et ehk jõutakse vähemalt 2048 märgini. Ebameeldiv on see, et varchar(1024) saab indeksit luua, aga kui sinna tabelisse lisada kirje,
mis on >=991 märki, siis seda kirjet ei sisestata.

Üks muudatus samuti, et enam "ametlikult" text/ntext muutujat ei toetata, nende asemel nüüd: varchar(MAX), nvarchar(MAX).


Kuna pidevalt suur kiire, siis hetkel pole aega formateerida, siia panen mõne vajaliku SQL koodi:

Asjad, mida minult küsitud:

* Kuidas teada saada kohaliku aja erinevust GMT ajast tundides?

select datediff(hh,GETUTCDATE(),getdate())

* Kuidas konverteerida ajaformaat stringi kujule dd.mm.yyyy või (odbc str )yyyy-mm-dd ?

select convert(char(10),getdate(),120),convert(char(10),getdate(),104)





* CTE, mis loom see on veel

WITH ajutineRez(sona) as

(

 select sona

 from @at

 where sona in ('jäätis','hea')

)

select * from ajutineRez

Milleks, CTE kasulik on...parim suurte päringute tükeldamiseks.




Näiteks:

select 'Töö telefon : '+telefon+'; Mobiil: '+mobiil+';' as telefonid

from mingitabel

Nüüd, kui telefon või mobiil sisaldab NULL väärtust, oleks selle kirjet kogu väärtuseks samuti NULL.

select 'Töö telefon : '+isnull(telefon,'puudub')+'; Mobiil: '+isnull(mobiil,'puudub')+';' as telefonid

from mingitabel

Nüüd, kui telefon puudub, on ka asendatakse NULL väärtus "puudub"; siin pole vaja ka enam karta, et kirje annaks tulemuseks NULL. Coalesce't antud juhul pole mõtet kasutada, kuigi sisult teeb ta sama.

Coalesce näited:

declare @muutuja varchar(15)

declare @muutuja1 varchar(15)

declare @muutuja2 varchar(15)

set @muutuja=null

set @muutuja1=null

set @muutuja2='OK'

select COALESCE(@muutuja,@muutuja1,@muutuja2)

Põhimõtteliselt käib antud juhul coalesce läbi kõik muutujad, kuni leiab esimese muutuja, mis ei ole tühi (NULL).

Kui oleksime soovinud, et telefoni numbri päring oleks tagastanud ühe numbritest, mis polnud tühi, tuleks teha järgnevalt:

select 'Telefon : '+coalesce(telefon,mobiil,'puudub') as telefon

from mingitabel

---

:::Tutvustan ka lühidalt mõningaid ühenduse spetsiifilisi SQL direktiive.



Loome esmalt ajutise tabeli

create table #ajutine(i int null)

insert into #ajutine(i) values (123)

insert into #ajutine(i) values (null)

SET ANSI_NULLS ON -- vaikimisi (jälgitakse SQL-92 standardit)

select * from #ajutine where i=NULL

select * from #ajutine where i<>NULL

SET ANSI_NULLS off

select * from #ajutine where i=NULL

select * from #ajutine where i<>NULL

Nüüd saate mõlemal juhul tulemuseks 1 kirje. Esimesel juhul kirje väärtuseks NULL ja teisel juhul 123.

MÄRKUS !

Igasugune standartidest kõrvale kaldumine võib tuua kaasa ebameeldivaid tagajärgi !

SET ANSI_NULLS off kasutada ainult väga äärmuslikes olukordades.

Kasutage ikka süntaksit :

select * from #ajutine where i IS NULL -- ehk on määramatus /väli tühi

select * from #ajutine where i IS NOT NULL -- ei sisalda määramatust

Kui soovite, et päringusse ikka tuleks ka NULL väärtusega välja; kasutage järgmis lahendust.

select * from #ajutine where isnull(i,0)<>100000

Kui Teil peaks tekkima olukord, kus võrreldakse kahte NULL väärtusega välja, kasutage lahendust

select * from #ajutine where isnull(i,0)=isnull(i,0)

SET ANSI_WARNINGS (ON|OFF)

Näite illustreerimiseks loome esmalt ajutise tabeli.

insert into #test(i) values (5)

insert into #test(i) values (145)

insert into #test(i) values (null)

update #test set i=i-1 või update #test set i=i+1

Kui Te ei soovi, et antud hoiatus tekiks, seadke SET ANSI_WARNINGS OFF

SET ANSI_PADDING (ON|OFF) -- vaike-väärtuseks soovitatakse SET ANSI_PADDING ON

ON  - antud juhul insert lause puhul täidetakse char välja vaba osa tühikutega ning ning varchar väljale sisestatava väärtuse lõpust tühikuid ei koristada ära.

OFF - char tüüpi välja vaba osa ei täideta tühikutega ja varchar väljale sisestatava väärtuse lõpp trimmitakse (tühikud eemaldatakse)

if object_id("tempdb..#test")!=0 drop table #test

create table #test

(

numbridv varchar(10),

numbridc char(10)

)

insert into #test(numbridv,numbridc) values('TEST  ','TEST')

select '['+numbridv+']','['+numbridc+']'

from #test

---

Tihti olen kohanud, et programmeerijad teostavad selle operatsiooni cursori abil, kuid on olemas palju lihtsam meetod.

CREATE TABLE laused (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[laused] [varchar] (25) NOT NULL ,

CONSTRAINT [PK_laused] PRIMARY KEY CLUSTERED

(

[id]

) ON [PRIMARY]

)

Set nocount on -- ütleme serverile, et ei tagastaks meile infot, mitu kirjet sisestati

insert into laused(laused) values('mina')

insert into laused(laused) values('olen')

insert into laused(laused) values('inimene')

declare @muutuja varchar(8000)

select @muutuja = COALESCE(@muutuja,'')+laused.laused +';'

from laused

where laused.laused is not null

select @muutuja

---

::: MSSQL server ja ajutised tabelid

Ajutisi tabeleid on kahte tüüpi:

1. Ühe trelliga #tabel1

2. Kahe trelliga ##tabel1

Ajutised tabelid on alati soovitav ära kustutada, kui Te enam neid ei kasuta. Antud reeglit peaks järgima eriti siis, kui ajutised tabelid sisaldavad palju kirjeid, sest muidu nad raiskavad mõtetult SQL serveri kettaressursi.

St. ka kahe trelliga tabel kustub, enam teised kasutajad seda ei näe.

Näited, ajutiste tabelite loomiseks:

create table #test1

(

id int not null,

automark varchar(45) not null

)

või siis

create table ##test1

(

id int not null,

automark varchar(45) not null

)

(teeme uue ajutise tabeli, kasutades olemas-oleva tabeli struktuuri)

select id,automark

into #test1 või vajadusel into ##test1

from koikautod

where 0=1

Märkus: ajutises tabelis jääb väli Id ikka Identity tüüpi (kuna põhitabelis on identity), kuid ta on kaotanud primary key constraindi !

Soovitav ka ajutise tabeli ID väli muuta Primary Key'ks.

Kuid tegelikkuses, me ei vajaks ajutises tabelis IDENTITY tüüpi ID välja, meile piisaks "tavaliselt" ID väljast.

select cast(null as int) as id,automark

into #test1 või vajadusel into ##test1

from koikautod

where 0=1

ALTER TABLE #test1 ADD PRIMARY KEY(id)



Nüüd liigume tutvustava osa juurest reaalsema näite poole;

MIKS üldse kasutada ajutisi tabeleid; näiteks on kolm tabelit; esimeses on 9 miljonit kirjet,

Näiteks tahaksime teada isikuid, kelle autoks on (VW või BMW) ning rahvus oleks türklane.

select k.automark,i.isikunimi,e.pusiaadress

from isikud i,koikautod k,elukoht e

where (i.rahvus="türklane")