ANSI_NULLS et QUOTED_IDENTIFIER ont tué des choses. À quoi servent-ils?

OK, du sharepoint vue d'un développeur d'applications, voici ce que font ces parameters:

QUOTED_IDENTIFIER

Ce paramètre contrôle comment les guillemets ".." sont interprétés par le compilateur SQL. Lorsque QUOTED_IDENTIFIER est ON alors les guillemets sont traités comme des parenthèses ( [...] ) et peuvent être utilisés pour citer des noms d'objects SQL comme des noms de tables, des colonnes, etc. Quand il est OFF (non recommandé), les guillemets sont traités comme des apostrophes ( '..' ) et peut être utilisé pour citer des strings de text dans les commands SQL.

ANSI_NULLS

Ce paramètre contrôle ce qui se passe lorsque vous essayez d'utiliser un opérateur de comparaison autre que IS sur NULL. Quand il est sur, ces comparaisons suivent la norme qui dit que comparer à NULL échoue toujours (parce que ce n'est pas une valeur, c'est un drapeau) et returnne FALSE . Lorsque ce paramètre est OFF (vraiment déconseillé), vous pouvez le traiter avec succès comme une valeur et utiliser = , <> , etc. dessus et revenir TRUE comme approprié.

La bonne façon de gérer cela est d'utiliser à la place l' IS ( ColumnValue IS NULL .. ).

CONCAT_NULL_YIELDS_NULL

Ce paramètre contrôle si NULL "Propogate" est utilisé dans les expressions de string. Lorsque ce paramètre est sur ON, il suit la norme et une expression comme 'some ssortingng' + NULL .. renvoie toujours NULL. Ainsi, dans une série de concaténations de strings, une valeur NULL peut entraîner la valeur NULL de l'expression entière. Si vous désactivez cette option (également, non recommandée), les valeurs NULL seront traitées comme des strings vides, de sorte que 'some ssortingng' + NULL simplement évaluée comme 'some ssortingng' .

La bonne façon de gérer cela est avec la fonction COALESCE (ou ISNULL): 'some ssortingng' + COALESCE(NULL, '') ..

Je pense que tout en reconstruisant les index, il s'est éteint.

Vérifiez les options SET avec leurs valeurs de paramètre requirejses tout en travaillant avec l'index filtré

Vous devez activer le paramètre ci-dessous en traitant l'index filtré:

 SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON 

Vous devez append pour append

 SET ANSI_NULLS, QUOTED_IDENTIFIER ON 

pour toutes mes procédures stockées éditant une table avec une colonne calculée pour éviter cette erreur.

ANSI_NULLS:

Lorsque SET ANSI_NULLS est ON, une instruction SELECT qui utilise WHERE nom_colonne = NULL renvoie zéro ligne même s'il existe des valeurs NULL dans nom_colonne. Une instruction SELECT qui utilise WHERE nom_colonne <> NULL renvoie zéro ligne même s'il existe des valeurs non-nom dans nom_colonne.

Lorsque SET ANSI_NULLS est désactivé, les opérateurs de comparaison Equals (=) et Not Equal To (<>) ne suivent pas la norme ISO. Une instruction SELECT qui utilise WHERE nom_colonne = NULL renvoie les lignes qui ont des valeurs NULL dans nom_colonne. Une instruction SELECT qui utilise WHERE nom_colonne <> NULL renvoie les lignes qui ont des valeurs non nulles dans la colonne. En outre, une instruction SELECT qui utilise WHERE nom_colonne <> valeur_XYZ renvoie toutes les lignes qui ne sont pas XYZ_value et qui ne sont pas NULL.

QUOTED_IDENTIFIER

Lorsque SET QUOTED_IDENTIFIER est activé, les identificateurs peuvent être délimités par des guillemets doubles et les littéraux doivent être délimités par des guillemets simples. Lorsque SET QUOTED_IDENTIFIER est désactivé, les identifiants ne peuvent pas être quotés et doivent respecter toutes les règles Transact-SQL pour les identificateurs. Pour plus d'informations, voir Identificateurs de database. Les littéraux peuvent être délimités par des guillemets simples ou doubles.

Lorsque SET QUOTED_IDENTIFIER est activé (par défaut), toutes les strings délimitées par des guillemets doubles sont interprétées comme des identificateurs d'object. Par conséquent, les identificateurs entre guillemets n'ont pas à suivre les règles Transact-SQL pour les identificateurs. Ils peuvent être des mots-keys réservés et peuvent inclure des caractères généralement non autorisés dans les identifiants Transact-SQL. Les guillemets doubles ne peuvent pas être utilisés pour délimiter des expressions de string littérales; les guillemets simples doivent être utilisés pour entourer les strings littérales. Si un seul guillemet (') fait partie de la string littérale, il peut être représenté par deux guillemets simples ("). SET QUOTED_IDENTIFIER doit être activé lorsque des mots-keys réservés sont utilisés pour les noms d'object dans la database.

CONCAT_NULL_YIELDS_NULL

Lorsque SET CONCAT_NULL_YIELDS_NULL est ON, la concaténation d'une valeur null avec une string donne un résultat NULL. Par exemple, SELECT 'abc' + NULL donne NULL. Lorsque SET CONCAT_NULL_YIELDS_NULL est désactivé, la concaténation d'une valeur null avec une string renvoie la string elle-même (la valeur null est traitée comme une string vide). Par exemple, SELECT 'abc' + NULL donne abc.

Si SET CONCAT_NULL_YIELDS_NULL n'est pas spécifié, le paramètre de l'option de database CONCAT_NULL_YIELDS_NULL s'applique.

Je trouve la documentation , les articles de blog , les réponses Stackoverflow inutiles pour expliquer ce QUOTED_IDENTIFIER signifie QUOTED_IDENTIFIER .

Les time anciens

À l'origine, SQL Server vous permettait d'utiliser des guillemets ( "..." ) et des apostrophes ( '...' ) de manière interchangeable (comme le fait Javascript):

• SELECT "Hello, world!" –Guillemet
• SELECT 'Hello, world!' –apostrophe

Et si vous vouliez une table de noms, une vue, une procédure, une colonne, etc. avec quelque chose qui enfreindrait toutes les règles de dénomination des objects, vous pourriez l'inclure entre crochets ( [ , ] ):

 CREATE TABLE [The world's most awful table name] ( [Hello, world!] int ) SELECT [Hello, world!] FROM [The world's most awful table name] 

Et tout cela a fonctionné, et fait sens.

Puis est venu ANSI

Puis ANSI est arrivé et avait d'autres idées:

• si vous avez un nom funky, placez-le entre guillemets ( "..." )
• utiliser apostrophe ( '...' ) pour les strings
• et nous ne nous soucions même pas de vos crochets

Ce qui signifie que si vous vouliez "citer" une colonne funky ou un nom de table, vous devez utiliser des guillemets:

 SELECT "Hello, world!" FROM "The world's most awful table name" 

Si vous connaissiez SQL Server, vous saviez que les guillemets étaient déjà utilisés pour représenter les strings. Si vous avez essayé aveuglément d'exécuter ce code ANSI-SQL comme s'il s'agissait de T-SQL : c'est un non-sens, et SQL Server vous l'a dit:

 Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'The world's most awful table name'. 

Vous devez vous inscrire au nouveau comportement ANSI

Microsoft a donc ajouté une fonctionnalité pour vous permettre de vous inscrire à la version ANSI de SQL.

Original

 SELECT "Hello, world!" --valid SELECT 'Hello, world!' --valid 

SET QUOTED_IDENTIFIER ON

 SELECT "Hello, world!" --INVALID SELECT 'Hello, world!' --valid 

Ces jours-ci, tout le monde a SET QUOTED_IDENTIFIERS ON , ce qui signifie techniquement que vous devriez utiliser des quotes plutôt que square brackets autour des identifiants:

T-SQL (mauvais?) (Par exemple SQL généré par Entity Framework)

 UPDATE [dbo].[Customers] SET [FirstName] = N'Ian' WHERE [CustomerID] = 7 

ANSI-SQL (bien?)

 UPDATE "dbo"."Customers" SET "FirstName" = N'Ian' WHERE "CustomerID" = 7 

ANSI_NULLS ON rend toute expression booleanne binary avec une valeur nulle évaluée à false. En utilisant le model suivant:

 declare @varA, @varB int if <binary boolean expression> begin print 'true' end else begin print 'false' end @varA: NULL; @varB: NULL; @varA = @varB evaluates to false @varA: 1; @varB: NULL; @varA <> @varB evaluates to false 

La bonne façon de tester null est d'utiliser is [not] NULL

 @varA: NULL; @varA is NULL evaluates to true @varA: 1; @varA is not NULL evaluates to true 

QUOTED_IDENTIFER ON vous permet simplement d'utiliser des guillemets doubles pour délimiter les identifiants (mauvaise idée IMO, juste entre crochets user)

 from tblA "a" -- ok when ON, not ok when OFF from tblA [a] -- always ok