Oracle and SQL Server Database Concepts

SQL Server 2005 introduced CLR integration, allowing users to create assemblies using .NET code. This was a major new feature that enabled stored procedures, functions, triggers, and types to be written in C#, VB.NET, or other .NET languages. The CREATE ASSEMBLY statement is used for this purpose.

The question asks which function takes a numeric argument and returns a related value. While CEILING, SQRT, EXP, and FLOOR all accept numeric arguments, FLOOR is marked correct. This appears to be a poorly framed question as multiple options fit the description. FLOOR specifically returns the largest integer less than or equal to the input.

SQL Server functions are called using schema qualification: DatabaseName.SchemaOwner.FunctionName(). The default schema owner is dbo, so DatabaseName.dbo.FunctionName() is correct syntax. The schema qualification is required for UDFs (User-Defined Functions) when calling from outside the function's database.

Transact-SQL reserves certain keywords that have special meaning in the language. datetime is a data type keyword, hierarchyid is a data type for hierarchical data, and status is a reserved keyword. views and value are NOT reserved keywords in T-SQL. Knowing reserved words is important to avoid naming conflicts.

sp_rename 'OldName', 'NewName' is the correct system stored procedure to rename a table (or other database objects). The first parameter is the current name, the second is the new name. Options A and D reverse the order, and sp_renametable doesn't exist - only sp_rename.

TOP() is a SELECT clause used to limit result rows, not an UPDATE statement feature. In SQL Server, updates with TOP require a subquery or specific syntax, and in Oracle/database systems, TOP doesn't exist for updates at all.

Oracle DATE columns store both date AND time components (down to seconds). The time portion defaults to midnight (00:00:00) if not specified, but it always exists. Use TIMESTAMP for fractional seconds or explicit time handling.

Analytic functions differ from standard aggregate functions because they return a result for each individual row in the query, rather than collapsing the group into a single summary row. Standard aggregate functions with GROUP BY are what return a single row per group.

Oracle has strict context rules for expressions. For example, you cannot use aggregate functions in WHERE clauses (need HAVING), and certain expressions are prohibited in DEFAULT values, CHECK constraints, or some DDL statements. Expression validity depends on the SQL clause context.

In SQL, two consecutive minus signs (--) are interpreted as the start of a single-line comment. Using them directly together in an arithmetic expression comments out the remainder of the line, causing syntax errors. To subtract a negative value, a space or parentheses must separate the operators.

Scalar functions like NVL, COALESCE, NVL2, and DECODE specifically handle null arguments without returning null. They're designed to substitute or evaluate based on null presence. Only functions like LENGTH, SUBSTR, or CONCAT return null when input is null.

ROWID is a physical address that changes if the row is exported/imported, moved due to table reorganization, or in certain partition operations. Using it as a primary key breaks data integrity. Use surrogate keys (sequences) or natural keys instead.

VARCHAR datatype specifies variable-length character strings where storage equals actual length (up to the max). Unlike CHAR which pads with spaces, VARCHAR only stores what you insert. This is fundamental to SQL character data types.

Oracle optimizer hints are embedded in SQL comments using the format /*+ hint_name / or /+ hint_name(table_alias) */. These comments are specially parsed by the optimizer to influence execution plans while remaining valid SQL comments.

Oracle treats empty strings ('') as NULL, unlike many other databases. This means '' = NULL evaluates to TRUE (unknown), and empty strings require NVL or COALESCE handling. This is a critical Oracle-specific behavior for string operations.

In hierarchical queries (CONNECT BY), Oracle processes CONNECT BY first to build the hierarchy, then applies WHERE to filter the results. WHERE operates on the hierarchy tree after it's constructed, not before. This order matters for filtering parent-child relationships.

In Oracle SQL, all set operators (UNION, UNION ALL, INTERSECT, and MINUS) share the same level of precedence. Consequently, Oracle evaluates them in order from left to right as they appear in the query, unless parentheses are explicitly used to specify a different evaluation order.

Basic SELECT syntax is SELECT [select_list] FROM [table]. The select list (columns or expressions) immediately follows SELECT keyword and precedes FROM. This is fundamental SQL structure and never varies.

UNION ALL combines result sets without removing duplicates. UNION (without ALL) performs a sort and deduplication operation, which is slower. Use UNION ALL when you know rows are unique or specifically want duplicates retained.

ALTER SEQUENCE modifies future sequence numbers only. Numbers already allocated to the session cache (typically 20) are not affected. Changes impact the next sequence value generated, not values already used or cached.