Delen via

sp_tableoption (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Stelt optiewaarden in voor door de gebruiker gedefinieerde tabellen. sp_tableoption kan worden gebruikt om het gedrag in de rij van tabellen te regelen met varchar(max),nvarchar(max),varbinary(max),xml, text, ntext, image of grote door de gebruiker gedefinieerde typekolommen.

Belangrijk

De tekst in rij-functie zal in een toekomstige versie van SQL Server worden verwijderd. Om data met grote waarden op te slaan, raden we aan om de datatypes varchar(max),nvarchar(max) en varbinary(max) te gebruiken.

Transact-SQL syntaxis-conventies

Syntaxis

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Arguments

[ @TableNamePattern = ] N'TableNamePattern'

De gekwalificeerde of niet-gekwalificeerde naam van een door de gebruiker gedefinieerde databasetabel. @TableNamePattern is nvarchar(776), zonder standaard. Als een volledig gekwalificeerde tabelnaam, inclusief een databasenaam, wordt opgegeven, moet de databasenaam de naam zijn van de huidige database. Tafelopties voor meerdere tabellen kunnen niet tegelijk worden ingesteld.

[ @OptionName = ] 'OptionName'

Een naam van een tafeloptie. @OptionName is varchar(35) en kan een van de volgende waarden zijn.

Waarde Description
table lock on bulk load Wanneer uitgeschakeld (de standaard), zorgt het ervoor dat het bulkload proces op door de gebruiker gedefinieerde tabellen rijvergrendelingen krijgt. Wanneer ingeschakeld, zorgt het ervoor dat bulkbelastingprocessen op door gebruikers gedefinieerde tabellen een bulk update-lock krijgen.
insert row lock Niet meer ondersteund.

Deze optie heeft geen effect op het vergrendelingsgedrag van SQL Server en is alleen inbegrepen voor compatibiliteit van bestaande scripts en procedures.
text in row Wanneer OFF of 0 (uitgeschakeld, de standaard), verandert het huidige gedrag niet, en er is geen BLOB in een rij.

Wanneer gespecificeerd en @OptionValue is ON ingeschakeld of een geheel getal van 24 via 7000, worden nieuwe tekst, ntext of afbeeldingsreeksen direct opgeslagen in de datarij. Alle bestaande BLOB (binary large object: text, ntext or image) gegevens worden omgezet in tekst in rijformaat wanneer de BLOB-waarde wordt bijgewerkt. Zie Opmerkingen voor meer informatie.
large value types out of row 1 = varchar(max), nvarchar(max), varbinary(max), xml en grote door de gebruiker gedefinieerde type (UDT) kolommen in de tabel worden buiten de rij opgeslagen, met een 16-byte pointer naar de wortel.

0 = varchar(max),nvarchar(max),varbinary(max),xml en grote UDT-waarden worden direct opgeslagen in de datarij, tot een limiet van 8.000 bytes en zolang de waarde in het record past. Als de waarde niet in het record past, wordt een pointer in de rij opgeslagen en de rest buiten de rij in de LOB-opslagruimte. 0 is de standaardwaarde.

Groot door de gebruiker gedefinieerd type (UDT) is van toepassing op: SQL Server 2008 (10.0.x) en later.

Gebruik de TEXTIMAGE_ON optie CREATE TABLE om een locatie aan te geven voor opslag van grote datatypen.
vardecimale opslagformaat van toepassing op: SQL Server 2008 (10.0.x) en hoger.

Wanneer TRUE, ON, of 1, is de aangewezen tabel ingeschakeld voor het vardecimale opslagformaat. Wanneer FALSE, OFF, of 0, is de tabel niet ingeschakeld voor vardecimale opslagformaat. Het vardecimale opslagformaat kan alleen worden ingeschakeld wanneer de database is ingeschakeld voor het vardecimale opslagformaat door gebruik te maken van sp_db_vardecimal_storage_format. In SQL Server 2008 (10.0.x) en later is het vardecimale opslagformaat verouderd. Gebruik ROW in plaats daarvan compressie. Zie Gegevenscompressie voor meer informatie. 0 is de standaardwaarde.

[ @OptionValue = ] 'OptionValue'

Specificeert of de @OptionName is ingeschakeld (TRUE, ON, of 1) of uitgeschakeld (FALSE, OFF, , of 0). @OptionValue is varchar(12), zonder standaard. @OptionValue is niet hoofdlettergevoelig.

Voor de optie tekst in rij zijn 0geldige optiewaarden , ON, OFF, of een geheel getal van tot 24 .7000 Wanneer @OptionValue , ONstandaard 256 bytes is, is de limiet standaard.

Codewaarden retourneren

0 (succes) of foutnummer (mislukt).

Opmerkingen

sp_tableoption kan alleen worden gebruikt om optiewaarden in te stellen voor door de gebruiker gedefinieerde tabellen. Om tabeleigenschappen weer te geven, gebruik je OBJECTPROPERTY of query sys.tables.

De optie sp_tableoption tekst in rij kan alleen worden ingeschakeld of uitgeschakeld op tabellen met tekstkolommen. Als de tabel geen tekstkolom heeft, geeft SQL Server een foutmelding.

Wanneer de optie tekst in rij is ingeschakeld, stelt de parameter @OptionValue gebruikers in staat de maximale grootte aan te geven die in een rij voor een BLOB moet worden opgeslagen. De standaard is 256 bytes, maar waarden kunnen variëren van 24 tot 7000 bytes.

Tekst-, ntext- of afbeeldingsstrings worden opgeslagen in de data-rij als de volgende voorwaarden van toepassing zijn:

  • Text in rij is ingeschakeld.
  • De lengte van de snaar is korter dan de limiet die in @OptionValue is gespecificeerd.
  • Er is genoeg ruimte beschikbaar in de datarij.

Wanneer BLOB-strings in de datarij worden opgeslagen, kan het lezen en schrijven van tekst-, ntext- of afbeeldingsstrings net zo snel zijn als het lezen of schrijven van teken- en binaire strings. SQL Server hoeft geen aparte pagina's te openen om de BLOB-string te lezen of te schrijven.

Als een tekst-, ntext- of afbeeldingsstring groter is dan de gespecificeerde limiet of de beschikbare ruimte in de rij, worden pointers in de rij opgeslagen. De voorwaarden voor het opslaan van de BLOB-strings in de rij gelden desalniettemin: Er moet voldoende ruimte zijn in de data-rij om de pointers vast te houden.

BLOB-strings en pointers die in de rij van een tabel zijn opgeslagen, worden op vergelijkbare wijze behandeld als strings van variabele lengte. SQL Server gebruikt alleen het aantal bytes dat nodig is om de string of pointer op te slaan.

Bestaande BLOB-strings worden niet direct omgezet wanneer text in row voor het eerst wordt ingeschakeld. De strings worden alleen geconverteerd wanneer ze worden bijgewerkt. Evenzo, wanneer de limiet voor de tekst in rij wordt verhoogd, worden de tekst-, ntext- of afbeeldingsstrings die al in de data-rij zitten pas omgezet om aan de nieuwe limiet te voldoen zodra ze worden bijgewerkt.

Opmerking

Het uitschakelen van de tekst-in-rij-optie of het verlagen van de limiet van de optie vereist de conversie van alle BLOBs; daarom kan het proces lang duren, afhankelijk van het aantal BLOB-strings dat moet worden omgezet. De tabel wordt vergrendeld tijdens het conversieproces.

Een tabelvariabele, inclusief een functie die een tabelvariabele teruggeeft, heeft automatisch de optie tekst in rij ingeschakeld met een standaard inlinelimiet van 256. Deze optie kan niet worden gewijzigd.

De optie tekst in rij ondersteunt de functies TEXTPTR, WRITETEXT, UPDATETEXT en READTEXT. Gebruikers kunnen delen van een BLOB lezen met de SUBSTRING()-functie, maar moeten onthouden dat tekstpointers in rij andere duur- en aantallimieten hebben dan andere tekstpointers.

Om een tabel van vardecimale opslag terug te veranderen naar het normale decimale opslagformaat, moet de database zich in het SIMPLE herstelmodel bevinden. Het wijzigen van het herstelmodel zal de logketen breken voor back-updoeleinden, daarom moet je een volledige database-back-up maken nadat je het vardecimale opslagformaat uit een tabel hebt verwijderd.

Als je een bestaande LOB-datatypekolom (tekst, ntext of afbeelding) converteert naar kleine tot middelgrote grote waardetypen (varchar(max),nvarchar(max) of varbinary(max)), en de meeste statements verwijzen niet naar de kolommen met grote waardetypes in je omgeving, overweeg dan om large_value_types_out_of_row te wijzigen naar 1 om optimale prestaties te behalen. Wanneer de waarde van de large_value_types_out_of_row optie wordt gewijzigd, worden bestaande varchar(max), nvarchar(max), varbinary(max) en xml-waarden niet direct omgezet. De opslag van de snaren wordt aangepast naarmate ze later worden bijgewerkt. Alle nieuwe waarden die in een tabel worden ingevoegd, worden opgeslagen volgens de tabeloptie die van kracht is. Voor directe resultaten maakt u ofwel een kopie van de data en vult u de tabel opnieuw aan na het wijzigen van de large_value_types_out_of_row-instelling , of werkt u elke kolom van kleine tot middelgrote grote waarden bij zodat de opslag van de strings wordt aangepast met de tabeloptie in werking. Overweeg om de indexen in de tabel na de update of herpopulatie opnieuw op te bouwen om de tabel te verkleinen.

Permissions

Om uit te voeren sp_tableoption is toestemming op de tabel nodig ALTER .

Voorbeelden

Eén. Sla XML-gegevens op uit de rij

Het volgende voorbeeld specificeert dat de xml-gegevens in de tabel buiten de HumanResources.JobCandidate rij moeten worden opgeslagen.

USE AdventureWorks2022;
GO

EXECUTE sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Schakel het vardecimale opslagformaat in op een tabel in

Het volgende voorbeeld wijzigt de Production.WorkOrderRouting tabel om het decimale datatype op te slaan in het vardecimale opslagformaat.

USE master;
GO

-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXECUTE sp_db_vardecimal_storage_format 'AdventureWorks2022', 'ON';
GO

USE AdventureWorks2022;
GO

EXECUTE sp_tableoption 'Production.WorkOrderRouting', 'vardecimal storage format', 'ON';

Verwante inhoud

  • Last updated on