C3-Implement data types and NULLs Flashcards
Should you use smallint as a primary key?
No, it can only store 32767 records
What spacial data can SQL Server support?
SQL Server supports two spatial data types: the geometry data type and the geography data type.
- The geometry type represents data in a Euclidean (flat) coordinate system.
- The geography type represents data in a round-earth coordinate system.
What is SET XACT_ABORT?
DEF:Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
EX:
- Changes statement-terminating errors to batch-terminating errors;
- Ensures that statement-terminating errors will abort the execution;
syntaxsql:
SET XACT_ABORT { ON | OFF }
Remarks:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
What are transactions?
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
What are the transactions modes?
SQL Server operates in the following transaction modes:
Autocommit transactions:
Each individual statement is a transaction.
Explicit transactions:
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit Transactions:
A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
Batch-scoped transactions:
Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.
What is CAST and CONVERT and what are their syntaxes?
These functions convert an expression of one data type to another;
CAST Syntax: CAST ( expression AS data_type [( length )] )
CONVERT Syntax: CONVERT ( data_type [( length )] , expression [, style] )
PARSE-def, syntax, remarks
def: Returns the result of an expression, translated to the requested data type in SQL Server.
syntax: PARSE ( string_value AS data_type [USING culture] )
remarks:
Null values passed as arguments to PARSE are treated in two ways:
If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner.
If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch.
Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.
TRY_PARSE def, syntax, remarks
def: Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.
syntax: TRY_PARSE ( string_value AS data_type [USING culture] )
remarks: Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.
TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).
This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.
SET ANSI_NULLS-def, syntax, remarks
def: Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2019;
syntax: SET ANSI_NULLS { ON | OFF }
remarks:
- When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
- When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
- When ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. For more information about the ANSI_NULLS database option, see ALTER DATABASE (Transact-SQL).
- The comparison of two columns is not affected by SET ANSI_NULLS OFF;
- Future versions of SQL SERVER Will have ANSI_NULLS always set to ON;
ISNULL def, syntax, remarks
def: Replaces NULL with the specified replacement value. ISNULL takes two arguments: the first tests if an expression IS NULL and the second contains what to replace NULL value with
syntax: ISNULL ( check_expression , replacement_value );
remarks: The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.
IIF def, syntax, remarks
def:Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server
Syntax: IIF ( boolean_expression, true_value, false_value )
remarks:
IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF.
SET CONCAT_NULL_YIELDS_NULL def, syntax, remarks
def: Controls whether concatenation results are treated as null or empty string values.
syntax: SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
remarks: When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT ‘abc’ + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT ‘abc’ + NULL yields abc.
If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies
CONCAT-def, syntax, remarks
def: This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner.
syntax: CONCAT ( string_value1, string_value2 [, string_valueN] )
remarks: CONCAT takes a variable number of string arguments and concatenates (or joins) them into a single string. It requires a minimum of two input values; otherwise, CONCAT will raise an error. CONCAT implicitly converts all arguments to string types before concatenation. CONCAT implicitly converts null values to empty strings.
The implicit conversion to strings follows the existing rules for data type conversions
