Tipo di testo di SQL Server e tipo di dati varchar

Ho dati di carattere di lunghezza variabile e voglio memorizzare nel database di SQL Server (2005). Voglio imparare alcune best practice su come scegliere TEXT tipo SQL o scegliere VARCHAR tipo SQL, pro e contro in performance / footprint / funzione.

Se si utilizza SQL Server 2005 o versione successiva, utilizzare varchar(MAX) . Il tipo di dati di text è deprecato e non dovrebbe essere utilizzato per nuovi lavori di sviluppo. Dai documenti :

Importante

i tipi di dati ntext , text e image verranno rimossi in una versione futura di Microsoft SQL Server. Evitare l’uso di questi tipi di dati in un nuovo lavoro di sviluppo e pianificare di modificare le applicazioni che attualmente li utilizzano. Usa invece nvarchar (max) , varchar (max) e varbinary (max) .

TEXT è usato per grandi pezzi di dati di stringa. Se la lunghezza del campo supera una certa soglia, il testo viene memorizzato fuori riga.

VARCHAR è sempre archiviato in righe e ha un limite di 8000 caratteri. Se provi a creare un VARCHAR(x) , dove x> 8000 , ottieni un errore:

Server: messaggio 131, livello 15, stato 3, riga 1

La dimensione () data al tipo ‘varchar’ supera il massimo consentito per qualsiasi tipo di dati (8000)

Queste limitazioni di lunghezza non riguardano VARCHAR(MAX) in SQL Server 2005 , che può essere memorizzato fuori riga, proprio come TEXT .

Nota che MAX non è un tipo di costante qui, VARCHAR e VARCHAR(MAX) sono tipi molto diversi, essendo quest’ultimo molto vicino a TEXT .

Nelle versioni precedenti di SQL Server non si poteva accedere direttamente al TEXT , si poteva solo ottenere un TEXTPTR e usarlo nelle funzioni READTEXT e WRITETEXT .

In SQL Server 2005 è ansible accedere direttamente alle colonne TEXT (sebbene sia ancora necessario un cast esplicito su VARCHAR per assegnargli un valore).

TEXT è buono:

  • Se è necessario memorizzare testi di grandi dimensioni nel database
  • Se non si effettua una ricerca sul valore della colonna
  • Se selezioni raramente questa colonna e non ti iscrivi.

VARCHAR è buono:

  • Se immagazzini piccole stringhe
  • Se cerchi il valore della stringa
  • Se lo selezioni sempre o lo usi in join.

Selezionando qui intendo l’emissione di query che restituiscono il valore della colonna.

Cercando qui intendo l’emissione di query il cui risultato dipende dal valore della colonna TEXT o VARCHAR . Ciò include l’utilizzo in qualsiasi condizione JOIN o WHERE .

Poiché TEXT è memorizzato fuori riga, le query che non riguardano la colonna TEXT sono in genere più veloci.

Alcuni esempi di ciò che TEXT è buono per:

  • Commenti sul blog
  • Pagine Wiki
  • Codice sorgente

Alcuni esempi di ciò a cui VARCHAR è utile:

  • Nomi utente
  • Titoli di pagina
  • I nomi dei file

Come regola generale, se hai mai bisogno del tuo valore di testo per superare i 200 caratteri E non utilizzare join su questa colonna, usa TEXT .

Altrimenti usa VARCHAR .

PS Lo stesso vale per NTEXT e NVARCHAR abilitati UNICODE , che dovresti usare per gli esempi sopra.

PPS Lo stesso vale per VARCHAR(MAX) e NVARCHAR(MAX) che SQL Server 2005+ utilizza al posto di TEXT e NTEXT . Dovrai abilitare i large value types out of row per loro con sp_tableoption se vuoi che siano sempre archiviati fuori riga.

Come menzionato sopra e qui , TEXT sarà deprecato nelle versioni future:

L’opzione text in row verrà rimossa in una versione futura di SQL Server . Evita di usare questa opzione in un nuovo lavoro di sviluppo e pianifica di modificare le applicazioni che attualmente usano il text in row . Si consiglia di archiviare dati di grandi dimensioni utilizzando i tipi di dati varchar(max) , nvarchar(max) o varbinary(max) . Per controllare il comportamento in-row e out-of-row di questi tipi di dati, utilizzare l’opzione large value types out of row .

In SQL Server 2005 sono stati introdotti nuovi tipi di dati: varchar(max) e nvarchar(max) Hanno i vantaggi del vecchio tipo di testo: possono contenere fino a 2 GB di dati, ma hanno anche la maggior parte dei vantaggi di varchar e nvarchar . Tra questi vantaggi vi è la possibilità di utilizzare le funzioni di manipolazione delle stringhe come la sottostringa ().

Inoltre, varchar (max) viene memorizzato nello spazio della tabella (disco / memoria) mentre la dimensione è inferiore a 8 KB. Solo quando si inseriscono più dati nel campo, viene memorizzato fuori dallo spazio della tabella. I dati memorizzati nello spazio della tabella sono (di solito) recuperati più rapidamente.

In breve, non utilizzare mai il testo, in quanto esiste un’alternativa migliore: (n) varchar (max). E usa solo varchar (max) quando un varchar regolare non è abbastanza grande, cioè se ti aspetti che la stringa che stai per memorizzare superi gli 8000 caratteri.

Come è stato notato, è ansible utilizzare SUBSTRING sul tipo di dati TEXT, ma solo fino a quando i campi TEXT contengono meno di 8000 caratteri.

Ci sono stati alcuni importanti cambiamenti in ms 2008 -> Potrebbe valere la pena considerare il seguente articolo quando si prendono decisioni su quale tipo di dati utilizzare. http://msdn.microsoft.com/en-us/library/ms143432.aspx

Byte per

  1. colonna varchar (max), varbinary (max), xml, testo o immagine 2 ^ 31-1 2 ^ 31-1
  2. nvarchar (max) colonna 2 ^ 30-1 2 ^ 30-1