MS SQL Differences for Dot net Interview
1. Difference between Primary key and Unique key.
| Primary Key | Unique key | |
| to create unique identifier. | to create uniqueness in a column. | |
| only one primary key allowed. | multiple unique key allowed. | |
| don't allow null value. | allow one null value. | |
| creates clustered index on a column. | creates non clustered index on a column. |
2. Difference between stored procedure and Functions.
| Stored Procedure | Functions | |
| We can call function in SP. | We can't call SP in Function. | |
| Can't call with Select statement. | Can call with select statement. | |
| we can write DDL and DML commands in SP. | We can write only Select statement in SP. | |
| Return value is optional. | must Return a value. | |
| supports try catch. | don't support try catch. | |
| supports truncation statements. | don't support transaction statements. | |
| can have input as well as output parameters. | can have only input parameter. |
3. Difference between Delete, Drop, Truncate;
| Delete | Drop | Truncate | |
| DML Command | DDL command | DDL command | |
| Deletes one or more records on a condition. | Deletes all and remove table from database. | Delete all records and don't remove table schema. | |
| Do not free the allocated space in memory. | free allocated space in memory. | does not free allocated space. | |
| we can Roll back data. | Can not Roll back. | cannot Roll back. | |
| slower than two. | faster than delete but slower than truncate. | faster than delete and drop. |
4.Difference between where and having clause;
| Where | having | |
| Filter the Data from table based on specific condition. | Filter Data from groups based on the specific condition. | |
| can be used without GROUP BY Clause. | can not be used without GROUP BY Clause. | |
| cannot contain aggregate function | can contain aggregate function | |
| used with SELECT, UPDATE, DELETE statement. | used with SELECT statement only. | |
| used before GROUP BY Clause. | used after GROUP BY Clause. |
5.Difference between DDL and DML command;
Data Definition Language (DDL) and Data Manipulation Language (DML). The main differences between these two types of commands are:
a) Purpose:
DDL is used to define the structure of a database, create, modify or delete database objects such as tables, indexes, and views. DML is used to manipulate the data stored in those objects.b) Execution:
DDL statements are executed immediately when they are issued, while DML statements are executed at runtime when a program or query is executed.c) Transactions:
DDL statements are typically executed as auto commit transactions, meaning that they are committed immediately after execution. DML statements, on the other hand, are usually executed as part of a transaction that may include multiple DML statements and must be explicitly committed or rolled back.d) Impact on data:
DDL statements may have a significant impact on the structure of a database, potentially affecting multiple tables or objects. DML statements only affect the data stored in individual tables or views.
6.Difference between union and union all;
The main difference between UNION and UNION ALL is that UNION removes duplicate rows from the combined result set, while UNION ALL includes all rows from all SELECT statements, including duplicates.
7.Difference between char and varchar;
The main differences between CHAR and VARCHAR are:
a) Storage:
CHAR is a fixed-length data type, which means that it always allocates a specific amount of storage space, regardless of the actual length of the data being stored. VARCHAR, on the other hand, is a variable-length data type, which means that it only allocates the amount of storage space required to store the actual length of the data being stored.b) Padding:
Since CHAR is a fixed-length data type, it pads any unused space with blank spaces. For example, if you define a CHAR(15) column and store the string "Hello" in it, the remaining five characters will be filled with blank spaces. VARCHAR, on the other hand, does not pad unused space.c) Performance:
In some cases, CHAR can be faster than VARCHAR because it is a fixed-length data type and the database engine knows exactly how much storage space is required for each row. VARCHAR, on the other hand, is a variable-length data type, so the database engine has to calculate the storage space required for each row.
8.Difference between varchar and nvarchar;
the way they store and handle data.
The main differences between VARCHAR and NVARCHAR are:
a) Storage:
VARCHAR is a variable-length data type that stores non-Unicode characters, such as ASCII characters, using one byte per character. NVARCHAR, on the other hand, is a variable-length data type that stores Unicode characters, such as Chinese or Arabic characters, using two bytes per character.b) Character set:
VARCHAR only supports non-Unicode character sets, such as ASCII or ISO-8859. NVARCHAR, on the other hand, supports all Unicode character sets, such as UTF-8, UTF-16, or UTF-32.c) Storage size:
Since NVARCHAR uses two bytes per character, it requires twice as much storage space as VARCHAR. For example, if you define a column as VARCHAR(50), it will allocate 50 bytes of storage space. If you define the same column as NVARCHAR(50), it will allocate 100 bytes of storage space.
9.Difference between clustered and non-clustered index;
Clustered Index:
When you create a clustered index on a table, the data is physically reordered based on the index key. Therefore, the table can be sorted in only one way at any time. Clustered indexes are often used for columns that have a high number of distinct values, and that are frequently used in range queries.
Comments
Post a Comment