T-SQL-ohjelmointi

SQL Server tarjoaa mahdollisuuden kirjoittaa rekursiivisia kyselyitä Common Table Expression-rakenteen avulla. Sinäsä CTE ei ole mitenkään SQL Server-spesifinen – sama rakenne on periaatteessa käytettävissä myös mm. Oraclen tietokannassa. Rekursioiden määrä on defaultista rajoitettu SQL Serverin CTE:ssa sataan:

--menee läpi
DECLARE @LOOP INT = 100
;WITH cte_numero
AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM cte_numero WHERE n < @LOOP
)
SELECT n as numero
FROM cte_numero
GO

--tuottaa virheen
DECLARE @LOOP INT = 101
;WITH cte_numero
AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM cte_numero WHERE n < @LOOP
)
SELECT n as numero
FROM cte_numero
GO

Rekursioiden määrää voi lisätä lisäämäällä CTE:n loppuun OPTION(MAXRECURSION):

DECLARE @LOOP INT = 101
;WITH cte_numero
AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM cte_numero WHERE n < @LOOP
)
SELECT n as numero
FROM cte_numero
OPTION(MAXRECURSION 1000)
GO

Jos kyselyn loppuun laittaa määreen OPTION (MAXRECURSION 0), ei rekursion määrää ole rajoitettu, mutta se voi johtaa infinite luuppiin. Huomaa siis Microsoftin sivulta huomio:

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).

SQL Server 2012:ta (entiseltä nimeltään Denali) on nyt julkaistu ensimmäinen Release Candidate-versio RC0 ja se on ladattavissa Microsoftin sivuilta. Samalla sivulla on lyhyt yhteenveto uusista ominaisuuksista:

• Deliver required uptime and data protection with AlwaysOn
• Gain breakthrough & predictable performance with ColumnStore Index
• Help enable security and compliance with new User-defined Roles and Default Schema for Groups
• Enable rapid data discovery for deeper insights across the organization with ColumnStore Index
• Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
• Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Data Tools for a unified dev experience across database, BI, and cloud functions

SQL Server 2012:n lisenssoinnista on alkanut näkyä myös tietoa verkossa. Microsoft on siirtymässä CPU pohjaisesta lisenssoinista CPU Core-pohjaiseen lisenssointimallin (Server+CAL on tietysti myös edelleen mahdollinen). Myös eri versioiden määrä tippuu kuudesta neljään: Express, Standard, BI, Enterprise. BI on uusi lisä tähän joukkoon ja vaikuttaa erittäin mielenkiintoiselta. BI sisältää samat ominaisuudet kuin Standard, mutta sisältää myös jotain lisää. Se on lisensointu Server+CAL pohjaisesti. BI lisenssi mahdollistaa 20 CPU Corea tietokantapalvelimelle. BI sisältää seuraavat featuret:

• Data Quality Services
• Master Data Services
• Power View
• Power Pivot for SharePoint Services
• Semantic Model
• Advanced Analytics

Ohessa lisenssointiin liittyviä blogikirjoituksia:

SQL Server Licensing Changes

New Licensing for SQL Server 2012

SQL Server 2012 Licensing and Hardware Considerations

Tietovaraston tuotantokäyttöönottoon liittyvästä testauksesta on kohtalaisen vähän materiaalia olemassa tai tieto hajallaan kirjallisuudessa. Ohessa kuitenkin hyvä koonti tietovaraston testaamiseen liittyvistä asioista. Pääkohdat tässä lainattuna artikkelista:

• Data completeness. Ensures that all expected data is loaded.
• Data transformation. Ensures that all data is transformed correctly according to business rules and/or design specifications.
• Data quality. Ensures that the ETL application correctly rejects, substitutes default values, corrects or ignores and reports invalid data.
• Performance and scalability. Ensures that data loads and queries perform within expected time frames and that the technical architecture is scalable.
• Integration testing. Ensures that the ETL process functions well with other upstream and downstream processes.
• User-acceptance testing. Ensures the solution meets users’ current expectations and anticipates their future expectations.
• Regression testing. Ensures existing functionality remains intact each time a new release of code is completed.

DW:n testaus on laaja ja monitahoinen asia, ja kaikkia näkökulmia ei varmaan missä toteutuksessa saa otettua täysin huomioon. On hyvä kuitenkin tunnistaa kokonaisuudet ja valita niistä tärkeimmät osa-alueet testauksen piiriin.

SQL Serverissä voidaan kannan varmuuskopioinnissä käyttää nk. backup device-kohdetta. Käytännössä device on vain looginen nimi kohteesta, jonne varmuuskopio otetaan. Käytännössä useinmiten backup otetaan levylle, mutta myös TO TAPE vaihtoehto on käytettävissä. Ohessa esimerkki backup devicen luonnista T-SQL komennoilla ja backupi ottamisesta:

USE master;
GO

--lisätään looginen device backuppia varten
EXECUTE master.dbo.sp_addumpdevice @devtype = N'disk', 
        @logicalname = N'Bck_Dvc_Testi', 
        @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALITESTI\MSSQL\Backup\Bck_Dvc_Testi.bak';
GO

--katsotaan, että mitä devicejä löytyy
SELECT TOP 100 *
FROM   sys.backup_devices;

--otetaan kantabackuppi
BACKUP DATABASE Testi TO  Bck_Dvc_Testi WITH NOFORMAT, 
        NOINIT,  
        NAME = N'Testi_backup_2011_10_20_191842_2754151', 
        SKIP, 
        REWIND, 
        NOUNLOAD, 
        COMPRESSION,  
        STATS = 10
GO
--selvyyden vuoksi backupin verifiointi ja kompressointi etc. on jätetty tästä pois...

Instanssin kantojen nimet saa tavallisesti ulos sys.databases katalookinäkymästä, toinen tapa on käyttää PowerShelliä komentoriviltä. Kokeileppa mitä tämä palauttaa omassa ympäristössäsi (kun vaihdat koneen nimen ja instanssin oikein)?

#Otetaan sql-ympäristö käyttöön
Add-PSSnapin *SQL*

#Palautetaan kantojen nimet
Get-ChildItem 'SQLSERVER:SQL\<kone>\<instanssi>\Databases' | Select-Object Name

SQL Serverin uusin versio, koodinimeltään Denali, sisältää useita uusia funktioita, kuten mm.

TRY_CONVERT
PARSE
TRY_PARSE
EOMONTH
%FROMPARTS
CHOOSE
IIF
CONCAT
FORMAT
LOG

SQL-guru Itzik Ben-Gan käy nämä funktiot läpi selkein esimerkein tuoreessa koontiartikkelissa. Näillä uusilla funktioilla on taatusti jatkossa käyttöä.

Kentän tietotyypin muuttaminen käy näin:

ALTER TABLE <taulu> COLUMN <kenttä> <uusi tietotyyppi>

Yksinkertaista.