SQL Interview Questions and Answers - Part 24
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 the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by the 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 24th 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 to the current part:
SQL Interview Questions and Answers - Part 24
Q221. What is an IDENTITY in SQL?
An IDENTITY is a property that is used to create an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
The IDENTITY property is different from the SQL-DMO Identity property that exposes the row identity property of a column. Only one identity column can be created per table.
IDENTITY [ (seed , increment) ]
Here the seed is a value that is used for the very first row loaded into the table and increment is the incremental value that is added to the identity value of the previous row that was loaded.
You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
CREATE TABLE User (ID INT IDENTITY(1,1), NAME VARCHAR(50));
Identity columns can be used for generating key values. The identity property on a column guarantees that:
- Each new value is generated based on the current seed & increment.
- Each new value for a particular transaction is different from other concurrent transactions on the table.
Limitations of IDENTITY:
Identity property on a column does not guarantee the Uniqueness of the value. So uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.
Identity property on a column does not guarantee Consecutive Values within a transaction. A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table.
Identity property on a column does not guarantee Consecutive values after server restart or other failures. Because SQL Servers might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insertion. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.
SQL Server does not guarantee to reuse of the identity values. For a given identity property with a specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.
The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. So Failed statements and transactions can change the current identity for a table and create gaps in the identity column values.
You cannot insert the value into the IDENTITY column explicitly. For that, you must have to use SET IDENTITY_INSERT ON. It allows explicit values to be inserted into the identity column of a table.
But at any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, you can not use SET IDENTITY_INSERT ON statement on another table. If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the newly inserted value as the current identity value.
Q222. What is the use of the @@IDENTITY function in SQL?
@@IDENTITY Is a system function that returns the last inserted identity value. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
After an INSERT, SELECT INTO, or Bulk Copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement.
@@IDENTITY contains the last identity value that is generated by the statement. So If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers.
If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert.
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
Q223. What is the use of the DBCC CHECKIDENT command?
DBCC stands for Database Console Commands. DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.
DBCC CHECKIDENT checks the current identity value for the specified table. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.
DBCC CHECKIDENT ('tblUser'); -- Returns the current IDENTITY value
Checking identity information: current identity value '9', current column value '9'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKIDENT ('tblUser', RESEED, 1 ) -- RESET the IDENTITY value to 1
Checking identity information: current identity value '1'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
For using the DBCC CHECKIDENT command the caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Q224. What is Sequence in SQL?
A sequence is a feature supported by some database systems to produce unique values on demand. A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created.
Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence. AUTO_INCREMENT is applied on columns, it automatically increments the column value by 1 each time a new record is inserted into the table. The sequence is also somewhat similar to AUTO_INCREMENT but it has some additional features too.
The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
CREATE SEQUENCE seqIncrementCount
START WITH 1
INCREMENT BY 1 ;
If you want to sequence in descending order:
CREATE SEQUENCE seqDerementCount
START WITH 100
INCREMENT BY -1;
Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value.
The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.
Unlike identity column values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.
Q225. What is the difference between an IDENTITY Column and a Sequence Object in SQL?
The Identity Property of a column is available from SQL Server 2000 whereas the Sequence object is available from SQL Server 2012.
Identity value cannot be controlled by application code whereas Sequence can be controlled by application code.
If any column is marked as Identity then we cannot insert data into this column directly. In that case, we must first turn off the Identity of the column whereas a Sequence object does not depend on the table so we can insert any value in the column.
The Identity property is a column property so it is tied to the table, whereas the sequence is a user-defined database object and it is not tied to any specific table meaning its value can be shared by multiple tables.
In the case of Identity to generate the next identity value, a row has to be inserted into the table, whereas in the case of a sequence object we don’t require adding a row into the table in order to generate the next sequence value. You can use the NEXT VALUE FOR clause to generate the next sequence value in SQL Server.
In the case of Sequence Object, you can use the CYCLE option to specify whether the sequence should restart automatically when the sequence object value reached the max or min value, whereas in the case of Identity property we don’t have such an option to automatically restart the identity values.
The maximum value for the identity property cannot be specified. In the case of Identity, the maximum value will be the maximum value of the corresponding column data type on which the Identity property is specified. In the case of the sequence object, we can use the MAXVALUE option to specify the maximum value. If the MAXVALUE option is not specified for the sequence object, then the maximum value will be the maximum value of its data type.
We can reseed an Identity property but we cannot change the step size whereas we can alter the Seed as well as the Step size of a Sequence object at any time.
Q226. What is the use of the OUTPUT INTO clause in SQL?
The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statement. It even supports with a MERGE statement, which was introduced in SQL Server 2008 version.
These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.
So this clause is most commonly used for audit purposes. By using this someone can implement a custom process that would work like CDC.
The OUTPUT clause has access to the INSERTED and DELETED magic tables. These virtual tables are populated when an INSERT/UPDATE/DELETE operation is done on a table. As a result, the OUTPUT clause can provide us the affected records by referencing these tables.
An UPDATE, INSERT or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.
Using the OUTPUT clause, we can display the rows inserted into a table in the output window by selecting the column names with the INSERTED prefix or using INSERTED.* to display all the columns.
INSERT INTO Employee(Name, RoleName, Salary)
OUTPUT INSERTED.*
VALUES('Alvin', 'Annonymous', 35000);
Here DELETED table cannot be used with the OUTPUT clause in the INSERT statement.
The result from the OUTPUT clause can be inserted into a separate table or table variable during the execution of the query. The table_variable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.
Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
DECLARE @TableVariable TABLE
(
ID INT,
Name VARCHAR(50),
RoleName VARCHAR(50),
Salary NUMERIC(18,2)
);
INSERT INTO Employee(Name, RoleName, Salary)
OUTPUT INSERTED.* INTO @TableVariable
VALUES('Alvin', 'Annonymous', 35000);
SELECT * FROM @TableVariable
It is also possible to use two OUTPUT statements: one to view the values and another to insert the values into a table variable at the same time.
Similarly, you can use the OUTPUT clause with the DELETE and UPDATE statement to view the record being deleted or updated as well. The OUTPUT clause should be used in between the DELETE and WHERE Statements.
DELETE FROM Employee
OUTPUT DELETED.*
WHERE ID = 11;
The INSERTED table cannot be used with the OUTPUT clause in the DELETE statement.
But keep in mind that the target table (Employee) of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
Q227. What is a wildcard character in SQL? What is the use of the LIKE keyword in SQL?
A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The SQL Server LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters.
The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching. To negate the result of the LIKE operator, you use the NOT operator as well.
The pattern is a sequence of characters to search for in the column or expression. There are different symbols like %, _, [], ^, and - which are used in wildcard search. All the wildcards can also be used in combinations.
Percent Wildcard Symbol(%): Represents zero or more characters. Like bl% finds bl, black, blue, and blob-like that.
Underscore Wildcard Symbol(_): Represents a single character. Like h_t finds hot, hat, and hit like that.
Square Wildcard Bracket([]): Represents any single character within the brackets. Like h[oa]t finds hot and hat, but not hit.
Hyphen Wildcard Symbol (-): Represents any single character within the specified range. Like c[a-b]t finds cat and cbt like that.
Caret Wildcard Symbol (^): It represents negation. It represents any character, not in the brackets. Like h[^oa]t finds hit, but not hot and hat.
The wildcard characters make the LIKE operator more flexible than the equal (=) and not equal (!=) string comparison operators.
SELECT * FROM Employee WHERE Name LIKE 'A%'
-- Returns any Name starting with the character 'A'
SELECT * FROM Employee WHERE Name LIKE '_l%'
-- Returns any Name whose second character is 'l'
SELECT * FROM Employee WHERE Name LIKE '[AM]%'
-- Returns any Name starting with the character 'A' or 'M'
SELECT * FROM Employee WHERE Name LIKE '[A-M]%'
-- Returns any Name starting with the letter range 'A' to 'M'
SELECT * FROM Employee WHERE Name LIKE '[^A-M]%'
-- Returns any Name whose first letter is not in the range 'A' to 'M'
SELECT * FROM Employee WHERE Name NOT LIKE 'A%'
-- Returns any Name which is not started with the character 'A'
If you are pattern matching with char datatypes, remember that chars are padded with spaces at the end to fill the length of the field. This may give you unexpected results when you use the LIKE condition to pattern match at the end of a string.
Escape Character:
The escape_character is a character put in front of a wildcard character to indicate that the wildcard is interpreted as a regular character and not as a wildcard.
The escape character instructs the LIKE operator to treat the wildcard characters as regular characters. The escape character has no default value and must be evaluated to only one character.
SELECT * FROM Blog WHERE Comment LIKE '%20!%%' ESCAPE '!'
-- Returns any comment that contains a 20% string
Q228. What is SQL Data Type in SQL? How a scaler values type is different from a table-valued type?
A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
In SQL Server, each column, local variable, expression, and parameter has a related data type.
System-Defined Data Type:
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. There are the following categories of system data types:
Unicode Character Strings: nchar, nvarchar, ntext
Non-Unicode Character Strings: char, varchar, text
Exact Numerics: int, bigint, bit, numeric, smallint, tinyint, decimal, money, smallmoney,
Approximate Numerics: float, real
Date and Time: date, time, datetime, datetime2, datetimeoffset, smalldatetime
Binary Strings: binary, varbinary, image
Large Value Data Types: varchar(max), and nvarchar(max)
Large Object Value Data Types: text, ntext, image, varbinary(max), and xml
Other Data Types: uniqueidentifier, cursor, xml, table, rowversion, sql_variant, hierarchyid, Spatial Geometry Types, Spatial Geography Types
User-Defined Data Types:
You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types or user-defined data types are based on the system-supplied data types.
CREATE TYPE NonNullableValue FROM VARCHAR(50) NOT NULL;
You can also create user-defined table types as well:
CREATE TYPE TVP AS TABLE
(
ID INT,
RoleName VARCHAR(50)
);
Q229. How can you do Bulk Copy in SQL? What is the SQL BulkCopy?
There are different ways available for bulk data operations:
- BCP (Bulk Copy Program) utility
- BULK INSERT
- Using OPENROWSET
- Import/Export wizard
BCP Utility:
Microsoft SQL Server provides a popular command-prompt utility named BCP for moving data from one table to another, whether on a single server or between servers.
The BCP (Bulk Copy Program) utility is a command-line program that bulk-copies data between a SQL instance and a data file using a special format file.
The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information.
SqlBulkCopy Class:
The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. SqlBulkCopy .Net Class enables you efficiently bulk load a SQL Server table with data from another source.
There are other ways to load data into a SQL Server table, but SqlBulkCopy offers a significant performance advantage over them.
The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.
Q230. What are the different types of backup in SQL?
Backing up your SQL Server database is essential for protecting your data. Backup Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.
There are the following most common types of backups available in SQL Server:
- Full Backup
- Differential Backup
- Transaction Log Backup
- Tail Log Backup
There are some other backup types available as well:
- Copy-only Backup
- File Backup
- Partial Backup
Full Backups:
A full backup backs up everything. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes, etc. It is the foundation base of any other kind of backup.
After having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.
A full backup creates a complete backup of the database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration since all of the contents are contained in one single backup. Full database backups represent the whole database at the time the backup is finished.
A full backup must be done at least once before any of the other types of backups can be run this is the foundation for every other kind of backup.
BACKUP DATABASE [TechPointDB]
To DISK='D:\SQLBackup\TechPoinDB.BAK'
WITH FORMAT,
MEDIANAME = 'Native_SQLServerBackup',
NAME = 'Full-TechPointDB backup';
Differential Backups:
Differential database backups contain only changes made to the database since its most recent full database backup. A differential database backup is the superset of the last full backup and contains all changes that have been made since the last full backup.
So, if there are very few transactions that have happened recently, a differential backup might be small in size, but if you have made a large number of transactions, the differential backup could be very large in size.
Since a differential backup doesn’t back up everything, the backup usually runs quicker than a full backup. A differential database backup captures the state of the changed extents at the time that backup was created.
BACKUP DATABASE [TechPointDB]
To DISK='d:\PowerSQL\TechPointDB_Diff.BAK'
WITH DIFFERENTIAL,
MEDIANAME = 'Native_SQLServerDiffBackup',
NAME = 'Diff-TechPointDB backup';
Transaction Log Backup:
The transaction log backup only backs up the transaction logs. This backup type is possible only with full or bulk-logged recovery models.
A transaction log file stores a series of logs that provide the history of every modification of data, in a database. A transaction log backup contains all log records that have not been included in the last transaction log backup.
It allows the database to be recovered to a specific point in time. This means that the transaction log backups are incremental and differential backups are cumulative in nature.
If you want to restore the database to a specific point in time, you need to restore a full, recent differential, and all the corresponding transaction log records which are necessary to build the database up to that specific point, or to a point very close to the desired point in time, just before the occurrence of the accident that resulted in the data loss.
BACKUP LOG [TechPointDB]
To DISK='d:\PowerSQL\TechPointDB_Log.trn'
WITH
MEDIANAME = 'Native_SQLServerLogBackup',
NAME = 'Log-TechPointDB backup';
Partial Backups:
Partial backups are one of the least-used backup methods available in SQL Server. All database recovery models support partial backups, but partial backups are mostly used in the simple recovery model in order to improve flexibility when backing up large databases that contain read-only filegroups.
Concurrency Restrictions for Backup:
SQL Server uses an online backup process to allow for a database backup while the database is still being used. During a backup, most operations are possible; for example, INSERT, UPDATE or DELETE statements are allowed during a backup operation.
However, if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.
Operations that cannot run during a database backup or transaction log backup include the following:
- File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
- Shrink database or shrink file operations. This includes auto-shrink operations.
- If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.
To Be Continued Part-25...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.