De IDENTITY column property en het sequence object zijn beide manieren om automatisch een nummer te laten genereren. De IDENTITY property bestaat al lang, terwijl het sequence objectin Sql Server 2012 is geïntroduceerd,
Using the IDENTITY column property
IDENTITY is een eigenschap van een kolom. De eigenschap voorziet de kolom automatisch van een waarde als een rij wordt aangemaakt. Voor een IDENTITY kan een increment en een seed worden opgegeven. Increment: Hoeveel moet de vorige waarde van IDENTITY worden verhoogd. Seed: de beginwaarde van de IDENTITY kolom.
T-Sql biedt drie maniere om de laatste identity waarde op te halen:
- De SCOPE_IDENTITY functie geeft de laatst gegenereerde identity waarde terug van de sessie in de huidige scope
- De @@IDENTITY functie geeft de laatst gegenereerde identity waarde terug, ongeacht de scope
- De IDENT_CURRENT functie heeft een tabel als input en geeft de laatst gegenereerde identity waarde van deze tabel.
Het verschil tussen @@IDENTITY en SCOPE_IDENTITY
- Een INSERT zorgt er voor dat een nieuwe identity waarde wordt gegenereerd;
- Een aanroep van SP1 doet ook een INSERT, wat wederom een nieuwe identity waarde genereerd;
- SCOPE_IDENTITY: zal de waarde van de eerste INSERT statement teruggeven (zelfde sessie en scope)
- @@IDENTITY: zal de gegenereerde identity waarde van SP1 teruggeven (zelfde sessie, scope onafhankelijk).
Bij een TRUNCATE wordt de seed weer op 1 gezet.
Om de seed weer met 1 te laten beginnen kan de volgende command worden uitgevoerd:
DBCC CHECKIDENT('Sales.MyOrders', RESEED, 4);
Standaard biedt IDENTITY geen uniekheid. Dit kan enkel bewerkstelligd worden door van de kolom een PK te maken of een UNIQUE constraint.
Ook kunnen er gaten onstaan tussen de waarden. Als een rij in het midden van de tabel wordt verwijderd, wordt de bijbehorende identity waarde niet hergebruikt. Dit gebeurt ook als een INSERT statement niet lukt, de waarde wordt dan niet teruggedraaid.
Als bij de IDENTITY het maximaal aantal te gebruiken nummers is behaald, dan kunnen er geen nieuwe rijen meer worden toegevoegd, de INSERT zal falen.
Using the sequence object
In Sql Server 2012 is het sequence object geïntroduceerd. In tegenstelling tot de IDENTITY kolom eigenschap, is het sequence object een onafhankelijk object in de database. Verschillen tussen de IDENTITY kolom roperty en het sequence object zijn:
- De IDENTITY eigenschap is gebonden aan een kolom van een tabel
- Soms is het nodig om over meerdere tabellen een unieke sleutel te definiëren, dit kan niet met de IDENTITY kolom property
- Soms is het nodig dat eerst de waarde wordt gegenereerd voordat deze wordt gebruikt. Met IDENTITY moet eerst een rij worden aangemaakt voordat de waarde bekend is.
- Een IDENTITY-waarde kan niet worden bijgewerkt
- IDENTITY ondersteund geen cycling
- Een TRUNCATE statement reset de identity waarde.
Het sequence object kent bovenstaande beperkingen niet. Een sequence is een database object en wordt als volgt aangemaakt:
CREATE SEQUENCE Sales.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
Als geen type wordt opgegeven wordt het type BIGINT gebruikt. Een type kan worden opgegeven met AS na de naam van de sequence. Sequence ondersteunt de volgende properties:
- INCREMENT BY: de increment waarde. De standaard is 1
- MINVALUE: De minimale waarde die ondersteunt wordt. De standaard is de minimale waarde van het type
- MAXVALUE: De maximale waarde die ondersteunt wordt. De standaard is de maximale waarde van het type:
- CYCLE of NO CYCLE: Definieert om de sequence te laten cyclen of niet
- START WITH: De sartwaarde van de sequence. Default is de minimale waarde van het type.
Met de NEXT VALUE FOR functie kan de volgende waarde worden opgevraagd van een sequence:
SELECT NEXT VALUE FOR Sales.SeqOrderIDs;
Alles kan van een sequence gewijzigd worden, behalve het data type. De huidige kan bijv. opnieuw gezet worden met het volgende statement:
ALTER SEQUENCE Sales.SeqOrderIDs
RESTART WITH 1;
Als een tabel geen gebruik maakt van de IDENTITY eignschap, maar van een sequence object, dan ziet een INSERT statement er als volgt uit:
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) VALUES
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 2, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 3, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 2, 2, '20120620');
De NEXT VALUE FOR functie kan ook gebruikt worden in een DEFAULT constraint. Op deze manier wordt automatisch de waarde opgeslagen als een nieuwe rij wordt toegevoegd:
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;
Het sequence object kent een caching optie die bepaalt hoe vaak de huidige sequence waarde naar schijf wordt geschreven of naar het geheugen. Een sequence met CACHE 100 schrijft eens per 100 wijzigingen naar de schijf. Sql Server houdt twee members in het geheugen, de ene houdt de huidige sequence waarde vast en de andere hoeveel waarden er nog over zijn. Hij schrijft dus maar 100 keer naar het geheugen en als de waarden op zijn, dan schrijft hij de 100 waarden naar schijf. Het voordeel hiervan is performance voor het toekennen van de sequence waarden. Het risico is dat een range van waarden verloren gaat als de service onverwachts wordt afgesloten.
Net als IDENTITY kunnen er gaten onstaan in de nummering bij en sequence object. Dit kan gebeuren als een transactie faalt waarin een gebruik gemaakt wordt van een sequence object. De verandering van de waarde van het sequence object wordt dan niet ongedaan gemaakt.
Er is een groot performance verschil tussen het gebruik van CACHE en NO CACHE. Met NO CACHE moet Sql Server voor elk request een nieuwe sequence waarde naar de schijf schrijven. In 2015 is de standaard cache waarde 50.
Met de SP sp_sequence_get_range kan een range van sequence waarden worden toegewezen van een bepaalde grootte. De @range_size parameter bevat de grootte range en de output parameter @range_first_value bevat de eerste waarde van die range.
De view sys.sequences kan gebruikt worden om de eigenschappen van sequences op te vragen.
Les 2
Met het MERGE statement kan data worden samengevoegd van een brontabel nar een doeltabel.
Using the MERGE statement
Voorbeeld van MERGE statement:
DECLARE
@orderid AS INT = 1,
@custid AS INT = 1,
@empid AS INT = 2,
@orderdate AS DATE = '20120620';
MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT
USING (VALUES(@orderid, @custid, @empid, @orderdate))
- MERGE INTO : Definieer de doeltabel. Kan gebruik maken van een alias
- USING : Defiieer de brontabel. Kan van een alias gebruik maken. De USING clause is hetzelfde ontworpen als de FROM clause van een SELECT statement. Er kan dus gebruik gemaakt worden van joins, derived tables, CTEs of een table function zoals OPENROWSET. De output van de USING cluase wordt beschouwd als de bron van de merge operatie.
- ON : In deze clause wordt de predicate bepaald die de rijen matched tussen bron- en de doeltabellen.
- WHEN MATCHED [AND ] THEN : Bepaalt wat er moet gebeuren (action) als een rij uit de brontabel matched met een rij uit de doeltabel. Omdat een rij in de doeltabel reeds bestaat kan het INSERT statement niet gebruikt worden. Alleen de statements UPDATE en DELETE zijn toegestaan. Als verschillende acties nodig zijn bij rijen die matchen, dan kunnen er meerdere WHEN MATCHED clauses worden gebruikt, welke met een andere predicate.
- WHEN NOT MATCHED [BY TARGET] [AND ] THEN : Bepaalt wat er moet gebeuren als er geen matches zijn. Omdat de doeltabel geen overeenkomende rij bevat is enkel de INSERT statement toegestaan.
- WHEN NOT MATCHED BY SOURCE [AND ] THEN: Bepaalt wat er moet gebeuren als een rij uit de doeltabel niet voorkomt in de brontabel. Omdat de rij reeds bestaat, is de INSERT statement niet geldig, maar wel een UPDATE of DELETE.
Onderstaand een voorbeeld als de doeltabel een rij heeft die niet voorkomt in de brontabel. Deze rijen zullen worden verwijderd:
T-Sql kent een OUTPUT cluase voor modification statements, welke gebruikt kan worden om informatie terug te geven van de aangepaste rijen.
Working with the OUTPUT clause
De OUTPUT is in gebruik gelijkwaardig als de SELECT clause in die zin dan expressies gespecificeerd kunnen worden en hieraan aliasses kunnen worden gekoppeld. Een verschil met de SELECT clause is dat bij de OUTPUT clause de kolomnamen een prefix moeten krijgen: inserted of deleted.
INSERT with OUTPUT
De OUTPUT clause kan gebruikt worden in een INSERT statement om informatie terug te geven over de ingevoerde rijen.
INSERT INTO Sales.MyOrders(custid, empid, orderdate)
Bij een UPDATE verwijdt de deleted prefix naar de originele waarde en de inserted prefix naar de nieuwe waarde.
MERGE with OUTPUT
De $action function in een MERGE statement geeft aan welke actie er is uitgevoerd. De geeft doet dit door een string waarde te retourneren: 'INSERT', 'UPDATE', 'DELETE'.
COALESCE(inserted.orderid, deleted.orderid) AS orderid;
Output:
the_action orderid
---------- -----------
INSERT 1
INSERT 5
UPDATE 2
UPDATE 3
Composable DML (Data Manipulation Language)
Met T-Sql kan iets gedefinieerd worden dat lijkt op een derived table wat gebaseerd is op een modification met een OUTPUT clause. Daar kan vervolgens een INSERT SELECT statement omheen worden gezet. Met de WHERE clause van deze outer SELECT kan vervolgens bepaald worden welk soort OUTPUT teruggegeven moet worden ('INSERT', 'UPDATE', 'DELETE'):
DECLARE @InsertedOrders AS TABLE
(
orderid INT NOT NULL PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
);
INSERT INTO @InsertedOrders(orderid, custid, empid, orderdate)
Bij het maken van een tabel in SQL Server 2008 heb je de mogelijkheid om een veld van het type varchar of nvarchar te voorzien. Wat is nu eigenlijk het verschil? Het verschil zit hem in het volgende: varchar Varchar heeft een maximum van 8000 tekens. Er wordt 1 byte per teken gereserveerd en wordt gebruikt om ASCII en ANSI in op te slaan. nvarchar Nvarchar neemt twee keer zoveel ruimte in als varchar. Dit komt omdat per teken twee bytes wordt gereserveerd en daarom kunnen er 4000 tekens worden opgeslagen. Dit type wordt gebruikt bij Unicode. Dus stel dat je een klantensite heb met invoerveleden waar wereldwijd gebruik van wordt gemaakt dan moet je gebruik maken van nvarchar. (n)char En dan is er ook nog (n)char. Deze string wordt opgevuld met spaties totdat de opgegeven lengte is bereikt.
Enkel even een code dump deze keer: public void Serialize(Configuration c) { XmlSerializer serializer = new XmlSerializer( c.GetType() ); StreamWriter streamwrite = new StreamWriter("config.xml"); serializer.Serialize(streamwrite, this); streamwrite.Close(); } public static Configuration Deserialize() { FileInfo file = new FileInfo("config.xml"); if (file.Exists) { StreamReader reader = new StreamReader("config.xml"); XmlSerializer ser = new XmlSerializer(typeof(Configuration)); Configuration c = (Configuration)ser.Deserialize(reader); reader.Close(); return c; } else return null; }
In IIS is het mogelijk om gebruik te maken van Basic Authentication. De werking van Basic Authentication (BA) is als volgt. 1) De browser maakt een request naar een pagina die BA gebruikt 2) IIS stuurt een 401 naar de browser met een 'WWW-Authenticate' header. Door deze header weet de browser dat er inloggegevens nodig zijn. 3) De browser toont een login-scherm waarin de gebruiker zijn credentials kan invoeren 4) De browser plakt de gebruikersnaam en wachtwoord aan elkaar met daartussen een dubbele punt (:). Vervolgens maakt hij hier een Base64-string van. Daarna maakt de browser een Authentication header aan met als waarde "Basic ", bijv: Authorization: Basic QXNwZWN0XHJzLjExNDE0Ny1saXZlOkZkNyoxWCV4 5) IIS decrypt de Base64-string en gaat na of de opgegeven gebruiker voldoende rechten heeft. Zo ja, dan stuurt IIS een response header terug met code 200, oftewel OK. Als de gebruiker onbekend is, wordt wederom het login-scherm getoond. Schematisch ziet dit er a...
Reacties
Een reactie posten