Top 40 Microsoft SQL database functions

1. GETDATE():

   - Explanation: Returns the current system date and time.

   - Example: SELECT GETDATE(); 

   - Output: 2023-05-31 10:15:30.123

2. DATEPART():

   - Explanation: Extracts a specific part (e.g., year, month, day) from a date.

   - Example: SELECT DATEPART(YEAR, GETDATE());

   - Output: 2023

3. DATEADD():

   - Explanation: Adds or subtracts a specific time interval from a date.

   - Example: SELECT DATEADD(DAY, 7, GETDATE());

   - Output: 2023-06-07 10:15:30.123

4. DATEDIFF():

   - Explanation: Calculates the difference between two dates in a specified time interval.

   - Example: SELECT DATEDIFF(DAY, '2023-01-01', '2023-01-15');

   - Output: 14

5. GETUTCDATE():

   - Explanation: Returns the current UTC date and time.

   - Example: SELECT GETUTCDATE();

   - Output: 2023-05-31 14:15:30.123

6. MONTH():

   - Explanation: Extracts the month from a date.

   - Example: SELECT MONTH(GETDATE());

   - Output: 5

7. YEAR():

   - Explanation: Extracts the year from a date.

   - Example: SELECT YEAR(GETDATE());

   - Output: 2023

8. DAY():

   - Explanation: Extracts the day of the month from a date.

   - Example: SELECT DAY(GETDATE());

   - Output: 31

9. DATENAME():

   - Explanation: Returns a string representing a specific part of a date.

   - Example: SELECT DATENAME(MONTH, GETDATE());

   - Output: May

10. LOWER():

    - Explanation: Converts a string to lowercase.

    - Example: SELECT LOWER('Hello World');

    - Output: hello world

11. UPPER():

    - Explanation: Converts a string to uppercase.

    - Example: SELECT UPPER('Hello World');

    - Output: HELLO WORLD

12. LEN():

    - Explanation: Returns the length of a string.

    - Example: SELECT LEN('Hello World');

    - Output: 11

13. REPLACE():

    - Explanation: Replaces all occurrences of a specified string with another string.

    - Example: SELECT REPLACE('Hello World', 'World', 'Universe');

    - Output: Hello Universe

14. SUBSTRING():

    - Explanation: Returns a substring from a specified string, starting at a specified position for a specified length.

    - Example: SELECT SUBSTRING('Hello World', 7, 5);

    - Output: World

15. LEFT():

    - Explanation: Returns the left part of a string with a specified length.

    - Example: SELECT LEFT('Hello World', 5);

    - Output: Hello

16. RIGHT():

    - Explanation: Returns the right part of a string with a specified length.

    - Example: SELECT RIGHT('Hello World', 5);

    - Output: World

17. CONCAT():

    - Explanation: Concatenates two or more strings.

    - Example: SELECT CONCAT('Hello', ' ', 'World');

    - Output: Hello World

18. LTRIM():

    - Explanation: Removes leading spaces from a string.

    - Example: SELECT LTRIM('   Hello World');

    - Output: Hello World

19. RTRIM():

    - Explanation: Removes trailing spaces from a string.

    - Example: SELECT RTRIM('Hello World   ');

    - Output: Hello World

20. FORMAT():

    - Explanation: Formats a value with the specified format and optional culture.

    - Example: SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');

    - Output: 31/05/2023

21. ISNULL():

    - Explanation: Returns the specified value if the expression is NULL, otherwise, returns the expression.

    - Example: SELECT ISNULL(NULL, 'N/A');

    - Output: N/A

22. NULLIF():

    - Explanation: Returns NULL if the two specified expressions are equal, otherwise, returns the first expression.

    - Example: SELECT NULLIF(10, 10);

    - Output: NULL

23. COALESCE():

    - Explanation: Returns the first non-null expression in the list.

    - Example: SELECT COALESCE(NULL, 'Value 1', 'Value 2');

    - Output: Value 1

24. RAND():

    - Explanation: Returns a random float value between 0 and 1.

    - Example: SELECT RAND();

    - Output: 0.759612873284

25. NEWID():

    - Explanation: Returns a uniqueidentifier (GUID) value.

    - Example: SELECT NEWID();

    - Output: 47E90FD0-7A23-4C1B-A9C8-9447F9532A29

26. ABS():

   - Explanation: Returns the absolute value of a numeric expression.

   - Example: SELECT ABS(-10);

   - Output: 10

27. CEILING():

   - Explanation: Returns the smallest integer greater than or equal to a numeric expression.

   - Example: SELECT CEILING(3.2);

   - Output: 4

28. FLOOR():

   - Explanation: Returns the largest integer less than or equal to a numeric expression.

   - Example: SELECT FLOOR(3.9);

   - Output: 3

29. ROUND():

   - Explanation: Returns a numeric expression rounded to the specified length or precision.

   - Example: SELECT ROUND(3.14159, 2);

   - Output: 3.14

30. SQRT():

   - Explanation: Returns the square root of a numeric expression.

   - Example: SELECT SQRT(16);

   - Output: 4

31. POWER():

   - Explanation: Returns the result of raising a numeric expression to a specified power.

   - Example: SELECT POWER(2, 3);

   - Output: 8

32. SIN():

   - Explanation: Returns the sine of the specified angle.

   - Example: SELECT SIN(45);

   - Output: 0.707106781186547

33. COS():

   - Explanation: Returns the cosine of the specified angle.

   - Example: SELECT COS(60);

   - Output: 0.5

34. TAN():

   - Explanation: Returns the tangent of the specified angle.

   - Example: SELECT TAN(30);

   - Output: -6.40533119664628

35. LOG():

   - Explanation: Returns the natural logarithm of a specified number.

   - Example: SELECT LOG(10);

   - Output: 2.30258509299405

36. EXP():

   - Explanation: Returns the value of Euler's number raised to the power of a specified exponent.

   - Example: SELECT EXP(2);

   - Output: 7.38905609893065

37. CHARINDEX():

   - Explanation: Returns the starting position of a substring within a string.

   - Example: SELECT CHARINDEX('World', 'Hello World');

   - Output: 7

38. ASCII():

   - Explanation: Returns the ASCII value of the first character in a string expression.

   - Example: SELECT ASCII('A');

   - Output: 65

39. PATINDEX():

   - Explanation: Returns the starting position of a pattern within a string.

   - Example: SELECT PATINDEX('%World%', 'Hello World');

   - Output: 7

40. SOUNDEX():

   - Explanation: Returns a four-character code to evaluate the similarity of two strings.

   - Example: SELECT SOUNDEX('Hello');

   - Output: H400

Infosys interview questions for Freshers .Net developers

 

1. Garbage Collector in C#:

The garbage collector in C# is a part of the .NET runtime that automatically manages memory by reclaiming memory occupied by objects that are no longer in use. It tracks object references, identifies unused objects, and frees up memory for future allocations.

2. CLR (Common Language Runtime): 

CLR is the execution environment in the .NET framework that provides various services such as memory management, exception handling, security, and code execution. It compiles and manages code written in different .NET languages into a common intermediate language (CIL) and executes it.

3. Difference between String and StringBuilder: 

In C#, a String is an immutable sequence of characters, meaning it cannot be modified once created. StringBuilder, on the other hand, is a mutable class that allows efficient modification of strings by appending, inserting, or replacing characters without creating new instances.

4. Enum Keyword: 

The enum keyword in C# is used to declare an enumeration, which is a distinct type representing a set of named constants. It provides a way to define a group of related values that can be assigned to a variable, improving code readability and maintainability.

5. Enum Value Types: 

Yes, enum values in C# are value types. Each enumerated value represents a named constant of the enum type, and they are internally represented as integer values.

6. Managed Code and Unmanaged Code:

Managed code refers to code that runs within the managed environment of the CLR. It is written in languages such as C# or VB.NET and benefits from automatic memory management, security enforcement, and exception handling. Unmanaged code, on the other hand, is typically written in languages like C or C++ and runs outside the CLR's control, without the same level of automatic memory management and other managed code benefits.

7. Difference between Generic and Non-Generic: 

In C#, generics provide a way to create reusable, type-safe code that works with multiple data types. Generic classes, methods, and interfaces can be parameterized with specific types, allowing for greater flexibility and code reusability. Non-generic counterparts, on the other hand, are not type-safe and typically operate on a specific data type without the flexibility of working with different types.

8. Namespace for Generic: 

The System.Collections.Generic namespace in C# contains classes and interfaces for generic collections, such as List<T>, Dictionary<TKey, TValue>, etc.

9. Namespace for Non-Generic: 

The System.Collections namespace in C# contains classes and interfaces for non-generic collections, such as ArrayList, Hashtable, etc.

10. Example for Generic: 

An example of a generic class in C# is List<T>. It allows you to create a list that can hold elements of any specific type, such as List<int>, List<string>, List<Person>, etc.

11. Difference between Dictionary and DataTable: 

• Dictionary is a generic collection that stores key-value pairs, allowing efficient lookup of values based on keys. It is typically used when you need fast access to elements based on unique keys.
• DataTable, on the other hand, is a tabular representation of data that can hold multiple rows and columns. It is commonly used for storing and manipulating structured data, similar to a database table.

12. Difference between Function Overloading and Function Overriding:

• Function Overloading: Function overloading in C# allows multiple methods in a class to have the same name but with different parameters. The compiler distinguishes between the overloaded methods based on the number, types, and order of the parameters.
• Function Overriding: Function overriding occurs in inheritance when a derived class provides its own implementation of a method that is already defined in its base class. The overridden method in the derived class should have the same signature as the base class method.

13. Two Keywords for Function Overriding: The two keywords

used for function overriding in C# are "override" and "virtual". The base class method that can be overridden is marked with the "virtual" keyword, and the derived class method that overrides it is marked with the "override" keyword.

14. Use of "using" Keyword in C#: 

The "using" keyword in C# is used to define a scope within which a specific resource is used. It ensures that the resource is properly disposed of, even if an exception occurs, by implementing the IDisposable interface. It is commonly used with objects that access external resources, such as database connections or file streams.

15. Difference between Array and ArrayList:

Array: In C#, an array is a fixed-size collection of elements of the same type. The size of an array is determined at the time of its creation and cannot be changed. Arrays provide fast access to elements by their index.

ArrayList: ArrayList is a non-generic collection in C# that can dynamically grow or shrink in size. It can hold elements of different types and provides methods for adding, removing, and accessing elements. However, it is slower compared to arrays due to boxing and unboxing operations.

16. Difference between "is" and "as" Keyword: 

• "is" keyword in C# is used for type checking. It checks if an object is of a specified type and returns a boolean value (true or false).
• "as" keyword in C# is used for type casting. It attempts to cast an object to a specified type. If the cast is successful, it returns the cast object; otherwise, it returns null.

17. Extension Method: 

An extension method in C# allows you to add new methods to existing types without modifying their original implementation or creating a new derived type. It is defined as a static method in a static class and must be in the same namespace as the extended type. Extension methods are called as if they were instance methods of the extended type.

18. Sealed Class: 

• A sealed class in C# is a class that cannot be inherited.
•  It is marked with the "sealed" keyword.
• Sealing a class prevents it from being used as a base class for other classes.
•  Sealed classes are used when you want to restrict further inheritance and maintain the integrity of the class's implementation.

19. Multiple Try-Catch Blocks in C#: 

Yes, it is possible to use multiple try-catch blocks in C#. Each try block can be followed by one or more catch blocks that handle specific types of exceptions. This allows you to handle different exceptions separately and perform specific actions based on the exception type.

20. Difference between "==" Operator and "Equals" Method in C#: 

• The "==" operator in C# is used for equality comparison between two variables. For value types, it compares the actual values, while for reference types, it compares the references (memory addresses) of the objects.
• The "Equals" method in C# is a method defined in the Object class that can be overridden in derived classes. It is used to compare the equality of two objects based on their values or properties. The behavior of the "Equals" method can be customized based on the class's implementation.

21. Access Modifiers in C#: 

The access modifiers in C# determine the accessibility or visibility of types and members (fields, methods, properties, etc.). The main access modifiers are:

1. public: The type or member is accessible from any code.
2. private: The type or member is accessible only within the containing class.
3. protected: The type or member is accessible within the containing class and its derived classes.
4. internal: The type or member is accessible within the same assembly (project or DLL).
5.  protected internal: The type or member is accessible within the same assembly and its derived classes, both inside and outside the assembly.

22. Use of "FirstOrDefault"

In LINQ: The "FirstOrDefault" method in LINQ is used to retrieve the first element of a sequence that satisfies a specified condition. If no element matches the condition, it returns the default value for the type, such as null for reference types or 0 for numeric types. It is commonly used to avoid null reference exceptions when accessing the first element of a sequence.

23. Default Database in SQL Server: 

The default database in SQL Server refers to the initial database that is used when a user connects to the SQL Server instance. The default database can be specified for each user, and it determines the database where the user's queries and operations are performed by default.

In SQL Server, the default databases that are commonly available include:

1.  master: The master database stores system-level information and configuration settings. It records information about all other databases and is crucial for the functioning of the SQL Server instance.
2. tempdb: The tempdb database is used to store temporary objects such as temporary tables, global temporary tables, and temporary stored procedures. It is recreated every time SQL Server starts.
3. model: The model database serves as a template for creating new databases. When a new database is created, it is initialized with the contents of the model database.
4.  msdb: The msdb database stores information related to SQL Server Agent, including jobs, alerts, operators, and backup and restore history. It is used for managing scheduling, maintenance plans, and other administrative tasks.

These are the default databases commonly found in SQL Server installations. However, it's worth noting that additional user-defined databases can be created as per specific requirements.

24. Primary Key in SQL: 

In SQL, a primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures the uniqueness and integrity of the data in the table. The primary key constraint enforces the uniqueness and non-nullability of the specified column(s).

25. Difference between Primary Key and Foreign Key:

Primary Key: A primary key is a column or a set of columns in a table that uniquely identifies each row. It ensures the uniqueness and integrity of the data within the table.

Foreign Key: A foreign key is a column or a set of columns in a table that establishes a relationship with the primary key of another table. It creates referential integrity between two tables and enforces data consistency and integrity across the tables.

26. Use of Null Value in Primary Key: 

In SQL, a primary key column cannot contain a null value. It must have a unique and non-null value for each row, as it is used to identify each record uniquely.

Top 50 interview questions and answers SQL Server Primary keys, Unique keys, Foreign keys, and Composite keys:

What is a primary key in SQL Server?

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures the uniqueness and integrity of the data.

What is the purpose of a primary key?

The primary key serves as a unique identifier for each row in a table. It enforces data integrity and provides a way to uniquely identify and reference a specific row.

How do you define a primary key in SQL Server?

A primary key can be defined when creating a table using the "PRIMARY KEY" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.

Can a table have multiple primary keys?

No, a table can have only one primary key. However, a primary key can consist of multiple columns (composite key).

What is a unique key in SQL Server?

A unique key is similar to a primary key in that it ensures the uniqueness of data. However, unlike a primary key, a unique key allows NULL values.

What is the difference between a primary key and a unique key?

A primary key is used to uniquely identify each row in a table and does not allow NULL values, while a unique key also ensures uniqueness but allows NULL values.

How do you define a unique key in SQL Server?

A unique key can be defined when creating a table using the "UNIQUE" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.

Can a table have multiple unique keys?

Yes, a table can have multiple unique keys. Each unique key will enforce uniqueness independently.

What is a foreign key in SQL Server?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables.

What is the purpose of a foreign key?

A foreign key is used to enforce referential integrity between related tables. It ensures that the values in the foreign key column(s) match the values in the referenced primary key column(s).

How do you define a foreign key in SQL Server?

A foreign key can be defined when creating a table using the "FOREIGN KEY" constraint, or it can be added to an existing table using the "ALTER TABLE" statement.

Can a table have multiple foreign keys?

Yes, a table can have multiple foreign keys. Each foreign key establishes a separate relationship with a different table.

What are the types of relationships that can be established using foreign keys?

The types of relationships are: one-to-one, one-to-many, and many-to-many.

What is a composite key?

A composite key is a primary key or a unique key that consists of more than one column. It provides a way to uniquely identify a row using a combination of multiple columns.

Why would you use a composite key?

A composite key is used when a single column is not sufficient to uniquely identify a row. It allows for more precise and specific identification of rows.

Can a table have multiple composite keys?

No, a table can have only one primary key or unique key, which can be a composite key. However, multiple composite keys are not allowed.

What is the difference between a primary key and a composite key?

A primary key is a unique identifier for a row in a table and can be a single column or a combination of columns. A composite key, on the other hand, is specifically a key that consists of multiple columns.

How do you create a composite key in SQL Server?

To create a composite key, you need to specify multiple columns when defining the primary key or unique key constraint.

What is the purpose of an index in SQL Server?

An index is a database structure that improves the speed of data retrieval operations on a table. It allows for faster searching, sorting, and filtering of data.

Can you create an index on a primary key or unique key?

Yes, a primary key or unique key automatically creates a clustered index by default in SQL Server. However, you can also create additional non-clustered indexes on these keys for further optimization.

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in a table and is typically created on the primary key. A non-clustered index is a separate structure that points to the data in the table and can be created on any column(s) in a table.

What is a surrogate key?

A surrogate key is an artificial primary key assigned to a row in a table, typically as an auto-incrementing integer value. It is used when there is no suitable natural key available.

What is a natural key?

A natural key is a primary key that is derived from the data itself, such as a person's social security number or a product's barcode. It is based on real-world attributes or business meaning.

What is the difference between a primary key and a surrogate key?

A primary key is based on real-world attributes or business meaning and can be either a natural key or a composite key. A surrogate key, on the other hand, is an artificial key generated solely for the purpose of identifying a row in a table.

What is referential integrity?

Referential integrity is a database concept that ensures that relationships between tables are consistent and valid. It guarantees that foreign key values always correspond to the primary key values they reference.

What happens when a foreign key constraint is violated?

If a foreign key constraint is violated, such as when a foreign key value does not exist in the referenced primary key column, the database engine will reject the operation and raise an error.

Can you have a foreign key that references multiple tables?

No, a foreign key can only reference a single table. However, you can establish multiple foreign keys in a table to reference different tables.

What is the "CASCADE" option in a foreign key constraint?

The "CASCADE" option allows for automatic cascading of changes or deletions to related records. For example, if a row in the referenced table is deleted, all related rows in the referencing table will also be deleted.

What is the "ON DELETE SET NULL" option in a foreign key constraint?

The "ON DELETE SET NULL" option sets the foreign key value to NULL when the referenced row is deleted. This is useful when you want to allow orphaned records in the referencing table.

What is the "ON UPDATE CASCADE" option in a foreign key constraint?

The "ON UPDATE CASCADE" option ensures that any updates to the primary key values in the referenced table are automatically propagated to the foreign key values in the referencing table.

What is the difference between "DELETE" and "TRUNCATE" in SQL Server?

"DELETE" is a DML (Data Manipulation Language) statement that removes specific rows from a table, while "TRUNCATE" is a DDL (Data Definition Language) statement that removes all rows from a table and resets identity columns.

Can you have a primary key or unique key with duplicate values?

No, a primary key and a unique key must have unique values. Duplicate values are not allowed.

Can you have a foreign key with NULL values?

Yes, a foreign key can have NULL values unless it is explicitly defined as "NOT NULL" when creating the table.

What is the purpose of the "IDENTITY" property in SQL Server?

The "IDENTITY" property is used to automatically generate a unique value for a column, typically used for surrogate keys. It is often combined with the primary key column.

What is the purpose of the "SCOPE_IDENTITY()" function in SQL Server?

The "SCOPE_IDENTITY()" function returns the last identity value generated in the current scope. It is commonly used to retrieve the value of an identity column after an insert operation.

Can a primary key or unique key be a foreign key in another table?

Yes, a primary key or unique key can be referenced by a foreign key in another table. This establishes a relationship between the two tables.

What is the purpose of the "CHECK" constraint in SQL Server?

The "CHECK" constraint is used to enforce domain integrity by defining a condition that must be met for the data in a column.

Can you create a primary key or unique key on a nullable column?

Yes, a primary key or unique key can include nullable columns. However, only one NULL value is allowed in a unique key column.

What is the purpose of the "NOT NULL" constraint in SQL Server?

The "NOT NULL" constraint is used to ensure that a column does not contain NULL values. It enforces data integrity by requiring a value to be present.

Can you have duplicate values in a clustered index?

No, a clustered index determines the physical order of data in a table and enforces uniqueness. Duplicate values are not allowed.

What is the purpose of the "WITH NOCHECK" option when creating a foreign key constraint?

The "WITH NOCHECK" option allows you to create a foreign key constraint without checking the existing data for validity. It is useful when creating relationships on existing data that might violate the constraint.

What is the purpose of the "WITH CHECK" option when altering a foreign key constraint?

The "WITH CHECK" option ensures that the existing data in the referencing table is checked for validity against the foreign key constraint. It is useful when you want to verify the integrity of the data.

What is the purpose of the "DISABLE TRIGGER" statement in SQL Server?

The "DISABLE TRIGGER" statement is used to temporarily disable one or more triggers on a table. This can be useful when you need to perform data modifications without triggering the associated triggers.

What is the purpose of the "ENABLE TRIGGER" statement in SQL Server?

The "ENABLE TRIGGER" statement is used to re-enable one or more triggers on a table after they have been disabled. This allows the triggers to be activated again.

Can you drop a primary key or unique key constraint in SQL Server?

Yes, you can drop a primary key or unique key constraint using the "ALTER TABLE" statement with the "DROP CONSTRAINT" clause.

Can you drop a foreign key constraint in SQL Server?

Yes, you can drop a foreign key constraint using the "ALTER TABLE" statement with the "DROP CONSTRAINT" clause.

Can you create a primary key or unique key constraint on an existing table in SQL Server?

Yes, you can add a primary key or unique key constraint to an existing table using the

 "ALTER TABLE" statement with the "ADD CONSTRAINT" clause.

Can you create a foreign key constraint on an existing table in SQL Server?

Yes, you can add a foreign key constraint to an existing table using the "ALTER TABLE" statement with the "ADD CONSTRAINT" clause.

What is the purpose of the "CASCADE CONSTRAINTS" option when dropping a table in SQL Server?

The "CASCADE CONSTRAINTS" option automatically drops all the foreign key constraints that reference the table being dropped. It ensures that the dependencies are handled automatically.

What is the purpose of the "ALTER TABLE ... NOCHECK CONSTRAINT" statement in SQL Server?

The "ALTER TABLE ... NOCHECK CONSTRAINT" statement temporarily disables all the foreign key constraints on a table. This allows you to perform data modifications without enforcing the integrity constraints.

Interview questions: Strings VS StringBuilders, value types, reference types, stack, and heap memory in C#

What is the primary difference between string and  StringBuilder in C#?

The primary difference between `string` and `StringBuilder` is how they handle string manipulation. A `string` in C# is immutable, which means that every time you modify a string, a new string object is created in memory. `StringBuilder`, on the other hand, is mutable and designed specifically for efficient string manipulation without creating new objects.

When should you use  string  over `StringBuilder ?

You should use string when you have a relatively small number of string concatenations or modifications, and memory usage is not a critical concern. For simple operations, string is simpler to work with. 

When should you use StringBuilder over string ?

You should use `StringBuilder` when you need to perform a significant number of string manipulations, concatenations, or modifications. `StringBuilder` is more memory-efficient in such scenarios as it doesn't create new string objects for each modification, thus reducing memory overhead and improving performance.

How does immutability of `string` affect memory usage and performance?

Since `string` is immutable, any modification results in the creation of a new string object, leading to increased memory usage and potentially causing performance issues when dealing with a large number of string manipulations. This can also contribute to memory fragmentation.

Can you convert a `StringBuilder` to a `string`?

Yes, you can convert a `StringBuilder` to a `string` using the `ToString()` method of the `StringBuilder` class. This method returns the current content of the `StringBuilder` as a string.

Can strings be modified in C#?

No, strings are immutable in C#. Once created, their value cannot be changed. However, you can use a StringBuilder to efficiently modify strings if needed.

What is the difference between value types and reference types in C#?

Value types are stored on the stack, while reference types are stored on the heap. Value types store the actual value, whereas reference types store a reference to the value. Value types include simple types like int, float, etc., and structs. Reference types include classes, interfaces, delegates, and strings.

What is stack memory and heap memory in C#?

In C#, memory is managed through two primary areas: the stack and the heap. These two areas serve different purposes and have distinct characteristics.

Stack:

• Usage: The stack is primarily used for storing local variables and managing function call information, including return addresses and parameters.
• Memory Management: Memory allocation and deallocation on the stack are automatic and fast. When a function is called, a new stack frame is created for that function's local variables and parameters. When the function exits, its stack frame is automatically removed, freeing up the memory.
• Data Type: Value types are stored on the stack. Value types directly contain their values and are usually smaller in size.
• Access Time: Accessing memory on the stack is faster compared to the heap, as it involves simple pointer manipulation.
• Lifetime: The lifetime of data on the stack is limited to the scope of the function or block in which it is defined. When the function or block ends, the memory is automatically deallocated.

Heap:

1. Usage: The heap is used for dynamic memory allocation, primarily for objects whose size cannot be determined at compile time or for reference types.
2. Memory Management: Memory allocation and deallocation on the heap are manual. Developers need to manage memory using techniques like garbage collection (automatic memory management) or explicitly allocating and deallocating memory.
3. Data Type: Reference types (objects, arrays, etc.) are stored on the heap. Reference types store a reference (a pointer) to the memory location of the actual data.
4. Access Time: Accessing memory on the heap involves an additional level of indirection due to the reference. This makes heap memory access slightly slower compared to the stack.
5. Lifetime: The lifetime of data on the heap is longer and can extend beyond the scope of the function or block where it was created. It relies on memory management mechanisms to determine when memory should be released (usually through garbage collection).

How is memory allocated and deallocated for value types and reference types in C#?

Value types are usually allocated on the stack and deallocated automatically when they go out of scope. Reference types, however, are allocated on the heap, and memory for them is managed by the garbage collector. The garbage collector automatically determines when objects are no longer needed and frees up their memory.

String vs StringBuilder:

string str = "Hello";

str += " World"; // This creates a new string object

StringBuilder sb = new StringBuilder();

sb.Append("Hello");

sb.Append(" World"); // This modifies the underlying character sequence

Value Types vs Reference Types:

// Value type (stored on the stack)

int value1 = 10;

int value2 = value1;

// Reference type (stored on the heap)

StringBuilder sb1 = new StringBuilder("Hello");

StringBuilder sb2 = sb1;

sb2.Append(" World"); // Modifying sb2 also modifies sb1

Stack Memory and Heap Memory:

// Stack memory

int number = 5;

string name = "John";

// Heap memory

StringBuilder sb = new StringBuilder("Hello");

Top interview questions on FOR LOOP with example programs in C#

What is a for loop in C#?

A for loop is a control flow statement in C# that allows you to repeatedly execute a block of code based on a specified condition.

What is the syntax of a for loop in C#?

The syntax of a for loop in C# is as follows:

for (initialization; condition; iteration)

{

    // code to be executed

}

What is the purpose of the initialization expression in a for loop?

The initialization expression is used to initialize the loop counter or any other variables before the loop starts executing.

What is the purpose of the condition expression in a for loop?

The condition expression is evaluated before each iteration. If the condition evaluates to true, the loop continues executing. If it evaluates to false, the loop terminates.

What is the purpose of the iteration expression in a for loop?

The iteration expression is executed after each iteration. It typically updates the loop counter or performs any necessary modifications.

Can you have multiple initialization expressions in a for loop?

Yes, you can have multiple initialization expressions in a for loop. They should be separated by commas.

Can you have multiple condition expressions in a for loop?

No, you can only have a single condition expression in a for loop. However, you can use logical operators (e.g., &&, ||) to combine multiple conditions.

Can you have multiple iteration expressions in a for loop?

Yes, you can have multiple iteration expressions in a for loop. They should be separated by commas.

What happens if the condition expression in a for loop is initially false?

If the condition expression is false initially, the loop will not execute at all.

Can you omit the initialization, condition, or iteration expressions in a for loop?

Yes, you can omit any of the expressions in a for loop. However, at least one semicolon (;) is required to separate the expressions.

How can you create an infinite loop using a for loop?

You can create an infinite loop by omitting the condition expression or by using a condition that always evaluates to true.

How can you exit a for loop before it has finished executing?

You can use the break statement within the loop to exit it prematurely based on a certain condition.

Can you nest for loops in C#?

Yes, you can nest for loops in C#. This allows you to create loops within loops to perform complex iterations.

How can you skip the current iteration and continue to the next iteration in a for loop?

You can use the continue statement within the loop to skip the current iteration and proceed to the next iteration.

What is the difference between a while loop and a for loop?

The main difference is that a for loop provides a compact way to specify the initialization, condition, and iteration expressions within a single line, while a while loop requires explicit statements for these expressions.

Can the loop counter variable be modified inside the loop body in a for loop?

Yes, the loop counter variable can be modified inside the loop body. However, you should be cautious when modifying it, as it may affect the loop's behavior.

Write a program to display numbers from 1 to 10 using a for loop.

for (int i = 1; i <= 10; i++)

{

    Console.WriteLine(i);

}

Write a program to calculate the sum of all numbers from 1 to 100 using a for loop.

int sum = 0;

for (int i = 1; i <= 100; i++)

{

    sum += i;

}

Console.WriteLine("Sum: " + sum);

Write a program to print the factorial of a given number using a for loop.

Console.Write("Enter a number: ");

int number = Convert.ToInt32(Console.ReadLine());

int factorial = 1;

for (int i = 1; i <= number; i++)

{

    factorial *= i;

}

Console.WriteLine("Factorial: " + factorial);

Write a program to check if a given number is prime or not using a for loop.

Console.Write("Enter a number: ");

int number = Convert.ToInt32(Console.ReadLine());

bool isPrime = true;

for (int i = 2; i <= Math.Sqrt(number); i++)

{

    if (number % i == 0)

    {

        isPrime = false;

        break;

    }

}

 

if (isPrime)

{

    Console.WriteLine("Prime number");

}

else

{

    Console.WriteLine("Not a prime number");

}

Write a program to print a pattern of asterisks using a for loop.

for (int i = 1; i <= 5; i++)

{

    for (int j = 1; j <= i; j++)

    {

        Console.Write("*");

    }

    Console.WriteLine();

}

SQL Server View interview questions and answers

What is a View in SQL Server?

A View is a virtual table that is based on the result of a query. It does not store any data on its own but rather displays the data from one or more tables. Views can be used to simplify complex queries, provide a level of abstraction, restrict access to certain columns or rows, and enhance security.

What are the advantages of using Views?

There are several advantages of using Views:

• Simplify complex queries by encapsulating them into a reusable object.
• Provide a level of abstraction, allowing users to work with a subset of data without exposing the underlying table structure.
• Enhance security by restricting access to specific columns or rows.
• Improve performance by pre-computing complex joins or aggregations.

How do you create a View in SQL Server?

 To create a View, you can use the following syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

You specify the columns you want to include and define the query that retrieves the data. Optionally, you can also add a WHERE clause to filter the rows.

Can you update or insert data into a View?

Yes and no. It depends on the type of View. In SQL Server, you can update or insert data into a View if the following conditions are met:

• The View is based on a single table (not a join or complex query).
• The View contains all the NOT NULL columns from the underlying table.
• The View does not have any DISTINCT, GROUP BY, or HAVING clauses.

How can you modify an existing View?

You can modify a View in SQL Server using the ALTER VIEW statement. Here's the syntax:

ALTER VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

You specify the new query or changes you want to make to the existing View.

How do you drop a View in SQL Server?

To drop a View, you can use the following statement:

DROP VIEW view_name;

This removes the View and its definition from the database.

Can you create an index on a View?

Yes, you can create an index on a View in SQL Server. It's called an Indexed View or Materialized View. However, there are certain requirements that must be met, such as the View must have a unique clustered index, the underlying tables must have certain characteristics, and the View must be schema-bound.

How can you check the definition of a View in SQL Server?

You can use the following system catalog views to retrieve the definition of a View:

SELECT VIEW_DEFINITION

FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME = 'view_name';

This query will return the definition of the specified View.

These are some common interview questions related to SQL Server Views. It's important to note that the specific questions asked may vary, and it's always a good idea to study and prepare based on the job requirements and the level of expertise expected.

SQL Server Identity Interview questions ad answers

What is an Identity column in SQL Server?

An Identity column is a column in a SQL Server table that automatically generates a unique numeric value for each new row inserted into the table. It is often used as a primary key for the table.

How do you define an Identity column in SQL Server?

To define an Identity column in SQL Server, you can use the IDENTITY property. Here's an example:

CREATE TABLE TableName

(

   ID INT IDENTITY(1,1) PRIMARY KEY,

   Column1 datatype,

   Column2 datatype,

   ...

)

The IDENTITY(1,1) indicates that the column will start at 1 and increment by 1 for each new row.

Can you change the value of an Identity column after it has been inserted?

No, the value of an Identity column cannot be changed once it has been inserted. It is automatically generated and managed by the SQL Server engine.

How can you insert a new row into a table with an Identity column?

When inserting a new row into a table with an Identity column, you should not specify a value for the Identity column. SQL Server will automatically generate the value for you. Here's an example:

INSERT INTO TableName (Column1, Column2, ...)

VALUES (Value1, Value2, ...)

The Identity column will be populated automatically.

How can you retrieve the most recently generated Identity value?

After inserting a row into a table with an Identity column, you can use the `SCOPE_IDENTITY()` function to retrieve the most recently generated Identity value. Here's an example:

INSERT INTO TableName (Column1, Column2, ...)

VALUES (Value1, Value2, ...)

SELECT SCOPE_IDENTITY()

This will return the Identity value of the inserted row.

Can you have multiple Identity columns in a single table?

No, a SQL Server table can have only one Identity column. The Identity column is used to generate a unique identifier for each row in the table.

How can you reset the Identity column to a specific value?

To reset the Identity column to a specific value, you can use the `DBCC CHECKIDENT` command. Here's an example:

DBCC CHECKIDENT ('TableName', RESEED, new_value)

This command resets the Identity column to the specified `new_value` and reseeds the column's identity value.

Can you disable the Identity property temporarily for a table?

Yes, you can temporarily disable the Identity property for a table using the `SET IDENTITY_INSERT` command. Here's an example:

SET IDENTITY_INSERT TableName ON

-- Perform the insert or update operations here

SET IDENTITY_INSERT TableName OFF

This allows you to explicitly insert or update values in the Identity column for the specified table.

Certainly! Here are some more SQL Server Identity-related interview questions and answers:

Can you have an Identity column on a table that is part of a replication setup?

Yes, you can have an Identity column on a table that is part of a replication setup. SQL Server replication supports tables with Identity columns, and the replication process handles the synchronization of Identity values across the replicated instances.

How can you check if a column is an Identity column in SQL Server?

You can query the `sys.columns` system catalog view to check if a column is an Identity column. The `is_identity` column of the view indicates whether a column has the Identity property. Here's an example:

SELECT COLUMN_NAME

FROM sys.columns

WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'

AND is_identity = 1;

This query will return the Identity column(s) of the specified table.

What is the maximum value that an Identity column can reach in SQL Server?

The maximum value that an Identity column can reach in SQL Server depends on the data type of the column. For example, an `INT` Identity column can have a maximum value of 2,147,483,647, while a `BIGINT` Identity column can have a maximum value of 9,223,372,036,854,775,807. If the Identity column reaches the maximum value, an error will be thrown when trying to insert a new row.

How can you reseed an Identity column to its maximum value?

To reseed an Identity column to its maximum value, you can use the `DBCC CHECKIDENT` command with the `RESEED` option and specify the maximum value. Here's an example:

DBCC CHECKIDENT ('TableName', RESEED, maximum_value)

By setting the `maximum_value` as the new value for the Identity column, the next inserted row will use that value.

What happens when you delete all rows from a table with an Identity column?

When you delete all rows from a table with an Identity column, the Identity column's value will not be reset automatically. If you want to reset the Identity column, you can use the `DBCC CHECKIDENT` command with the `RESEED` option and specify a new value.

Can you change the increment value for an Identity column?

No, the increment value for an Identity column cannot be changed once it has been defined. The increment value is set at the time of table creation and remains constant.

Can you change the data type of an Identity column?

No, you cannot change the data type of an existing Identity column. If you need to change the data type, you would need to drop and recreate the column with the desired data type.

Can you have negative values in an Identity column?

No, by default, Identity columns in SQL Server cannot have negative values. The values generated by the Identity column are always positive. However, you can define a seed value that is negative, and the generated values will be negative accordingly.

Can you disable or enable the Identity property for an existing column?

No, you cannot disable or enable the Identity property for an existing column. Once the Identity property is set for a column, it remains enabled and cannot be changed. If you need to disable the Identity property, you would need to recreate the table or column without the Identity property.

How can you find the last Identity value inserted into a table?

You can use the `@@IDENTITY` system function or the `IDENT_CURRENT('TableName')` function to retrieve the last Identity value inserted into a table. Here's an example using `IDENT_CURRENT`:

SELECT IDENT_CURRENT('TableName')

This will return the last Identity value inserted into the specified table.

What is the purpose of the IDENTITY_INSERT property in SQL Server?

The `IDENTITY_INSERT` property in SQL Server allows you to explicitly insert values into an Identity column for a specified table. By default, you cannot insert values into an Identity column. However, if you enable the `IDENTITY_INSERT` property for a table, you can perform explicit inserts into the Identity column.

Can you have an Identity column with a non-numeric data type?

No, an Identity column in SQL Server must have a numeric data type. The Identity property is designed to generate numeric values automatically. If you need to generate unique values for a non-numeric column, you can use other techniques like using a UNIQUEIDENTIFIER (GUID) column or a sequence.

Can you alter an Identity column to change the seed value?

No, you cannot directly alter an Identity column to change the seed value. To change the seed value, you would need to create a new table with the desired seed value and then insert the data from the old table into the new table.

Can you have an Identity column with a negative increment value?

No, the increment value for an Identity column must be a positive number or 1. It determines how the Identity values are incremented for each new row. Negative increment values are not supported.

Can you have an Identity column in a temporary table?

Yes, you can have an Identity column in a temporary table in SQL Server. Temporary tables behave similar to regular tables, and you can define an Identity column within them.

Can you have a composite primary key with an Identity column?

Yes, you can have a composite primary key with an Identity column in SQL Server. The Identity column can be part of a composite primary key by including it along with other columns in the primary key definition.