General Flashcards
What is a predicate?
A predicate is a condition expression that evaluates to a boolean value, either true or false.
Where can we use predicates?
- In a SELECT statement’s WHERE clause or HAVING clause to determine which rows are relevant to a particular query. Note that not all predicates can be used in a HAVING clause.
- In a JOIN operation’s ON clause to determine which rows are relevant to the join operation.
- In an UPDATE or DELETE statement’s WHERE clause, to determine which rows are to be modified.
- In a WHERE CURRENT OF statement’s AND clause.
- In a CREATE TRIGGER statement’s WHEN clause to determine when to apply triggered action code.
What is execute time, parse time and runtime in SQL?
All mean the same thing the time it takes editor to execute a command. However parsetime only referse to those commands that execute date or string data.
What is WHERE clause?
Specifies the search condition for the rows returned by the query.It only filters unaggregated values
What is dataType precedence?
When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. If the conversion isn’t a supported implicit conversion, an error is returned. For an operator combining operand expressions having the same data type, the result of the operation has that data type.
Remarks: Because it always changes storing the age is never a good idea.
What are data types (Transact-SQL)?
In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types.
When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following:
The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. For more information, see Data Type Precedence (Transact-SQL).
The collation of the result is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL).
The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
What are int, bigint, smallint, and tinyint?
Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person’s age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building’s age because a building can be more than 255 years old.
remarks:
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
bigint fits between smallmoney and int in the data type precedence chart.
Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.
- tinyint 0-255 1 Byte;
- smallint (-) 32768 - (+) 32768 2 Byte;
- int (-) 2,147,483,648 - (+) 2,147,483,647 4 Byte;
- Bigint (-) 9,223,372,036,854,775,808 - (+) 9,223,372,036,854,775,807 8 Byte;
What is nchar and nvarchar?
Character data types that are either fixed-size, nchar, or variable-size, nvarchar. Starting with SQL Server 2012 (11.x), when a Supplementary Character (SC) enabled collation is used, these data types store the full range of Unicode character data and use the UTF-16 character encoding. If a non-SC collation is specified, then these data types store only the subset of character data supported by the UCS-2 character encoding?
remarks:
- nvarchar(max):
- Allows 2GB of data to be used;
- Preserves whitespaces and original element tags;
- Allows unicode data types.
Using special datatypes
Special data types are those that do not fit into any of the other data type categories. In SQL Server, the special data types include the bit, hierarchyid, sql_variant, sysname, table, timestamp and alias data types.
- bit: The bitdata type is a numeric data type that stores either 0 or 1
- hierarchyid:The hierarchyid data type is used to manage hierarchical data and tables that have a hierarchical structure. To work with hierarchical data in Transact-SQL code, use hierarchyid functions.
- The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers.
- also Databases and other object names are defined as sysname datatype
- The table data type functions like a temporary table. It is used to store a result set for later processing. This data type can only be used to define local variables of type table and the return value of a user-defined function.
- timestamp-The timestamp data type has nothing to do with times or dates. timestamp values are binary numbers that indicate the relative sequence in which data modifications have occurred in a database.
- Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time that the row is modified
- Alias Data Types:
- Alias data types enable you to extend a SQL Server base data type, such as varchar, with a descriptive name and format that can be customized for a specific use. For example, the following statement implements a birthday user-defined data type that is based on the datetime data type and allows for null values (NULL):
- Xml data type does not preserve whitespaces and orginal elements tag
- Stores xml in the canonical form
- Geography data type can hold latitude and longitude date type.
- Geometry data type assumes a flat surface
What are Null Values?
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Null values generally indicate data that is unknown, not applicable, or that the data will be added later. For example, a customer’s middle initial may not be known at the time the customer places an order.
Following is information about nulls:
- To test for null values in a query, use IS NULL or IS NOT NULL in the WHERE clause.
- When query results are viewed in SQL Server Management Studio Code editor, null values are shown as NULL in the result set.
- Null values can be inserted into a column by explicitly stating NULL in an INSERT or UPDATE statement, by leaving a column out of an INSERT statement, or when adding a new column to an existing table by using the ALTER TABLE statement.
- Null values cannot be used for information that is required to distinguish one row in a table from another row in a table, such as primary keys.
What is a bit?
An integer data type that can take a value of 1, 0, or NULL.
Remarks:
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Converting to bit promotes any nonzero value to 1.
What is the difference between char and varchar?
Character data types that are either fixed-size, char, or variable-size, varchar. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, these data types store the full range of Unicode character data and use the UTF-8 character encoding. If a non-UTF-8 collation is specified, then these data types store only a subset of characters supported by the corresponding code page of that collation.
Remarks:
A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n never defines numbers of characters that can be stored.
When n isn’t specified in a data definition or variable declaration statement, the default length is 1. If n isn’t specified when using the CAST and CONVERT functions, the default length is 30.
