Quali sono i casi d’uso per selezionare CHAR su VARCHAR in SQL?

Mi rendo conto che CHAR è consigliato se tutti i miei valori sono a larghezza fissa. Ma allora? Perché non scegliere VARCHAR per tutti i campi di testo solo per sicurezza.

Generalmente scegli CHAR se tutte le righe avranno una lunghezza simile alla stessa . Scegli VARCHAR quando la lunghezza varia in modo significativo. CHAR può anche essere un po ‘più veloce perché tutte le file hanno la stessa lunghezza.

Varia a seconda dell’implementazione del DB, ma in genere VARCHAR utilizza uno o due ulteriori byte di memoria (per la lunghezza o la terminazione) oltre ai dati effettivi. Quindi (supponendo che tu stia usando un set di caratteri da un byte) memorizzando la parola “FooBar”

  • CHAR (6) = 6 byte (nessun sovraccarico)
  • VARCHAR (10) = 8 byte (2 byte di sovraccarico)
  • CHAR (10) = 10 byte (4 byte di sovraccarico)

La linea di fondo è che CHAR può essere più veloce e più efficiente in termini di spazio per i dati con una lunghezza relativamente uguale (con una differenza di lunghezza di due caratteri).

Nota : Microsoft SQL ha 2 byte di overhead per un VARCHAR. Questo può variare da DB a DB, ma generalmente c’è almeno 1 byte di overhead necessario per indicare la lunghezza o EOL su un VARCHAR.

Come è stato sottolineato da Gaven nei commenti, se si utilizza un set di caratteri a lunghezza variabile multi-byte come UTF8, allora CHAR memorizza il numero massimo di byte necessario per memorizzare il numero di caratteri. Quindi se UTF8 ha bisogno di un massimo di 3 byte per memorizzare un carattere, allora CHAR (6) sarà fissato a 18 byte, anche se si memorizza solo caratteri latini1. Quindi in questo caso VARCHAR diventa una scelta molto migliore.

Se stai lavorando con me e stai lavorando con Oracle, probabilmente ti farò usare varchar in quasi tutte le circostanze. L’ipotesi che char utilizzi meno potenza di elaborazione rispetto a varchar potrebbe essere vera … per ora … ma i motori di database migliorano nel tempo e questa sorta di regola generale ha la realizzazione di un futuro “mito”.

Un’altra cosa: non ho mai visto un problema di prestazioni perché qualcuno ha deciso di andare con varchar . Farai un uso migliore del tuo tempo scrivendo un buon codice (meno chiamate al database) e un SQL efficiente (come funzionano gli indici, come fa l’ottimizzatore a prendere decisioni, perché exists più velocemente che in genere …).

Pensiero finale: ho visto tutti i tipi di problemi con l’uso di CHAR , le persone che cercano “quando dovrebbero cercare” o le persone che cercano “FOO” quando dovrebbero cercare “FOO” (un sacco di spazi qui) ‘, o le persone che non ritagliano gli spazi finali o bug con Powerbuilder aggiungendo fino a 2000 spazi bianchi al valore restituito da una procedura Oracle.

Oltre ai vantaggi in termini di prestazioni, CHAR può essere utilizzato per indicare che tutti i valori devono avere la stessa lunghezza, ad esempio una colonna per le abbreviazioni degli Stati Uniti.

Char è un po ‘più veloce, quindi se hai una colonna che SAPI avrà una certa lunghezza, usa char. Ad esempio, memorizzare (M) ale / (F) emale / (U) nknown per sesso o 2 caratteri per uno stato USA.

NChar o Char hanno prestazioni migliori rispetto alle loro alternative var?

Grande domanda. La semplice risposta è sì in determinate situazioni. Vediamo se questo può essere spiegato.

Ovviamente tutti sappiamo che se creo una tabella con una colonna di varchar (255) (chiamiamo questa colonna myColumn) e inserisco un milione di righe ma inserisco solo pochi caratteri in myColumn per ogni riga, la tabella sarà molto più piccola (nel complesso numero di pagine di dati necessarie per il motore di archiviazione) rispetto a se avessi creato myColumn come char (255). Ogni volta che eseguo un’operazione (DML) su quella tabella e richiedo un sacco di righe, sarà più veloce quando myColumn è varchar perché non devo spostarmi tutti quegli spazi “extra” alla fine. Sposta, come in quando SQL Server esegue ordinamenti interni come durante un’operazione distinta o unione, o se sceglie un’unione durante il suo piano di query, ecc. Sposta potrebbe anche significare il tempo necessario per ottenere i dati dal server al mio locale PC o su un altro computer o dovunque sarà consumato.

Ma c’è un sovraccarico nell’uso di varchar. SQL Server deve utilizzare un indicatore a due byte (overhead) per, su ogni riga, per sapere quanti byte contiene la riga specifica myColumn. Non sono i 2 byte in più che presenta il problema, è il dover “decodificare” la lunghezza dei dati in myColumn su ogni riga.

Nelle mie esperienze ha più senso usare char invece di varchar sulle colonne a cui si aggiungeranno le query. Ad esempio la chiave primaria di una tabella o un’altra colonna che verrà indicizzata. CustomerNumber su una tabella demografica o CodeID su una tabella di decodifica o su OrderNumber su una tabella di ordini. Usando char, il motore di query può eseguire più rapidamente il join perché può fare aritmetica puntatore diritta (in modo deterministico) piuttosto che dover spostare i suoi puntatori di una quantità variabile di byte mentre legge le pagine. So che potrei averti perso in quell’ultima frase. I join in SQL Server si basano sull’idea di “predicati”. Un predicato è una condizione. Ad esempio myColumn = 1 o OrderNumber <500.

Quindi, se SQL Server sta eseguendo un’istruzione DML e se i predicati o le “chiavi” che si aggiungono su una lunghezza fissa (char), il motore di query non deve eseguire il lavoro necessario per associare le righe da una tabella a righe da un altro tavolo Non dovrà scoprire per quanto tempo i dati sono nella riga e poi scendere la stringa per trovare la fine. Tutto ciò richiede tempo.

Ora tenete a mente che questo può essere facilmente implementato male. Ho visto il char utilizzato per i campi chiave primari nei sistemi online. La larghezza deve essere mantenuta piccola, ad esempio char (15) o qualcosa di ragionevole. E funziona meglio nei sistemi online, perché di solito si recupera o si sposta solo un numero limitato di righe, quindi dover “rtrim” gli spazi finali che si otterranno nel set di risultati è un compito banale invece di dover unire milioni di righe da una tabella a milioni di righe su un’altra tabella.

Un’altra ragione per cui CHAR ha senso sui varchar sui sistemi online è che riduce le divisioni di pagina. Usando char, si sta essenzialmente “riservando” (e sprecando) quello spazio, quindi se un utente arriva più tardi e mette più dati in quella colonna, SQL ha già assegnato spazio per esso e in esso va.

Un altro motivo per usare CHAR è simile alla seconda ragione. Se un programmatore o un utente esegue un aggiornamento “batch” su milioni di righe, ad esempio aggiungendo qualche frase ad un campo note, non riceverai una chiamata dal tuo DBA nel bel mezzo della notte, chiedendo perché le loro unità sono piene. In altre parole, porta a una crescita più prevedibile delle dimensioni di un database.

Quindi, questi sono 3 modi in cui un sistema online (OLTP) può trarre vantaggio da char su varchar. Non uso quasi mai char in uno scenario warehouse / analysis / OLAP perché di solito hai tanti dati che tutte quelle colonne char possono sumrsi a un sacco di spazio sprecato.

Tieni presente che char può rendere il tuo database molto più grande, ma la maggior parte degli strumenti di backup hanno la compressione dei dati, quindi i tuoi backup tendono ad avere le stesse dimensioni di quelli che avevi usato varchar. Ad esempio LiteSpeed ​​o RedGate SQL Backup.

Un altro uso è nelle viste create per esportare i dati su un file a larghezza fissa. Diciamo che devo esportare alcuni dati in un file flat per essere letto da un mainframe. È a larghezza fissa (non delimitata). Mi piace memorizzare i dati nella mia tabella di “staging” come varchar (occupando così meno spazio sul mio database) e quindi utilizzare una vista per eseguire il cast di tutto sul suo equivalente in char, con la lunghezza corrispondente alla larghezza della larghezza fissa per quella colonna . Per esempio:

 create table tblStagingTable ( pkID BIGINT (IDENTITY,1,1), CustomerFirstName varchar(30), CustomerLastName varchar(30), CustomerCityStateZip varchar(100), CustomerCurrentBalance money ) insert into tblStagingTable (CustomerFirstName,CustomerLastName, CustomerCityStateZip) ('Joe','Blow','123 Main St Washington, MD 12345', 123.45) create view vwStagingTable AS SELECT CustomerFirstName = CAST(CustomerFirstName as CHAR(30)), CustomerLastName = CAST(CustomerLastName as CHAR(30)), CustomerCityStateZip = CAST(CustomerCityStateZip as CHAR(100)), CustomerCurrentBalance = CAST(CAST(CustomerCurrentBalance as NUMERIC(9,2)) AS CHAR(10)) SELECT * from vwStagingTable 

Questo è bello perché internamente i miei dati occupano meno spazio perché usa varchar. Ma quando uso DTS o SSIS o anche solo un copia e incolla da SSMS a Blocco note, posso usare la vista e ottenere il giusto numero di spazi finali. In DTS avevamo una funzione chiamata, dannazione, dimentico che penso si chiamasse “suggerire colonne” o qualcosa del genere. In SSIS non puoi più farlo, devi definire noiosamente il flat connection manager di file. Ma dal momento che hai la tua configurazione vista, SSIS può conoscere la larghezza di ogni colonna e può risparmiare un sacco di tempo quando si costruiscono le attività di stream di dati.

Quindi linea di fondo … usa varchar. Ci sono un numero molto basso di motivi per usare char ed è solo per motivi di prestazioni. Se hai un sistema con centinaia di milioni di righe vedrai una differenza evidente se i predicati sono deterministici (char), ma per la maggior parte dei sistemi che utilizzano char è semplicemente lo spreco di spazio.

Spero possa aiutare. Jeff

Esistono vantaggi in termini di prestazioni, ma eccone uno che non è stato menzionato: la migrazione delle righe. Con char, ti riservi l’intero spazio in anticipo. Supponiamo di avere un carattere (1000) e di memorizzare 10 caratteri, userai tutti i 1000 caratteri di spazio. In un varchar2 (1000), verranno utilizzati solo 10 caratteri. Il problema si presenta quando si modificano i dati. Supponiamo che tu aggiorni la colonna per contenere ora 900 caratteri. È ansible che lo spazio per espandere varchar non sia disponibile nel blocco corrente. In tal caso, il motore DB deve migrare la riga in un altro blocco e creare un puntatore nel blocco originale sulla nuova riga nel nuovo blocco. Per leggere questi dati, il motore DB dovrà ora leggere 2 blocchi.
Nessuno può equivocamente affermare che varchar o char siano migliori. C’è uno spazio per il compromesso temporale e la considerazione se i dati saranno aggiornati, specialmente se ci sono buone probabilità che cresca.

C’è una differenza tra l’ottimizzazione delle prestazioni iniziali e l’utilizzo di un tipo di regola di best practice. Se stai creando nuove tabelle in cui avrai sempre un campo a lunghezza fissa, ha senso usare CHAR, dovresti usarlo in quel caso. Questa non è l’ottimizzazione iniziale, ma piuttosto l’implementazione di una regola empirica (o best practice).

es. – Se hai un campo di stato a 2 lettere, usa CHAR (2). Se si dispone di un campo con i nomi degli stati effettivi, utilizzare VARCHAR.

Sceglierei varchar a meno che la colonna non memorizzi un valore fisso come il codice di stato degli Stati Uniti – che è sempre lungo 2 caratteri e l’elenco di codice degli Stati Uniti valido non cambia spesso :).

In ogni altro caso, anche come memorizzare la password hash (che è una lunghezza fissa), sceglierei varchar.

Perché – la colonna del tipo di carattere viene sempre soddisfatta con spazi, il che rende la colonna my_column definita come char (5) con il valore ‘ABC’ all’interno del confronto:

 my_column = 'ABC' -- my_column stores 'ABC ' value which is different then 'ABC' 

falsa.

Questa caratteristica potrebbe portare a molti bug fastidiosi durante lo sviluppo e rende più difficili i test.

CHAR occupa meno spazio di archiviazione rispetto a VARCHAR se tutti i valori dei dati in quel campo hanno la stessa lunghezza. Ora forse nel 2009 un database da 800 GB è lo stesso a tutti gli effetti come un 810GB se convertite i VARCHAR in CHAR, ma per stringhe brevi (1 o 2 caratteri), CHAR è ancora una “best practice” del settore, direi.

Ora, se si osserva l’ampia varietà di tipi di dati che la maggior parte dei database fornisce anche solo per i numeri interi (bit, tiny, int, bigint), ci sono motivi per scegliere l’uno rispetto all’altro. Scegliere semplicemente bigint ogni volta è in realtà un po ‘ignorante degli scopi e degli usi del campo. Se un campo rappresenta semplicemente un’età di persone in anni, un bigint è eccessivo. Ora non è necessariamente “sbagliato”, ma non è efficiente.

Ma è un argomento interessante, e man mano che i database migliorano nel tempo, si potrebbe sostenere che CHAR vs VARCHAR diventa meno rilevante.

Mi schiero dal commento di Jim McKeeth.

Inoltre, le scansioni dell’indicizzazione e della tabella completa sono più veloci se la tabella contiene solo colonne CHAR. Fondamentalmente l’ottimizzatore sarà in grado di prevedere quanto grande sia ogni record se ha solo colonne CHAR, mentre deve controllare il valore della dimensione di ogni colonna VARCHAR.

Inoltre, se si aggiorna una colonna VARCHAR a una dimensione maggiore del suo contenuto precedente, è ansible forzare il database a ricostruirne gli indici (perché si è costretti il ​​database a spostare fisicamente il record sul disco). Mentre con le colonne CHAR non succederà mai.

Ma probabilmente non ti interesserà la performance hit a meno che il tuo tavolo non sia enorme.

Ricorda le sagge parole di Djikstra. L’ottimizzazione anticipata delle prestazioni è la radice di tutto il male.

È lo spazio classico contro il compromesso delle prestazioni.

In MS SQL 2005, Varchar (o NVarchar per le lanuaghe che richiedono due byte per carattere cioè cinese) sono di lunghezza variabile. Se si aggiunge alla riga dopo che è stata scritta sul disco rigido, i dati verranno localizzati in una posizione non contesa alla riga originale e porteranno alla frammentazione dei file di dati. Ciò influenzerà le prestazioni.

Quindi, se lo spazio non è un problema, Char è migliore per le prestazioni, ma se vuoi mantenere le dimensioni del database in basso, allora i varchar sono migliori.

C’è un po ‘di overhead di elaborazione nel calcolare la dimensione effettiva necessaria per un valore di colonna e allocare lo spazio per un Varchar, quindi se sei sicuramente sicuro per quanto tempo il valore sarà sempre, è meglio usare Char ed evitare il colpo.

Penso che nel tuo caso probabilmente non c’è motivo di non scegliere Varchar. Ti dà la flessibilità e, come è stato detto da un certo numero di rispondenti, le prestazioni sono tali ora che, tranne in circostanze molto specifiche, noi mortali meer (al contrario di Google DBA) non noteremo la differenza.

Una cosa interessante degna di nota quando si tratta di tipi DB è la sqlite (un mini database popolare con prestazioni piuttosto impressionanti) che mette tutto nel database come una stringa e digita al volo.

Io uso sempre il VarChar e di solito lo faccio molto più grande di quanto io possa aver bisogno di qualcosa. Per esempio. 50 per Firstname, come dici tu perché non solo per essere al sicuro.

Molte persone hanno sottolineato che se si conosce la lunghezza esatta del valore utilizzando CHAR ha alcuni vantaggi. Ma mentre si memorizzano gli stati degli Stati Uniti come CHAR (2) è grandioso oggi, quando ricevi il messaggio dalle vendite che “Abbiamo appena fatto la nostra prima vendita in Australia”, sei in un mondo di dolore. Invito sempre a sovrastimare per quanto tempo penso che i campi dovranno essere piuttosto che fare un’ipotesi “esatta” da coprire per gli eventi futuri. VARCHAR mi darà più flessibilità in questo settore.

quando si utilizzano valori varchar, SQL Server necessita di 2 byte aggiuntivi per riga per memorizzare alcune informazioni su tale colonna, mentre se si utilizza char non è necessario, a meno che non si

La frammentazione. Il Char riserva lo spazio e VarChar no. La suddivisione di pagina può essere richiesta per accogliere l’aggiornamento su varchar.

In alcuni database SQL, VARCHAR verrà espanso alla dimensione massima per ottimizzare gli offset, in modo da velocizzare scansioni e indici di tabelle complete.

Per questo motivo, non si ha alcun risparmio di spazio utilizzando un VARCHAR (200) rispetto a un CHAR (200)

L’utilizzo di CHAR (NCHAR) e VARCHAR (NVARCHAR) comporta differenze nei modi in cui il database archivia i dati. Il primo introduce spazi finali finali; Ho riscontrato dei problemi durante l’utilizzo con l’operatore LIKE nelle funzioni SQL SERVER. Quindi devo metterlo al sicuro usando VARCHAR (NVARCHAR) tutte le volte.

Ad esempio, se abbiamo una tabella TEST (ID INT, Status CHAR (1)) e scrivi una funzione per elencare tutti i record con un valore specifico come il seguente:

 CREATE FUNCTION List(@Status AS CHAR(1) = '') RETURNS TABLE AS RETURN SELECT * FROM TEST WHERE Status LIKE '%' + @Status '%' 

In questa funzione ci aspettiamo che quando inseriamo il parametro predefinito la funzione restituisca tutte le righe, ma in realtà non lo fa. Modificare il tipo di dati @Status in VARCHAR risolverà il problema.

Non userò MAI i caratteri. Ho avuto questo dibattito con molte persone e hanno sempre sollevato il solito cliché che il char è più veloce. Bene, dico, quanto più veloce? Di cosa stiamo parlando qui, millisecondi, secondi e se sì, quanti? Mi stai dicendo perché qualcuno afferma di essere più veloce di qualche millisecondo, dovremmo introdurre tonnellate di errori difficili da correggere nel sistema?

Quindi ecco alcuni problemi che incontrerai:

Ogni campo sarà riempito, così finirai con il codice per sempre che ha RTRIMS ovunque. Questo è anche un enorme spreco di spazio su disco per i campi più lunghi.

Ora supponiamo di avere l’esempio per eccellenza di un campo char di un solo carattere, ma il campo è facoltativo. Se qualcuno passa una stringa vuota a quel campo diventa uno spazio. Quindi, quando un’altra applicazione / processo lo interroga, ottengono uno spazio singolo, se non usano rtrim. Abbiamo avuto documenti XML, file e altri programmi, visualizzato solo uno spazio, nei campi opzionali e rompere le cose.

Quindi ora devi assicurarti di passare null e non stringhe vuote, nel campo char. Ma NON è l’uso corretto di null. Ecco l’uso di null. Diciamo che ottieni un file da un venditore

Nome | Sesso | Città Bob || Los Angeles

Se il sesso non è specificato di quanto non si inserisce Bob, stringa vuota e Los Angeles nel tavolo. Ora diciamo che si ottiene il file e il suo formato cambia e il genere non è più incluso ma era nel passato.

Nome | Città Bob | Seattle

Bene visto che il genere non è incluso, userei null. Varchars supportano questo senza problemi.

Char d’altra parte è diverso. Devi sempre inviare null. Se invii mai una stringa vuota, finirai con un campo che contiene degli spazi.

Potrei andare avanti e avanti con tutti i bug che ho dovuto risolvere dai caratteri e in circa 20 anni di sviluppo.