Introduction
T-SQL helps to create and manage objects in a physical or virtual instance, as well as to insert, retrieve, modify and delete data tables.
The SQL standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI) defined a software language, and Transact-SQL was developed and extended from this definition.
If you are a T-SQL fan, here is this list of the top T-SQL Server interview questions for beginners:
1. What is the difference between SQL, T-SQL, and PL/SQL?
- SQL is a query language to operate on sets, the standard of both ISO and ANSI. More or less standardized, it is used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.
- T-SQL and PL/SQL are dialects of standard SQL
- PL/SQL is a combination of SQL along with the procedural features of programming languages developed by Oracle Corporation
- TSQL is a proprietary procedural language used by Microsoft in SQL Server, an extended form of SQL that adds declared variables, transaction control, error and exception handling, and row processing to SQL
2. What’s the difference between the ON clause and the WHERE clause?
- The where clause applies to the whole result set.
- The on clause only applies to the join in question.
3. Is there a guarantee that the expressions in the WHERE clause will be evaluated in a specific order; in other words, can you rely on a short-circuit?
- SQL Server will short-circuit an expression if it determines that the expression evaluates to either TRUE or FALSE
- There's no query hint you can apply to force the optimizer to use the exact expression you wrote so that the expression will appear the same way in the plan that SQL Server builds.
4. What’s the purpose of NULLs, and what kind of complexities do they add to the language?
- In SQL, a NULL is a marker, or a placeholder, for a missing value.
- Based on SQLPerformance.com :"NULLs add their share of complexity to your SQL code writing. You always want to think about the potential for the presence of NULLs in the data, and make sure that you use the right query constructs, and add the relevant logic to your solutions to handle NULLs correctly. Ignoring them is a sure way to end up with bugs in your code."
5. When you join a table with a derived table, can the derived table query refer to columns from the other table in the join, and why?
- A derived table is a subquery nested within a FROM clause.
- The subquery in the FROM clause must have a name.
- Because of being in a FROM clause, the subquery's result set can be used similarly to a SQL Server table.
- A derived table that is part of a JOINcannot reference objects outside of the subquery's scope.
- A derived table that is part of an APPLY can reference columns outside of the subquery's scope.
6. Can you use a column alias that you define in the SELECT clause in the WHERE clause, and why?
Column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause since the SELECT clause is processed after the WHERE,GROUP BY, and HAVING clauses.
The reason for the error is that the query attempts to use the alias before it was defined.
Again, it's all about the query processing logic.
7. How do you make a column alias available to clauses like WHERE, GROUP BY, HAVING, SELECT?
Using a CTE or a derived table, but this approach adds a layer of complexity to the code.
8. What’s the difference between the nature of a query result when you have a presentation ORDER BY clause in the query and when you don’t?
When ORDER BY is specified, the result isn't relational anymore it’s what standard SQL calls a cursor.
When we issue a query against a table and don’t indicate explicitly that we want to return the rows in particular presentation order, the result is supposed to be relational.
9. What’s the difference between a group aggregate function and a window aggregate function?
- SQL Server Window Functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
- An aggregate function performs a calculation of one or more values and returns a single value.
- The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.
10. If you don’t have a presentation ORDER BY clause in the query, are there any circumstances where the query presentation order is guaranteed?
- The only thing that reliably delivers the rows in a certain order, is an explicit ORDER BY clause.
- But a specific order may be guaranteed due to:
-
RDMBS-es different behaviors
- RDMBS-es status (a "warm" database behaves differently than a "cold" one, a small table behaves differently than a large one)
- Implementation : Clustered or Non-clustered indexes