SQL Q&A
SQL Q&A - Part1
1) What is a Function?
A function is a database object
A function performs an action and returns the results
It may or may not have input parameters
It doesn’t have output parameters
2) What are the types of user-defined functions?
Scalar functions
Inline table-valued functions
Multiline table-valued functions
3) What is Schemabinding?
Schemabinding is binding of the object to the underlying tables or views
This ensures that the underlying tables and views cannot be modified that would affect the definition of schema bound object
4) What is the difference between inline table-valued function and multi-statement table-valued function?
| Inline table-valued function | Multi-statement table-valued function |
|---|---|
| Returns single select statement | Can contain more than one statement |
| The structure of the table returned cannot be defined. It depends on the select statement. | The structure of the table returned can be defined |
| Can update the underlying table | Cannot update the underlying table |
5) What is the difference between a function and a stored procedure?
| Function | Stored Procedure |
|---|---|
| Returns a value | May or may not return a value |
| Have only input parameters | Have both input and output parameters |
| Can be called from procedures | Cannot be called from a function |
| Can call another function but not procedure | Can call another function or procedure |
| Transactions cannot be used in function | Transactions can be used in Stored Procedure |
| Can be used in Select, Where clause and Having clause | Cannot be used in Select, Where clause and Having clause |
| Insert, Update and Delete statements cannot be used | Insert, Update and Delete statements can be used |
| Compiles and executes every time when called | Pre-compiled objects |
6) What constraints are used to specify rules for data in a table?
Primary key constraint
Foreign key constraint
Unique constraint
Check constraint
Default constraint
Not Null constraint
7) What is Primary key constraint?
It uniquely identifies each row
There can be only one primary key in a table
It cannot have null values
It creates a clustered index by default
It cannot be a foreign key
8) What is Foreign key constraint and Check constraint?
Foreign key constraint is a column or combination of columns
Foreign key constraint establishes a relationship between 2 tables
Foreign key ensures referential integrity
Check key constraint restricts the values that can be added to a column
9) What is referential integrity and which constraint ensures referential integrity?
Referential integrity is the accuracy and consistency of data within a relationship. By using cascading referential integrity constraints actions can be defined on a foreign key relationship
Foreign key ensures referential integrity
10) What are cascading referential integrity constraints? What are the cascading actions?
Cascading referential integrity constraints define the actions the database engine takes when a delete or update occurs to a key to which existing foreign keys point.
The cascading actions are
Cascade – Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table
No Action – The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back
Set Null – All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted
Set Default – All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted
11) What is Unique key constraint, Default constraint and Not Null constraint?
Unique key constraint restricts column to contain unique values
Default constraint specifies the default values
Not Null constraint specifies that the column does not accept null values
12) What data types are there in SQL Server?
Numeric data types
Char and String data types
Unicode data types
Date data types
Binary data types
Other data types
13) List out numeric data types and number of bytes they occupy
Bit – 1 byte
Tiny int – 1 byte
Small int – 2 bytes
Int – 4 bytes
Big int – 8 bytes
Numeric(precision, scale) – depends on precision
Decimal(precision,scale) – depends on precision
Float(n) – Depends on n
Real – 4 bytes
Small money – 4 bytes
Money – 8 bytes
14) What is precision and scale in numeric and decimal data types?
Precision is the total number of digits. E.g. In the number 123.45, there are total 5 digits. Therefore 5 is the precision
Scale is the number of digits to the right of decimal point. E.g. In the number 123.45, the scale is 2
15) List out char and string data types and give their ranges?
Char(n). Range is 1 to 8000. Char is of fixed length
Varchar(n). Range is 1 to 8000. Varchar is of variable length
Varchar(max). Range is 2^31-1
16) List out Unicode data types and give their ranges?
NChar(n). Range is 1 to 4000. Char is of fixed length
NVarchar(n). Range is 1 to 4000.
NVarchar(max). Range is 2^30-1
(Unicode is Universal Character encoding standard. Unlike Ascii which supports only English, Unicode supports all languages)
17) List out Date data types and number of bytes they occupy?
Date – 3 bytes
Small datetime – 4 bytes.
Datetime – 8 bytes
Datetime2 – 6 to 8 bytes
Datetimeoffset – 10 bytes
Time – 5 bytes
18) What is difference between Datetime, Datetime2 and Datetimeoffset?
Datetime – e.g. 2020-07-31 00:00:00.000
Datetime2 – More precise. e.g. 2020-07-31 00:00:00.0000000
Datetimeoffset – It has time zone offset. e.g. 2020-07-31 19:00:00.0000000 +05:30. This means India is 5 hours 30 minutes ahead of UTC time
19) What are binary data types and other data types?
Binary data types are Binary and Varbinary. They are of the range 0 to 8000. Binary is a byte array. They store data like images, text files and word files
Other data types are
timestamp – it is an autogenerated number
uniqueidentifier – It is a 16 byte GUID
Sql_variant – Stores values of various data types
Cursor – Used for variables
XML – Used to store XML data
20) What are the operators?
Arithmetic operators (Addition, Subtraction, Multiplication, Division, Modulus)
Comparison operators (=,<>,>,<,>=,<=)
Logical operators (And, Or, Between, exists, In, Any, Like, All, Not)
Set Operators (Union All, Union, Intersect, Except)
21) What is modulus operator?
Modulus operator returns reminder of one number divided by the other. e.g.47/3. Remainder is 2.
22) What is the difference between join, cross apply and outer apply ?
Join operator joins tables. Apply operator joins table and table-valued function with table being on the left side and table-valued function on the right side
Cross apply retrieves the match rows between table and table-valued function. It is like inner join
Outer apply retrieves all the records from the table and the table-valued function. It is like outer join
23) What is a transaction and what are ACID properties?
A transaction is a single unit of work that contains one or more SQL Statements. If any of the statements fail, the transaction fails. ACID properties are:
Atomicity: The transaction takes place in entirety or not at all
Consistency: The database should be in consistent state before and after the transaction.
Isolation: Intermediate state of a transaction should be invisible to other transactions
Durable: once the transaction is successfully completed, then the changes it made to the database will be permanent even if there is system failure
24) How can you handle exceptions? What are different types of errors? How can you view System defined errors? How can you raise errors?
Exceptions can be handled with try…catch
The different types of errors are System defined errors and User defined errors
System defined errors can be viewed in Sys.messages
Errors can be raised by “Raiseerror” and “Throw”
25) What is open transaction, closed transaction and @@trancount
When a transaction is not closed with either commit or rollback the transaction is open. The different types of errors are System defined errors and User defined errors
If the transaction is closed with either commit or rollback the transaction is closed
@@trancount returns the number of open transactions in the current session
26) What are savepoints in a transaction? What is Xact_Abort and Xact_State?
Savepoint is used to set the point from where the transaction is to be rolled back
Xact_Abort specifies whether the current transaction automatically rolls back when a T-SQL statement raises a run-time error. Set Xact_Abort on rolls back the current transaction automatically when a T-SQL statement raises a run-time error, while Set Xact_Abort off will not
Xact_State indicates whether the request has an active user transaction and whether the transaction is capable of being committed. ‘1’ indicates active user transaction, ‘0’ indicates no active user transaction and -1 indicates that the request has an active user transaction but is not committable
27) What are the ways data changes can be tracked in SQL Server?
Change tracking records DML activity changes in a table. It only stores the last change made to the row. It does not keep the history of previous changes
CDC records DML activity changes in a It can track changes that have occurred over time
Temporal table when enabled creates a history table which keeps track of updates and deletes to the main table. Either of the tables cannot be dropped as long as the system versioning is on. Unlike CDC and CT, no code change to your existing queries are required when you turn on the Temporal Table feature. When new rows are inserted, they do not have any history yet so they don’t get tracked in the history table, but they are assigned a system time which marks the beginning of the validity period
28) What is CLR? How do you enable CLR?
CLR is Common Language Runtime
CLR provides the execution environment for all .NET Framework code.
Code that runs within the CLR is referred to as managed code.
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO
29) What is the value of the following? (Answers in the next slide)
Select Ceiling(123.45)
Answer: 124
Select Floor(123.45)
Answer:123
Select Round(123.452367,3)
Answer: 123.452
Select TRY_CAST(‘test’ AS float)
Answer: Null
Select Cast(‘123.40’ AS float)
Answer: 123.4
Select Datediff(hour, 2019-11-16, 2019-11-11)
Answer: 120
Select Coalesce(NULL, ‘Hi’, ‘Hello’, NULL)
Answer: Hi
Select Nullif(10, 10) result
Answer: Null
Select Nullif(20, 10) result
Answer: 20