Tech Point Fundamentals

Friday, July 1, 2022

SQL Interview Questions and Answers - Part 06

SQL Interview Questions and Answers - Part 06

sql-interview-questions-answers

Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:




Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.




Introduction


This is the 6th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:





SQL Interview Questions and Answers - Part 06


Q42. What is the difference between CAST vs. CONVERT in SQL?

SQL Server supports both implicit and explicit conversions. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. 
Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. 

Both CAST and CONVERT are functions used to convert one data type to another data type. In many instances, both CAST and CONVERT are used in combination and with each other to achieve certain effects in the data. Without using CAST or CONVERT functions, implicit conversions occur.

The first difference between CAST and CONVERT is CAST is an ANSI standard while CONVERT is a specific function in the SQL server.



The CONVERT function can be used for formatting purposes, especially for date/time, data type, and money/data type. But CAST is used to remove or reduce format while still converting.

So CAST can’t be used for Formatting Purposes. For example CAST('12/01/2016' AS DATE). But CONVERT can be used for Formatting Purposes by passing the appropriate style as a parameter. For example CONVERT(VARCHAR(20), GETDATE(), 101).

CAST is also a more portable function because the CAST function can be used by many databases. So CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data’s format.

CAST function is also used to restore the decimals and numerical values to integers while converting. It also can be used to truncate the decimal portion or value of an integer.




Q43. What does it mean to have QUOTED_IDENTIFIER ON in SQL?

  1. When SET QUOTED_IDENTIFIER is ON (default), identifiers can be delimited by double quotation marks (" "), and literals must be delimited by single quotation marks (' '). 
  2. All strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers.
  3. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings.
  4. SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.
  5. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
  6. However, QUOTED_IDENTIFIER does not affect delimited identifiers enclosed in brackets ([ ]).
  7. SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns or tables with indexed views. 






Please watch the QUOTED_IDENTIFIER flag video here for the live demo.



Q44. What does it mean to have ANSI_NULL ON or OFF in SQL?

The ANSI_NULL Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server.

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.



When ANSI_NULLS is ON:

  1. When ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.
  2. A SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. 
  3. 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:

  1. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.
  2. The Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. 
  3. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. 
  4. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column.
  5. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.



SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. 

If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. ANSI_NULLS should be set to ON for executing distributed queries.

ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. 

If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

Please watch the ANSI_NULLS  video here for a live demo.



Q45. What does it mean to have ANSI_PADDING ON or OFF in SQL?

ANSI_PADDING  controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. 

The default for SET ANSI_PADDING is ON.  ANSI_PADDING should always be set to ON. When ANSI_DEFAULTS is ON, ANSI_PADDING is also enabled. ANSI_PADDING must be ON when you are creating or changing indexes on computed columns or indexed views. 

Columns defined with char, varchar, binary, and varbinary data types have a defined size. The ANSI_PADDING setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.








The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image, varbinary(max), varchar(max), and nvarchar(max) data types. They always display the SET ANSI_PADDING ON behavior. This means trailing spaces and zeros are not trimmed.

When padded, char columns are padded with blanks, and binary columns are padded with zeros. When trimmed, char columns have the trailing blanks trimmed, and binary columns have the trailing zeros trimmed. The setting of ANSI_PADDING is defined at execute or run time and not at parse time.

Please watch the ANSI_PADDING set options video here for the live demo.



Q46. What is the difference between GO and Semicolon(;) Statement Terminator in SQL?

GO Terminator:

  1. The GO command was introduced by Microsoft tools as a way to separate batch statements such as the end of a stored procedure.
  2. The GO command is a client command and not a T-SQL server command. GO is not a SQL keyword.
  3. A "GO" is a batch separator and doesn`t actually even get sent to the server.
  4. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.
  5. Go is a Batch terminator. Go Signifies the end of a batch. The statements in the batch are compiled into a single execution plan. 
  6. The GO Statement must be written in a new line as it is not a T-SQL command i.e A Transact-SQL statement cannot occupy the same line as a GO command. 
  7. You cannot put a GO into a string of SQL (Dynamic SQL) and send it as part of the ADO.NET command object as SQL itself does not understand the term.


  8. GO can be used to invoke the same DML block multiple times using the following syntax: GO [count]
  9. Unlike semicolons, GO is mandatory before a new DDL.
  10. The command GO means the end of a batch, therefore all variables declared before GO are invalid after the GO command. Therefore the scope of local variables is limited to a batch, and cannot be referenced after a GO command.
  11. Any declarations of Variables, Table Variables, Temp Table, etc do not go across GO statements.
  12. You can not use a semicolon as a statement terminator after GO.
  13. You must have to use EXEC before the system stores procedures like sp_who if it is not the first statement in the batch.  
  14. All the CREATE statements must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
  15. So CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch.








Semicolon(;) Terminator:

  1. A semicolon is a statement terminator or statement separator. This is purely used to identify where a particular statement ends. 
  2. The semi-colon is used to signify the end of a statement itself, not necessarily a whole batch.
  3. Terminating SQL statements with semicolons was optional in SQL Server 2000 and in some cases actually not allowed.
  4. In SQL Server 2005, the introduction of Common Table Expressions (CTEs) and Service Broker Statements brought new formatting rules. It is used before a CTE Statement.
  5. Terminating a statement that preceded the Service Broker Statements (SEND, RECEIVE, BEGIN DIALOG CONVERSATION, and BEGIN CONVERSATION TIMER) and the CTEs (the WITH statement) became mandatory if those statements were not first in the batch.
  6. SQL Server 2008 introduced a new rule for terminating the MERGE statement with a semicolon. A MERGE statement must be terminated by a semi-colon.
  7. In SQL Server 2012 the THROW statement was introduced with the same rule for terminating the SQL statement as the WITH statement. If the THROW statement is not the first statement in the CATCH block the statement that precedes it must be terminated with the semicolon.
  8. This is noticeable in SQL scripts that need to return multiple recordsets, such as `select * from table1; select * from table2;` which would result in two separate recordsets on the client`s side.

Please watch the GO vs Semicolon statement terminator video here.



Q47. What is NULL in SQL? What is the difference between NULL and UNKNOWN?

ANSI-SQL defines NULL as a special value, or mark, that is used to indicate the absence of any data value. The ANSI-92 standard says that NULL in SQL is not a "data value", but rather an indicator that we have missing data. 

NULL is a special marker used in SQL to indicate that a data value does not exist in the database as of now but it may be present in the future when known. So, NULLs represent our "Known Unknowns".

The concept of NULL was Introduced by the creator of the relational database model i.e E. F. Codd in 1975. SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information".

A NULL is not the same as a zero or empty string or a blank string. A SQL NULL is a state, not a value. 








You must use the IS NULL or IS NOT NULL operators to check for a NULL value.
It is not possible to test for NULL values with comparison operators, such as =, <, or <> when the ANSI NULL is set to OFF.

A NULL is not equal to other NULL. So every NULL is unique. You can not compare two NULL values. In fact, a NULL is not equal to anything. Comparisons with NULL always result in UNKNOWN.

Variables that have been declared, but not yet initialized via SET statement default to NULL. They are treated exactly like any other NULL values. 

If you perform scalar math operations and string concatenation functions with NULL, the result is always NULL. 

The LENGTH(NULL) will yield NULL but LENGTH('') would yield 0.

The COALESCE() function returns the first non-NULL value in its list of values.

Please watch the SQL NULL Value video here and the ISNULL vs COALESCE video here for more details.








Q48. What is SQL Three Valued Logic? How does it affect the application logic?

The Boolean or Binary logic has two values either true or false but three-valued logic has an additional value called “unknown”.  SQL uses a three-valued logic because besides true and false, the result of logical expressions can also be unknown.

SQL’s three-valued logic is a consequence of supporting NULL to mark absent data. The ternary logic involves when we try to compare NULLs to data values, or other NULLs. If a NULL value affects the result of a logical expression, the result is neither true nor false but unknown.



sql-3vl-logic



As per the ANSI-92 SQL Standard, comparisons with NULL always result in Unknown. NULL is equal to nothing, even NULL is not equal to NULL because each NULL could be different. So the ANSI-92 SQL Standard states that if one of the operands is NULL, the result of the comparison is "UNKNOWN".

Please watch the SQL Three Valued Logic video here for a live demo.








Q49. What is the difference between ISNULL() and COALESCE() function?

The COALESCE and ISNULL perform about the same in most cases in SQL Server. The ISNULL function has an important advantage over COALESCE in that internally it doesn`t evaluate an input expression more than once.

ISNULL is not the same as IS NULL (space between IS and NULL). IS NULL is an ANSI standard way to check for NULL value while ISNULL() is a function to replace the NULL value with an alternative value.

  1. The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments. On the other hand, the Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.
  2. The COALESCE function is defined by the ANSI SQL standard and supported in all major databases. but ISNULL() is a T-SQL (Transact SQL) function and only works with Microsoft products e.g. Microsoft SQL Server.
  3. ISNULL is faster than COALESCE in SQL Server because of its built-in function implemented in the Database engine, while COALESCE translates to CASE statements.
  4. ISNULL() is a function while COALESCE is an expression in Microsoft SQL Server. Since ISNULL() is a function it is only evaluated once, but the input values for the COALESCE expression can be evaluated multiple times.







  5. The ISNULL() function contains only two parameters while the COALESCE() function contains multiple parameters. If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions. 
  6. The COALESCE and ISNULL SQL Server statements handle data type precedence differently. COALESCE determines the type of the output based on data type precedence. While with ISNULL, the data type is not influenced by data type precedence, but rather by the first item in the list.
  7. The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length but COALESCE() does not have this restriction.
  8. The ISNULL() function contains various types of parameters. The COALESCE() function doesn't limit the number of arguments, but they must all be of the same data type.
  9. Table columns created as the result of COALESCE are NULLable, while columns created as a result of ISNULL are not. The biggest impact you'll see from this difference is if you use a computed column and try to create a primary key or other non-null constraints on a computed column defined with COALESCE, you will receive an error.
  10. The COALESCE ignores the padding implicitly associated with concatenating a CHAR data type, while ISNULL obeys the specification for the first input and converts the empty string to a CHAR type size.
  11. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters are considered to be NULL. 

Please watch the ISNULL vs COALESCE video here.


To Be Continued Part-07...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedInFacebookTelegramQuoraYouTubeTwitterPinterestTumbler, and VK for regular updates.

    

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.