What is implicit schema name resolution?
T-SQL supports omitting the schema name as in “FROM Employees”. T-SQL will use implicit schema name resolution to look it up if it is not provided. It is considered a best practice to always include the schema name as in “FROM HR.Employees”. Including schema name can prevent you from ending up with a schema name that you did not intend to be used, and can also remove the cost involved in the implicit resolution
process, although this cost is minor.
How do you alias a queried table?
In the FROM clause, you can alias a queried table using the form as in “HR.Employees E” or as as in “HR.Employees AS E”. Using AS is preferred. If you assign an alias to a table, the table is renamed for the duration of the query. The original table name isn’t visible any more, only the alias.
Why is the asterisks a bad practice as an alternative to listing all attributes in the SELECT clause?
You typically only need to return a subset of the attributes. (1) By returning more, you can prevent SQL from using covering indexes in respect to an interesting set of attributes. (2) Also, this results in sending more data over the network. (3) Also, the table definition could change over time. * might have been all the attributes you needed at one point, but it might not be the case if the table changes.
What are the supported forms of column aliases?
AS , , = . The first form with AS is both standard and most readable.
Why would you intentionally alias an column?
(1) When you need the result attribute to be named differently than the source. (2) When you need to assign a name to an attribute that results from an expression that would otherwise be unnamed (creates a relational result). *(Note: T-SQL allows a result attribute to be without a name when the expression is based on a computation without an alias.)
What are the mandatory clauses in a valid T-SQL SELECT query?
T-SQL supports a SELECT query with only a SELECT clause and without a FROM clause, e.g. “SELECT 10 AS col1, ‘abc’ AS col2”. Standard SQL requires both a SELECT and FROM.
How do you delimit identifiers in T-SQL?
T-SQL supports both a standard form using double quotation marks as in “Sales”.”Orders”. T-SQL also supports a proprietary form using square brackets as in [Sales].[Orders].
When do you delimit identifiers in T-SQL?
When the identifier is regular delimiting is optional. Otherwise, the identifier must be delimited, e.g. .
A regular identifier must have a letter in the range A-Z (upper or lower), _, @, or # as the first character. Subsequent characters can include letters, numbers, @, $, #, _. The identifier must not be a reserved keyword in SQL. The identifier must not have spaces.
How large is an int?
4 bytes (-2^31 to 2^31-1)
How large is a tinyint?
1 byte (0 to 255)
How large is a datetime data type?
8 bytes; January 1, 1753, through December 31, 9999 23:59:59.997
How large is a date data type? What is it used for?
3 bytes; used to store just date without time; January 1, 1 A.D. through December 31, 9999
How large is a datetime2 data type? What is it used for?
6 to 8 bytes depending on precision; it’s an extension of the datetime type, January 1, 1 A.D. through December 31, 9999 00:00:00 through 23:59:59.9999999; it has a larger date range, a larger default fractional precision and an optional user-specified precision.
How large is a smalldatetime data type? What is it used for?
4 bytes; used to store dates/times within the following range: January 1, 1900, through June 6, 2079 23:59 (seconds are always 00)
How large is a real data type?
How large is a float data type?
What are the advantages and disadvantages of float and real data types?
The benefit in these types is that they can represent very large and very small numbers beyond what any other numerical type in SQL can represent. The downside is that they are just approximations. Not all values in the data range can be represented exactly. Use them to represent very large or very small numbers for scientific numbers that don’t need complete accuracy. Do not use them for precise values.
What are the differences between fixed types and dynamic types?
Fixed types (char, nchar, binary) use the storage for the indicated size, e.g. char(30) uses storage for 30 characters whether all are used or not. Updates will not require the row to physically expand - no data shifting is required. Good for attributes that get updated frequently as far as performance. Variable types (varchar, nvarchar, varbinary) use storage for what you enter plus a couple of bytes for offset information. Good for widely varying sizes of strings - you can save a lot on storage.
What are the two main roles of the FROM clause?
(1) Where you indicate tables you want to query. (2) Where you can apply table operators like join to input tables.
What are the two main roles of the SELECT clause?
(1) Evaluates expressions that define the attributes in the query’s result - assigning them with aliases if needed. (2) Using a DISTINCT clause, you can eliminate duplicate rows in the result if needed.
What does the USE statement do?
The USE statement (e.g. USE TSQL2012;) ensures that you are connected to the target database.
How large is a smallint?
2 bytes ( -2^15 to 2^15-1)
How large is a bigint?
8 bytes (-2^63 to 2^63-1)
What is physical data independence?
The data type is a property of the value stored in the database, and the internal storage shouldn’t be your concern. The formatting of the value is supposed to be the responsibility of the application when the data is presented.
What’s the difference between regular character types and unicode character types?
Regular character types (CHAR and VARCHAR) use 1 byte per character and support only one language besides English.Unicode character types (NCHAR and NVARCHAR) use 2 bytes of storage per character and support multiple languages.
When using types that can have a length, such as CHAR and VARCHAR, can the length be omitted?
Yes. T-SQL supports omitting the length and then uses the default length; however, the defaults can be different depending on the context, so it’s considered a best practice to always be explicit.
What’s the difference between the conversion functions with try and those without (CAST vs TRY_CAST)?
Those without TRY fail if the value isn’t convertible whereas those with the TRY return a NULL in such as case.
Explain CAST and TRY_CAST.
Indicate the expression and target type: e.g. SELECT CAST(‘abc’ AS INT); or SELECT TRY_CAST(‘abc’ AS INT); CAST is standard SQL.
Explain CONVERT and TRY_CONVERT.
Indicate the expression, target type, and a style for the conversion which is used for some conversions such as between character string and date/time values. e.g. CONVERT(DATE, ‘1/2/2012’, 101) (uses style 101 representing US standard).
Explain PARSE and TRY_PARSE.
Indicate the culture by using any culture supported by .NET, e.g, PARSE(‘1/2/2012’ AS DATE USING ‘en-US’).
When using expressions that involve operands of different types (1 + ‘1’), how does T-SQL handle this?
T-SQL converts the type that has the lower precedence to the one with higher. INT precedes VARCHAR, therefore SQL converts the VARCHAR value ‘1’ into an INT 1.The result of the expression is 2 and not ‘11’.
When using expressions that involve operands of the same type, what is the type of the result?
If all operands of the expression are of the same type, that’s also going to be the type of the result. 5 / 2 will be 2 (INT) and not 2.5 (NUMERIC) because 5 and 2 are considered INT.You must CAST the 5 and 2 as NUMERIC to get 2.5.
What are surrogate keys?
Surrogate keys are not derived from application data - it is purely a key, e.g. Id.
What are “intelligent” keys/natural keys?
Intelligent keys are derived from application data, e.g. CustomerNumber.
What are the typical options people use to generate surrogate keys?
(1) The identity column property (2) The sequence object (3) Nonsequential GUIDs (4) Sequential GUIDs (5) Custom solutions.
What is the identity column property?
A property that automatically generates keys in an attribute of a numeric types with a scale of 0 (TINYINT, SMALLINT, INT, BIGINT) or NUMERIC/DECIMAL with scale of 0.
What is the sequence object?
An independent object in the database from which you can obtain new sequence values. Supports any numeric type with a scale of 0. Unlike identity, it is not tied to a particular column; instead, it is an independent database object. You can request a new value from a sequence object before using it.
What are nonsequential guids?
Nonsequential (random) global unique identifier that can be stored in a column of type UNIQUEIDENTIFIER. You can use the T-SQL function NEWID to generate a new GUID or attach it to a default expression on a column. You can also generate them from the client. GUIDs are guaranteed to be unique across space and time.
What are sequential guids?
Sequential global unique identifier that can be generated in T-SQL using the NEWSEQUENTIALID function.
Explain why size of key’s data type is important.
The bigger the data type, the more storage is required, and hence the slower the reads are. The storage requirements on a surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a PK). Keep in mind, the clustered index key columns are used by all nonclustered indexes internally - the effect is multiplied.
How large is a uniqueidentifier?
What should be considered when comparing sequential keys (identity, sequence, NEWSEQUENTIALID) vs nonsequential (NEWID)?
With sequential keys, when a page is full, SQL Server allocates a new page and fills it. This results in less fragmentation in the index, which is beneficial for read performance. Sequential keys also have quick insertions when single session. With nonsequential keys, when a page is full, SQL Server performs a classic page split. It allocates a new page and moves half the rows from the original page to the new one. A page split has cost plus it results in index fragmentation.
What are latches?
Latches are objects used to synchronize access to database pages. When loading data from multiple sessions, you will end up with page latch contention.
When using numeric sequential keys, can negative values be used?
Yes, when using numeric sequential keys, you can always start with the lowest value in the type to use the entire range. For INT, instead of 1, you can start with -2,147,483,648.
What are the functions that return the current date and time?
(1) GETDATE, (2) CURRENT_TIMESTAMP, (3) GETUTCDATE, (4) SYSDATETIME, (5) SYSUTCDATETIME, (6) SYSDATETIMEOFFSET
What is the GETDATE function?
GETDATE is T-SQL specific and returns the current date and time in the SQL Server instance you’re connected to as a DATETIME data type.
What is the CURRENT_TIMESTAMP function?
CURRENT_TIMESTAMP is the standard and recommended way to return the current date and time.
What is the SYSDATETIME function?
SYSDATETIME is the same as GETDATE except it returns the value as the more precise DATETIME2.
What is the SYSDATETIMEOFFSET function?
Returns a DATETIMEOFFSET value that contains the date, time and timezone offset information.
What is the GETUTCDATE function?
Returns the current date and time in UTC terms as a DATETIME data type.
What is the SYSUTCDATE function?
Returns the current date and time in UTC terms as a DATETIME2 data type.
How can you get the current date or time individually?
There are no built in functions to do so. Simply cast the SYSDATETIME function to a DATE or TIME, e.g. CAST(SYSDATETIME() AS DATE)
What is the DATEPART function?
Using an input date and time value, this function allows you to extract desired parts such as year, minute, or nanoseconds and return the extracted part as an integer, e.g. DATEPART(month, ‘20120212’) returns 2. Alternatively, T-SQL provides the functions YEAR, MONTH, and DAY abbreviations to use instead of the fully-fledged DATEPART function.
What is the DATENAME function?
Similar to DATEPART; however, it returns the name of the part as a character string instead of the integer value. This is language dependent. e.g. DATENAME(month, ‘20120212’) return ‘February’.
What are the six date and time types?
(1) DATETIME, (2) DATETIME2, (3) DATE, (4) TIME, (5) DATETIMEOFFSET, (6) SMALLDATETIME
What functions are available to construct a date and time value from it’s numeric parts?
(1) DATEFROMPARTS, (2) DATETIME2FROMPARTS, (3) DATETIMEFROMPARTS, (4) DATETIMEOFFSETFROMPARTS, (5) SMALLDATETIMEFROMPARTS, (6) TIMEFROMPARTS. For example, to build a DATE value from it’s parts, you would use DATEFROMPARTS(2012, 02, 12).
What is the EOMONTH function?
Computes the respective end of the month date for the input date and time value, e.g. EOMONTH(SYSDATETIME()) ran on 2/12/2012 would return 2/29/2012.
What is the DATEADD function?
Allows you to add a requested number of units of a specified part to a specified date and time value, e.g. DATEADD(year, 1, ‘20120212’) adds 1 year to 2/12/2012.
What is the DATEDIFF function?
Returns the difference in terms of a requested part between two date and time values, e.g. DATEDIFF(day, ‘20110212’, ‘20120212’) returns 365.
What is the SWITCHOFFSET function?
Allows you to return an input DATETIMEOFFSET value in a requested offset term, e.g. SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-08:00’) presents the current date and time value in terms of offset -08:00 regardless of the instance you’re connected to.
What is the TODATETIMEOFFSET function?
Used to construct a DATETIMEOFFSET value from two inputs: the first is a date and time value that is not offset aware, and the second is the offset, e.g. TODATETIMEOFFSET(‘2/12/2013 14:00’, ‘-8:00’) returns ‘2/12/2013 14:00’
What does SELECT SWITCHOFFSET(‘2/12/2013 14:00 -08:00’, ‘-05:00’) return?
2/12/2013 17:00 -05:00
What are the T-SQL concatenation functions? What is the difference between them?
(1) + and (2) CONCAT function; when any of the inputs is NULL, the + operator returns a NULL (can be changed via session option called CONCAT_NULL_YIELDS_NULL_INPUT). To substitute NULL with the empty string, use COALESCE(, ‘’) or use CONCAT which substitutes NULL for empty string: CONCAT ( s1, s2 [, sN ] ).
What is the SUBSTRING function?
Extracts a substring from a string given as the first argument, starting with the position given as the second argument, and a length given as the third argument, e.g. SUBSTRING(‘abcde’, 1. 3) returns ‘abc’. If the third argument is greater than what it would take to reach the end of the string, it just returns what it can.
What are the LEFT and RIGHT functions?
These functions extract a requested number of characters from the left and right ends of the input string, respectively, e.g. LEFT(‘abcde’, 3) returns ‘abc’ and RIGHT(‘abcde’, 3) returns ‘cde’).
What is the CHARINDEX function?
Returns the position of the first occurrence of the string provided as the first argument within the string provided as the second argument, e.g. CHARINDEX(‘ ‘, ‘Itzik Ben-Gan’) returns 6.
What is the PATINDEX function?
Returns the position of the first occurrence of the pattern provided as the first argument within the string provided as the second argument, e.g. PATINDEX(‘%[0-9]%’, ‘abcd123efgh’). % matches any string, _ matches a single character, and  matches a single character from a certain range.
What is the LEN function?
LEN returns the length of an input string in terms of the number of characters, e.g. LEN(N’xyz’) returns 3. If there are any trailing spaces, LEN removes them.
What is the DATALENGTH function?
DATALENGTH returns the length of the input in terms of number of bytes, e.g. DATALENGTH(N’xyz’) returns 6 since there are 2 bytes per unicode character. DATALENGTH does not remove trailing spaces.
What is the REPLACE function?
You can replace in an input string provided as the first argument all occurrences of the string provided as the second argument with the string provided as the third argument, e.g. REPLACE(‘.1.2.3.’, ‘.’, ‘/’) substitutes all .’s with ‘/’s and returns ‘/1/2/3/’
What is the REPLICATE function?
Allows you to replicate an input string a requested number of times, e.g. REPLICATE(‘0’, 10) returns ‘0000000000’
What is the STUFF function?
Operates on the input string provided as the first argument, then from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument, e.g. STUFF(‘,x,y,z’, 1, 1, ‘’) returns ‘x,y,z’
What are the UPPER and LOWER functions?
UPPER returns the uppercase form of the input and LOWER returns the lowercase form of the input.
What are the LTRIM and RTRIM functions?
LTRIM removes leading spaces and RTRIM removes trailing spaces. Note that there is no TRIM function, so you must use LTRIM(RTRIM()) together.
What is the FORMAT function?
Formats an input value based on a format string and optionally specify the culture as a third argument. You can use any format string supported by the .NET framework, e.g. FORMAT(1759, ‘0000000000’) returns ‘0000001759’.
What is the simple form of CASE?
Compares an input expression to multiple possible scalar when expressions and returns the result expression, e.g. SELECT CASE discontinued WHEN 0 THEN ‘No’ WHEN 1 THEN ‘Yes’ ELSE ‘Unknown’ END AS discontinued_description. If there’s no ELSE specified, the default is ELSE NULL.If the input is NULL the else is returned.
What is the searched form of CASE?
Uses predicates in the WHEN clauses and the first predicate that evaluates to true determines which expression is returned. SELECT CASE WHEN unitprice < 20.00 THEN ‘Low’ WHEN unitprice < 40.00 THEN ‘medium’ WHEN unitprice >= 40.00 THEN ‘High’ ELSE ‘Unknown’. If the input is NULL, the else is returned.
What is the COALESCE function?
Standard function that accepts a list of expressions and returns the first that is not NULL or NULL if all inputs are NULLs, e.g. COALESCE(NULL, ‘x’, ‘y’) returns ‘x’. Often used to substitute a NULL with something else. Type is determined by the returned (non-NULL) input.
What is the NULLIF function?
This function accepts two input expressions and returns NULL if they are equal and returns the first input if they are not, e.g. NULLIF(col1, col2) If col1 is equal to col2, the function returns NULL; otherwise, it returns the col1 value.
What is the ISNULL function?
Nonstandard function that is similar to COALESCE, but it’s a bit more limited in the sense that it supports only two inputs, e.g. ISNULL(region, ‘’). Type is determined by the first input.
What is the IIF function?
Nonstandard T-SQL function added to simplify migration from MS Access platform. You can return one value if an input predicate is true and another value otherwise. IFF(, ,
What is the CHOOSE function?
Nonstandard T-SQL function added to simplify migration from MS Access platform. The CHOOSE function allows you to provide a position and a list of expressions and returns the expression in the indicated position, e.g. CHOOSE(2, ‘x’, ‘y’, ‘z’) returns ‘y’.
What are the differences between COALESCE and ISNULL?
(1) COALESCE is standard and ISNULL is TSQL specific.
(2) COALESCE accepts a variable number of expressions and ISNULL accepts two.
(3) The data type of a COALESCE expression is determined by the returned (non-null) input. The data type of an ISNULL expression is determined by the first input.
(4) The “nullability” of a COALESCE and ISNULL is determined differently.
Why is it not a good practice to use ISNULL or COALESCE to substitute NULL with a value when combining sets (with joins or filtering data)?
Applying manipulation to the attributes you are comparing via ISNULL or COALESCE (e.g. ISNULL(T1.col1, -1) = ISNULL(T2.col1, -1) causes SQL Server to not use the available indexes efficiently. It is recommended to use the longer form: T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL).
What is the difference between NEWID and NEWSEQUENTIALID?
THE NEWID function generates GUID values in random order, where as the NEWSEQUENTIALID function generates GUID values that increase sequentially.
Which function returns the current date/time as a datetime2 type?
The SYSDATETIME function.
When concatenating character strings, what is the difference between + operator and the CONCAT function?
+ yields a NULL result on a NULL input; CONCAT function treats NULLs as empty strings.
What is the data type precedence order among the types INT, DATETIME, and VARCHAR?
Recall that the data type with the lower precedence is converted to the data type with the higher precedence. The order is defined as DATETIME, INT, and then VARCHAR.